检查SQL Server上的常用密码

5n0oy7gb  于 2022-12-10  发布在  SQL Server
关注(0)|答案(1)|浏览(147)

NCSC has published a list of the 100.000 most used passwords, see 100K passwords
I wondered if any of my users had used one of these passwords, so I wrote a check, inspired by an idea from bp_check.
Am I doing it the correct way?
I had problems with UniCode, but that should have been solved by now.

pkln4tw6

pkln4tw61#

Here is my code to run such a password check:

--    script to check if any of the passwords on your SQL Server is amongst the 100.000 most used passwords that Troy Hunt and NCSC released

--    see https://www.ncsc.gov.uk/blog-post/passwords-passwords-everywhere

    --drop table dbo.PwnedPasswordTop100k 
    create table dbo.PwnedPasswordTop100k ( pw nvarchar(500) collate Latin1_General_CS_AS not null)
    go
    bulk insert dbo.PwnedPasswordTop100k
    FROM 'c:\temp\PwnedPasswordTop100k.txt'
    WITH
    (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
     , codepage=65001 
    )
    go
    
    SELECT sl.name , ppt.pw 
    from sys.sql_logins sl
    cross join dbo.PwnedPasswordTop100k ppt
    where PWDCOMPARE(ppt.pw, sl.password_hash) = 1  
    union all
    SELECT s.name, 'password is NULL' FROM sys.sql_logins s  -- password is null (from idea from BP_Check http://aka.ms/BPCheck;)
    where password_hash is null 
    AND exists(SELECT * FROM fn_my_permissions(NULL, 'SERVER') where permission_name='CONTROL SERVER')
    and name NOT IN ('MSCRMSqlClrLogin','##MS_SmoExtendedSigningCertificate##','##MS_PolicySigningCertificate##','##MS_SQLResourceSigningCertificate##','##MS_SQLReplicationSigningCertificate##','##MS_SQLAuthenticatorCertificate##','##MS_AgentSigningCertificate##','##MS_SQLEnableSystemAssemblyLoadingUser##')
    union all
    SELECT s.name, s.Name FROM sys.sql_logins s   -- password the same as login (from idea from BP_Check http://aka.ms/BPCheck;)
    where PWDCOMPARE(s.name, s.name) = 1
    union all 
    select 'not SYSADM', 'You do not have CONTROL SERVER permissions, and cannot see any password_hashes'
    where not exists(SELECT * FROM fn_my_permissions(NULL, 'SERVER') where permission_name='CONTROL SERVER')

    
    --select top (10000) * from dbo.PwnedPasswordTop100k where pw like N'пїЅпїЅпїЅпїЅ'
    
    begin try
      drop table dbo.PwnedPasswordTop100k 
    end try
    begin catch
    end catch

The PwnedPasswordTop100k.txt file must be copied to c:\temp on the SQL Server, and the account that runs SQL Server, must have access to the file. Alternatively, change the path to a place which the SQL Server can see.
Not everyone has access to a share that SQL Server can see.
So I've also written a script with all the passwords: Script with 100.000 passwords
It is a SELECT Statement that is 1500 kB.
Quite big, but you can run it in SQL Server Management Studio.
My SQL Server can check about 6 users per minute, so the script is not very fast.

相关问题