Salts and UUIDs for your MS SQL Server database - Panos Zafiropoulos

Reading time: 5 Minutes

221102

A concise post for practical implementation steps for UUIDs and Password Salted Hashes when working with an MS SQL Server Database.

Intro

This post is a case of practical implementations of UUIDs and Salts when working with MS SQL Server Databases. The topics that will be discussed are:

Working with the database and creating the demousers table
MS SQL Server UUIDs – Using the built-in uuid() function as column data type
Using Trigger(s)
Using a hash verification stored function

Prerequisites

It is supposed that you understand the basic database concepts, UUIDs, and password salting and hashing. The previous post of mine gives some grasp on those subjects.

Moreover, in order to follow the examples of this post, you must have access to a running MS SQL Server instance and the sqlcmd CLI tool [Here you can find how you can install it in your system as a stand-alone tool]. If you wish you can take a look at my post below on how to create and start using an MS SQL Server container.

Start working with the MS SQL Server, create a new user, and the demousers table

Connect to your running instance of MS SQL Server using the sqlcnd CLI utility, using the right credentials (username and password).

~ sqlcmd -S 192.168.0.84,1443 -U SA -P 'Rpassw!1'

After you have accessed the running instance of the MS SQL Server, you can use the CLI to work with. Let’s start.

You can either create a new database or just connect to your preferable database. Here we will create and use the database named “ticket-management”, e.g.:

> CREATE DATABASE [ticket-management];
> GO
> USE [ticket-management]
> GO

After that, create a new user “user1” with password “Upassw!1” and then assign him all privileges of the ticket-management database. For the MS SQL Server first, we have to create a “login” object. A “login” object grants access to the server and a then a “user” grants a login access to a database. First create a Login:

> CREATE LOGIN [user1] WITH PASSWORD 'Upassw!1'
> GO

Then you can use the following script to check if the login object it has been created:

Below is the output

Now, switch to ticket-management database and create the user1 for that database:

> USE [ticket-management]
> GO

> CREATE USER user1 FOR LOGIN user1
> GO

Then you can use the following script to check if the user1 has been created:

The output is given below:

As a next step we have to add the ‘db_owner‘ role to the user1 owner of the ticket-management database. Note that anyone with the db_owner role can perform all configuration and maintenance activities on the database.

> EXEC sp_addrolemember 'db_owner', 'user1';
> GO

After that, logout and re-login, as user1:

~ sqlcmd -S 192.168.0.17,1443 -U user1 -P 'Upassw!1'

After you have logged in, change to ticket-management database and ensure that you are logged in as user1, and also you are using the tcket-management database. This is what the following SQL script does:

> SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name', DB_name() AS 'database'

Below is the output:

That’s it. Now it’s time to see how we can use UUIDs in the MS SQL Server.

MS SQL Server UUIDs

MS SQL Server offer us a built-in uuid() function which is compliant with RFC4122, and which by default returns a UUIDv4 string. Simply you can execute a Select statement:

Moreover, we can use it quite conveniently as the default value for an id column of a table.[Note, that this possibility is quite similar with the PostgreSQL]. So, there is no need to implement a trigger, with MS SQL Server, to deal with a new UUID for a column. We use this type for the column we wish, e.g. the id column.

Create a new table – the demousers table

So, use the following SQL script to create our demousers table:

You can also verify its schema:

Furthermore, we can add a couple of constrains that might be useful enough later. Actually, we will create a primary key, an index and a unique constraint for the username column:

> ALTER TABLE demousers ADD CONSTRAINT [PK_demousers1] PRIMARY KEY CLUSTERED (id)
> GO

> CREATE NONCLUSTERED INDEX index1 ON demousers (id ASC)
> GO

> ALTER TABLE demousers ADD CONSTRAINT uniques1 UNIQUE NONCLUSTERED (username ASC)
> GO

Then, if you wish, , you can obtain the related info for the demousers, using any of the sql commands provided below:

> EXEC sp_columns demousers
> GO

> EXEC sp_help  demousers
> GO

> SELECT * FROM information_schema.columns  WHERE table_name = 'demousers'
> GO

Great! So, far, so good! Now it’s time to see the effect of the default newid() function on id column, by adding a new user to demousers table:

> INSERT INTO demousers (username, password) VALUES ('panos', 'panospassw1')
> GO

The output is given below:

That’s it! As you can see, the auto-created “9BAEA927-3234-421E-B561-6E948D4FBE5E” UUID has been correctly inserted as the value in id field!

MS SQL Server Hashing and Salting

Now it’s time to create a trigger to do the job. You can read about MS SQL Server triggers at the official documentation.

The important cryptographic functions for hashing and salting offered by MS SQL Server, and which we are going to use here, are:

CRYPT_GEN_RANDOM() – The function is based on the Crypto API (CAPI) and it returns a cryptographic, randomly-generated number.
HASHBYTES() – This is the main hash value generator. It returns a digest (a hash) against the input provided. One can use any of the available algorithms (MD2, MD4, MD5, SHA, SHA1, or SHA2) as parameter.
Other functions we will use are: CONVERT() and SUBSTRING().

Below is the SQL script that creates our trigger:

The logic is similar with the hashing functions we used with other databases. First, we generate a random salt and. Then we add to it the plain-text password and for the whole string we calculate the hash value using the SHA-256 algorithm. Finally, we add the salt in front of the calculated hash and store it as the value of the password field/column.

After the trigger creation, you can check its presence querying the sys_triggers table:

Below it the output:

> SELECT name, create_date FROM sys.triggers WHERE type = 'TR';

Now, we can test the trigger by inserting a new user into the ticket-management table, and then querying the table rows:

> INSERT INTO demousers (username, password) VALUES ('panos2', 'panospassw2')
> GO
> Select * FROM demousers;
> GO

This is the output:

Cool! As you can see above, it works as expected.

Now, let’s proceed to create the verification function.

Password Verification with a stored function

Similar to other databases, here we also we can do that by using a stored function. (You can read more about MS SQL Server stored functions at the official documentation). Below is such an example:

If you wish, you can use the following query to check the stored function has been created:

> SELECT name, definition, type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE type_desc like '%function%';
> GO

Below is the output:

The function returns TRUE (1) if there is a match, or False (0) if the calculated hash does not match. In order to check a given password, we can use the following simple query:

> SELECT dbo.IsPasswordValid('panos2', 'panospassw2');
> GO

And as you can see from the output below, it returns 1 = TRUE, so we have a match!

That’s it all demonstrating how you can start dealing with UUID and salts and hashes in MS SQL Server!

Keep reading other posts of mine for implementation examples of other databases.

That’s it for now! I hope you enjoyed it!
Thanks for reading ? and stay tuned!

About Post Author

Panos

administrator

See author's posts