Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Storing passwords in a secure way in a SQL Server database


By:   |   Read Comments (36)   |   Related Tips: 1 | 2 | 3 | More > Encryption

Next Free Webcast - The more things change… DBAs versus Sysadmins in cloud availability


Problem

Everybody would agree that passwords should be secure, so users should consider these points when they choose passwords. Such as using a mix of characters and special symbols, not using simple words, using a combination of special symbols, letters and numbers, etc. But all these considerations are not enough if passwords are stored in an unsecure way.

In database applications passwords are usually stored in the database, so storing passwords in the database should be implemented very carefully. It is obvious that storing passwords in the table with plain text is very vulnerable, because if an attacker accesses the database he/she can steal users' passwords. It is indisputable that passwords in a database should be encrypted and made undecipherable as much as possible.

Solution

Let's see how to encrypt and store passwords in a SQL Server database. For encrypting passwords we'll use one-way hashing algorithms. These algorithms map the input value to encrypted output and for the same input it generates the same output text. Also there is no decryption algorithm. It means that it's impossible to revert to the original value using encrypted output.

The HASHBYTES function in SQL Server returns a hash for the input value generated with a given algorithm. Possible algorithms for this function are MD2, MD4, MD5, SHA, SHA1 and starting with SQL Server 2012 also include SHA2_256 and SHA2_512. We will choose the strongest - SHA2_512 - for our example (it generates a 130 symbol hash and uses 64 bytes). We should also consider the fact that the stronger algorithm, the more time that is needed for hashing than for weaker algorithms.

Let's assume that we need to create table which stores user's data such as:

CREATE TABLE dbo.[User]
(
    UserID INT IDENTITY(1,1) NOT NULL,
    LoginName NVARCHAR(40) NOT NULL,
    PasswordHash BINARY(64) NOT NULL,
    FirstName NVARCHAR(40) NULL,
    LastName NVARCHAR(40) NULL,
    CONSTRAINT [PK_User_UserID] PRIMARY KEY CLUSTERED (UserID ASC)
)
	

Also we will create a stored procedure to insert user's data (we developed this stored procedure in the simplest way to illustrate this example, but in reality these kind of procedures contain more complicated code):

CREATE PROCEDURE dbo.uspAddUser
    @pLogin NVARCHAR(50), 
    @pPassword NVARCHAR(50), 
    @pFirstName NVARCHAR(40) = NULL, 
    @pLastName NVARCHAR(40) = NULL,
    @responseMessage NVARCHAR(250) OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    BEGIN TRY

        INSERT INTO dbo.[User] (LoginName, PasswordHash, FirstName, LastName)
        VALUES(@pLogin, HASHBYTES('SHA2_512', @pPassword), @pFirstName, @pLastName)

        SET @responseMessage='Success'

    END TRY
    BEGIN CATCH
        SET @responseMessage=ERROR_MESSAGE() 
    END CATCH

END
	

As we can see, the stored procedure takes the password as an input parameter and inserts it into the database in an encrypted form - HASHBYTES('SHA2_512', @pPassword). We can run the stored procedure as follows:

DECLARE @responseMessage NVARCHAR(250)

EXEC dbo.uspAddUser
          @pLogin = N'Admin',
          @pPassword = N'123',
          @pFirstName = N'Admin',
          @pLastName = N'Administrator',
          @[email protected] OUTPUT

SELECT *
FROM [dbo].[User]
	

encrypted password

As we can see the password's text is unreadable. However encrypting passwords is not a 100% guarantee that passwords can't be cracked. They can be vulnerable to some attacks (dictionary, rainbow tables, etc.). One of the simple examples of this sort of cracking is that attackers can generate hashes for the group of simple, common passwords and store this "password-hash" mapping in the table. Thus using this table they can try to crack users' passwords by comparing hashes from that mapping table with the password hashes of users in case users' data becomes available for the attacker. The weaker the password is (simple, small, etc.), the easier it can be cracked. So, using strong passwords and using the strongest encryption algorithm will minimize the risks.

There is also a way to make a stronger hash, even if the user chooses a weak password. It is a hash generated from the combination of a password and randomly generated text. This randomly generated text is called a salt in cryptography. In this case the attacker should spend incomparably more time, because he/she should also consider the salt for cracking. Salt should be unique for each user, otherwise if two different users have the same password, their password hashes also will be the same and if their salts are the same, it means that the hashed password string for these users will be the same, which is risky because after cracking one of the passwords the attacker will know the other password too.  By using different salts for each user, we can avoid these kinds of situations.

Let's alter our table and the stored procedure to use a salt in the password encryption. We use UNIQUEIDENTIFIER for a salt, because it's randomly generated and a unique string.

ALTER TABLE dbo.[User] ADD Salt UNIQUEIDENTIFIER 
GO

ALTER PROCEDURE dbo.uspAddUser
    @pLogin NVARCHAR(50), 
    @pPassword NVARCHAR(50),
    @pFirstName NVARCHAR(40) = NULL, 
    @pLastName NVARCHAR(40) = NULL,
    @responseMessage NVARCHAR(250) OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @salt UNIQUEIDENTIFIER=NEWID()
    BEGIN TRY

        INSERT INTO dbo.[User] (LoginName, PasswordHash, Salt, FirstName, LastName)
        VALUES(@pLogin, HASHBYTES('SHA2_512', @pPassword+CAST(@salt AS NVARCHAR(36))), @salt, @pFirstName, @pLastName)

       SET @responseMessage='Success'

    END TRY
    BEGIN CATCH
        SET @responseMessage=ERROR_MESSAGE() 
    END CATCH

END
	

Then we truncate the table and run the procedure again:

TRUNCATE TABLE [dbo].[User]

DECLARE @responseMessage NVARCHAR(250)

EXEC dbo.uspAddUser
          @pLogin = N'Admin',
          @pPassword = N'123',
          @pFirstName = N'Admin',
          @pLastName = N'Administrator',
          @[email protected] OUTPUT

SELECT UserID, LoginName, PasswordHash, Salt, FirstName, LastName
FROM [dbo].[User]
	

And the result will be as follows:



encrypted password with salt

Please note, that salt is stored in the table with plain-text, there is no reason to encrypt it. Now let's create a simple procedure to authenticate the user using an encrypted password with the salt:

CREATE PROCEDURE dbo.uspLogin
    @pLoginName NVARCHAR(254),
    @pPassword NVARCHAR(50),
    @responseMessage NVARCHAR(250)='' OUTPUT
AS
BEGIN

    SET NOCOUNT ON

    DECLARE @userID INT

    IF EXISTS (SELECT TOP 1 UserID FROM [dbo].[User] WHERE [email protected])
    BEGIN
        SET @userID=(SELECT UserID FROM [dbo].[User] WHERE [email protected] AND PasswordHash=HASHBYTES('SHA2_512', @pPassword+CAST(Salt AS NVARCHAR(36))))

       IF(@userID IS NULL)
           SET @responseMessage='Incorrect password'
       ELSE 
           SET @responseMessage='User successfully logged in'
    END
    ELSE
       SET @responseMessage='Invalid login'

END
	

And we can test it as follows:

DECLARE	@responseMessage nvarchar(250)

--Correct login and password
EXEC	dbo.uspLogin
		@pLoginName = N'Admin',
		@pPassword = N'123',
		@responseMessage = @responseMessage OUTPUT

SELECT	@responseMessage as N'@responseMessage'

--Incorrect login
EXEC	dbo.uspLogin
		@pLoginName = N'Admin1', 
		@pPassword = N'123',
		@responseMessage = @responseMessage OUTPUT

SELECT	@responseMessage as N'@responseMessage'

--Incorrect password
EXEC	dbo.uspLogin
		@pLoginName = N'Admin', 
		@pPassword = N'1234',
		@responseMessage = @responseMessage OUTPUT

SELECT	@responseMessage as N'@responseMessage'
	

test encrypted password storage
Conclusion

Storing passwords in an encrypted way in the database and using unique salts for passwords, decreases the risks that passwords can be cracked. The SQL Server UNIQUEIDENTIFIER data type is a good candidate for a salt taking into consideration its uniqueness and randomness.

Next Steps


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 6 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Send me SQL tips:

    



Monday, March 12, 2018 - 12:11:28 AM - hada Back To Top

which datatype is used  to take special symbols from user in sql,as i want to take password?

 


Sunday, March 04, 2018 - 1:49:49 PM - Sergey Gigoyan Back To Top

Samuel, if some 'DB guy' has direct access to the database, especially full access, he can do everything there, but this is another story. By the way, for instance, if this hacker has stolen some company's database and restored it, he cannot see users' passwords there.


Saturday, March 03, 2018 - 11:18:22 PM - Samuel Back To Top

 Good Article Sergey - But,keep in mind though, a DB guy can still hack the above solution.He can create his own hash and Salt and update the user table, therby having access to the login.

This is the precise reason why salt and hash should not be kept together.

 Below is how this can be hacked

------------------------------------------

   DECLARE @salt UNIQUEIDENTIFIER=NEWID()

DECLARE @pPassword NVARCHAR(50) ='Hacker'

   print  HASHBYTES('SHA2_512', @pPassword+CAST(@salt AS NVARCHAR(36)))

   print @salt

 

update dbo.[User] set PasswordHash = <<above hash>>

salt = 'above salt'

where LoginName ='Admin'

 

 


Monday, August 28, 2017 - 12:19:17 PM - RED Back To Top

 

Hi,

Hope you can help me, i always get null userid and incorrect password when calling the sp uspLogin...

I am trying to update existing password here...

Many many thanks


Monday, June 19, 2017 - 9:21:59 AM - Jai Back To Top

This is not working. I have implemented same logic in my web application developed in .net and tried calling your uspLogin which is always returning null.

I have queried and found the data stored and return both are different.

 

SELECT PWD from Users

Union

SELECT HASHBYTES('SHA2_512',N'[email protected]'+CAST(Salt AS NVARCHAR(36))) from Users 

 

Result:

0x74F6CE0F9F27146384DFD2F7A63C716AE30C296D632D3BB1B15D6AD21B3D0030ADEAE62D00C64D3A692F2F6687603933AEC6E736423BDEA7590A627DD332FA4E
0x9888626DCFF51EDB445FCC91E56385B75FE1813E9889AA36FE601D8FAB741F8B125FEFDC309E55D4FA2EB371624B6FAE884C49451A5E1F1A381440456EA96E90

 

Please provide the information how to fix it / whether i am doing anything wrong?

 

Thanks & Regards,

Jai


Tuesday, February 21, 2017 - 3:37:00 AM - Karl Bergen Back To Top

 very good article, short and precise, good solution for actual problem

 


Monday, February 06, 2017 - 2:45:12 PM - leroux Back To Top

 

THANKS !!!!


Monday, January 30, 2017 - 11:41:45 AM - SqlMel Back To Top

Good artile. Very helpful. Thanks!


Tuesday, August 30, 2016 - 10:41:08 AM - Suresh Back To Top

 

 Nice article

when i  execute the above script the password showed as NULL . i am using sql 2012 


Wednesday, July 27, 2016 - 12:45:36 AM - sohan yadav Back To Top

 

create table e1(userid varchar(20),password1 varbinary(max));

insert into e1 values('[email protected]',HASHBYTES('sha1','1234'))

 

decrpt this password and please provide the query in detail.

Thanks in advance


Friday, July 08, 2016 - 10:58:37 PM - Christopher Quinn Back To Top

 I was just starting to map out my capstone project and was trying to see best way to encrypt users passwords thanks

 


Wednesday, June 15, 2016 - 2:14:14 PM - Prashant Gupta Back To Top

 Thanks a lot! Very easily explained and straight forward to implement.

 


Thursday, May 26, 2016 - 2:10:31 AM - Pablo Back To Top

 Thank you so much! Awesome step by step!

 

 

 


Wednesday, May 11, 2016 - 2:16:04 PM - [email protected] Back To Top

