Home    XP_CRYPT Online Help Prev Next
How To use XP_CRYPT GUI
Introduction
What is XP_CRYPT
System requirements
Free version limitations
End user license agreement
Installing XP_CRYPT
Installation procedure
Activation
XP_CRYPT API
What's new
Encryption methods overview
Session
xp_crypt_set_var
xp_crypt_get_var
xp_crypt_close_session
xp_crypt_init_session
xp_crypt_set_option
Asymmetric keys
DSA
xp_dsa_free_key
xp_dsa_load_key
xp_dsa_save_key
xp_dsa_generate_couple
RSA
xp_rsa_generate_couple
xp_rsa_generate_pub_key
xp_rsa_load_key
xp_rsa_priv_dec
xp_rsa_pub_enc
xp_rsa_save_key
xp_rsa_free_key
Digital signatures
DSA
xp_dsa_verify
xp_dsa_sign
RSA
xp_rsa_sign
xp_rsa_verify
Hashing
xp_crypt
xp_md5
xp_sha1
Symmetric encryption
AES128
xp_aes128_decrypt
xp_aes128_encrypt
AES
xp_aes_decrypt
xp_aes_encrypt
DESX
xp_desx_decrypt
xp_desx_encrypt
RC4
xp_rc4_decrypt
xp_rc4_encrypt
Triple DES
xp_des3_encrypt
xp_des3_decrypt
Error codes
Tutorial
How to use GUI
Advanced technique (Old)
Table encryption (Old)
Troubleshooting
Troubleshooting overview

Step by step table encryption with XP_CRYPT

In this tutorial I will demonstrate how to encrypt sensible data in your MSSQL Server database with strong RSA encryption. All code will be written with native MSSQL language - T-SQL. Here you will find all ready-to-use scripts, and you can start the work immediately.

You need a free copy of xp_crypt tool for MSSQL Server , you can get it here

 Let's consider a real life sample protecting table that stores credit cards. The Sample table called tbl_CCards has the following fields : 

Username    VARCHAR(16) 
Password    VARCHAR(16)
CredCardNum VARCHAR(16)

 This table has already some data.

SELECT * FROM tbl_CCards 

Username Password CredCardNum 
-------- -------- ---------------- 
james    god      1234567890123456 
lucas    sex      2894787650102827 
anna     love     3234563638716434 

   You have to implement protection of user password and credit card numbers. The most secure way of storing passwords is to store their hash, but not the password in a clear text. Hash is an algorithm of getting some magic number FROM the source data. It is impossible to restore the original text FROM the hash except of applying long-term bruteforce calculations. This method is not suitable for credit cards. Instead of this we will use RSA encryption. 

   First of all, we need to extend the field size. It must be enough to hold data in the encrypted form. The size of SHA1 hash, which we will apply for the encryption of the Password field, is 40 bytes. The size of the credit card field encrypted by 256 bit RSA key will be equal to ~50 chars. Luckily, MSSQL Server provides a comfortable way of extending fields just FROM the table designer, all your data remains untouchable. Just type in new field sizes. 

   Our first task is to protect Password field. We will write a small User defined function (UDF) to make it look nice and natively. Please note that UDFs appear only in MSSQL Server 2000. 
   Here is the text of UDF which will take a char string and return its SHA1 hash 

CREATE FUNCTION ud_MakeSHA1 (@clearpass VARCHAR (8000) )
RETURNS VARCHAR (40)
 AS 
BEGIN
DECLARE @ret as VARCHAR(40)
EXEC master..xp_sha1 @clearpass,@ret OUTPUT 
RETURN @ret
END

   After creating this function  we can easily apply hashing to our table : 

UPDATE tbl_CCards SET password = dbo.ud_MakeSHA1(Password)

  Let's see what our table has now: 

SELECT * FROM tbl_CCards 

Username Password                                 CredCardNum 
-------------   ------------------------------------------------------------------   ---------------------------
james    21298DF8A3277357EE55B01DF9530B535CF08EC1 1234567890123456 
lucas    68BB04BD54B8F6C530695E0B77DE298276A0511D 2894787650102827 
anna     9F2FEB0F1EF425B292F2F94BC8482494DF430413 3234563638716434 

As you see, the passwords are hashed. They are replaced with 160 bit value of SHA1 hash. When you do user authorization, you need also make hash of the supplied password and compare it with an already existing one in your database. Here is the sample function which can be used :

CREATE FUNCTION ud_CheckUser (@username VARCHAR(16),@clear_pass VARCHAR (16))
 RETURNS INTEGER
