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

XP_CRYPT GUI. Code generation tool.

Japanese version

Quick Introduction


XP_CRYPT giu is a tool to simplify the creation of UDFs, triggers and views for encryption with XP_CRYPT 4.0 and higher.

How to encrypt

XP_CRYPT supports 5 encryption algorithms: RSA (asymmetric algorithm), AES, Triple DES, DESX and RC4 (symmetric algorithms). You can choose an algorithm depending on your needs. However please note that RSA is a relatively slow encryption comparing to symmetric algorithms.  

To start protecting your data login to the database. We take database "pubs" which is installed together with SQL Server and contains some demo data about books and authors. 

After successful login, you see a window with existing tables and a list of encryption keys. Now it is empty.

Now, you need to choose which algorithm you want to use and create a key of this type. Lets choose RC4 and RSA.

Choose "Add New Key" or press Ctrl-N, the following dialog will appear:

Consider "Password Alias"  as a name of the key. Actually this is a name of variable that will hold a password in your SQL session. 

The key is created, now, you must enter the password. The password will be used to encrypt existing data in tables. Click on "Enter Password" and enter "test" as password.

Green "A" sign means "Activated". This means you can start using this key now. 

We will encrypt  first and last names of the authors. Those fields are in "authors" table. Expand the tree and choose field "au_lname'. Check "Encrypt Field" checkbox and choose encryption key in the combobox. 

"Choose Encryption" drop down menu shows keys created in "Keys and Passwords" window. Currently there is only our RC4 key. Choose it and press "Save" on the toolbar (Ctrl-S)

At this stage XP_CRYPT starts creating a schema needed for encryption. You can see the log of the operation if you click on "Tables" on the left tree.

XP_CRYPT added a field called "enc_au_lname" and filled it with encrypted data from  the field au_lname. We can see it if we select some data.

select  enc_au_lname from authors

enc_au_lname 
------------------------------
ydSrcsidbufzu9XZ
ydSrcsidbvfpt8TS
ydSrccidbvP6oNLTBg==
ydSrcMidbv+8nsTdGhA=
ydSrf8idbuPvoMDVDwGw

XP_CRYPT created a VIEW which shows decrypted result. After your table is encrypted, you should use this view to select and insert data. It will be automatically encrypted. This VIEW has the same name as the table + "View" at the end. You can rename it after it has been created.

Lets select from this view:

SELECT au_lname from authorsView

au_lname 
---------------------------------------- 
NULL
NULL
NULL

Now, there are NULLs instead of the real data, because your SQL session does not have the password. The password is set with XP_CRYPT function called xp_crypt_set_var. We need to set the password "test" for a variable "pubs.pass1". Actually this is the same password we entered at the step 3.

exec master..xp_crypt_set_var 'pubs.pass1','test'
select au_lname from authorsview

Output:

au_lname 
---------------------------------------- 
White
Green
Carson
....

Your application needs to set the password only once. It will be kept in the session. Try to select again without calling xp_crypt_set_var. It will work until you close Query Analyzer or a timeout expires. Other users connected to SQL Server cannot see this data. The password is set only for your session.

Now, lets create RSA key and encrypt the last name field.

Choose Asymmetric  encryption , algorithm RSA, key size 1024, and password alias "pass2". After you press ok, you will be asked for a password, this password will be used to store the key is the table. Do not forget this password. Lets enter rsatest as a password. Of course in real life you should choose better passwords. 

We see that the key is already activated and ready to use. If you want to look how the key looks like, make a double click, you will see the public and the private key. The public key is used for encryption and is stored unencrypted, the private key should be kept securely and encrypted with Triple DES. Both keys are in PEM format. If necessary, you can use them in another applications. In real life you have to copy those keys elsewhere to avoid the loss. 

Select fields you wan to encrypt. In our case it is au_fname and phone. Choose RSA key for both fields and press Ctrl-S or choose "Save"  in menu.

Everything what is needed is created automatically, existing data is also encrypted. 

NOTE: You may see that original table authors still have au_lname, au_fname and phone fields which contain unencrypted information. XP_CRYPT GUI intentionally DOES NOT DELETE those fields from the table. You must do it yourself after you are sure that data is safe. We recommend you to backup the database before encryption.

Test if everything works:

exec master..xp_crypt_set_var 'pubs.pass1','test'
exec master..xp_crypt_set_var 'pubs.pass2','rsatest'
select au_lname,au_fname,phone from authorsview

Output:

au_lname                                 au_fname             phone 
---------------------------------------- -------------------- ------------ 
White                                    Johnson              408 496-7223
Green                                    Marjorie             415 986-7020
Carson                                   Cheryl               415 548-7723

After you make sure that everything works you can delete the original fields from source table. In our case  au_lname,  au_fname and phone in table authors are not needed. Because they are stored in encrypted fields. What you see in "select au_lname,au_fname,phone from authorsview"  is fields created by the view on-fly as a result of decryption.

System tables

During the work, XP_CRYPT gui creates 3 tables for every database which is used to keep information about encrypted fields, triggers, views and so on:

xpcrypt_ENCRYPTED_FIELDS
xpcrypt_GENERATED_KEYS
xpcrypt_TABLES

You do not need to create those tables, they will be created automatically after you login to the database from application.

Attention! Table xpcrypt_TABLES stores RSA and DSA keys, after you setup encryption you must backup this table to avoid the loss of keys. If you lose RSA or DSA keys, you will not be able to decrypt or validate your data. This tables does not store passwords. Do not edit those tables manually. Keys are stored in tables which are created automatically for every database.

 
2004-2011 (c) Active Crypt Software. All rights reserved.

Browser Based Help. Published by chm2web software.