Post reply

How to check SQL Server Login and Password Properties? (7 Questions for DBA)

  • March 5, 2010 at 8:54 am

    #217494

    Hello everyone,

    I have a situation - I have around 2500 SQL Server 2008 Logins and I (DBA) did not set there passwords. But my concern is to implement strong password policy so I wish to find below questions from SQL Server end, some questions’ answers I have found but others are not. Please suggest if someone having the answers? I was try LOGINPROPERTY and SQL Server Management Policies but??????

    1.Which login having blank Password?

    Answer : SELECT name FROM sys.sql_logins

    WHERE PWDCOMPARE('', password_hash) = 1

    OR PWDCOMPARE('', password_hash, 1) = 1

    2.Which login having same password (password is login name)?

    Answer : SELECT name FROM sys.sql_logins

    WHERE PWDCOMPARE(name, password_hash) = 1

    OR PWDCOMPARE(name, password_hash, 1) = 1

    3.Which login having guessable password?

    Answer : SELECT name FROM sys.sql_logins

    WHERE PWDCOMPARE(name+’@123’, password_hash) = 1

    OR PWDCOMPARE(name+’@123’, password_hash, 1) = 1

    4.Which login did not having strong password (strong mean minimum 10 character or whatever I wish to check)?

    ?

    5.What is the length of the password of a particular login?

    ?

    6.Can I do same question on Windows Logins (Windows Logins those mapped in SQL Server)?

    ?

    7.Is there any option to convert SQL Server Login Password (HASH) to clear text (in term of DBA perspective)

    ?

    I request to sqlservercentral.com DBA community to add/ write in reply more questions related to this topic if they have.

    Thanks All

    Ram
    MSSQL DBA

  • March 5, 2010 at 9:16 am

    #1129256

    Homework? Test?

    The 'encryption' that protects the SQL passwords is a one-way hash.

    If you want to enforce strong password policy, enable password policy on those logins. SQL will take the rules from the DC of the domain it's in.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 5, 2010 at 10:24 am

    #1129300

    Hi,

    I have this on production server.

    windows account policies are good if I'm going to create new login.

    My problem is that I want to find out thoses login who should be in security policy.

    Ram
    MSSQL DBA

  • March 9, 2010 at 2:31 am

    #1130511

    Reo (3/5/2010)

    Hi,

    I have this on production server.

    windows account policies are good if I'm going to create new login.

    My problem is that I want to find out thoses login who should be in security policy.

    Try Policy-Based Management.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply