June 13, 2010 at 8:43 pm
SQL server 2005 with Windows Server 2003 R2
During installation of SQL Server 2005, sa login was created, sa id is not used by any one; however sa is the dbo of master, msdb, model & tempdb.
Scripting of the sa id shows:
CREATE LOGIN [sa] WITH PASSWORD=N'...', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
EXEC sys.sp_addsrvrolemember @loginame = N'sa', @rolename = N'sysadmin'
GO
All ID's are windows authenticated, except for this sa ID.
Windows password change policy was never implemented for years, now they require it, and the policy kicked in couple of weeks ago.
SQL Server services run under local system, with no password expiration.
Is it necessary for me to change the sa password?
What sort of problems are expected if I do not change or do change.
SQL Server crashes some times when a stored procedure is run which uses Oracle linked server, at other times same procedures run fine under the same conditions!! IE, when they are executed via SQL Server Management studio.
I did change the windows ID that is in the linked server that gets mapped to Oracle ID (Oracle ID does not conform to password policy, it is on unix server anyway). Do I have to do anything with the linked server which is already there, because I changed the password on the windows ID.
Unable to explain these crashes.
Thanks in advance.
June 16, 2010 at 8:26 am
Please run and post...
exec sp_helplinkedsrvlogin;
go
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.July 1, 2010 at 11:54 am
I did that, it looked fine.
It appears to be fixed now, still don't know exactly what caused the problem, and how it really got fixed. Here are the three things that were done, after that it looks good of so far.
1) IDs had a weak password (windows authenticated), changed to suit to conform to new password rules. couple of IDs were sysadmins.
2) Applied windows 2003 server patches. Before the patches, was getting several errors, mostly related to security and some related to linked server. After applying patches, narrowed down to couple of error messages.
3) I had to go back to the stored procedures that were causing the crash, and rewrite every line in a new file (i.e. I typed every word in a new file), did not copy and paste, started fresh.
I had this problem before somehow invisible characters were getting embedded into the code, in the past also it crashed SQL Server, but consistently in the same place with same error, this time it was crashing randomly with different types of messages.
So far it looks good. Thanks for your help.
July 4, 2010 at 4:32 pm
Glad to help.
On the other hand, nothing is more scary that knowing neither the root cause of a problem nor why the problem went away 🙂
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply