SQL Server's Undocumented Password Encryption Functions

Tip: Migrating password encryption from SQL Server 6.5 to 7.0

The following question from a reader prompted me to explain how to use the undocumented pwdcompare and pwdencrypt functions when migrating from SQL Server 6.5 to SQL Server 7.0. You can use the same techniques to build your own password encryption tools in SQL Server 2000. A short piece of sample code for SQL Server 7.0 or 2000 at the end of this article demonstrates how you can use the undocumented pwdcompare and pwdencrypt functions.

I read on the newsgroups that SQL Server 7.0 doesn't support the SQL Server 6.5 versions of pwdencrypt and pwdcompare. Our SQL Server 6.5 application uses an application-level password-encryption technique involving these functions. We’re having a difficult time migrating because we can’t use pwdcompare in SQL Server 7.0 to decrypt our application-level passwords. Do you know of a workaround for this problem?

Pwdencrypt and pwdcompare are internal, undocumented functions that SQL Server uses to manage passwords. Pwdencrypt uses a one-way hash that takes a clear string and returns an encrypted version of that string. Pwdcompare compares an unencrypted string to its encrypted representation to see whether they match. Microsoft cautions against using undocumented internal features, but sometimes people just can’t resist. With that said, yes, there is an undocumented, relatively unknown way to make strings encrypted with the SQL Server 6.5 version of pwdencrypt work with the SQL Server 7.0 version of pwdcompare.

Expanding upon the problem, let’s assume you’ve built a SQL Server 6.5 application that stores a 4-character PIN to use for a simple password check. You could have spent a lot of time writing your own encryption algorithms, or you could have used the Microsoft Cryptography API, but you’re a rebel, so you decided to use the undocumented, unsupported pwdencrypt and pwdcompare functions. The application uses pwdencrypt to encrypt the PIN, then uses pwdcompare to check a clear-text version of the PIN against the encrypted version to see whether they match. Everything works well until you try to upgrade to SQL Server 7.0; then the pwdcompare function starts returning FALSE even when the clear-text and encrypted versions of the PIN string match. How can you make the old custom-encrypted PIN numbers work with pwdcompare when you upgrade to SQL Server 7.0?

To answer this question, I assumed that because the passwords from an upgraded SQL Server 6.5 database work fine, SQL Server 7.0 must have a way to compare SQL Server 6.5 passwords encrypted with pwdencrypt. Doing a little detective work with the T-SQL source code behind sp_addlogin and sp_password gave me the answers I was looking for. (Reading system procedures is a great way to learn new tricks!) Both these stored procedures internally use the SQL Server encryption functions, and they both need to deal with SQL Server 6.5 versions of passwords.

Reading the sp_addlogin T-SQL code and the supporting SQL Server Books Online (BOL) documentation shows a possible value of skip_encryption_old for the @encryptopt parameter. According to BOL, this value means "The password is not encrypted. The supplied password was encrypted by an earlier version of SQL Server. This option is provided for upgrade purposes only."

Reading further in the T-SQL code for sp_addlogin clearly shows that sp_addlogin doesn't apply the SQL Server 7.0 version of pwdencrypt to the @passwd string if @encryptopt = 'skip_encryption_old'. But SQL Server 7.0 does apply some CONVERT functions to the @passwd parameter to store the string in the new SQL Server 7.0 data type format for passwords.

The relevant snippet of T-SQL code from sp_addlogin looks like this:

ELSE IF @encryptopt = 'skip_encryption_old'BEGINSELECT @xstatus = @xstatus | 0x800, -- old-style encryption@passwd = CONVERT(sysname,CONVERT(varbinary(30), CONVERT(varchar(30), @passwd)))

Pay close attention to the three-step CONVERT process that SQL Server puts the @passwd parameter through: @passwd is originally passed in as a string; the code converts it to varchar(30), then converts the varchar(30) to varbinary(30). So you’re essentially taking a string and converting it to its binary representation.

Now let’s take a look at the T-SQL code for sp_password. One of the checks determines whether the old password matches. You'll see a snippet of code that looks like this:

pwdcompare(@old, password, (CASE WHEN xstatus&2048 = 2048 THEN 1 ELSE 0END)) 

The xstatus column contains a bitmask. This CASE statement checks to see whether a particular bit has been set (represented by the value 2048). If this bit is on, the CASE returns 1; otherwise, it returns 0. This code shows that the SQL Server 7.0 version of pwdcompare now takes three parameters, rather than the two parameters the SQL Sever 6.5 version used. After some experimentation, I discovered that the third parameter is optional and defaults to 0. When you set this parameter to 0, pwdcompare uses the SQL Server 7.0 algorithm; setting this parameter to 1 tells SQL Server 7.0 to use the SQL Server 6.5 version of the pwdcompare algorithm.

The sample stored procedure in Listing 1 shows how you can use the SQL Server 7.0 version of pwdcompare to access strings you originally encrypted under the SQL Server 6.5 version of pwdencrypt. Setting the third parameter of SQL Server 7.0’s pwdcompare function to 1 lets you use the SQL Server 7.0 version of pwdcompare with the SQL Server 6.5 version of pwdencrypt. This stored procedure assumes that your application asks users to provide a Social Security Number (SSN) and PIN, which are stored in a table called MyTable. I'm also assuming that the application had previously used the SQL Server 6.5 version of pwdencrypt to encrypt the value of PIN.

If you're running SQL Server 7.0 or SQL Server 2000, the following code snippet shows how you can use the current versions of pwdencrypt and pwdcompare to create your own one-way hash password-management algorithms.

DECLARE @ClearPIN varchar(255) DECLARE @EncryptedPIN varbinary(255)SELECT @ClearPIN = 'test'SELECT @EncryptedPIN = CONVERT(varbinary(255), pwdencrypt(@ClearPIN))

SELECT pwdcompare(@ClearPIN, @EncryptedPIN, 0)

The final SELECT statement will return 1, denoting a TRUE condition. In other words, SQL Server puts ClearPIN through a one-way encryption hash and determines that the unencrypted string matches the encrypted version.