XP_CRYPT Online Help - Advanced tutorial

This tutorial demonstrates how to encrypt important data stored in your MSSQL Server database with strong, hacker-proof RSA encryption. All code showed in examples is written with native MSSQL language - T-SQL. Here you can find all ready-to-use scripts, and you can start working with them immediately. It shows you how to work with new XP_CRYPT in the most effective way and how to use it in triggers and for searching encrypted values.

If you need a free copy of XP_CRYPT tool for MS SQL Server, you can get it here

The last version of the XP_CRYPT introduces new features, such as bulk encryption and password caching. This tutorial teaches how to get the maximum performance from using it with INSTEAD OF triggers and user defined functions.

Bulk encryption is the way of encrypting multiple variables (even with different types) into one encrypted block. By using this method, you achieve an increase in the speed of encryption performance and greatly reduce space required for the encrypted values. RSA encryption adds some random data (padding) to every variable to make bruteforce decryption harder. Besides, by the nature of RSA encryption and base64 encoding the minimum length of the output value will be more then the length of the used key in bytes. For example if you use 512 bit key the minimum output will be longer then 512 / 8 = 64 chars. With bulk encryption you can encrypt many values at once so the overhead will be much smaller.

Another newly introduced feature is password caching. This gives you an ability to work with encrypted tables as if they were not encrypted. Your SQL queries do not need to be modified! You should only specify the password once at the beginning of the session and that's all. You can write decrypting stored procedures and triggers without providing the correct password! Earlier, the only choice was 'WITH ENCRYPTION' tag on such triggers or procedures, but this is not convenient because this code becomes non-editable.

Let's consider a real life example - protecting table that stores some valuable information.

This tutor assumes that you are using a free version of XP_CRYPT that's why the number is small and values of the variables are short. The full version does not have such size limitations

Creating key pair

Creating key pair is simple and fast:

DECLARE @PrivateKey  VARCHAR (10)
DECLARE @PublicKey  VARCHAR (10)
    
EXEC master..xp_rsa_generate_couple '256', @PrivateKey OUTPUT, @PublicKey OUTPUT
EXEC master..xp_rsa_save_key @PublicKey , 'c:\xp_crypt\demo\publickey.pem'
EXEC master..xp_rsa_save_key @PrivateKey , 'c:\xp_crypt\demo\privatekey.pem', 'xp_crypt'
          
EXEC master..xp_rsa_free_key @PublicKey
EXEC master..xp_rsa_free_key @PrivateKey
GO

A pair of 256 bits RSA keys is created and saved into two separate files. After saving, it was unloaded from memory. Places, where you need encryption only, you can have just a public key. For decryption, you must have a private key and a password for loading it.

Creating source table

We will use a sample table called users. It has the following fields:

user_id INT
username VARCHAR(5) 
secretvalue SMALLINT

Here is an SQL script to create such table

CREATE TABLE [dbo].[users] (
        [user_id] [INT] IDENTITY (1, 1) NOT NULL ,
        [username] [VARCHAR] (5) NOT NULL ,
        [secretvalue] [SMALLINT] NOT NULL 
         ) ON [PRIMARY]
GO

Let's populate the table with some data

INSERT INTO users(username,secretvalue) VALUES ('james',1234)
INSERT INTO users(username,secretvalue) VALUES ('lucas',1976)
INSERT INTO users(username,secretvalue) VALUES ('anna',1979)

Suppose now, that in real life the table we have has much more fields associated with a user and the table is quite big. So, you choose not to encrypt each value separately, but encrypt them all at once.

Suppose also that in future your system will have to search users by their names or emails or by any other field. In our simple example it will be a username field.

The problem with a search is that SQL server would like to have data decrypted when it searches for clear text data. It seems that the only way to do it is to decrypt data and compare it with a search value. Now what if we have a table with million records? The search on this table with this method will be incredibly slow. Luckily, there is a better approach - using hashes. We must store the hash of the clear text values, as they make an index on this hash. Later, we will use this field as a search criterion.

Creating table for encryption

Now, assuming all of the above, let's create a table that holds encrypted information.

We will keep user_id unencrypted,
username and secretvalue fields must be encrypted.
Searching field username must be possible
Insertion and select statements must be as simple as possible

Here is a table we created that meets all of the above requirements:

CREATE TABLE [dbo].[users_enc] (
        [user_id] [INT] IDENTITY (1, 1) NOT NULL ,
        [encryptedvars] [VARCHAR] (60) NOT NULL ,
        [username_srch] [CHAR] (7) NOT NULL 
         ) ON [PRIMARY]
