 |
Installing XP_CRYPT |
|
|
XP_CRYPT API |
|
|
Session |
|
|
Asymmetric keys |
|
|
DSA |
|
|
RSA |
|
|
DSA |
|
|
RSA |
|
|
Hashing |
|
|
Symmetric encryption |
|
|
AES128 |
|
|
AES |
|
|
DESX |
|
|
RC4 |
|
|
Triple DES |
|
|
Tutorial |
|
|
Troubleshooting |
|
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')
-----------
0
(1 row(s) affected) |
Another try:
| SELECT dbo.ud_CheckUser
('anna','love')
-----------
1
(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.
|
