Skip to content
Friday, May 9, 2025
Latest:
  • Changing Service tiers? Do you need downtime?
  • Different Database Services in AWS
  • Google Launched second Cloud Region in India
  • SQL Server IaaS Agent Extension |Part-1
  • Day-3 | SQL Server Internals and SQL Azure Workshop | IBM Noida

SQLChamp

Let's learn together!

  • Home
  • LearnSQL
  • SQLServer
  • SQLTips
  • Azure Data Platform
    • SQLAzure
    • SQLServer on Azure VM
  • Best Practices
  • Speaking Events
    • In-Person Events
    • Events
  • Contact – us
    • form7
LearnSQL

SQL Server Password Audit for SQL Server Logins

March 24, 2016 Sarabpreet Singh Anand

SQL Server Password audit is a must for all SQL Servers. Weak or Blank passwords are invitation to trouble and such type of passwords may result in security breach. 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. Now the question arises how to get rid of these.

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. In this article I’ll explain you how to do password audit and how to customize it according to your environment. For understanding Password Auditing, you need to understand the built-in function PWDCOMPARE

 

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 i.e., Denali, 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_hashparameter, 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.

 

A. 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 ;

 

B. Searching for passwords which are same as login names

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

SELECT name FROM sys.sql_logins

WHERE PWDCOMPARE(name, password_hash) = 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. This will check for a password specified as password.

SELECT name FROM sys.sql_logins

WHERE PWDCOMPARE(‘password’, password_hash) = 1 ;

 

 

Hope this post helped you, do leave comments.

Regards

Sarabpreet Anand

  • Built in Functions in SQL Server 2012
  • What is SQLAzure (Azure SQL Database)
  • Azure (2)
  • Azure Data Platform (32)
  • Azure Data Platform (2)
  • Best Practices (37)
  • Events (9)
  • In-Person (9)
  • In-Person Events (22)
  • Latest Announcements (6)
  • LearnSQL (32)
  • Powershell for SQL (5)
  • Powershell-Basics (1)
  • Pro-Tip (1)
  • Product Reviews (1)
  • Speaking Events (18)
  • SQL for Beginners (5)
  • SQL Server (1)
  • SQLAzure (33)
  • SQLServer (15)
  • SQLServer on Azure VM (17)
  • SQLServer on Azure VM (1)
  • SQLTips (22)
  • Uncategorized (9)
  • Webcast (6)

Always Encrypted (6) azure (32) azure portal (6) Azure SQL Database (10) backup (5) backup encryption (6) cloud (6) Database (4) DDM (5) deadlock (4) Dynamic Data Masking (5) error log (3) event (21) Execute As (4) Execution context (3) free (10) Free event (8) free sql learning (6) Function (3) grant (3) gurgaon (9) in person (9) learn (3) Learning (9) limitations (4) MCT (8) Microsoft (8) MVP (8) permissions (3) powershell (8) Powershell4SQL (3) restore (3) sarab (19) sarabpreet singh anand (4) security (11) SQL (51) SQL 2012 (5) SQL 2014 (8) SQL Azure (12) SQLAzure (10) SQL Database (10) SQL Saturday (9) SQLServer (31) sql server (17) sql server 2012 (6) SQLServer 2014 (9) sql server 2014 (4) SQL Server 2016 (8) SQLServerday (13) SQL Server day (9) SQLServer Workshop (3) sql tips (4) step-by-step (4) t-sql (8) tech days (3) tips (4) Training (10) tricks (3) webcast (7) windows azure (6)

Copyright © 2025 SQLChamp. All rights reserved.
Theme: ColorMag by ThemeGrill. Powered by WordPress.