SQL SERVER 2008 Hosting :: Database Table Encryption Using Symmetric Key in SQL Server 2008 R2.
Today I will explains about Database Table Encryption using Symmetric Key in SQL Server 2008 R2. The purpose of this article is to provide security to a database column’s value so that no one can understand what the actual value.
Actually a few days ago I had the situation to add one more layer of security, like encryption and decryption of some keys (for example SaltKey or RSAKey), that were being used in my C# code base file. These keys were being used to encrypt and decrypt the username and password, however I don’t want to make it public these keys (for example SaltKey or RSAKey) because anyone can read this easily using reflector if obfuscation is not applied to the C# code base file (.cs file). So I thought to put these keys into the database and put up all the columns values in encrypted mode and decrypt it since I needed the actual values of these keys.
I studied about this from various portals before implementing. In this article I’ll also share some findings that may be helpful to you to create rudimentary queries.
There are many approaches to implement encryption but I’ve chosen column encryption of a database table. I just encrypted and decrypted the data placed in the table. This approach takes little time to do the required procedure than the other approach.
So let’s start with encryption and decryption process executed with a Symmetric Key. I’ve created a database with the name DBEncrypt with a table “TestTable” that has three columns in it. Kindly look at the screenshot given below:
If you notice in the image shown above there are three columns with some text value. My main objective is to encrypt those values and decrypt accordingly.
Create a master key, each database can have one master key and this master key used to protect the private keys of the certificates. After this we’ll create a certificate to encrypt the data in the database and symmetric key (the symmetric key used by the sender and the receiver of a message, it’s a common key used to encrypt and decrypt the message). We’ve created a symmetric key using the certificate (EncryptTestCert).
|
1 2 3 4 5 6 7 8 9 |
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Support@123' CREATE CERTIFICATE EncryptTestCert WITH SUBJECT = 'SupportCert' CREATE SYMMETRIC KEY TestTableKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE EncryptTestCert. |
Now it’s time to create another three columns that will keep encrypted data of these three columns (SaltKey, RSAKey and PrivateKey) of table respectively. I’ve also added one more Column SaltKeyEnCol2 as nvarchar(max). I will share the reason to create an extra column later in this document.
|
1 2 3 4 5 |
ALTER TABLE TestTable ADD SaltKeyEnCol VARBINARY(256),RSAKeyEnCol VARBINARY(256),PrivateKeyEnCol VARBINARY(256) ALTER TABLE TestTable ADD SaltKeyEnCol2 nvarchar(max) |
The code given below encrypts three columns (SaltKey, RSAKey and PrivateKey) values to the newly created columns above with an alter commnad. Also note that we are using the same certificate(EncryptTestCert) to open the symmetric key and make it available for use. Kindly have a look at the syntax given below.
|
1 2 3 4 |
OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert, UPDATE TestTable SET SaltKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey),RSAKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),RSAKey),PrivateKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),PrivateKey) |
Kindly manually close the symmetric key otherwise it will remain open for the current session.
After the execution of the preceding query you can have a look and see the affects made into the database. Kindly have a look at the image given below:
If you notice in the image above, it only shows you <Binary data> in encrypted Columns. Which was a little tricky for me initially, after looking at these values I thought I made some mistake and the data doesn’t look OK.
To cater this issue, I again created another column with the name “SaltKeyEnCol2” as I have shared this in the preceding in the document and is also shown below. It helped me to see the actual value.
|
1 2 |
ALTER TABLE TestTable ADD SaltKeyEnCol2 nvarchar(max) |
Please execute the SQL query given below and see the effect.
|
1 2 3 |
OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert UPDATE TestTable SET SaltKeyEnCol2 = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey) |
The column SaltKeyEnCol2 has some encrypted value in SaltKeyEnCol2 as depicted below in the screen shot rather than the <Binary Data> value.
As we can see above, we’ve achived the encryption of the required columns. Now the turn is to decrypt the data placed in the encrypted columns. Kindly refer to the image given below with the outcome.
Kindly find in the following the complete database query script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Support@123' CREATE CERTIFICATE EncryptTestCert WITH SUBJECT = 'SupportCert' CREATE SYMMETRIC KEY TestTableKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE EncryptTestCert ALTER TABLE TestTable ADD SaltKeyEnCol VARBINARY(256),RSAKeyEnCol VARBINARY(256),PrivateKeyEnCol VARBINARY(256) ALTER TABLE TestTable ADD SaltKeyEnCol3 nvarchar(max) --drop COLUMN SaltKeyEnCol3 OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert UPDATE TestTable SET SaltKeyEnCol3 = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey) OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert, UPDATE TestTable SET SaltKeyEnCol2 = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SaltKey),RSAKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),RSAKey),PrivateKeyEnCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),PrivateKey) CLOSE SYMMETRIC KEY TestTableKey; OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert SELECT CONVERT(nvarchar(max),DECRYPTBYKEY(SaltKeyEnCol2)) AS DecryptSaltKeyEnCol FROM TestTable OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert SELECT CONVERT(nvarchar(max),DECRYPTBYKEY(SaltKeyEnCol2)) AS DecryptSaltKeyEnCol,CONVERT(nvarchar(max),DECRYPTBYKEY(RSAKeyEnCol)) AS DecryptRSAKeyEnCol, CONVERT(nvarchar(max),DECRYPTBYKEY(PrivateKeyEnCol)) AS DecryptPrivateKeyEnCol FROM TestTable CLOSE SYMMETRIC KEY TestTableKey |
I hope it will help you..
Best Recommended SQL SERVER Hosting
ASPHostPortal.com
ASPHostPortal.com is Perfect, suitable SQL SERVER hosting plan. ASPHostPortal the leading provider of Windows hosting and affordable SQL SERVER Hosting. ASPHostPortal proudly working to help grow the backbone of the Internet, the millions of individuals, families, micro-businesses, small business, and fledgling online businesses. ASPHostPortal has ability to support the latest Microsoft and ASP.NET technology, such as: WebMatrix, WebDeploy, Visual Studio 2015, .NET 5/ASP.NET 4.5.2, ASP.NET MVC 6.0/5.2, Silverlight 6 and Visual Studio Lightswitch, ASPHostPortal guarantees the highest quality product, top security, and unshakeable reliability, carefully chose high-quality servers, networking, and infrastructure equipment to ensure the utmost reliability.
HostForLIFE.eu
HostForLIFE.eu guarantees 99.9% uptime for their professional SQL SERVER 2014 hosting and actually implements the guarantee in practice. HostForLIFE.eu is the service are excellent and the features of the web hosting plan are even greater than many hosting. HostForLIFE.eu offer IT professionals more advanced features and the latest technology. HostForLIFe Best , Cheap SQL SERVER 2014 Hosting, Relibility, Stability and Performance of servers remain and TOP priority. Even basic service plans are equipped with standard service level agreements for 99.99% uptime. Advanced options raise the bar to 99.99%. HostForLIFE.eu revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24×7 access to their server and site configuration tools.
DiscountService.com.au
DiscountService.com.au is The Best and Cheap SQL SERVER Hosting. DiscountService.com.au was established to cater to an under served market in the hosting industry web hosting for customers who want excellent service. DiscountService.com.au guarantees the highest quality product, top security, and unshakeable reliability, carefully chose high-quality servers, networking, and infrastructure equipment to ensure the utmost reliability. DiscountService.com.au has ability to support the latest Microsoft and ASP.NET technology, such as: WebMatrix, WebDeploy, Visual Studio 2015, .NET 5/ASP.NET 4.5.2, ASP.NET MVC 6.0/5.2, Silverlight 6 and Visual Studio Lightswitch. DiscountService.com.au is devoted to offering the best Windows hosting solution for you.
Related Posts
About The Author
