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?

  • 2
    I think you may be confusing SQL Management Studio (SSMS) with some kind of app that uses SQL Server or do you mean SSMS is starting automatically on this box? If yes, why? – Joel Mansford Nov 12 '12 at 17:55
  • Create a new login, give it necessary permissions and reconfigure your application to use this new login instead of sa. – user15311 Nov 13 '12 at 19:25
  • This is a good advice. However, it doesn't really answer the question. – dezso Nov 13 '12 at 19:27
  • @Jamie: SQL Server or Management Studio (SSMS), by itself, doesn't revert any password. Anything you do, remains done, as long as some script/other user doesn't change that. So open a Profiler and see what credentials your application is using to connect to the SQL Server. Then you'll be able to see further whom to give permissions to. – Marian Nov 13 '12 at 21:12

As stated in the comments to your question SQL Server will not revert a password back to the previous one used. I believe the problem you are having is that your application has the sa password 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.