Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
I was doing some security work to make a SQL Server production environment safer and ready for inspections. I had more than 900 SQL Login in the environment.
First of all, with the help of the following query, I looked at how much of these SQL Logins were checked with Password Policy:
1 | SELECT is_policy_checked, count(*) [count] FROM sys.sql_logins GROUP BY is_policy_checked |
First of all, let me tell you how I did the test. On SQL Server, there is a Function named “PWDCOMPARE“. This function basically takes 2 parameters, the first one is the password you will try, the second one is the encryption has.
I ran queries similar to the following:
1 2 3 4 5 6 7 8 | SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('1234', password_hash) = 1 SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('12345', password_hash) = 1 SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('123456', password_hash) = 1 SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('12345678', password_hash) = 1 SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('1', password_hash) = 1 SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('111111', password_hash) = 1 SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('qwerty', password_hash) = 1 SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('abc123', password_hash) = 1 |
The following query returns the logins whose password is null:
1 | SELECT name FROM sys.sql_logins WHERE PWDCOMPARE('', password_hash) = 1 |
The following query returns the login whose password is the user name:
1 | SELECT name FROM sys.sql_logins WHERE PWDCOMPARE(name, password_hash) = 1 |
I strongly recommend that you do similar security work in SQL Server Instance that you take over. In every possible SQL Instance, you must try to enable Password Policy for Logins so that you can implement a specific security policy.
According to the Verizon Business 2012 data leakage survey report, 96% of the leaked data in 2011 were taken from database servers.
In %55 of this, predictable passwords were used.
In 40% of this, stolen account information was used.
According to Application Security Inc.research, the top 10 threats to their databases are users with weak passwords or blank passwords. So obviously two studies complement each other. With my test, three studies confirm each other. If you test your SQL logins in your own environment and share your findings with us, I would appreciate it!
