I'm using a program that requires SA log-in to SQL Server. We had issues where some 3rd party support numpty changed the SA password and never informed us, so the program couldn't log-in.
I've accessed our information via the SQL Server Management Studio. Fortunately, the 'remember password' option had been checked on the machine.
Via the object explorer, I've accessed the SA log-in credentials and changed the password accordingly. However, every time I re-load the SQL Server Management Studio, the password re-sets to the original one.
When I test the connection of the program that uses the SA log-in by manually entering the new password it appears to work OK. But when I let the program start-up automatically, Windows services is detailing that the log-in is failing due to incorrect log-in details – which I'm guessing is because SQL Server keeps reverting back to the old password?
Can anyone advise how I stop it from reverting back to the old password and overwrite with the new password definitively? (SQL Server 2008 R2 Express) I'm hoping that should help fix my more specific problem with the other program?
Best Answer
As stated in the comments to your question SQL Server will not
reverta password back to the previous one used. I believe the problem you are having is that your application has thesapassword stored somewhere that it needs to be saved to. That is likely why you are receiving the error regarding login credentials. I would suggest checking the documentation for your application (if it is COTS) to see where the password for the SQL Login is set at.If the application is a Windows Service you will likely find the password set in some configuration file or registry key. I would hope not in plain text, but no product is perfect. If you find the password is not in plain text (garbled or unreadable) this would indicate that the application has some configuration API (GUI or command-line/CLI) that needs to be used to set that password so the application stores it where it needs to see it.