Temporarily Changing an Unknown Password of the sa Account

You are asked for the sa password for a SQL Server in order to perform a software upgrade. You, the DBA, don't know the password and it's not documented. Rodney Landrum provides a way out of this dilemma, demonstrating two techniques for temporarily changing the password, and then returning it to its previous unknown value.

I have been working with SQL Server since version 6.5. In the early days, SQL Authentication was a given and I understood most of its nuances. Eventually, SQL Server 7.0 and 2000 surfaced along with Active Directory and the promise of Windows Authentication, which was a godsend. Very soon, SQL Authentication began to feel like a relic, a fossil left over for the SQL archeologists clinging to history, decrying backward compatibility as essential to forward momentum. So, I started to install every instance of SQL Server in Mixed Mode Authentication, never really wading from the shallow end to the 6 foot depths of SQL security potential.

I’m probably like many other DBAs in this respect. And, likewise, the following scenario is likely to visit most DBAs at some point in the travels and travails of their career: an IT manager stands at your cubicle waiting for you to acknowledge his presence. You turn slowly away from a half-baked stored procedure or from setting a background image on your desktop, and see the concern on his face.

“Do you know the ‘sa’ password for Server_57? We are going to need it for an upgrade to our third-party Help Desk management software.”

The logical flow of the conversation usually goes something like this:

  1. You are the DBA. You should know the sa password
  2. I use Windows authentication on 97% of the SQL Servers I manage
  3. I manage 60 SQL Servers
  4. All SQL Servers are in Mixed mode authentication
  5. I have installed 20 of the 60 servers
  6. I know the sa password on all of the 20 servers I installed
  7. Other DBAs, no longer with the company installed the other 40 servers
  8. The other DBAs did not fully document the sa password on all instances.
  9. I know the sa password on 30 of the remaining 40 servers
  10. Server_57 is not in the 30 remaining servers I know the sa password to
  11. No one remaining in IT knows the sa password on Server_57
  12. Server_57 is a production SQL Server
  13. Server_57 has jobs that may fail if the sa password is changed to a known value to perform the upgrade request
  14. The upgrade that is requested is high priority and must be accomplished ASAP

So, no, you do not know the ‘sa’ password, but…you do know that the ‘sa’ password will only be needed for the upgrade, not for ongoing connectivity, so you proffer a solution, “I will change the ‘sa’ password for you for long enough to complete the upgrade, and then I will need to change it back again. This is because I do not know what processes may be trying to use ‘sa'”.

It is permissible, at this point, to wince at the dreadful thought that there might be a process tied to the ‘sa’ account. It might seem preferable to change the ‘sa’ account password permanently. Thoughtful DBAs would, if they did this, inform job owners of the change ahead of time. Thoughtful job owners should, by right, not be using ‘sa’ to begin with.

The sp_help_revlogin stored procedure

How does one go about changing a password back to what it was if one does not know what it was initially? It is possible to do this in both SQL Server 2000 and SQL Server 2005. The steps are quite different, but both rely on a very handy stored procedure provided by Microsoft. The stored procedure, sp_help_revlogin, was designed to migrate logins from one instance of SQL Server to another, in order to prevent orphaned uses in restored databases. I will not elaborate on the orphaned user issue here, but I will state that it relates to the Security ID or SID created with each user that is specific to an SQL Server instance. What is important is that sp_help_revlogin can be used to generate a current hashed password for each SQL Server authenticated login, which can then be used for reverting to the original password should something goes awry after changing to a known password.

The code to create sp_help_revlogin as well as sp_hexadecimal. on which sp_help_revlogin relies, can be found at: http://support.microsoft.com/kb/246133.

Using sp_help_revlogin on SQL Server 2000

Executing sp_help_revlogin on a source SQL Server produces a script that, when run on a target SQL Server, creates identical logins, including SIDs and passwords. Sample output of sp_help_revlogin can be seen in Listing 1.

Listing 1:

/* sp_help_revlogin script 
** Generated Jun 28 2007 10:07PM on UAFCQASRV103 */
DECLARE @pwd sysname
  -- Login: BUILTIN\Administrators
