How Safe Are Your SQL Server Passwords

Password encryption in SQL Server 6.5 is not as secure as you think. Anyone with alittle knowledge and SQL Server systems administrator (SA) privileges can capture passwords ordisable encryption checks, essentially making the password clear text. You can't do much to preventthese problems, but you can be aware of them and understand their significance. If you know aboutsome holes in SQL Server's password encryption, at least you'll know where you're exposed. With alittle knowledge, you can work around the holes and use SQL Server's quirks to your benefit.

A History of SQL Server Passwords
Before SQL Server 6.0, SQL Server passwords were extremely vulnerable. Earlier versions of SQLServer stored clear text passwords in the password column of the syslogins systemtable, which has one row of values for each valid SQL Server logon. Anyone with the properpermissions could read the password column and then log on as that user. Most people weren'tconcerned about the passwords' security because people assumed--incorrectly--that users needed SAprivileges to read the password. However, using any text editor, even Notepad, any user could readpasswords directly from a database backup.

SQL Server 6.0 greatly improved security by encrypting passwords. SQL Server still storespasswords in syslogins, but it encrypts the string so even the SA can't read it. Therefore,you might think that because curious users can no longer pilfer passwords from a dump, passwords aresecure and database administrators (DBAs) live happily ever after. Unfortunately, the storydoesn't end quite so nicely. SAs can capture the encrypted form of a password in several wayswithout user knowledge.

Security Hole 1:
SAs Can Capture Passwords in a Private Table
Two built-in system procedures (SPs), sp_addlogin and sp_password, manage SQLServer passwords. The procedure sp_addlogin (shown in Listing 1) creates new usersand passwords, and sp_password changes existing users' passwords. Microsoft and third-partyapplications that manage user accounts call these procedures under the covers even if you don'texplicitly call them. For example, SQL Enterprise Manager (SEM) calls sp_addlogin wheneveryou add a new login. (You can easily verify this action by watching Transact-SQL--T-SQL--activitywith SQLTrace.) Calling sp_addlogin moves passwords into the procedure and stores them in@passwd in clear text. Stealing passwords is simple in this model: You add an insert statement tosp_addlogin that captures the clear text version of the password in a history table:

Insert into PasswordHistory VALUES (@loginame, @passwd)

Now you can compile a complete list of passwords as SQL Server adds new logins to the system.The procedure sp_password has the same hole as sp_addlogin, so you can keep track ofpassword changes by adding the same insert statement to sp_password.

Security Hole 2:
SAs Can Disable Password Encryption for Each Logon
In Listing 1, the password and status columns in the Values clause in the insertstatement expose two more SQL Server secrets. SQL Server encrypts passwords with pwdencrypt,an undocumented system function that accepts a string variable and returns an encrypted version ofthe string. The system then stores the encrypted version in syslogins. SQL Server canvalidate a user's encrypted password at logon because instead of unencrypting the value in syslogins,SQL Server uses pwdcompare, another undocumented function call, to compare the clear textand encrypted versions. The logon proceeds if pwdcompare determines that the clear text andencrypted version match.

Here's where the status column comes in. The comment for status says, "0*08bit means pw encrypt new alogorithm" (that's algorithm to you and me). Thatcomment got me thinking, so just for fun, I removed the 0x08 bit from a few status columns insyslogins using ^, SQL Server's bitwise exclusive OR operator. (If you're not familiar withbitwise operators, pretend I'm subtracting 8 from the status value.) Sure enough, this actiondisabled password encryption. The passwords were clear text in the eyes of the database. I could nowlog on to the server using one of the supposedly encrypted passwords from syslogins; infact, the server no longer accepted the encrypted string as a valid password. So, rogue SAs candefeat password encryption in SQL Server 6.5 by capturing passwords in a private table ortemporarily disabling password encryption altogether for each logon.