GO

To simplify the work with the encrypted tables in future we must do some additional work now. The best way would be to add view here and define INSTEAD OF INSERT trigger.

Creating view

For now we created a simple view without decryption (we will improve it later).

CREATE VIEW dbo.users_enc_view
AS
SELECT user_id, username ,secretvalue FROM dbo.users
GO

Creating encryption trigger

This view currently shows decrypted values. Currently we do not have decryption functions here but we need to insert values with the correct size and type so that SQL Server can handle them correctly. Now, let's add INSTEAD OF INSERT trigger so that all values that we insert to this view are automatically encrypted with our public key.

CREATE TRIGGER encrypt_fields ON [dbo].[users_enc_view] 
INSTEAD OF INSERT
AS
   INSERT users_enc(encryptedvars,username_srch)
   SELECT dbo.encrypt_pair(username,secretvalue),dbo.make_short_hash(username) FROM INSERTED
GO

This trigger modifies the data passed by inserting command; also it inserts modified values into users_enc table. It can handle single and multiply inserts

Now, the encrypt_pair UDF used in the last trigger will take a pair of values VARCHAR and SMALLINT and output VARCHAR(60) as encrypted string. Change path to the one you generated previously. Note, that the first char of the path is < symbol - do not remove it. It gives a command to the encryption function that it must load the key from the file itself. .

CREATE FUNCTION encrypt_pair (@username VARCHAR(5) , @secretvalue SMALLINT ) 
RETURNS VARCHAR (60)  AS 
BEGIN 
   DECLARE @encryptedvars VARCHAR(60)
   EXEC master..xp_rsa_pub_enc @username,@secretvalue,'<c:\xp_crypt\demo\publickey.pem',
        @encryptedvars OUTPUT
   RETURN  @encryptedvars 
END

Another UDF that we've used in encrypted_fields is make_short_hash. We will use this function to produce hashes for search on encrypted values. This function returns a part of hash to make bruteforcing harder. More about search you will find in the end of tutorial.

CREATE FUNCTION make_short_hash (@name VARCHAR(100)) 
RETURNS CHAR(2) AS 
BEGIN 
   DECLARE @hash VARCHAR (32)
   EXEC master..xp_md5 @name,@hash OUTPUT
   RETURN substring (@hash,1,2)
END

Testing encryption

From now on, users_enc_view can handle inserts, encrypt them and store them in the table users_enc. Here we copy and encrypt on-the-fly all old table users into users_enc_view. Remember that the view virtually represents the actual storage place - table users_enc

INSERT INTO users_enc_view([user_id],username,secretvalue) SELECT * FROM users

Or you can try to fill out the table manually:

INSERT INTO users_enc_view(user_id,username,secretvalue) VALUES (-1,'james',12345)
INSERT INTO users_enc_view(user_id,username,secretvalue) VALUES (-1,'lucas',1976)
INSERT INTO users_enc_view(user_id,username,secretvalue) VALUES (-1,'anna',1979)

Note that we still have to mention some unused fields like user_id even if they are not used inside the trigger. Let's take a look what we have in the encrypted table now

SELECT * FROM users_enc

user_id	encryptedvars	username_srch
1	DiHCcdpiVPhvOFOE8sQNquF8OhrKB8L6x5DZwWKieV0=	b4
2	DfTpwx686yndRNUh6wFDBNH7NO+Tx9jNEFVPSYBoGqw=	dc
3	lyTjLNXaNFzy+7x8p1p4OJLM+jq9xtYjF7dvrgk7jxs=	a7

In the future, we will select decrypted information from the same view we insert data from. Currently, this view does not have a decryption trigger.

You will have different encryptedvars values, but username_srch field should be the same. We intentionally do not use the whole hash value in the trigger because this would be a possible security leak. Someone could use the hash value for bruteforcing. When we cut off a part of hash, we increase the number of possible collisions but also it increases the number of fake "successful" combinations. You should experimentally set this length according to the size of your table. The larger table you have and the quicker and sharper search you need, the longer hash you should use.

Part 2

Creating decryption functions

Now let's modify the view to decrypt data from this table. Remember, in a view you can not use a complicated login like the ones in user defined functions or procedures. Only select a valid statement. We have 2 different types of encrypted variables - SMALLINT and VARCHAR. First, we need 2 different UDFs, the first one decrypts the string and returns a first variable and the second one returns the second variable.

