Thursday, January 19, 2012

Password Audit for SQL Server Logins


This time I am going to talk about password audit for SQL Server. Weak or Blank passwords are an invitartion to trouble. It results in a security risk. Everybody knows the importance of Strong and Complex password but as they say it’s always easy to preach but difficult to practice, we tend to make use of weak and sometimes blank passwords.

How can we avoid it.

Regular Password Audits is the solution. As a SQL DBA we must do a regular audit and remind the users to change their passwords to something more complex. For understanding Password Auditing, you need to understand the built-in function PWDCOMPARE

This function hashes a password and compares the hash to the hash of existing password. You must be wondering why are we again hashing, SQL Server stores the Hash of password for SQL Logins and hashing is only one side, un-hashing or decrypting the hashed data is not at all possible. This function is available from SQL Server 7.0 to SQL Server 2012, means you can make use of this function in all SQL Server versions.

The function takes 3 parameters Clear_test_password, Password_Hash and Version where the first two are mandatory and the third one is optional and obsolete but it is there for backward compatibility. It returns 1 if the hash of the clear_text_password matches the password_hash parameter, and 0 if it does not.

Note: 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 required: PWDENCRYPT is available to public and CONTROL SERVER permission is required to examine the password_hash column of sys.sql_logins.

1. Identifying logins that have no passwords.

The following example identifies SQL Server logins that have no passwords.

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

2. Searching for login names wich have the same value for passwords.

This code identifies the login where the user has given the same text as login and password, for example Login ButerJ is a login with password ButerJ

SELECT [name] FROM sys.sql_logins WHERE PWDCOMPARE([name], password_hash) = 1;

3. Seaching for common passwords

To search for common passwords that you want to identify and change, specify the password as the first parameter. This will check for a password specified as password.

SELECT [name] FROM sys.sql_logins WHERE PWDCOMPARE('P@ssw0rd', password_hash) = 1;

1 comment:

  1. Hello Friends,

    Password audit is a must for all SQL Servers. Regular Password Audits is the solution. As a SQL DBA we must do a regular audit and remind the users to change their passwords to something more complex. SQL Server Audit includes the ability to provide centralized storage of audit logs and integration with System Center, as well as noticeably better performance. The Server Audit Specification object describes what to audit at the server level. Thanks a lot...

    ReplyDelete