Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » SQL Server » SQL Server Articles Archive » Calling CryptoAPI from Stored Procedures
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3556
 

Calling CryptoAPI from Stored Procedures

by Gregory A. Larsen

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.

Hashing Passwords

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.

Encrypting Data

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.

Conclusion

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

Which approach is better?

Posted by marwa at 2005-12-27 03:00 AM
First thank you for the article,It helps me a lot to understand how to encrypt data through stored procedure. But actually I have question here
My project is a web application that takes the input from the user and need to store these data encrypted into database and I have 2 options to do that:
1. Through my application asp.net by encrypting data before inserting into database and decrypting data after retrieving from database.
2. By making stored procedure calling CryptoAPI as it was explained in the above article.
My Questions are
1. Which approach is better to have good performance?
2. While doing search within encrypted data which approach is better?(Note: I have read that to do search through encrypted data through my application, the search that I can only do is exact matching OR I have to retrieve all the data from my application then filter them(which is bad performance)).
I will appreciate your advice.

Calling CryptoAPI from Stored Procedures

Posted by jemacc at 2006-10-17 03:54 PM
I am reading this wrong; do you have to separate parts of this stored procedure one for password encryption and one for credit card encryption?

Encrypting all stored procedures

Posted by Johan27 at 2007-05-23 10:28 AM
I want to write a small application that will encrypt all my SQL 2000 stored procedures so that no one else can see them and steel our logic.
But the more I read up about the "with encryption" added to the stored procedure the more it seemed to me that there is to many ways to get past it....
So is there any other way I can encrypt all my stored procedures?
Any help will be appriciated amencely
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone