Password encryption in SQL Server, how to tell if a user is using a weak password

I’ve made some interesting discoveries recently. I’ve used MS SQL Server for years now, but never gave much thought to how user ids and passwords are stored internally, until today.

Today we had an issue deleting a user from the server, for some reason the delete method was unable to delete the user, but it was able to tell me a vague reason why it failed. This is annoying as I think the delete should be able to handle all situations, but really it’s ultimately no one’s fault but my companies for using SQL Server 2000 for so long.

The error I received was:
“Login ‘TheLoginName’ is aliased or mapped to a user in one or more database(s). Drop the user or alias before dropping the login. (.Net SqlClient Data Provider)”

Pretty annoying, no? I read a number of articles and wound up cleaning up the issue, I won’t go into that issue in any further detail since this article is about password encryption, perhaps another time. In the process of fixing this issue I started checking out just how SQL server manages user id’s and passwords. If you’ve never seen it there is a table that SQL Server uses that stores all user id’s and passwords, to view it run this:
select * from master.dbo.sysxlogins
apparently this works on newer versions of SQL Server:

SELECT * FROM sys.sql_logins 

In this data you’ll notice there is a ‘password’ column. It will look something like this:
0x0100813F782D66EF15E40B1A3FDF7AB88B322F51401A87D8D3E3A8483C4351A3D96FC38499E6CDD2B6F9C01AD6BD

This password is stored with one way encryption, the method SQL server uses to create this password (marked as deprecated btw) is pwdencrypt. You can view the output of pwdencrypt by running this:

select pwdencrypt('test')

Note that if you run it multiple times the hash is different, it will not give you the same hash for a single password.

Note that if you wish to avoid writing the Hash & Salt code necessary to have strong password storage and are eyeing up pwdencrypt, don’t. Microsoft has provided a better (not deprecated) method to do password hashing with hashbytes.
eg of using hashbytes:

DECLARE @HashThis nvarchar(4000);
SELECT @HashThis = CONVERT(nvarchar(4000),'dslfdkjLK85kldhnv$n000#knf');
SELECT HashBytes('SHA1', @HashThis);
GO

So, it seems I’ve been taking a bit of the scenic route getting to the point of this article.. sorry! There were lots of good tidbits I found out on this journey of mine that I wanted to share!

Here’s the guts of how to tell if a user is using a weak password Microsoft SQL Server provides a method for comparing passwords called pwdcompare. This method almost seems like a security issue to me, but here it is. Do you have users that tend to use the word ‘password’ as their password? Run this to see who they are:

SELECT name FROM master.dbo.sysxlogins	
WHERE PWDCOMPARE('password', password) = 1

Use this on newer installations of SQL Server:

SELECT name FROM sys.sql_logins 
WHERE PWDCOMPARE('password', password_hash) = 1 

Now, this method, PWDCOMPARE, seems to me to open up MS SQL Server users to dictionary attacks. It wouldn’t take much to run this method against a 1000 or so passwords to take a bunch of user’s passwords, but I digress. It is pretty useful to tell your users to smarten up and not use weak passwords.

Also interesting on the password migration note. If you need to migrate passwords from Microsoft SQL Server 2005 (or higher I’d imagine) run the script available on this page:http://support.microsoft.com/kb/918992. If you need to migrate passwords from Microsoft SQL Server 2000 replace the sp_help_revlogin procedure with the one below (Thanks Wardy IT).

As always feel free to post your comments / questions below. I always enjoy hearing your thoughts!

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name    sysname
DECLARE @xstatus int
DECLARE @binpwd  varbinary (256)
DECLARE @txtpwd  sysname
DECLARE @tmpstr  varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name  'sa'
ELSE
  DECLARE login_curs CURSOR FOR
    SELECT sid, name, xstatus, password FROM master..sysxlogins
    WHERE srvid IS NULL AND name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
  + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status  -1)
BEGIN
  IF (@@fetch_status  -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@xstatus & 4) = 4
    BEGIN -- NT authenticated account/group
      IF (@xstatus & 1) = 1
      BEGIN -- NT login is denied access
        SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
        PRINT @tmpstr
      END
      ELSE BEGIN -- NT login has access
        SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
        PRINT @tmpstr
      END
    END
    ELSE BEGIN -- SQL Server authentication
      IF (@binpwd IS NOT NULL)
      BEGIN -- Non-null password
        EXEC sp_hexadecimal @binpwd, @txtpwd OUT
        IF (@xstatus & 2048) = 2048
          SET @tmpstr = 'SET @pwd = CONVERT (varchar(256), ' + @txtpwd + ')'
        ELSE
          SET @tmpstr = 'SET @pwd = CONVERT (varbinary(256), ' + @txtpwd + ')'
        PRINT @tmpstr
 EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', @pwd, @sid = ' + @SID_string + ', @encryptopt = '
      END
      ELSE BEGIN
        -- Null password
 EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
        SET @tmpstr = 'EXEC master..sp_addlogin ''' + @name
          + ''', NULL, @sid = ' + @SID_string + ', @encryptopt = '
      END
      IF (@xstatus & 2048) = 2048
        -- login upgraded from 6.5
        SET @tmpstr = @tmpstr + '''skip_encryption_old'''
      ELSE
        SET @tmpstr = @tmpstr + '''skip_encryption'''
      PRINT @tmpstr
    END
  END
  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @xstatus, @binpwd
  END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: