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 Listing 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 Listing 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 0x08 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.)