Another thing to consider is adding another two salt components.   First, a component not stored in the DB, but passed into the check routines.  I'd suggest a string stored in a config file.   Second, include something unique to the row in the salt.  Something as simple as the row ID.   This prevents developers from simply copying a known password's encrypted value and salt from one row to another to gain access to the target row.


Sunday, April 17, 2016 - 8:16:15 AM - Clyde Back To Top

Sorry:

Silly question: Answer is: Encode the loginname


Sunday, April 17, 2016 - 7:58:04 AM - Clyde Back To Top

Hi:

Thank you for this helpful article. It appears however that this approach leaves a glaring security hole; i.e., encodedpasswords passwords of identifiable usernames (say a lower level user) can be copied over an encoded password of an Admin thereby giving an "internal" attacker access to admin level functions if the client application is so designed. Any suggestions?


Monday, March 21, 2016 - 1:38:42 PM - Sergey Gigoyan Back To Top

Hermawan,

You cannot decrypt data which has been encrypted using HASHBYTES, it is a one-way encryption. You can store answers to questions for password reset in the same way as passwords (one-way encryption using salt), because you will not need to decrypt these answers, you only need to compare them with an answer hash provided by user (like passwords). However you need to show questions to users, so you cannot use one-way encryption for securing questions. In this case you can encrypt questions using symmetric keys, and decrypt them when needed.

Thanks

 


Saturday, March 19, 2016 - 12:27:30 AM - Hermawan Back To Top

Nice article. this was i looking for. now password in my database more secure than before. but how to convert from binary values which added along with salt to nvarchar or string that inserted in table user? like 1234. I am developing an application if user forgot the password for instance someone was forgot the password but he/she was already key in recover password such as question and answer to get his/her password. thank you very much.


Tuesday, October 13, 2015 - 4:08:19 AM - UnoTurbo Back To Top

BCrypt is a better choice for storing passwords for many many reasons.  The only problem is that SQL Sever doesn't support it.  The good news is that you can add it using a CLR function.  Here is an article that gives you the CLR and the functions to encrypt and check the encrypted passwords:

http://blog.tcs.de/using-the-bcrypt-hash-algorithm-in-ms-sql-server/


Monday, October 12, 2015 - 10:14:29 AM - Wim Back To Top

U can use a CLR and ecnrypt it like that with SHA2even in older versions of sql server.


Friday, October 09, 2015 - 3:11:22 PM - Sergey Gigoyan Back To Top

I agree Steve, of course the security can be increased the way you suggested. I just chose to address  only database side security in this article.

 


Friday, October 09, 2015 - 8:58:36 AM - Steve Hood Back To Top

Wouldn't you want to do the encryption on the application layer to avoid the password being visible as much as possible?  For instance, we're encrypting it at rest so if someone gains access to our tables then they can't see the passwords.  However, we're not encrypting it in transit, so if someone gains access to run Profiler or Extended Events then they can get the RPC Completed event that has the plain text password in it.

Keeping a salt in the database is still a good idea where the application can query the database to get the salt for a specific username.  It can be used one of two ways.  First, query the database for the salt for a specific user, use the application to encrypt the password, then query the database a second time to compare the hashed passwords.  Second method would be to query the database for both the salt and the encrypted password and do all the comparisons in the app with that single database call.

This makes sense to me.  However, I'm not a security expert and there may be reasons not to do it one of these ways.


Friday, October 09, 2015 - 6:15:47 AM - Greg Robidoux Back To Top

Thanks Scott, the tip has been updated.


Friday, October 09, 2015 - 4:45:36 AM - Scott Back To Top

It's a small point, but in paragraph 4 you refer to the function as HASHBITES, not HASHBYTES.


Friday, October 09, 2015 - 3:04:06 AM - dd Back To Top

Doesn't SQL have bcrypt or any other crypt function? Hashing algorithms are ridiculously fast and hence are easy to brute force. For storing passwords you need a slow one way crypting algorithm like bcrypt.

But using sha512 is better than nothing I suppose.


Friday, October 02, 2015 - 8:58:57 AM - Diego Miguez Back To Top