AS BEGIN 
DECLARE @res INTEGER
SELECT @res = count(*) FROM tbl_CCards where username=@username AND password=dbo.ud_MakeSHA1(@clear_pass)
IF @res > 1 SELECT @res= 0
RETURN @res
END

Let's do a simple check: 

SELECT dbo.ud_CheckUser ('anna','fido')

-----------

(1 row(s) affected) 

Another try: 

SELECT dbo.ud_CheckUser ('anna','love')

----------- 

(1 row(s) affected) 

   Many users don't worry too much about online security and input the same passwords on many sites. If one got hacked, be sure that hackers will try to apply the same login passwords on other sites. If you don't store clear passwords, you prevent hacker from reusing login credentials on other sites. Some sites must store passwords in the clear form to send it to a user by email when he forgets it. This is an unsecured practice, it is much better to use question-answer to reauthorize the access in the case of the forgotten password. The answer should be also hashed. Everywhere, where you need only to check, the using of hash is a good practice. 

Let's consider now a little bit more complicated encryption using RSA and applying it to our data. 
First of all, we need to generate and store a couple of keys which will be used further. The public key for encryption and the private key for decryption. This sample script generates those 256 bit length keys. If you plan to store real data, please note that VISA cardholder requirements demand 768 bit keys. The commercial version of XP_CRYPT is able to generate longer keys, but the demo version is limited to 256 bits. 

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:\publickey.pem'
EXEC master..xp_rsa_save_key @PrivateKey , 'c:\privatekey.pem', 'SecurePassword'
EXEC master..xp_rsa_free_key @PublicKey
EXEC master..xp_rsa_free_key @PrivateKey
GO

   Normally, you will do this operation only once, so there is no need to make a special procedure for it. Please don't forget your password or don't loose the private key after you have encrypted some data with it! In this case nobody will be able to help you ! 
   We've generated 256 bits key couple and saved it to files. The private key MUST be stored with a password (at least 4 chars), the public key is stored without any password. After this, we freed the keys. They are getting unloaded from the memory of the SQL Server, but still remain on the disk. If you want to remove your key files FROM the disk, use xp_cmdshell function or just remove it like any other file on your computer. 

   Please don't confuse the values, which you can see when you do "SELECT @PublicKey, @PrivateKey" with real keys. The value in those variables is the handle to the key, which is present in the memory and can be visible only when saved to a file. That handles can not be transferred on another computer. They are only valid is the context of the server where they are loaded. If you need to transfer the key to another server, use xp_rsa_save_key function to dump the key to the file and then copy this file to another computer and use xp_rsa_load_key to load in there. Please keep in mind that if you don't need the key loaded anymore, you should explicitly free it with xp_rsa_free_key function.

  Back to practice. Again, we can create UDF to make our life easier. 

CREATE FUNCTION ud_CCEncrypt (@clear VARCHAR(8000),@preloadedkey VARCHAR(10))
RETURNS VARCHAR(8000)
AS BEGIN
DECLARE @k VARCHAR(10)
DECLARE @crypted VARCHAR (8000)
-- IF @preloadedkey is NULL, then it was not preloaded and we 
-- must load it,use it and free at the end 
IF @preloadedkey IS NULL BEGIN 
   EXEC master..xp_rsa_load_key 'c:\publickey.pem',@k OUTPUT
END ELSE BEGIN 
   SELECT @k=@preloadedkey
END
IF @k IS NOT NULL BEGIN
-- Key is loaded, do encryption now ! 
EXEC master..xp_rsa_pub_enc @clear, @k, @crypted OUTPUT
END
ELSE BEGIN
-- Here we are if key is not loaded by some reason 
-- ( maybe file not found )
SELECT @crypted = @clear
END
-- if key was not given at the BEGINing, we must free it.
IF @preloadedkey IS NULL EXEC master..xp_rsa_free_key @k
-- returning encrypted string
RETURN @crypted
END

 This function encrypts data with the given key. When it's not provided it loads the public key from the file. Loading of the public key does not require any passwords, thus, we can have it open and readable for the whole world. It is impossible to decrypt data with the private key. If you want to encrypt any already existing huge table, it will be more efficient if you preload key, encrypt all data with it , and then free the key. We will do without and with preloading. 

We are ready to go with encryption! 

SELECT dbo.ud_CCEncrypt( CredCardNum,NULL) FROM tbl_CCards 

-------------------------------------------------- 
q+69v+007OBbMn5S4NnVWrkqczyEeCeN853WMnFq+DU= 
sN20xmROxrZmlnT7Lw4v6PN0mLOAKAHTUbpIdSkM8NE= 
nJjx25BpXRSFFmlBQ2lrHD3jBD/VFUlI4rVt/4MzVic= 
(3 row(s) affected)

