Sql user ids and passwords

Posted on 2007-10-17
Last Modified: 2007-10-18
Is it possible to export a list of user sql ids and passwords if one is logged in as SA.

There is an id being used by a vendor package that no one seems to remember the password for

Was wondering if it was possible to get password if logged in as SA?

Vendor is long gone and don't want to change password because the password is embedded somewhere in the software and thus changing it would render the software useless

using sql server 2000
Question by:johnnyg123
    LVL 17

    Accepted Solution

    You can't export passwords.  Best bet is to try and use a brute force method of recovery.  Here's code I use.  You will have to configure a wordlist.txt that is used in 2 places in the code.  This is just a plain-text file of words.  I can supply one if you wish...

    This script is used to audit SQL Server 2000 passwords against some standards.  It will check for blank passwords, 1 and 2 character passwords,
    passwords that are the same as the username, and optionally, it will check all that passwords against a 50,000+ word dictionary.

    If you're going to check the passwords against the dictionary the wordlist.txt file must be copied to the path below, as shown by the @Wordlist
    variable, on the server your're checking.  If you're NOT going to check against the dictionary then set the variable to NULL.

    This script originally lived at and has been modified by Chris for readability and functionality.

    Set NoCount On

    -- Check if any of our temporary objects already exist and if so, drop them
    If (object_id('tempdb..#tLogins') Is Not Null)
      Drop Table #tLogins
    If (object_id('tempdb..#tBruteDict') Is Not Null)
      Drop Table #tBruteDict
    If (object_id('tempdb..#tUserDict') Is Not Null)
      Drop Table #tUserDict
    If (object_id('tempdb..#tBruteCS') Is Not Null)
      Drop Table #tBruteCS

    -- Variables for use throughout the script
    Declare @Counter int, @WordList varchar(1000)

    -- Set this to NULL if you don't want to check the passwords against the dictionary
    --Set @WordList = NULL
    Set @Wordlist = '\\{path}\wordlist.txt'

    -- Create tables to hold temporary data
          Create Table #tLogins(UID int IDENTITY(1,1) Not Null, Login SYSNAME Not Null, SID VARBINARY(85) Not Null, Password SYSNAME Null, PasswordHash VARBINARY(256) Null
          , IsAdmin BIT DEFAULT(0) Not Null, BlankPass BIT DEFAULT(0) Not Null, SamePass BIT DEFAULT(0) Not Null, BrutePass BIT DEFAULT(0) Not Null)
          Create Table #tBruteDict(RawText SYSNAME Not Null)
          Create Table #tBruteCS(RawText SYSNAME Not Null)
          Create Table #tUserDict(RawText SYSNAME Not Null)
          Raiserror ('Tables Created.',10,1) With NoWait
    -- Create a list of the regular (non-nt) users
          Insert Into #tLogins(SID,Login,IsAdmin,PasswordHash) Select L.sid,,
          Case When L.sysadmin = 1 Or L.securityadmin = 1 Or L.serveradmin = 1 Or L.setupadmin = 1 Or L.processadmin = 1 Or L.diskadmin = 1 Then 1 Else 0 End, LX.password
          From master.dbo.syslogins As L Left Join master.dbo.sysxlogins LX On L.sid = LX.sid Where L.isntname = 0
          Raiserror ('User list generated.',10,1) With NoWait
    -- Identify users with blank passwords
          Update #tLogins Set BlankPass = 1 From #tLogins Where PasswordHash Is Null
          Raiserror ('Blank passwords identified.',10,1) With NoWait
    -- Identify users with password = username
          Update #tLogins Set SamePass = 1, Password = Login Where PWDCOMPARE(Login,PasswordHash) = 1
          Raiserror ('Username = Password identified.',10,1) With NoWait
    -- Identify users with one or two character (ASCII 33-126, printable, english characters) passwords
          -- Make a table of every ASCII character from value 33 to 126
                Set @Counter = 33
                While @Counter <= 126
                      Insert Into #tBruteCS(RawText) Values (char(@Counter))
                      Set @Counter = @Counter + 1

          -- Put a row into another table for each individual character
                Insert Into #tBruteDict(RawText) Select Src.RawText From #tBruteCS As Src

          -- Put a row into the same table for a full cartesian of each character to each character which will check for ALL 2 letter combinations
                Insert Into #tBruteDict(RawText) Select Src.RawText + New.RawText From #tBruteDict As Src, #tBruteCS As New
                Raiserror ('1 and 2 character support tables created.',10,1) With NoWait
          -- Check each login and set some info depending on whether they have a password that resembles anything in the table
                Update #tLogins Set BrutePass = 1, Password = Dict.RawText From #tLogins As Src Left Join #tBruteDict As Dict On PWDCOMPARE(Dict.RawText, Src.PasswordHash) = 1
                      Where Dict.RawText Is Not Null And Src.BrutePass = 0 And Src.BlankPass = 0 And Src.SamePass = 0
                Raiserror ('1 and 2 character passwords identified.',10,1) With NoWait
    -- Identify users with passwords from our dictionary.  Load the dictionary from the bulk file and then process it.
          If @WordList Is Not Null
                Bulk Insert #tUserDict From '\\{path}\wordlist.txt'
                Raiserror ('Brute force dictionary loaded.',10,1) With NoWait
                -- Check each login against the dictionary and set some info if they have a password on the list
                Update #tLogins Set BrutePass = 1, Password = Dict.RawText From #tLogins As Src Left Join #tUserDict As Dict On PWDCOMPARE(Dict.RawText, Src.PasswordHash) = 1
                        Where Dict.RawText Is Not Null And Src.BrutePass = 0 And Src.BlankPass = 0 And Src.SamePass = 0
                Raiserror ('Brute force passwords identified.',10,1) With NoWait

    -- Reset nocount
          Set NoCount Off

    -- Report any users whose logins were weak
          Select  Login As Username, IsNull(Password, '-- currently unknown --' ) As Password, Case When BlankPass = 1 Or SamePass = 1 Or BrutePass = 1 Then 1 Else 0 End As [Cracked?],
          IsAdmin As [Admin User?], BlankPass As [Blank Password?], SamePass As [Username = Password?], BrutePass As [Password Bruteforced?] From #tLogins Order By Login
          Raiserror ('Code completed.',10,1) With NoWait

    -- Clean up Temp tables
          Drop Table #tBruteDict
          Drop Table #tBruteCS
          Drop Table #tUserDict
          Drop Table #tLogins
    LVL 15

    Expert Comment

    Depends if you want to know the password or not?  If you are just trying to move the logins to a new server this maybe of help to you:

    Author Comment

    Wanted to know the actual password.

    I realize there is a security issue but I thought there might be something that can be done if logged in as sa.  My thinking being that you can do much more damage with sa authority than looking up a password.

    Just a thought.


    not sure how your post helps retrieve password

    LVL 17

    Expert Comment

    The script I sent checks for blank passwords, passwords that match the username, and passwords that are any 2 letter combination.  It stops to report them.  Then it continues on and does a brute force attempt to hash the wordlist against the stored password.  If it matches one it reports the match from the wordlist.

    Basically, if the stored password is 'test' the routine will encrypt the word 'test' and see if it matches with the stored password.  If it does, then you've guessed the password.

    Yes, as sa you can do lots of damage...  :)

    Author Comment

    Sorry if this seem like a silly question,  but does the wordlist.txt file contain the possible password values?

    We have no idea what the password might be

    maybe if you sent me a sample?
    LVL 17

    Expert Comment

    The wordlist.txt contains about 60,000 common words.  

    If the user has used a robust password like $JDf7$jhf then no, this will not reveal the password.  If the user is lieke 98% of them out there they've used some common word and this script could reveal it.

    Author Comment

    ok ...thanks
    LVL 17

    Expert Comment

    I can email you the wordlist.txt file I use.  What email address should I send it to?  My email would be coming from if you need to add me to a whitelist.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now