On a positive note, however, you can use the secret password management functions to enhance acustom security model. I've seen many applications where developers have extended SQL Server's basesecurity by adding a customized user table that contains a password column. You can call pwdencryptand pwdcompare from T-SQL like any other server function and use them to create user-levelpassword checks. The procedure pwdencrypt("un-encrypted") returns the encryptedstring; pwdcompare("unencrypted string", "encrypted string") returnsTRUE (1) if the strings match and FALSE (0) if they don't match. (Caution: The pwdencryptand pwdcompare functions are undocumented. Microsoft does not support them, and they canchange from release to release.)

Security Hole 3:
Network Sniffers Can Find Your Passwords
Network sniffers--tools that let you literally look at data packets as they move around thenetwork--are another technique that someone can use to access your passwords. The tightest databaseencryption in the world is penetrable if someone can easily read passwords directly from the networkprotocol layer. But you can use SQL Server's multi-protocol net-lib (MPN) to encrypt data within thenetwork packet. This encryption is great but comes at a price in performance, so use it wisely.Microsoft based MPN on Windows NT remote procedure calls, which can be 5 percent to 10 percentslower than other net-libs. Enabling encryption slows performance about another 10 percent. Thisslow performance takes a toll when you are moving large data sets around the network, but improvedsecurity may be worth the cost. The performance penalty is associated only with networkcommunication; query processing on the server doesn't take any longer.

Tip:
You Can Transfer Encrypted Passwords Across Servers
SEM's Transfer database option currently doesn't let you move logons from one server toanother and keep passwords intact. SEM transfers logon passwords as NULL. This feature makes usingSEM to transfer databases difficult in a production environment because people have to re-establishtheir passwords manually. Because re-establishing passwords is time-consuming, accounts have nopassword security while users are re-establishing passwords.

But you can circumvent this problem. Some experimentation shows that the pwdencrypt encryptionalgorithm is not server-dependent. So you can grab the encrypted passwords from one server andupdate the password column in syslogins on another server. Listing 2 showssp_TransferPasswords, a simple stored procedure that copies encrypted passwords from one serverto another. The sp_TransferPasswords procedure makes direct updates to system tables, sodon't use it unless you've read the source code and understand what it does.

I've used this technique to transfer passwords but always on servers of the same type--that is,the same version, sort order, character set, and hardware platform. I don't know whether theprocedure pwdencrypt uses different algorithms on different server types, so you may have toexperiment a little with this technique.

Run sp__TransferPasswords on the target server where you want to update passwordinformation. The name of the source server that contains the correct passwords is a mandatoryparameter. This technique requires that you configure the two servers as remote servers for eachother. SQL Server Books Online explains how to configure remote servers.

Suppose you do not want to set up the remote server access so you can execute remoteprocedures, or you want to transfer a password for only one user. Then you can create a newprocedure called sp_passwordNoEncrypt: you clone sp_password and remove the call topwdencrypt in the update statement, as Callout A in Listing 3, page 147, shows. After youmake this change, the sp_password clone no longer encrypts the password string you enter, soyou manually set the password on your new server to the encrypted version on the original server.Users can enter their old passwords on the new server and never know the difference. (Beware:Messing around with system tables and procedures isn't for the faint of heart. Please don't try thisunless a SQL Server expert is close by.)

Security Is Only as Good as Your Administrator
Despite these risks, don't panic just yet. Only SAs can use the password-detection techniquesI've discussed, and you already know that a computer is never truly safe from Administrator,god of the network. With the SETUSER command, SAs have always been able to impersonate a user. Ingeneral, then, your system is never secure if you can't trust your administrator.

My real beef with the current password encryption model is that people aren't getting what theyexpect. People assume password encryption means that no one, not even the SA, can hack passwords.This assumption is not true. The most dangerous security holes are the ones you don't know about. Ihope this article has informed you about the true status of password security.

I also hope that Microsoft won't be too angry with me for exposing a few flaws in SQL Server'spassword model. I'd hate for them to revoke my lifetime membership in the Bill G fan club and ask meto return my secret decoder ring keychain.