Asymmetric Key (Encryption – Decryption in SQL Server 2008 Part 5)

Asymmetric Key is a small brother of Certificate which I have explained in my previous article. Asymmetric Key is a combination of public key and private key which uses some different algorithms to encrypt and decrypt key. Algorithms are RSA_512, RSA_1024, RSA_2048. Details of these algorithms are beyond the scope of this article, I will write detailed article on these kind of algorithm very soon.
Now let us look small T-SQL practice for this.
–create database master key before creating Asymmetric key
–if it is not already exist
CREATE MASTER KEY ENCRYPTION BY PASSWORD=‘$qlhub’


–creaet asymmetric key with
–RSA_512 algorithm
CREATE ASYMMETRIC KEY AsymADV
WITH ALGORITHM = RSA_512
go


–let us encrypt and decrypt data with Asymmetric key
DECLARE @Text nvarchar(max)
DECLARE @TextEnrypt varbinary(128)
DECLARE @TextDecrypt nvarchar(max)
SET @Text=N’hi’–, this is first Assymetric test, created by Ritesh Shah’
SET @TextEnrypt=ENCRYPTBYASYMKEY(AsymKey_ID(N’AsymADV’),@Text)
SET @TextDecrypt=DECRYPTBYASYMKEY (AsymKey_ID(N’AsymADV’),@TextEnrypt)
SELECT @Text AS ‘ORIGINAL TEXT’,@TextEnrypt AS ‘Encrypted Text’,@TextDecrypt as ‘Decrypted Text’
GO


–drop Asymmetric key
DROP ASYMMETRIC KEY AsymADV
GO


–drop master key
drop master key



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com
Advertisements

Asymmetric Key (Encryption – Decryption in SQL Server 2008 Part 5)

Asymmetric Key is a small brother of Certificate which I have explained in my previous article. Asymmetric Key is a combination of public key and private key which uses some different algorithms to encrypt and decrypt key. Algorithms are RSA_512, RSA_1024, RSA_2048. Details of these algorithms are beyond the scope of this article, I will write detailed article on these kind of algorithm very soon.
Now let us look small T-SQL practice for this.
–create database master key before creating Asymmetric key
–if it is not already exist
CREATE MASTER KEY ENCRYPTION BY PASSWORD=‘$qlhub’


–creaet asymmetric key with
–RSA_512 algorithm
CREATE ASYMMETRIC KEY AsymADV
WITH ALGORITHM = RSA_512
go


–let us encrypt and decrypt data with Asymmetric key
DECLARE @Text nvarchar(max)
DECLARE @TextEnrypt varbinary(128)
DECLARE @TextDecrypt nvarchar(max)
SET @Text=N’hi’–, this is first Assymetric test, created by Ritesh Shah’
SET @TextEnrypt=ENCRYPTBYASYMKEY(AsymKey_ID(N’AsymADV’),@Text)
SET @TextDecrypt=DECRYPTBYASYMKEY (AsymKey_ID(N’AsymADV’),@TextEnrypt)
SELECT @Text AS ‘ORIGINAL TEXT’,@TextEnrypt AS ‘Encrypted Text’,@TextDecrypt as ‘Decrypted Text’
GO


–drop Asymmetric key
DROP ASYMMETRIC KEY AsymADV
GO


–drop master key
drop master key



Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder ofhttp://www.SQLHub.com

Encryption – Decryption in SQL Server 2008 – Part 1

Though Encryption and Decryption process is resource intensive, it is become necessary in some cases. If you look back in SQL Server 2000 and 7.0 days, you didn’t have any in-built mechanism and had to depend on 3rd party tools. But from SQL Server 2005, Microsoft started providing in-built support for encryption and decryption. Let us look in details about what is it? How does it work?

Since this is pretty big topic, it is not desirable to have it in one article so I will upload it in part. Before we move further in topic, let us find out Architecture (Hierarchy) of Encryption and Decryption mechanism in SQL Server 2008. Following image will give you an idea about that.

SQL Server 2008 encryption model inherits Windows Crypto API to encrypt and decrypt data in your database and supports layered approach. At the second level of encryption, there will be SMK (Service Master Key) . You can find one SMK (Service Master Key) per instance. It used to get generated by its own when it needs to encrypt any other key.  Well as I just told you that each server instance can have only one SMK but every database in your instance can have separate DMK (Database Master Key) which is encrypted by SMK.

At the bottom level of Encryption, you can find Certificates, Asymmetric key and Symmetric key. Detailed article with example about each of these are going to come soon. Keep Reading!!!!

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com