Click here to monitor SSC
  • Av rating:
  • Total votes: 128
  • Total comments: 23
Rodney Landrum

Temporarily Changing an Unknown Password of the sa Account

10 July 2007

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 
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)
DECLARE login_curs CURSOR FOR
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

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.

Rodney Landrum

Author profile:

Rodney Landrum has been architecting solutions for SQL Server for over 10 years. He has worked with and written about many SQL Server technologies, including DTS, Integration Services, Analysis Services, and Reporting Services. He has authored three books on Reporting Services including his most recent 2008 edition for Apress. He is a regular contributor to SQL Server magazine and Simple-talk.com, where he blogs on about things like spiders, beer, somnambulance and SQL. His three recent articles in SQL Server magazine on building a DBA repository with SSIS and SSRS have been well received and implemented widely by DBAs around the world. Rodney also speaks regularly on SQL topics at such events as SQL Saturday and the Pensacola SQL Server Users Group. His day job finds him overseeing the health and well being of over 100 SQL Servers as manager of database administration in Pensacola, Florida.

Search for other articles by Rodney Landrum

Rate this article:   Avg rating: from a total of 128 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Temporarily Changing an Unknown Password of the sa Account
Posted by: Anonymous (not signed in)
Posted on: Thursday, July 12, 2007 at 8:04 AM
Message: 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!!

Subject: Awesome
Posted by: J.Cruz (not signed in)
Posted on: Thursday, July 12, 2007 at 10:06 AM
Message: 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!

Subject: Keep for emergencies
Posted by: Anonymous (not signed in)
Posted on: Friday, July 13, 2007 at 2:04 AM
Message: 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.

Subject: An essential addition to the DBA's script library
Posted by: david.buckingham (view profile)
Posted on: Friday, July 13, 2007 at 9:58 AM
Message: That is by far the most elegant solution I've seen yet to this occasional problem. Thank you kindly for the excellent article.

Subject: Must 1 of the must have script for DBA's
Posted by: Anonymous (not signed in)
Posted on: Wednesday, July 18, 2007 at 1:49 AM
Message: Very very elegant solution

Subject: flawed
Posted by: Anonymous (not signed in)
Posted on: Wednesday, July 18, 2007 at 2:54 AM
Message: 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

Subject: not necessarily flawed, and may come in handy
Posted by: Anonymous (not signed in)
Posted on: Wednesday, July 18, 2007 at 7:25 AM
Message: You have have a compiled app hard coded to run as "sa".

thanks for sharing, Rodney.

Subject: This works!
Posted by: Anonymous (not signed in)
Posted on: Wednesday, July 18, 2007 at 7:35 AM
Message: 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".


Subject: Right On
Posted by: TPowell_3557 (view profile)
Posted on: Wednesday, July 18, 2007 at 7:44 AM
Message: 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.

Subject: Dear "flawed" DBA
Posted by: ShawnNWF (view profile)
Posted on: Wednesday, July 18, 2007 at 9:34 AM
Message: 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.

Subject: No Windows Authentication access
Posted by: Anonymous (not signed in)
Posted on: Wednesday, July 18, 2007 at 5:19 PM
Message: Just yesterday I stuck with such task. Developer left leaving us without sa password and deleted all logins including BUILTIN\Administrators group.
Now I cannot login into the SQL Server despite I'm in Local Admin group. :(
Does anyone has solution?

Subject: The solution
Posted by: Roust_m (not signed in)
Posted on: Wednesday, July 18, 2007 at 10:26 PM
Message: 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

Subject: Correction
Posted by: Roust_m (not signed in)
Posted on: Wednesday, July 18, 2007 at 10:29 PM
Message: 6. insert a record with the new login from master database into master2..sysxlogins table.


Subject: Other ways to recover the password
Posted by: K. Brian Kelley (view profile)
Posted on: Wednesday, July 18, 2007 at 10:42 PM
Message: 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.


Subject: Correction
Posted by: Roust_m (not signed in)
Posted on: Wednesday, July 18, 2007 at 11:03 PM
Message: 6. insert a record with the new login from master database into master2..sysxlogins table.


Subject: Hard coded sa password in apps???
Posted by: Anonymous (not signed in)
Posted on: Friday, July 20, 2007 at 2:20 AM
Message: 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

Subject: Mayberry RFD - Part 1
Posted by: ShawnNWF (view profile)
Posted on: Friday, July 20, 2007 at 8:48 AM
Message: 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!

Subject: Mayberry RFD - Part 2
Posted by: ShawnNWF (view profile)
Posted on: Friday, July 20, 2007 at 8:49 AM
Message: 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.

Subject: SA password
Posted by: Gomer (not signed in)
Posted on: Thursday, July 26, 2007 at 6:38 AM
Message: 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.

Subject: not good
Posted by: sham (not signed in)
Posted on: Monday, August 06, 2007 at 3:32 AM
Message: i got very much truble in sa acount we r not getting sa account password in i putted same password





Subject: how to protect the sql 2005 database password
Posted by: naren (not signed in)
Posted on: Friday, August 17, 2007 at 12:05 PM
Message: how to protect the sql 2005 database password

Subject: change sql server password
Posted by: yitiana (view profile)
Posted on: Monday, August 16, 2010 at 2:30 AM
Message: 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.

Subject: 4 methods to change sql server password
Posted by: yitiana (view profile)
Posted on: Monday, August 16, 2010 at 2:32 AM
Message: http://www.passwordunlocker.com/knowledge/change-sql-server-password.html

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.