When it comes to password security, it’s not just about choosing strong passwords. Storing passwords securely in a database is equally important. In this article, we will discuss how to encrypt and store passwords in a SQL Server database to ensure maximum security.

One-Way Hashing Algorithms

To encrypt passwords, we will use one-way hashing algorithms. These algorithms generate an encrypted output for a given input value, but there is no decryption algorithm. This means that it is impossible to revert to the original value using the encrypted output.

In SQL Server, the HASHBYTES function can be used to generate a hash for an input value using various algorithms such as MD2, MD4, MD5, SHA, SHA1, SHA2_256, and SHA2_512. For our example, we will choose the strongest algorithm – SHA2_512.

Creating the User Table

Let’s assume we need to create a table to store user data, including their login name, encrypted password hash, first name, and last name. We can create the table using the following SQL statement:

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

Creating the Stored Procedure

We will also create a stored procedure to insert user data into the table. The stored procedure takes the login name, password, first name, and last name as input parameters. Inside the stored procedure, we will use the HASHBYTES function to encrypt the password before inserting it into the table.

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

Encrypting and Storing Passwords

To encrypt and store passwords, we can execute 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

SELECT *
FROM [dbo].[User]

As you can see, the password’s text is unreadable in the database, ensuring that even if an attacker gains access to the database, they cannot easily steal users’ passwords.

Enhancing Security with Salts

Although encrypting passwords provides a significant level of security, there are still vulnerabilities, such as dictionary and rainbow table attacks. To further enhance security, we can use salts.

A salt is a randomly generated text that is combined with the password before hashing. This makes it much more difficult for attackers to crack passwords, even if they have access to the hashed passwords.

In our example, we will alter the user table and the stored procedure to include a salt column and generate a unique salt for each user:

ALTER TABLE dbo.[User] ADD Salt UNIQUEIDENTIFIER 

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

By using different salts for each user, we can avoid situations where two users with the same password have the same hashed password string. This significantly reduces the risk of an attacker cracking multiple passwords.

Authentication with Encrypted Passwords and Salts

To authenticate a user, we can create a stored procedure that checks if the login name and password match the stored encrypted password and 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 LoginName=@pLoginName)
    BEGIN
        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'
       ELSE 
           SET @responseMessage='User successfully logged in'
    END
    ELSE
       SET @responseMessage='Invalid login'

END

We can test the authentication procedure 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'

By securely storing passwords in an encrypted form and using unique salts for each user, we can significantly reduce the risk of password cracking. The use of the SQL Server UNIQUEIDENTIFIER data type for salts ensures uniqueness and randomness, further enhancing security.