CREATE FUNCTION username_decrypt (@encryptedvars VARCHAR(60))
RETURNS SMALLINT AS 
BEGIN 
   DECLARE @result INT
   DECLARE  @k VARCHAR (10)
   DECLARE @username VARCHAR(5)
   DECLARE @secretvalue SMALLINT
   EXEC @result =master.. xp_rsa_priv_dec @encryptedvars, '<c:\xp_crypt\demo\privatekey.pem',
      @username OUTPUT, @secretvalue OUTPUT,'xp_crypt'
   IF @result = 0 
   BEGIN
      RETURN @username
   END
   RETURN NULL
END

And the second function

CREATE FUNCTION secretvalue_decrypt (@encryptedvars VARCHAR(60))
RETURNS SMALLINT AS 
BEGIN 
   DECLARE @result INT
   DECLARE  @k VARCHAR (10)
   DECLARE @username VARCHAR(5)
   DECLARE @secretvalue SMALLINT
   EXEC @result =master.. xp_rsa_priv_dec @encryptedvars, '<c:\xp_crypt\demo\privatekey.pem',
           @username OUTPUT, @secretvalue OUTPUT,'xp_crypt'
   IF @result = 0 
   BEGIN
        RETURN @secretvalue
   END
   RETURN NULL 
END

It is not really effective to perform decryption for each variable, we can decrypt everything at once, but if we want to have it with a view, we have to do it in this way. Alternatively, if having decryption in view is not important for you, you can put decryption in the stored procedures and use cursors

Modifying the view

Now, we modify the view and change the old selected unencrypted values with our new functions. You can modify view from enterprise manager by clicking on view users_enc_view and choosing 'Design view' menu. Replace old statement with the new one:

SELECT user_id,  dbo.username_decrypt(encryptedvars) AS username, 
        dbo.secretvalue_decrypt(encryptedvars) AS secretvalue FROM dbo.users_enc

Now, select from this view

SELECT * FROM users_enc_view

If you did everything correctly you should have the output:

user_id	username	secretvalue
1	james	12345
2	lucas	1976
3	anna	1979

Works great!

Applying password caching

A small note about security: if you attentively look into those two decryption functions, you will notice that the password is stored in a clear text there. There are two possible solutions to fix this: the first one is to use WITH ENCRYPTION statement with CREATE FUNCTION to create an encrypted function. The text of this function will be encrypted. However, there are some tools in the Internet that can decrypt such functions quickly. Another problem with this way is that once having encrypted your data you cannot edit the text of this function. You must always keep the text somewhere else, which is not really convenient. Another solution for the problem of the opened password with XP_CRYPT is using a new feature called "password caching". The idea is the following, in publicly visible functions you use password '?'. If the session option 'pswd_cache' is set to 'yes', XP_CRYPT will search for the password for this source in the current session. Passwords are cached during loading and saving keys to string or files. By default, this feature is set to 'no'.

Let's demonstrate how it works. Edit the above functions in your SQL manager so that instead of the password 'xp_crypt' they use password '?'. Try to select it from the view again. You will get NULLs in all encrypted values. Now we need to put password into hash; simply load and free the privatekey:

DECLARE @privatekey VARCHAR (10)
EXEC master..xp_crypt_set_option 'pswd_cache','yes'
EXEC master..xp_rsa_load_key '<c:\xp_crypt\demo\privatekey.pem', @privatekey OUTPUT, 'xp_crypt'
EXEC master..xp_rsa_free_key @privatekey OUTPUT

You have preloaded the password now. Try to select it from view, you should see decrypted values. The session has a default idle timeout of 5 minutes. You can override this value with xp_crypt_set_option function. If the time expires, all keys are unloaded from memory and password cache is cleaned. If you explicitly close the session with xp_crypt_close_session - decryption becomes impossible again.

How to search

As you see, encryption and decryption works, what about search ? It would be nice to have something like

SELECT * FROM users_enc_view where username = 'user'

But unfortunately MSSQL Server does not provide INSTEAD OF SELECT trigger, so the maximum comfort you can achieve with

SELECT * FROM users_enc_view where username_srch = make_short_hash ('user')

We only miss the make_short_hash UDF. Let's create it. We made MD5 hash and used only first 2 chars like we did in encrypt_fields trigger.

Try it now :

SELECT * FROM users_enc_view where username_srch = make_short_hash ('anna')

This statement shows the corresponding record but searching for 'john' will give us nothing. Please do not rely on 2 char hash; it's possible that you get wrong match between a real value and a search value. For this size of table, 2 chars is a reasonable length.

We hope this tutorial helped you to understand how to use XP_CRYPT effectively. Now you can see how these easy methods can greatly improve the data safety.