Password encryption in SQL Server 6.5 is not as secure as you think. Anyone with a little knowledge and SQL Server systems administrator (SA) privileges can capture passwords or disable encryption checks, essentially making the password clear text. You can’t do much to prevent these problems, but you can be aware of them and understand their significance. If you know about some holes in SQL Server’s password encryption, at least you’ll know where you’re exposed. With a little 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 SQL Server stored clear text passwords in the password column of the syslogins system table, which has one row of values for each valid SQL Server logon. Anyone with the proper permissions could read the password column and then log on as that user. Most people weren’t concerned about the passwords’ security because people assumed — incorrectly — that users needed SA privileges to read the password. However, using any text editor, even Notepad, any user could read passwords directly from a database backup.
SQL Server 6.0 greatly improved security by encrypting passwords. SQL Server still stores passwords 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 are secure and database administrators (DBAs) live happily ever after. Unfortunately, the story doesn’t end quite so nicely. SAs can capture the encrypted form of a password in several ways without 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 SQL Server passwords. The procedure sp_addlogin (shown in Figure 1) creates new users and passwords, and sp_password changes existing users’ passwords.
Microsoft and third-party applications that manage user accounts call these procedures under the covers even if you don’t explicitly call them. For example, SQL Enterprise Manager (SEM) calls sp_addlogin whenever you add a new login. (You can easily verify this action by watching Transact-SQL — T-SQL — activity with 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 to sp_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 of password changes by adding the same insert statement to sp_password.
Security Hole 2: SAs Can Disable Password Encryption for Each Logon
In Figure 1, the password and status columns in the Values clause in the insert statement 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 of the string. The system then stores the encrypted version in syslogins. SQL Server can validate 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 text and encrypted versions. The logon proceeds if pwdcompare determines that the clear text and encrypted version match.
Here’s where the status column comes in. The comment for status says, “0×08 bit means pw encrypt new alogorithm” (that’s algorithm to you and me). That comment got me thinking, so just for fun, I removed the 0×08 bit from a few status columns in syslogins using ^ , SQL Server’s bitwise exclusive OR operator. (If you’re not familiar with bitwise operators, pretend I’m subtracting 8 from the status value.) Sure enough, this action disabled password encryption. The passwords were clear text in the eyes of the database. I could now log on to the server using one of the supposedly encrypted passwords from syslogins; in fact, the server no longer accepted the encrypted string as a valid password. So, rogue SAs can defeat password encryption in SQL Server 6.5 by capturing passwords in a private table or temporarily disabling password encryption altogether for each logon.
On a positive note, however, you can use the secret password management functions to enhance a custom security model. I’ve seen many applications where developers have extended SQL Server’s base security by adding a customized user table that contains a password column. You can call pwdencrypt and pwdcompare from T-SQL like any other server function and use them to create user-level password checks. The procedure pwdencrypt(“un-encrypted”) returns the encrypted string; pwdcompare(“unencrypted string”, “encrypted string”) returns TRUE (1) if the strings match and FALSE (0) if they don’t match. (Caution: The pwdencrypt and pwdcompare functions are undocumented. Microsoft does not support them, and they can change 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 the network — are another technique that someone can use to access your passwords. The tightest database encryption in the world is penetrable if someone can easily read passwords directly from the network protocol layer. But you can use SQL Server’s multi-protocol net-lib (MPN) to encrypt data within the network 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 percent slower than other net-libs. Enabling encryption slows performance about another 10 percent. This slow performance takes a toll when you are moving large data sets around the network, but improved security may be worth the cost. The performance penalty is associated only with network communication; 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 to another and keep passwords intact. SEM transfers logon passwords as NULL. This feature makes using SEM to transfer databases difficult in a production environment because people have to re-establish their passwords manually. Because re-establishing passwords is time consuming, accounts have no password security while users are re-establishing passwords.
But you can circumvent this problem. Some experimentation shows that the pwdencrypt encryption algorithm is not server dependent. So you can grab the encrypted passwords from one server and update the password column in syslogins on another server. Figure 2 shows sp_TransferPasswords, a simple stored procedure that copies encrypted passwords from one server to another. The sp_TransferPasswords procedure makes direct updates to system tables, so don’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 the procedure pwdencrypt uses different algorithms on different server types, so you may have to experiment a little with this technique.
Run sp__TransferPasswords on the target server where you want to update password information. The name of the source server that contains the correct passwords is a mandatory parameter. This technique requires that you configure the two servers as remote servers for each other. 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 remote procedures, or you want to transfer a password for only one user. Then you can create a new procedure called sp_passwordNoEncrypt: you clone sp_password and remove the call to pwdencrypt in the update statement, as Callout A in Figure 3 shows.
After you make this change, the sp_password clone no longer encrypts the password string you enter, so you 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 this unless 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 techniques I’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. In general, 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 they expect. 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. I hope 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’s password model. I’d hate for them to revoke my lifetime membership in the Bill G fan club and ask me to return my secret decoder ring keychain.
Critical Challenges of ESI & Email Retention Are you storing too much electronic information? Get expert legal advice and better understanding of what you are required to do as an IT professional.
Rev Up Your IT Know-How with Our Recharged Magazine! The improved Windows IT Pro provides trusted IT content with an enhanced new look and functionality! Get comprehensive coverage of industry topics, expert advice, and real-world solutions—PLUS access to over 10,000 articles online. Order today!
Get It All with Windows IT Pro VIP Stock your IT toolbox with every solution ever printed in Windows IT Pro and SQL Server Magazine plus bonus Web-exclusive content on hot topics. Subscribe to receive the VIP CD and a subscription to your choice of Windows IT Pro or SQL Server Magazine!
Order Your Fundamentals CD Today! Gain an introduction to Exchange, learn server security requirements, and understand how unified communications can play a role in your messaging strategies with this free Exchange CD.
Windows IT Pro
Register
