Calling CryptoAPI from Stored Procedures
Do you have sensitive data stored in SQL Server? Would you like to encrypt or hash some of that sensitive data? This article will show you how to call CAPICOM to invoke CryptoAPI from TSQL to hash or encrypt/decrypt data.
First, what are CAPICOM and CryptoAPI: CAPICOM is an ActiveX COM object provided by Microsoft that can be used to interface with CryptoAPI. CryptoAPI is a Microsoft component of Windows that has a number of data encryption components to encrypt, decrypt, and hash data. CAPICOM allows you to easily call CryptoAPI to perform a number of different hashing and encryption algorithms.
What is Encryption
Encryption is the process of taking a string of meaningful data, and a key, known as an encryption key and running it through an encryption algorithm to creating an encrypted string of data. The encrypted string contains a series of meaningless characters. The process of encryption is a two-way street. Encrypted data can be decrypted using the same encryption key and algorithm that encrypted the data. Thus, you can take your meaningless string of encrypted data and create the original string of meaningful data.
What is Hashing
Hashing differs from encryption in that it only encrypts the data. Like encryption, hashing takes a string of meaningful data, runs it through a process, and creates a hash value. This hash value is a string of meaningless characters of fixed length. The difference between encryption and hashing is that a hash value cannot be decrypted back to the original string. Therefore, hashing is one-way; encrypted data can be decrypted.
To find out more about Microsoft CryptoAPI and CAPICOM check out Microsoft's Cryptography portal at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/security/security/cryptography_portal.asp
How might you use CAPICOM/CryptoAPI
It's likely that quite a number of users have Web applications that prompt them to enter a login and a password. These logins and passwords are probably stored in a database. If so, CAPICOM can be used to call CryptoAPI to hash the password — you can store a hashed password instead of a clear-text password in your database. The advantages of this become obvious if you also store credit card information in SQL Server. You wouldn't want someone browsing your database and running across clear-text credit card numbers, would you? By using the encryption/decryption functionality of CryptoAPI, you could store those credit card number as encrypted fields on the database.
How to Install CAPICOM
It is a fairly easy process to install CAPICOM. CAPICOM consists of a single dll, capicom.dll, that must be placed in your system32 directory, then registered. You can download the CAPICOM dll from the Microsoft Download Center.
Once you have downloaded, and extracted the CAPICOM package, read the README.txt for an explanation of how to install CAPICOM. To call CAPICOM from TSQL, you will need to install CAPICOM on the SQL Server box that will be running the TSQL that calls CAPICOM.
Once you have installed CAPICOM on your SQL Server machine, you are ready to call CAPICOM from TSQL. Since CAPICOM is an ActiveX object, you will need to use OLE automation to process the CAPICOM object. Here is an example of how to utilize CAPICOM to hash some text using TSQ.
Let's review how this example works. First, a number of variables are defined, along with setting the @password variable to "super secret." Next, the OLE automation stored procedure (SP) sp_OACreate is called to create an instance of the CAPICOM A HashedData@ object. If an error occurs, the @rc variable is set to something other than zero, then the sp_OAGetErrorInfo SP is called to display some error information. This same error logic will be used for all OLE automation calls. Once the "HashData" object is successfully created, the script sets the hashing method to use. To set the hashing method, the sp_OASetProperty SP is used. For the script I've used in this article, I'm using the SHA-1 algorithm. CAPICOM supports a number of other algorithms; review the CAPICOM documentation to find out more about supported hashing algorithms. The next step is to call the CAPICOM hash method to hash the password using the sp_OAMethod SP. Once the password has been successfully hashed, the script retrieves the hashed password into variable @password_hash by executing the sp_OAGetProperty SP to return the hashed value. After the sp_OAGetProperty SP has run successfully, the hashed password can be displayed.
This example is only a shell, and it demonstrates how to call the CAPICOM object to hash a character string from TSQL. If you have an application that currently stores in a table Web user passwords in clear text format, you might consider building a process to store and manage Web users passwords, as hash data in a database file, by using CryptoAPI and CAPICOM.
To encrypt and decrypt data, you will need to use OLE automation, as in the example above, to call CAPICOM. Here is a simple example of some TSQL to encrypt a character string, in this case, a fictitious credit card number. Once the credit card number is encrypted, this example will take the encrypted information and decrypt it, so it can be displayed.
Let's discuss how this code works. First, a number of variables are defined, one of those being @secret_code. The @secret_code variable is used to store the encryption key. The encryption key is used along with the encryption method to tell CryptoAPI how to encrypt and decrypt the data. Next, sp_OACreate SP used to create a CAPICOM EncryptedData object. Once the EncryptedData object is created, the Algorithm Name and Keylength property are set by using the sp_OASetProperty SP. In my example, the encryption algorithm name is 3, which means use the 3DES encrypting algorithm; and the key length is also set to 3, which means use a 128-bit encryption key. For additional information about the algorithms and key lengths supported, review the CAPICOM documentation, mentioned previously.
Next, you can use the CAPICOM SetSecret method, using sp_OAMethod SP, to set the encryption key to encrypt the data. Finally, you can encrypt the data using the "Encrypt" method of CAPICOM. When this method is executed, the encrypted data will be placed into variable @Encrypted_CreditCard. After the data is encrypted, the encrypted credit card number is displayed.
To decrypt the credit card information, I first create a EncryptedData object. Once the object has been created, I specify the encryption key, again using the SetSecret method. Then I execute the Decrypt method to un-encrypt the encrypted credit card information. Lastly I retrieve the "Content" property value to populate the @Decrypted_CreditCard variable with a decrypted credit card value. Once the @Decrypted_CreditCard variable is populated I print out the original credit card number in clear text format.
Like with any new tool you need to determine how best this might be used in your application. I've shown you a couple of simple scripts that use OLE automation to call CryptoAPI using the downloadable CAPICOM ActiveX COM object via TSQL. By taking these examples you should be able to build a stored procedure to hash text and/or encrypted sensitive data for your application. I used only one hashing and encryption algorithm in my examples, be aware that CAPICOM supports a number of different hashing and encryption algorithms.
I'd like to leave you with one last consideration. The examples I've shown you need to be taken as is, they are not necessarily the most secure way to hide sensitive data from prying eyes. Although using this encryption technique above is better than having sensitive data stored as clear text in your database.
Gregory A. Larsen is a DBA at Washington State Department of Health. He has been working with computers since the late 1970s, and has a BS in Computer Science, with a minor area of study in Mathematics. During Greg’s DBA career, he has managed databases on Windows, Unix, and IBM mainframes; before becoming a DBA, he developed and maintained applications. Greg has written a number of articles for different web publishing sites, as well as SQL Server Magazine. Greg also maintains a Website that contains a number of SQL Server examples to common DBA and SQL developer issues.
Contributors : Gregory A. Larsen
Last modified 2005-04-12 06:21 AM