[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql user ids and passwords

Posted on 2007-10-17
8
Medium Priority
?
268 Views
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
0
Comment
Question by:johnnyg123
  • 4
  • 3
8 Comments
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 2000 total points
ID: 20096992
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 http://evolvedcode.net/ 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
Go

-- 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,L.name,
      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
            Begin
                  Insert Into #tBruteCS(RawText) Values (char(@Counter))
                  Set @Counter = @Counter + 1
            End

      -- 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
      Begin
            -- 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
      End

-- 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
0
 
LVL 15

Expert Comment

by:mcmonap
ID: 20098013
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:
http://support.microsoft.com/kb/246133/
0
 

Author Comment

by:johnnyg123
ID: 20103279
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.

Cmangus,

not sure how your post helps retrieve password

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20103309
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...  :)
0
 

Author Comment

by:johnnyg123
ID: 20103455
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?
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20103473
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.
0
 

Author Comment

by:johnnyg123
ID: 20104825
ok ...thanks
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 20104857
I can email you the wordlist.txt file I use.  What email address should I send it to?  My email would be coming from chrism@peoriaaz.gov if you need to add me to a whitelist.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

872 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