SYMMETRIC key & certificates role in sql server

CREATE SYMMETRIC KEY SecureSymmetricKey
    WITH ALGORITHM = DESX
    ENCRYPTION BY PASSWORD = N'StrongPassword';
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'DB Master key password!'
GO

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'My Certificate Subject'


CREATE SYMMETRIC KEY MySymetricKey
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificate

OPEN SYMMETRIC KEY MySymetricKey DECRYPTION
BY CERTIFICATE MyCertificate

and then :
SELECT  
       convert( NVARCHAR(max), decryptbykey(namePAss)) 
FROM  tbl1

on first server:
CREATE CERTIFICATE cert1 WITH SUBJECT = 'Certificate for my stuff'

BACKUP CERTIFICATE  cert1 TO FILE = 'd:\backup\cert1.dat' 
WITH PRIVATE KEY 
(
ENCRYPTION BY PASSWORD = 'mypassword',
FILE = 'd:\backup\cert1_privatekey.dat'
)


on second server:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypassword'

CREATE CERTIFICATE cert1 FROM FILE = 'd:\cert1.dat'
WITH PRIVATE KEY
(
FILE = 'd:\cert1_privatekey.dat',
DECRYPTION BY PASSWORD = 'mypassword'
)


You can use the makecert tool to generate a self-signed certificate for your own use. The following command would do the work:



makecert -r -pe -n "CN=YOUR_SERVER_FQDN" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 c:\test.cer



You must replace YOUR_SERVER_FQDN with appropriate stuff. You can get the usage of makecert tool fromhttp://msdn2.microsoft.com/en-us/library/bfsktky3(vs.71).aspx, makecert tool comes with winsdk and .Net SDK.



With this, your SQL Server will automatically load the certificate when the SQL Server restarts (if there are multiple certificates meets the requirements, SQL Server will load the first one it finds from the cert store.).



Note that, if you don’t have a certificate, SQL Server will automatically generate one self-signed certificate for you. However, the difference here is: the certificate generated by SQL Server will change every time the server restarts and the certificate’s subject CN is not your FQDN which is in general a critical part if client choose to authenticate the server. More specifically, if the client forces encryption, the connection attempt may fail because the subject CN does not match the server FQDN (in general) and the certificate is not trusted by the client. With the certificate we just generated, your connection attempt may also fail if your client forces encryption. You may get the following on your client machine:



C:\>osql -E -Syourserver

[SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.

[SQL Native Client]Client unable to establish connection



The reason of the failure is obvious, as stated in the error message. You can solve the issue by doing the following steps:



Copy c:\test.cer into your client machine, run c:\test.cer from command window, select "Install Certificate". -> click "Next" -> select "Place all certificates in the following store" --> click "Browser" -> select "Trusted Root Certification Authorities" -> select OK and Finish



By trusting the specific certificate, the client does not have to select “trust server certificate” when “force encryption” is selected. Client force encryption and trust server certificate properties can be configured by SQL Server Configuration Manager or by connection string. Check the following page about how the various properties interact. http://msdn2.microsoft.com/en-us/library/ms131691.aspx



Please note that the certificate generated by makecert tool should only be used for testing purpose.

You have to create a certificate in the master db first.

I used a self signed cert example:

USE MASTER;
CREATE CERTIFICATE testlogincert
ENCRYPTION BY PASSWORD = 'pounlmdsf'
WITH SUBJECT = 'your login',
EXPIRY_DATE = '12/31/2012';
GO

And then map a user to it.
CREATE LOGIN 'your login' FROM CERTIFICATE testlogincert;
GO

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.


Service Master Key is a base in hierarchy of SQL Server Encryption and Decryption, it directly access Windows Data Protection API. Only one Service Master Key can exist per SQL Server instance. I have already mentioned that Service Master Key (SMK) used to get generated by its own whenever it is needed first time than question may pops up in your mind that what should we need to do if it is generated by its own? Well, interesting question but the answer is also interesting. You need to do some administrative task for SMK as there is a possibility that sometime your SMK key gets corrupt and you need to restore it in your instance. If you don’t have it, you will definitely lose your encrypted data as there will not be any possibility that you can decrypt your data and use it if you don’t have your SMK.

I highly recommend whenever you install new instance of SQL Server 2008, get the copy of your SMK in file at some safe place so that you can restore it whenever you need it.



Now let us see some important administrative commands for managing SMK.



--backing up Service Master Key

--recommended that you take backup of

--your service master key as soon as you install new instance

BACKUP SERVICE MASTER KEY TO FILE= 'D:\SQL2K8.SMK'

ENCRYPTION BY PASSWORD = '$qlhub'

GO





--restoring service master key

--you can use file which we have backed up, whenever you need

RESTORE SERVICE MASTER KEY FROM FILE='D:\SQL2K8.SMK'

DECRYPTION BY PASSWORD = '$qlhub'

GO



--alterring service master key

--SMK use current service account of SQL Server

--so it is good practise to regenerate SMK whenever you

--make any change in your service account.

 ALTER SERVICE MASTER KEY REGENERATE;

 GO


Actually every database in SQL Server 2008 instance can have one DMK which used to encrypt and decrypt Asymmetric Key and Certificate Key. I will describe what Asymmetric Key is and what Certificate key is in later article as the scope of this article is DMK.

Well, after reading short description above, you must have came to know that DMK is somehow MUST to go further in native encryption and decryption of SQL Server 2008. Let us now look at some small practical snippets about DMK.



Use AdventureWorks

GO




--create database master key, it is good to kepp password with it

--Password use windows password complexiti policy, if there is any.

--when you create DMK with password, it uses triple Data Encryption Standard to protect it

CREATE MASTER KEY ENCRYPTION BY PASSWORD='$qlhub'

GO




--for backing up DMK, follow the script given here,

--it will backing up DMK in adv.dmk file to D drive

--with password '$qlhub'

--don't forget to remember password as it will be need 

--while restoring DMK from file in crisis situation

BACKUP MASTER KEY TO FILE = 'D:\adv.DMK'

ENCRYPTION BY PASSWORD='$qlhub'

GO




--restoring DMK from file whenever needed

RESTORE MASTER KEY FROM FILE = 'D:\adv.DMK' 

DECRYPTION BY PASSWORD = '$qlhub' --used for decrypt the DMK restored from file

ENCRYPTION BY PASSWORD='$qlhub.com' --this password will be used to encrypt DMK after it gets loaded into the DB

GO



By default, when you generate the Database Master Key (DMK), it is encrypted by SMK (Service Master Key) so that anybody with sysAdmin role can decrypt your DMK, this could be a security thread in some environment so you have to turn this feature off by following command.



--altering DMK

ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;





Now, let me share you one of my practical example I have used so many times. Generally while developing the project, I used to create DMK in development environment, when it is needed to put the work in live environment, I used to take backup of DMK from development server, put a .DMK file to live server, restore that .DMK file in live server and execute following commands so that certificates I created on my development DB works well on my live server too.



OPEN MASTER KEY DECRYPTION BY PASSWORD = '$qlhub'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY




Once you have SMK and DMK, you will have choice to go for Certificates, Asymmetric key, Symmetric key. Scope of this article is use of certificate. Let us start our journey.



Certificate is a kind of Asymmetric encryption with some additional metadata. In Asymmetric encryption data got encryption by two different methods but mathematically both are same. You can use “CREATE CERTIFICATE” T-SQL for creating new or use existing certificate.



--create new cerfiticate

CREATE CERTIFICATE ADV

ENCRYPTION BY PASSWORD ='$qlhub'

WITH SUBJECT ='ADVENTUREWORKS CERTIFICATE',

START_DATE='09/14/2009', --IF NOT PROVIDED, CURRENT DATE IS START DATE

EXPIRY_DATE='09/13/2015'-- IF NOT PROVIDED, ONE YEAR AFTER START_DATE IS EXPIRY_DATE

go




I have observed that people are really very cautious about the data and used to take regular backup of data but not that much cautious for certificates. I HIGHLY recommend taking backup of your certificate as soon as you create it, so let us seeing the script of that.



BACKUP CERTIFICATE ADV

TO FILE = 'd:\ADV.CER'

WITH PRIVATE KEY

(

      FILE='d:\ADV.PVK',

      ENCRYPTION BY PASSWORD='$qlhub',

      DECRYPTION BY PASSWORD='$qlhub'

)

go




Generally you think of restore command as soon as you finish backup. Let me tell you that there is no restore certificate command as you can restore your existing certificate from your backup file itself from CREATE CERTIFICATE command only. Have a look.



--LET US drop just created certificate and restore it from

--.cer file so that we are sure that certificate is created

--perfectly

DROP CERTIFICATE ADV



--restore from file

CREATE CERTIFICATE ADV

FROM FILE='D:\ADV.CER'




Once you create certificate and take a backup of it, now, you are ready to encrypt your data with certificate and decrypt it whenever it is needed.




DECLARE @Text nvarchar(max)

DECLARE @TextEnrypt varbinary(128)

DECLARE @TextDecrypt nvarchar(max)

SET @Text=N'hi, this is first certificate test, created by Ritesh Shah'

SET @TextEnrypt=ENCRYPTBYCERT(CERT_ID('ADV'),@Text)

SET@TextDecrypt=DECRYPTBYCERT(CERT_ID('ADV'),@TextEnrypt,N'$qlhub')

SELECT @Text AS 'ORIGINAL TEXT',@TextEnrypt AS 'Encrypted Text',@TextDecrypt as 'Decrypted Text'

GO




I have a Database Master key and planned out my encryption strategy at least for the near future as follows:

DATABASE MASTER KEY
|_____Certificate
         |________SYMMETRIC KEY encrypted by Certificate above.

So I created my DB Master Key with a Password, then I created my certificate and then created my Symmetric Key with decryption by certificate.  What I found out was that currently we had implemented encryption and decryption by using an external encrypter/decrypter which was function based.  So you would use something like:

SELECT dbo.fn_EncryptMyString('41111111111111111')

I would get the character value back with it encrypted.  Then I would be able to decrypt with

SELECT dbo.fn_DecryptMyString(fieldInDatabase)

When moving to SQL Server encryption using SYMMETRIC KEY en/decryption, doing it inside a Function is not supported for using a DATABASE MASTER KEY because of the nature of a function in a query.  So you cannot open a Symmetric Key inside a function, you must use Certificates.

This is what led me to this experience.  First you Create or Open your DATABASE MASTER KEY and then you would create the Certificate with all the information that is necessary to create it:

CREATE CERTIFICATE NameOfCertificateENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'WITH SUBJECT = 'This is My Subject', EXPIRY_DATE = '10/31/2009';

When you get done with that, you will want to backup the key (the Private key will be encrypted with the database master key) in order to recover if you lose the DB Master Key. Having the Private Key of the Certificate will allow you to still decrypt the data after you reestablish the DB Master Key in the database. 

BACKUP CERTIFICATE NameOfCertificate TO FILE = 'c:\storedcerts\NameOfCertificatecert'WITH PRIVATE KEY ( FILE = 'c:\storedkeys\NameOfCertificatekey' , ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh' );

This will basically backup the Certificate Private Key to the file named above and you can then restore it to the database after moving the database to another server or whatever.

This is all well and good, but when testing it as SA or Sysadmin you will always be able to use the Certificate, but as a normal user that does not have DBO privileges or other privileges that give you rights to the certificate, there are 2 permissions that you must set in order to use the Certificate as a non-DBO or higher privilege user.

I found this nice explanation on MSSQL Tips that is very easy to understand and is the same information as in BOL.
It is found here.

The permissions are:
GRANT VIEW DEFINITION ON CERTIFICATE :: certMSSQLTIPS TO <Database user/role>GRANT CONTROL ON CERTIFICATE :: certMSSQLTIPS TO <Database user/role>

If you do not grant these permissions, you will just get a NULL value back from either DecryptByCert or EncryptByCert, no error messages or anything, just a NULL.


What Can Certificates Be Used For?

Certificates, which are second in line in the encryption hierarchy, can be used to create symmetric keys for data encryption or to encrypt the data directly. Information on certificates currently in the database can be retrieved through the sys.certificates DMV:



How Can I Create a Certificate?

There are a number of ways to create certificates. SQL Server 2005 shipped with the ability to create self- signed certificates, so if you're in a hurry to get things up and running then create one using the following syntax:


USE MSSQLTIPS
CREATE CERTIFICATE certMSSQLTIPS WITH SUBJECT='MSSQLTips Certificate', 
EXPIRY_DATE = '08/12/2012'



If an expiration date is not specified in the creation statement then a default expiration date of one year from the creation date will be applied. Also, the certificate created by the statement above will be encrypted using the Database Master Key. If you would prefer that a password be used instead of the Database Master Key then the statementENCRYPTION BY PASSWORD='<Password>' can be used. You can see that the certificate is encrypted using the password in the sys.certificates view:



Another method of creating a certificate is by using .cer and .pvk files issued from a Certificate Authority. Since most, if not all, Active Directory domains have a Certificate Authority in the infrastructure, you can request a certificate by using an Advanced Request. Click here for additional information on requesting certificates from a Certificate Authority. Certificates can also be created by using signed executable files and dll's.

Can I Backup The Certificates?

Certificates can be backed up the same way as keys can be backed up. Remember that certificates and keys should always be backed up to secure media that can be stored offsite in case of disaster. The syntax for backing up certificates is:


USE MSSQLTIPS
BACKUP CERTIFICATE certMSSQLTIPS TO FILE = '<FileLocation>', 
ENCRYPTION BY PASSWORD='<Password>'



How Do I Restore a Certificate From a Backup?

A certificate can be restored from a previous backup, but there is no "restore certificate" command. You must use the CREATE CERTIFICATE command and specify the file location and password used during the backup process.

What Permissions Does A User Need To Use The Certificate?

In order to use a certificate for decryption a user must have VIEW DEFINITION and CONTROL permissions on the certificate. These permissions can be assigned either individually or through Database Roles. To grant permissions to users and groups, use the following syntax:


USE MSSQLTIPS
GRANT VIEW DEFINITION ON CERTIFICATE :: certMSSQLTIPS TO <Database user/role>
GRANT CONTROL ON CERTIFICATE :: certMSSQLTIPS TO <Database user/role>



This syntax differs slightly from traditional GRANT statements in that the scope qualifier (::) must be used.