You will surely get another values. Every time you encrypt even the same source you will get a different result. This is one of the feature of RSA algorithm. 

We see that all works like we expected and now we must update the table. 

UPDATE  tbl_CCards set CredCardNum = dbo.ud_CCEncrypt( CredCardNum,NULL) 

Check the result: 

SELECT username,CredCardNum FROM tbl_CCards 

Username CredCardNum 
-------- --------------------------------------------
james    nTVruKkwzwsu28QNhDbg2Ojf7yH9F60lNMBEEum8p48= 
lucas    AcA29BXmUx7Xpafdk+1DHYJ+qM7xJZ75aoD/S/arSvQ= 
anna     XEXdRily06VygNaq2aHLsyDDtjEJtgYqBYUmfhv3TXw= 

 

   Encryption works! Now , as I promised, a little bit more complicated but much fast way. If you have huge tables, you might want to use this one. They will give the same result, and it's only a question of the efficiency. The trick is that we load the key before update and  UDF don't load the key inside for every row. 

DECLARE @key VARCHAR (10)
EXEC master..xp_rsa_load_key 'd:\publickey.pem',@key OUTPUT 
UPDATE tbl_CCards set CredCardNum = dbo.ud_CCEncrypt(CredCardNum,@key)
EXEC master..xp_rsa_free_key @key 

   Note, that if we load the key, we must free it explicitly.

   Now we need to write UDF for the decryption. This is not much harder then UDF for the encryption. 
   Take a look:

CREATE FUNCTION ud_CCDecrypt (@crypted VARCHAR(8000), @preloadedkey VARCHAR(10), @password VARCHAR(32))
RETURNS VARCHAR(20) 
AS BEGIN
DECLARE @k VARCHAR (10)
DECLARE @clear VARCHAR (16)
-- IF @preloadedkey is NULL, then it was not preloaded and we 
-- must load it using provided password,use it and free at the end.
-- We need private key, that's why we need to specify the password.
IF @preloadedkey IS NULL BEGIN 
   EXEC master..xp_rsa_load_key 'c:\privatekey.pem',@k OUTPUT, @password
END ELSE BEGIN
  -- If key was already loaded, just use it.
  SELECT @k=@preloadedkey
END
-- If we have working key, we do encryption.
IF @k IS NOT NULL BEGIN 
   EXEC master..xp_rsa_priv_dec @crypted, @k , @clear OUTPUT
END ELSE BEGIN
   -- if key was not loaded by some reason, we return encrypted string as it is.
   SELECT @clear=@crypted
END 
-- if key was not given at the beginning, we must free it.
IF @preloadedkey IS NULL EXEC master..xp_rsa_free_key @k
-- returning decrypted string
RETURN @clear
END

  This procedure looks almost identical with the one for the encryption, but has an additional parameter - password, it needs to load the private key, in the case it was not provided. At the moment we don't care  of efficiency and can afford loading and freeing for every row in the table. That's why we use decryption without key preloading : 

SELECT username, dbo.ud_CCDecrypt(CredCardNum,NULL,'SecurePassword') AS cc FROM tbl_CCards 

username         cc 
---------------- -------------------- 
james            1234567890123456 
lucas            2894787650102827 
anna             3234563638716434 
(3 row(s) affected)
 

   As you see this is not that hard as it looks. 
   Security hint : never store the passwords for keys in the SQL text, make it a parameter of query.

   Here is a  more efficient way of the bulk decryption that wins on the performance, but looks less comfortable : 

DECLARE @key VARCHAR (10)
EXEC master..xp_rsa_load_key 'd:\privatekey.pem',@key OUTPUT,'SecurePassword'
SELECT dbo.ud_CCDecrypt(CredCardNum,@key,NULL) as CredCardNum FROM tbl_CCards
EXEC master..xp_rsa_free_key @key 

 Like in the previous case we load the key and use it for all rows and after that free it. Inserting data is not a problem having those nice UDFs. 

  To insert a new user simple insert the functions in the SQL body:

INSERT  tbl_CCards VALUES ('kimberly',dbo.ud_MakeSha1('robot'), dbo.ud_CCEncrypt('0987654321765432',NULL)) 
SELECT * FROM tbl_CCards WHERE username= 'kimberly' 
Username Password                                 CredCardNum 
-------- ---------------------------------------- -----------------------------------
kimberly B232AE125D1ABD56C43942A08AE6275802312111 ekFj5mOlBDiClhAEf/GzfbE7zEFXyUqm5hKC+cQkl2w=

That's all with the encryption. Now you can see how these easy methods can greatly improve the data safety.

Browser Based Help. Published by chm2web software.