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
EBFE486E9BEE063E8D3B332752E1BC2E7C5DF0F9EBFE486E9BEE063E8D3B3)
EXEC master..sp_addlogin 'sa_test', @pwd, @sid = 0x9BDCD87E84673D46B7FE
3584D06E970D, @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:

“0x01005B20054332752E1BC2E7C5DF0F9EBFE486E9BEE063E8D3B332752E1BC2E7C5DF0F9EBFE
 486E9BEE063E8D3B3″.

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
E1BC2E7C5DF0F9EBFE486E9BEE063E8D3B3
  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:

    -- CHANGE THE PASSWORD --
UPDATE master.dbo.sysxlogins
  SET password = CONVERT(varbinary(256),
pwdencrypt(@new)),
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:

    -- CHANGE THE PASSWORD --
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
8D3B332752E1BC2E7C5DF0F9EBFE486E9BEE063E8D3B3,@loginame='sa_test'

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)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password 
  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)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password 
  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

SELECT
   p.sid,
   p.name,
   p.type,
   p.is_disabled,
   p.default_database_name,
   l.hasaccess,
   l.denylogin 
  FROM
      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
CREATE LOGIN [sa]
   WITH PASSWORD = 0x01004086CEB6301EEC0A99
                   4E49E30DA235880057410264030797 HASHED,
   SID = 0x01, 
   DEFAULT_DATABASE = [master],

   CHECK_POLICY = OFF,

   CHECK_EXPIRATION = OFF

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:

ALTER LOGIN sa WITH PASSWORD =
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.