• Article

PWDCOMPARE (Transact-SQL)

Hashes a password and compares the hash to the hash of an existing password. PWDCOMPARE can be used to search for blank SQL Server login passwords or common weak passwords.

Topic link iconTransact-SQL Syntax Conventions

Syntax

PWDCOMPARE ( 'clear_text_password'
   , 'password_hash' 
   [ , version ] )

Arguments

  • 'clear_text_password'
    Is the unencrypted password. clear_text_password is sysname (nvarchar(128)).

  • 'password_hash'
    Is the encryption hash of a password. password_hash is varbinary(128).

  • version
    Optional parameter that can be set to 1 if password_hash represents a value from a login earlier than SQL Server 2000 that was migrated to SQL Server 2005 or SQL Server 2008 but never converted to the SQL Server 2000 system. version is int.

    Important

    This parameter is deprecated and might be removed in a future release of SQL Server.

Return Types

int

Returns 1 if the hash of the clear_text_password matches the password_hash parameter, and 0 if it does not.

Remarks

When you migrate an instance of SQL Server 7.0 to SQL Server 2000 or a later version, the password hash is not changed, and you must use the version parameter to test the password. After migration, when the login is used for the first time, the password hash is updated to the format that was first used in SQL Server 2000. From then on, PWDCOMPARE does not require the version parameter for that login.

The PWDCOMPARE function is not a threat against the strength of password hashes because the same test could be performed by trying to log in using the password provided as the first parameter.

Permissions

PWDENCRYPT is available to public.

CONTROL SERVER permission is required to examine the password_hash column of sys.sql_logins.

Examples

A. Identifying logins that have no passwords on SQL Server 2005 or SQL Server 2008

The following example identifies SQL Server logins that have no passwords. The first WHERE clause checks the password hash for values that are stored in the format used by SQL Server 2000 and later versions. The second WHERE clause includes the version parameter to check the password hash for values that are still stored in the format that is used by versions of SQL Server earlier than SQL Server 2000.

SELECT name FROM sys.sql_logins 
WHERE PWDCOMPARE('', password_hash) = 1 
OR PWDCOMPARE('', password_hash, 1) = 1 ;

B. Identifying logins that have no passwords on SQL Server 2000

The sys.sql_logins table does not exist in SQL Server 2000. You can execute the following statement against an instance of SQL Server 2000 to identify SQL Server logins that have no passwords.

SELECT name FROM syslogins 
WHERE PWDCOMPARE ('', password) = 1 
OR PWDCOMPARE('', password, 1) = 1  ;

C. Searching for common passwords

To search for common passwords that you want to identify and change, specify the password as the first parameter. For example, execute the following statement to search for a password specified as password.

SELECT name FROM sys.sql_logins 
WHERE PWDCOMPARE('password', password_hash) = 1 
OR PWDCOMPARE('password', password_hash, 1) = 1 ;