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:
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