Storing passwords in a secure way in a SQL Server database

By:   |   Comments (45)   |   Related: 1 | 2 | 3 | > Encryption


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.


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:

    LoginName NVARCHAR(40) NOT NULL,
    PasswordHash BINARY(64) NOT NULL,
    FirstName NVARCHAR(40) NULL,
    LastName NVARCHAR(40) NULL,

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

    @pLogin NVARCHAR(50), 
    @pPassword NVARCHAR(50), 
    @pFirstName NVARCHAR(40) = NULL, 
    @pLastName NVARCHAR(40) = NULL,
    @responseMessage NVARCHAR(250) OUTPUT


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

        SET @responseMessage='Success'

        SET @responseMessage=ERROR_MESSAGE() 


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',
          @responseMessage=@responseMessage OUTPUT

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.


    @pLogin NVARCHAR(50), 
    @pPassword NVARCHAR(50),
    @pFirstName NVARCHAR(40) = NULL, 
    @pLastName NVARCHAR(40) = NULL,
    @responseMessage NVARCHAR(250) OUTPUT


        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'

        SET @responseMessage=ERROR_MESSAGE() 


Then we truncate the table and run the procedure again:


DECLARE @responseMessage NVARCHAR(250)

EXEC dbo.uspAddUser
          @pLogin = N'Admin',
          @pPassword = N'123',
          @pFirstName = N'Admin',
          @pLastName = N'Administrator',
          @responseMessage=@responseMessage 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:

    @pLoginName NVARCHAR(254),
    @pPassword NVARCHAR(50),
    @responseMessage NVARCHAR(250)='' OUTPUT



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

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


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

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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, November 15, 2023 - 11:48:23 PM - Joel Back To Top (91761)
To make the database password more secure in the event of a database hack, we can create several columns with the email address and password (the email address should also be encrypted in my opinion), e.g.: columns from email_1 to email_7 and columns from password_1 to password_7. The password can be stored in one of the email columns and the email address in one of the password columns. In the remaining six we can store an encrypted random string of characters. Secondly, when hashing, we can use various hashing functions, e.g. our password = $password,

$password = hash('sha384', $password);
$password = hash('sha256', $password);
$password = hash('sha512', $password);
$password = hash('guest', $password);
$h1 = hash('md5', $password);
$h2 = hash('md5', $h1);
$h3 = hash('md5', $h2);
$h4 = hash('md5', $h3);
$hash_db = $h1 . $h2 . $h3 . $h4;

We store $hash_db in the database. The hash is 128 characters long, it looks like the sha512 algorithm is used.

Monday, December 30, 2019 - 11:41:37 AM - Dave Buttons Back To Top (83560)

Hashing is fine if you want to check that a *user* has *entered* a correct password, but what if you want to do something a little more exotic than that.

Say you had an application that connected out to an FTP site, checked for a new file and download it if it finds one.

Say you wanted to run that application every 15 minutes, and store the credentials for the FTP site in a database. How would you store it encrypted, but still be able to retreive it and decrypt it?

Thursday, September 12, 2019 - 7:15:46 PM - Greg Back To Top (82395)
--Incorrect password
EXEC	dbo.uspLogin
		@pLoginName = N'Admin', 
		@pPassword = N'1234',
		@responseMessage = @responseMessage OUTPUT

SELECT	@responseMessage as N'@responseMessage'

The test code above shows 'User successfully logged in'

Wednesday, December 12, 2018 - 5:00:38 PM - Will B. Back To Top (78474)

Good article.  Thank you.  Question:  Would changing the salt anytime they change their password be a good idea? Or is that even necessary?

Saturday, November 24, 2018 - 5:09:29 PM - Anthony Back To Top (78319)

If anyone is still getting @userID returning NULL I changed the argument in the signature from @Password NVARCHAR(50) to VARCHAR(50) and it started returning the userID.

Tuesday, November 6, 2018 - 3:27:30 PM - Laeark Back To Top (78173)

Excellent Article! 👍🏼

Wednesday, September 26, 2018 - 3:11:05 AM - Michael Lowden Back To Top (77727)

 I read somewhere that the "salt" must be greater than the resulting bit-size of the hash. Thus, for my implementation (similar to yours) I used:

DECLARE @Salt AS CHAR(73) = (


         UPPER( CONVERT( [varchar](36), newid() ) )


        ,LOWER( CONVERT( [varchar](36), newid() ) )



.... @Salt == 4DF8B82A-EE2F-4A2D-A804-F8CF3A67ADAE:6f0178f6-2807-4761-aa5d-0beed1c722b9

the ':' just adds a little flavour to the SALT.


Then thrown into the HASHBYTES clause:

DECLARE @Password_PLAIN AS NVARCHAR(256) = 'Pa$$w0rd1';


.... @Password_HASHED 'Pa$$w0rd1' == 1946A66640367DF46B8656F63EE8C4E41C17D141F495DFE31D16836213724B58

.... @Password_HASHED 'Pa$$w0rd2' == 16B9C8B35F93A2B5F9E4021C7178EC5A4CD6F416AEE4F0505763FA1DB6995064

Tuesday, August 7, 2018 - 8:38:37 AM - Simon Back To Top (77040)

 how can I block Users who try to log in more than 3 times?




Friday, April 27, 2018 - 7:25:13 AM - Oystein Back To Top (75800)

 Great stuff, working great for me, but I have a problem with password Update, do you have code for a procedure for that?


Thank :-)


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

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


Sunday, March 4, 2018 - 1:49:49 PM - Sergey Gigoyan Back To Top (75346)

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 3, 2018 - 11:18:22 PM - Samuel Back To Top (75344)

 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 @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 (65537)



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 (57738)

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


SELECT HASHBYTES('SHA2_512',N'Reset@123'+CAST(Salt AS NVARCHAR(36))) from Users 





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


Thanks & Regards,


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

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


Monday, February 6, 2017 - 2:45:12 PM - leroux Back To Top (46059)



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

Good artile. Very helpful. Thanks!

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


 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 (42979)


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

insert into e1 values('sohan@123',HASHBYTES('sha1','1234'))


decrpt this password and please provide the query in detail.

Thanks in advance

Friday, July 8, 2016 - 10:58:37 PM - Christopher Quinn Back To Top (41848)

 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 (41703)

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


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

 Thank you so much! Awesome step by step!




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

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 (41247)


Silly question: Answer is: Encode the loginname

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


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 (41012)


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.



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

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 (38869)

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:

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

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

Friday, October 9, 2015 - 3:11:22 PM - Sergey Gigoyan Back To Top (38853)

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 9, 2015 - 8:58:36 AM - Steve Hood Back To Top (38851)

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 9, 2015 - 6:15:47 AM - Greg Robidoux Back To Top (38849)

Thanks Scott, the tip has been updated.

Friday, October 9, 2015 - 4:45:36 AM - Scott Back To Top (38848)

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

Friday, October 9, 2015 - 3:04:06 AM - dd Back To Top (38847)

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 2, 2015 - 8:58:57 AM - Diego Miguez Back To Top (38811)

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

Friday, October 2, 2015 - 4:21:28 AM - Sergey Gigoyan Back To Top (38809)


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 1, 2015 - 5:11:49 PM - Wayne Back To Top (38806)

@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 1, 2015 - 12:34:02 PM - Sergey Gigoyan Back To Top (38802)

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 1, 2015 - 11:12:53 AM - Diego Miguez Back To Top (38801)

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 (38785)

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 (38784)

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 (38783)

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 (38780)

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 (38779)

Thank you for reading Mahesh.

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

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

get free sql tips
agree to terms