Database management--security management--Identifying logins for SQL Server hollow or weak passwords

Original: Database management--security management--Identifying logins for SQL Server hollow or weak passwords

Original translation from:

Http://www.mssqltips.com/sqlservertip/2775/identify-blank-and-weak-passwords-for-sql-server-logins/?utm_source =dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012925

Microsoft has always recommended the use of complex strong passwords. A weak password or a blank password can cause a security vulnerability. You can use the Pwdcompare security function to identify this type of login and use a complex strong password reset to avoid this security vulnerability.

You can use this function to find an empty or weak password. This function is executed by passing in two parameters "Text_password" and Password_hash, and returns 1 if the hash of the Clear_text_password matches the Password_hash parameter; otherwise 0. When accessing the catalog view sys.sql_logins, place this function in the WHERE clause to check for a blank password or a weak password.

Step One:

First create some logins with the same password. Here you create two logins: Mssqltips and Mssqltips_1, and have the same password, and both are blank passwords. Then create a corresponding user for each login and give db_owner permissions to the database.

--creating a login which have same password as its login name. Then creating a user with same name --and assigned it db_owner access. CREATE LOGIN mssqltips with PASSWORD = ' Mssqltips ' , Check_policy = OFF Go Use tempdb Go CREATE USER mssqltips for Login mssqltips Go sp_addrolemember ' db_owner ' , mssqltips Go --creating a Login without password. Then creating a user with same name and assigned it db_owner access. CREATE LOGIN Mssqltips_1 with PASSWORD = "' , Check_policy = OFF Go Use tempdb Go CREATE USER Mssqltips_1 for Login Mssqltips_1 Go sp_addrolemember ' db_owner ' , Mssqltips_1 Go

Step Two:

Now test the same password, create another login named "Mssqltips_2", and have the same password as mssqltips.

--creating a Login with common password. Then creating a user with same name and assigned it --db_owner access. CREATE LOGIN mssqltips_2 with PASSWORD = ' Mssqltips ' , Check_policy = OFF Go Use tempdb Go CREATE USER mssqltips_2 for Login mssqltips_2 Go sp_addrolemember ' db_owner ' , mssqltips_2 Go

Step Three:

To query for a weak password in SQL Server entity login:

To find the same password:

The following query is to return all logins with the same password, such as Mssqltips, and pass this password to the Pwdcompare function to get all eligible logins:

SELECT name , Type_desc , create_date , modify_date , Password_hash from SYS . Sql_logins WHERE Pwdcompare (' mssqltips ',password_hash) = 1 ;

Explicit Mssqltips and mssqltips_2 have the same password:

To find a blank password:

Execute the following statement:

SELECT name , Type_desc , create_date , modify_date , Password_hash from SYS . Sql_logins WHERE Pwdcompare (",password_hash) = 1 ;

Get the following results:

Find passwords and logins with the same login name:

SELECT name , Type_desc , create_date , modify_date , Password_hash from SYS . Sql_logins WHERE Pwdcompare (name,password_hash) = 1 ;

Get the following results:

Step Four:

When you find the problematic logins above, reset the strong password to make the server environment more secure.

Note: It is the original picture, and the script is to change the database from the original database to tempdb.

Database management--security management--Identifying logins for SQL Server hollow or weak passwords