EXEC master..sp_grantlogin 'BUILTIN\Administrators'
  -- Login: sa_test
  SET @pwd CONVERT (varbinary(256), 0x01005B20054332752E1BC2E7C5DF0F9
EXEC master..sp_addlogin 'sa_test'@pwd@sid 0x9BDCD87E84673D46B7FE
@encryptopt 'skip_encryption'

Notice the differences between a SQL login account sa_test and a Windows group or user, BUILTIN\Administrators. The sa_test account, which I am using here as a precursor to changing the actual ‘sa’ account, has a password. This hashed password in the script is converted to varbinary and then used by sp_addlogin to create the login.

In the sp_addlogin procedure, there is an option to bypass encryption called ‘skip encryption’. You can review the code of sp_addlogin, or other system stored procedures, by executing “sp_helptext sp_addlogin” in Query Analyzer. When the “skip encryption” option is added to the sp_addlogin stored procedure an important function is bypassed, which is pwdencrypt().

We will forego uncovering the technical mysteries of pwdencrypt(). It is enough to point out that this function is called as an option in sp_addlogin.

However, changing the password for an existing login involves use of another system stored procedure, sp_password. With sp_password, it is not possible by default to bypass the very same function. This makes it problematic to use sp_password to change a password using a hashed value, because the hashed value will be doubly encrypted and not retain its original value. In the case of our sa_test account, the value of 12345, or 0x01005B20054332752E1BC2E7C5DF0F9EBFE486E9BEE063E8D3B332752E1BC2E7C5DF0F9EBFE486
E9BEE063E8D3B3, would itself be obfuscated by pwdencrypt(). So, we have to bypass this using the same approach as sp_addlogin but with slight modification.

Changing the Password

  1. Create a login called sa_test in SQL Server 2000 using Enterprise Manager or sp_addlogin
  2. Set the password to “12345”
  3. Create the sp_help_revlogin from http://support.microsoft.com/kb/246133
  4. Execute sp_help_revlogin in Query Analyzer and save the output so we have the hashed password value for “12345”
  5. Change the password of sa_test to “54321” via Enterprise Manager or using:
    sp_password: sp_password @new=’54321′,@loginame=’sa_test’

Now, forget you ever knew the password was “12345”. All you know is that the original password was:


Changing the password back

Next is the fun. There are a couple of ways to set the password back to the forgotten/unknown password. The first is to update the sysxlogins table directly, which, of course, is ill-advised. If it is possible to do so, and there is even a special configuration to allow such practices, it can be used as a last resort.

The second technique I will demonstrate involves use of the sp_password stored procedure, but with a slight modification.

Allowing direct updates on sysxlogins

The option I am referring to is Allow Updates. You can see this option by executing sp_configure, see Figure 1.


If this option is off, indicated by a run value of 0, you can not directly update system tables via an ad-hoc query, like the following:

UPDATE master..sysxlogins SET
      password 0x01005B20054332752E1BC2E7C5DF0F9EBFE486E9BEE063E8D3B332752

  WHERE name 'sa_test'

If you execute an update statement that updates the sysxlogins table directly, using a hashed password, you will get the following error:

Server: Msg 259, Level 16, State 2, Line 1

Ad hoc updates to system catalogs are not enabled. The system
administrator must reconfigure SQL Server to allow this.

However, having set the Allow Updates option to 1, or True, the same query will work successfully, as shown in Figure 2.


Now that we can update system tables directly, it is possible to issue the same update statement to set the password back to “12345” at the appropriate time, see Figure 3.


To test the connectivity with the former password, “12345”, use Enterprise Manager or SQL Server Management Studio to connect to the instance as sa_test.

Because this type of request is very rare, there is nothing more to do than set the Allow Updates back to 0 for security.

Using a modified sp_password procedure

Being inclined to go off-road for the hell of it, I wanted to show how this same task can be accomplished with a slight alteration to the sp_password stored procedure. As discussed earlier, it is not possible, unlike for sp_addlogin, to bypass the pwdencrypt() function. However, you can take the base sp_passsword stored procedure and alter the section that encrypts the passed in password. In all fairness, sp_password was designed to use legitimate alpha-numeric and character values similar to:

sp_password @old='12345'@new='54321',@loginame='sa_test'

Fortunately for our purposes, the variable @old for sp_password is not required. The section of code in sp_password that controls the updating of the new password is in Listing 2. This section is not optional. The pwdencrypt() will always be called:

Listing 2:

UPDATE master.dbo.sysxlogins
  SET password CONVERT(varbinary(256),
xdate2 GETDATE(),
xstatus xstatus & (~2048)
      where name = @loginame and srvid IS NULL

The modification to the “Change the Password” section of sp_password, which will reside in a new stored procedure called sp_password_omg, is in Listing 3.

Listing 3:

UPDATE master.dbo.sysxlogins
  SET password CONVERT(varbinary(256),@new)
  WHERE name @loginame 
    AND srvid IS NULL

All that we are doing is removing the requirement for the pwdencrypt() function. The conversion to varbinary is fine since that is the defined length of the column in sysxlogins. The added benefit of creating the sp_password_omg stored procedure is that it circumvents the need to reconfigure the Allow Updates option for ad-hoc queries.

The statement to execute sp_password_omg is:

sp_password_omg  @new=0x01005B20054332752E1BC2E7C5DF0F9EBFE486E9BEE063E

To this point, I have shown a way to change a SQL user’s password to a new value with the facility to revert back to an unknown value if required. This has worked well for our test user, sa_test, which was really important to do prior to moving onto the all-powerful ‘sa’. The same techniques work the same for ‘sa’ with one minor caveat: the sp_help_revlogin stored procedure automatically excludes ‘sa’ from the list of accounts to create logins for. Listing 3 shows the exclusion code from sp_help_revlogin.

Listing 3

IF (@login_name IS NULL)
SELECT sidnamexstatuspassword 
  FROM master..sysxlogins
  WHERE srvid IS NULL 
    AND name <> 'sa'

In order to allow the above steps to work with ‘sa’ as easy as we worked with “sa_test”, we simply comment out the exclusion, as shown in Listing 4.

Listing 4

IF (@login_name IS NULL)
SELECT sidnamexstatuspassword 
  FROM master..sysxlogins
  WHERE srvid IS NULL /* AND name <> 'sa' */

Using sp_help_revlogin on SQL Server 2005

So, now that we know how to temporarily change an unknown password of the SA account in SQL Server 2000, let’s do the same in SQL Server 2005. Fortunately, the procedure is almost identical, save for the sp_help_revlogin stored procedure. Yes, we will still need to add the same comment to omit the ‘sa’ account as we did in Listing 4. The slight variation of the driving SELECT statement can be seen in Listing 5.

Listing 5

      sys.server_principals p LEFT JOIN sys.syslogins l
      ON l.name p.name 
  WHERE p.type IN 'S''G''U' /*AND p.name <> 'sa'*/

The other difference between SQL Server 2000 and 2005 is the result output of sp_help_revlogin. In SQL Server 2000 an sp_addlogin statement was created. In SQL Server 2005, a CREATE LOGIN statement is created:

Login: sa
WITH PASSWORD 0x01004086CEB6301EEC0A99
                   4E49E30DA235880057410264030797 HASHED
SID 0x01



Notice the use of HASHED in the output. We will use this password value, not in sp_password or in a direct update statement, but in an ALTER LOGIN statement as follows:

0x01004086CEB6301EEC0A994E49E30DA235880057410264030797 HASHED

And that is it. The password is set back to the unknown one, without the need to alter any system stored procedures or change server configurations. At this point you can tell the IT Manager with the deadline, and the dire need for the ‘sa’ password, that he can go ahead with his updates.

Any additional directives for him to relay to the vendor the insanity of requiring use of the ‘sa’ account, are optional at this point. However, a strong admonition that he should let you know the moment his updates are complete is essential.

Tags: , , , , , , ,


  • Rate
    [Total: 0    Average: 0/5]
  • Anonymous

    Temporarily Changing an Unknown Password of the sa Account
    I don’t know how many times I’ve come across this problem and had no solution other than to break the other processes (which I couldn’t do of course) or laboriously track down the owners of the processes (which I did do). Luckily, I’ve only worked on smaller user groups (3000 or so).
    Thank you for this solution. I have more confidence now!!

  • J.Cruz

    This is terrific; we’re knee-deep in migrating our databases from SQL 2000 to SQL 2005 and there are definitely some “orphaned” databases out there that are, of course, mission-critical and cannot have downtime (and no one knows the password of the user account that is accessing the database).

    Awesome article!

  • Anonymous

    Keep for emergencies
    I’m storing this in the “really useful for future reference” category!!
    But my first question to the manager which would then get passed to the supplier would be “Does it really need the sa account or just an account with sa privileges??” I’ve often created a temporary sys admin account for “ngineers” to install software which is then run from user or less privileged accounts.

  • david.buckingham

    An essential addition to the DBA’s script library
    That is by far the most elegant solution I’ve seen yet to this occasional problem. Thank you kindly for the excellent article.

  • Anonymous

    Must 1 of the must have script for DBA’s
    Very very elegant solution

  • Anonymous

    sorry – but if someone comes along and saya i want your sa password i tell them where to go and stick it. why not create an account for them with the correct priveleges and never dish out the sa password… at least if you find your contractor or developer is doing something dodgy you can revoke his or her priveleges … with sa you cannot do that.

    great technically but you’re missing the point

  • Anonymous

    not necessarily flawed, and may come in handy
    You have have a compiled app hard coded to run as “sa”.

    thanks for sharing, Rodney.

  • Anonymous

    This works!
    We have done this technique several times.

    I do want to comment to “flawed”…it sounds as if they don’t work in an established environment. Unless you get the opportunity to design a system from the ground up, it is quite likely things will be done “incorrectly”, and telling someone to stick it will not be an answer. Remember, most IT people support businesses…not IT…and it is important to frame decisions in a business context. Spending 300 hours to remove hard coded sa passwords from some application may be a poor business option when an alternative requiring 20 minutes of a single DBA time exists. While not pretty, elegant, or as secure as we’d like, that is the real world. DBAs who try to maintain an ivory tower idealism without creating working business solutions are not useful for most IT shops.

    Respectfully, I think Mr. Landrum has hit the point completely…he is describing a very real situation, and one that would not be resolved by telling IT management to “stick it”.

  • TPowell_3557

    Right On
    I concur with “This Works”, telling them to “stick it” might be satisfying but it’s not realistic. You have to deal with what is, not what you’d like.

  • ShawnNWF

    Dear “flawed” DBA
    It’s a funny thing about jobs. Some people have them, and some people end up in the 14th street unemployment line after telling IT management to “stick it”.

    People skills, check ’em out.

  • Anonymous

    No Windows Authentication access
    Just yesterday I stuck with such task. Developer left leaving us without sa password and deleted all logins including BUILTINAdministrators group.
    Now I cannot login into the SQL Server despite I’m in Local Admin group. 🙁
    Does anyone has solution?

  • Roust_m

    The solution
    1. Stop SQL server
    2. Copy master database files to another server
    3. Attach the files as master2 database
    4. Create an SQL login with SA server role membership on the other server
    5. Enable ‘allow updates’
    6. insert a record with the new login from master database into master2 database
    7. Detach master2 database from the other server
    8. Copy it back to the original server
    9. Start the original server
    10. Use the newly created login with SA priviliges

  • Roust_m

    6. insert a record with the new login from master database into master2..sysxlogins table.

  • K. Brian Kelley

    Other ways to recover the password
    SQL 2000:
    SQLCrack from NGS Software which can brute force it (if you find their whitepaper, the C++ code for a command-line version is available). There is a weakness in the way the hashes are stored (one of the hashes is the hash of the password in all UPPERCASE, making it significantly easier to brute force).

    SQL 2000/2005:
    If the client isn’t using SQL Server 2005 native client connecting to SQL Server 2005, sniff it on the wire. The “encryption” mechanism is trivial and well-documented as is what packets to look for. Netmon, a scientific calculator, a pencil, a piece of paper, and an ASCii table is all you need.

  • Roust_m

    6. insert a record with the new login from master database into master2..sysxlogins table.

  • Anonymous

    Hard coded sa password in apps???
    What if your sa password is compromised and you need to change your sa password? I agree with ‘flawed DBA’ that it is a totally bad idea to allow third party software to use sa account. Sometimes technical decision has to override business decisions especially when it comes to security

  • ShawnNWF

    Mayberry RFD – Part 1
    It must be nice to work in an environment where the software used in every aspect of the business is run by the DBA team for security and usability before the purchase orders go out. But, I don’t work in Mayberry. I work for a multi-billion dollar corporation where software decisions are being made in offices all over the world by people who hold my job in their hands. If the software they buy for the warehousing department in Saskatchewan doesn’t meet my utopian rules for database interactivity, guess what? I deal with it. I do my job. I nip it in the bud!

  • ShawnNWF

    Mayberry RFD – Part 2
    It’s not a perfect world and what Rodney has presented is an elegant solution to a ugly problem. He never once advocated giving sa passwords to developers or that this is a best practice. This is just how it is in the real world. And, if you have to deal with it, then this is a great solution for your problem.

    P.S. Say hi to Andy, Opie and Barney.

  • Gomer

    SA password
    I appreciate your effort and the shared knowledge. I too have worked with SQL Server since SQL 6.5 (and before). I assume that if you are willing to go to this trouble to allow someone to install software on one of your servers (instead of just doing it yourself) you have since setup processes that track who and what are logging on as SA on all of your servers and have since remedied the situation.

  • sham

    not good
    i got very much truble in sa acount we r not getting sa account password in i putted same password

  • naren

    how to protect the sql 2005 database password
    how to protect the sql 2005 database password

  • yitiana

    change sql server password
    Usually using command prompt can change it.
    Osql –S yourservername –E
    1> EXEC sp_password NULL, ’yourpassword’, ’sa’
    2> GO
    Of cource, there are other ways.
    This article is given 4 methods to <a href=”http://www.passwordunlocker.com/knowledge/change-sql-server-password.html”>change sql server password</a>, that could be help.

  • yitiana