SQL Server – death by changing sa password

Last week a client wanted to change the password for sa account in SQL Servver, and thats a good idea most of the time. But this time the password was changed, and boom, the shit hits the fan. The error log was filled with login failed for sa and some applications went down. Okey, there are some applications using the sa account. Not a good idea, but the worst part is that they couldn’t change the password for the application. Lets change back to the previous password. Oh no, it’s not that simple, the previous password was lost. That was the reason for the password change.

The login SA should never be used by any user or application as it has sysadmin rights. If you are using the SA login for sysadmin tasks, you probably need to share the login witth your collegues and in the long run you will have made the password available to a large number of users even if they shouldn’t have sysadmin rights. The best approach is to create a domain group in Active Directory and then use that account to create a login and then assign sysadmin rights to the login. With this approach, you only have to add users that should have sysadmin rights in the group account. If you need to remove the sysadmin rights, you just remove the user from the group.

But if you have been sharing the SA login to many users, it’s much harder to “remove” the sysadmin rights from someone. You need to change the password and distribute the new password to thoose who should have access.

If you do change the password of SA, make sure that it’s not used by any application, otherwise it will fail to login. and you need to “revert” to previous password. And YES, there are some applications that you can’t change the login user or password for and if you don’t know the previous sa password you are in trouble when you need to reveert,

Before you change the password you could use the sp_help_revlogin procedure, mentioned in http://support.microsoft.com/kb/918992 , with the sa username as a parameter to get a create login script with the password hash. The script can easily be chaned to an alter login to create the script needed for the password revert, without knowing the sa password.

by Håkan Winther

Twitter: @h_winther