In part I , we've talked about the Symmetric key; In this article , I'll be talking about Asymmetric key also known as Public-key cryptography, is a system in which the sender and the receiver of a message have a pair of cryptographic keys - a public key and a private key - to encrypt and decrypt the message. In which the sender can only encrypt the message and only the receiver who has the other pair of the key (private key) can decrypt the message and can’t encrypt it.

 

We can use three encryption algorithms with Asymmetric keys which are:

RSA_512, RSA_1024, RSA_2048, for more information about those algorithms.

 

 

How to protect your data using Asymmetric key?

 

First of all we need to create an Asymmetric key, using the following code to create a Asymmetric key:

 

IF NOT EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name =
'Asym_key1' )
BEGIN
create asymmetric key Asym_key1

with algorithm = RSA_2048

encryption by password='P@ssw0rd'

END
GO

 

Then we create the table that we need to encrypt its data by using the following code or any other code you want to:

CREATE TABLE Testencryption
(

Id int identity(1,1) primary key,

Data VARBINARY(2048))
GO

 

 

You need to declare your targeted field for encryption as varbinary data type to accept encryption.

 

Then we need to insert an encrypted value in our table, to do so we will need to use the

EncryptByAsymKey Method which takes the key Guid which is the Guid of the Asymmetric key which we can get using the following select statement:

 

DECLARE @Key_GUID UNIQUEIDENTIFIER

SELECT @Key_GUID = Key_GUID
FROM sys.Asymmetric_keys
WHERE Name = 'Asym_key1'

And the second parameter is the data you want to encrypt.

So the whole code will be like following:

DECLARE @Key_GUID UNIQUEIDENTIFIER

SELECT @Key_GUID = Key_GUID
FROM sys.Asymmetric_keys
WHERE Name = 'Asym_key1'

‘using the @Key_Guid to tell the EncryptByAsymKey Method which Asymmetric key it ‘will be using to encrypt the data

INSERT INTO Testencryption
(Data)
VALUES
(EncryptByAsymKey (@Key_Guid, 'whatever you want'))
GO

If you want to view the encrypted data, just select the data from the table using the following code:

SELECT Data
FROM Testencryption
GO

                If you want to decrypt the data, just follow this code:

select CONVERT(varchar(max),decryptbyasymkey(AsymKey_ID('Asym_key1'), Data,N'P@ssw0rd'))

from Testencryption

GO

For more Information check the following links:

http://msdn2.microsoft.com/en-us/library/ms174430.aspx

Download from here

Download Rosairo April 2008 CTP (VPC Image) from here

Check the Preparation Guide for 70-556 TS: Microsoft Office PerformancePoint Server 2007, Applications

Check some of the PerformancePoint Server 2007 training Materials here

 

1.       Symmetric key

Protected by a Certificate which is protected by database Master key

2.       Asymmetric key

Protected by a database Master key

 

N.B:

Don’t use Asymmetric keys unless you are in deep need for it as it requires high processing speed and try not to use it when dealing with multiple rows and data.

 

 

How to protect your data using Symmetric key?

 

First of all we need to create a Symmetric key, using the following code to create a symmetric key:

 

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name =
'PrivateData' )
BEGIN
CREATE SYMMETRIC KEY PrivateData WITH
ALGORITHM =Triple_DES

ENCRYPTION BY PASSWORD='P@ssw0rd'
END
GO

 

Secondly we need to open the created key to use it in encryption, so use the following code to open the key:

 

OPEN SYMMETRIC KEY PrivateData DECRYPTION
BY PASSWORD ='P@ssw0rd'
GO

 

Then we create the table that we need to encrypt its data by using the following code or any other code you want to:

CREATE TABLE Testencryption
(

Id int identity(1,1) primary key,

Data VARBINARY(255))
GO

 

 

You need to declare your targeted field for encryption as varbinary data type to accept encryption.

 

We will need to open our symmetric key so we can use in encryption by using the following code:

 

OPEN SYMMETRIC KEY PrivateData DECRYPTION
BY PASSWORD ='P@ssw0rd'
GO
CREATE TABLE Testencryption
(Data VARBINARY(255))
GO

 

Then we need to insert an encrypted value in our table, to do so we will need to use the

EncryptByKey Method which takes four parameters two of them mandatory which are the Guid of the symmetric key which we can get using the following select statement:

 

DECLARE @Key_GUID UNIQUEIDENTIFIER

SELECT @Key_GUID = Key_GUID
FROM sys.symmetric_keys
WHERE Name = 'PrivateData'

And the second parameter is the data you want to encrypt; the other two parameters are optional.

For more information about how to create symmetric key check this URL

So the whole code will be like following:

DECLARE @Key_GUID UNIQUEIDENTIFIER

SELECT @Key_GUID = Key_GUID
FROM sys.symmetric_keys
WHERE Name = 'PrivateData'

 ‘using the @Key_Guid to tell the EncryptByKey Method which symmetric key it ‘will be using to encrypt the data

INSERT INTO Testencryption
(Data)
VALUES
(EncryptByKey (@Key_Guid, 'whatever you want'))
GO

If you want to view the encrypted data, just select the data from the table using the following code:

SELECT Data
FROM Testencryption
GO

                If you want to decrypt the data, just follow this code:

SELECT CONVERT (VARCHAR (50),
DecryptByKey (Data, 1)) AS Data
FROM Testencryption
GO

By the way if you are tried to use AES – Advanced Encryption System on any operating system but Windows server 2003 it will generate this error because it’s only supported on the previously mentioned operating system.

Msg 15314, Level 16, State 1, Line 1

Either no algorithm has been specified or the bitlength and the algorithm specified for the key are not available in this installation of Windows.

 

Part II will talk about the encryption with Asymmetric key.