@Sergey / @Wayne: Ok, thank you very much for the prompt response !!


Friday, October 02, 2015 - 4:21:28 AM - Sergey Gigoyan Back To Top

Diego,

Storing salt in the database with plain text is not vulnerable, because attackers can't decrypt password using salt. Password Hash is generated by applying encryption function to combination of password and salt: HASHBYTES('SHA2_512', @pPassword+CAST(@salt AS NVARCHAR(36))), so having only the salt he can't find the password.

Thursday, October 01, 2015 - 5:11:49 PM - Wayne Back To Top

@Diego: I guess you need to look at the perspective of where does your system get compromised.  The password cracking that's possible with modern machines running multiple graphics cards and multiple permutations of rainbow tables can rip apart hashes fairly easily.  I read a recent article about the work involved in recovering Ashley Madison passwords: one security researcher had plateued when he decided to download 10,000 books from Project Gutenberg, then his recovery rate started increasing again.

If the data stream between users and the database server gets intercepted, they won't have access to the salt since that's hidden in the user table.  If a copy of the database is obtained, assuming the database and the backup are not encrypted, then you're in trouble, and where the salt is stored doesn't matter.

Personally, I would include a pre-salt of the server name + the database name + the user name as part of the hash in addition to the GUID, I would also encrypt the salt GUID to make it harder for a hacker to figure out my process without getting my source code or forcing them to reverse-engineer my login system.  This also means that a data thief would have to precisely duplicate environment for the database to make the password hashes usable without going to a lot of work.  If there was a way of doing non-reversible code in T-SQL, perhaps in CLR or Powershell(?), I'd use that, I'm not familiar with using certificates with stored procs so that might be a possiblity.  But NEVER use the encryption option on stored procedures, it's not difficult to reverse.


Thursday, October 01, 2015 - 12:34:02 PM - Sergey Gigoyan Back To Top

Hello Jim and Greg,

I agree with you. This part of article has been already edited. Thank you for your remark and sorry for misunderstanding.


Thursday, October 01, 2015 - 11:12:53 AM - Diego Miguez Back To Top

I want to know if it is good practice to store the salt in the table as a column. In this way, you are paving the way would be the attacker, is that correct?

If not, what other options are there?

Thank you.


Tuesday, September 29, 2015 - 4:16:26 PM - Greg Robidoux Back To Top

I think what Jim is saying makes sense.  If someone passes in a valid login ID and the correct password the system will still lookup the correct entry and allow that person access to the system. 

This is probably pretty unlikely that both logins 1234 and 1243 will have the same password.


Tuesday, September 29, 2015 - 2:25:14 PM - Jim Back To Top

I disagree. If at the login prompt I type "1243" as my user name (I'm "1234") and type "password" as my password, the system is going to let me in. The combination of 1243/password is a valid combination, regardless of salting. From the login prompt side of things, salting doesn't have any clue that I'm not 1243 doing a legitimate login.

From the database side of things, the random-ish salts do provide the added layer of defense that makes hash comparisons more difficult. Just not from the login side.


Tuesday, September 29, 2015 - 12:47:41 PM - Sergey Gigoyan Back To Top

Hello Jim,

Yes, users have the same passwords, but thanks to salting mechanism, their password hashes are different (the salt is unique), and upon authentication we check PasswordHashes (not passwords: see dbo.uspLogin stored procedure in the article). So, wrong user can't be authenticated.


Tuesday, September 29, 2015 - 9:27:43 AM - Jim Back To Top

One correction needs to be made to the article. If user "1234" and "1243" each have the same password, and user "1234" fat-fingers "1243" as the user name, they will be able to authenticate as the wrong user regardless of the salting mechanism used. They have the same password.

Otherwise, nice article.


Tuesday, September 29, 2015 - 9:26:39 AM - Sergey Gigoyan Back To Top

Thank you for reading Mahesh.


Tuesday, September 29, 2015 - 4:15:21 AM - Mahesh Back To Top

Nice article thanks for give me oppurtinty to learn such things .


Learn more about SQL Server tools