利用pwdcompare实现弱口令账号检测

[TOC]

本文将研究如何使用 SQL Server 中的内置 PWDCOMPARE 函数进行安全性测试密码。

准备工作

• 创建一个弱口令的测试账号

USE master
CREATE LOGIN test WITH PASSWORD = '123456', CHECK_POLICY=OFF

创建一个弱口令的密码库

• 将几组弱口令的密码写入一张表

USE tempdb
GO 
IF OBJECT_ID('tempdb..login_pwd') IS NOT NULL 
DROP TABLE login_pwd 
GO 
CREATE TABLE login_pwd(pwd_original varchar(100)) 
INSERT INTO login_pwd(pwd_original) VALUES('111111'),('123456'),('password'),('Aa123456'),('P@ssw0rd') 
SELECT * FROM login_pwd

• 或者下载作者收藏的 弱口令密码库，并保存到 c:\opt 目录下以供导入

USE tempdb
GO 
IF OBJECT_ID('tempdb..login_pwd') IS NOT NULL 
DROP TABLE login_pwd 
GO 
CREATE TABLE login_pwd(pwd_original varchar(100)) 
BULK INSERT login_pwd FROM 'c:\opt\weak_password_list.txt' 
SELECT * FROM login_pwd

附：查看密码库是否有重复的数据

SELECT distinct pwd_original,count(*) as cnt FROM login_pwd GROUP BY pwd_original HAVING COUNT(*)>1 ORDER BY cnt DESC

利用pwdcompare实现弱口令检测

USE tempdb
GO

SET NOCOUNT ON 
IF OBJECT_ID('tempdb..#login_pwd') IS NOT NULL 
DROP TABLE #login_pwd 
GO 

DECLARE @i int = 1 
DECLARE @j int 

CREATE TABLE #login_pwd(pwd_original varchar(100)) 
INSERT INTO #login_pwd(pwd_original) VALUES('111111'),('123456'),('password'),('Aa123456'),('P@ssw0rd') 
SET @j = @@ROWCOUNT 

ALTER TABLE #login_pwd ADD id int identity(1,1) 
CREATE CLUSTERED INDEX idx_id ON #login_pwd(id) 
DECLARE @pwd_original varchar(100) 
DECLARE @result TABLE (login_name sysname, login_password varchar(100), is_policy_checked bit) 

WHILE @i <= @j 
BEGIN 
  SELECT @pwd_original = pwd_original FROM #login_pwd WHERE id = @i 
  INSERT INTO @result SELECT name,@pwd_original,is_policy_checked FROM sys.sql_logins WITH(NOLOCK) WHERE PWDCOMPARE(@pwd_original, password_hash) = 1 
  SET @i = @i + 1 
END 

SELECT * FROM @result ORDER BY login_name

• 脚本执行结果如下表示：

• 测试完记得删掉 test 账号：

USE master
DROP LOGIN test