About Author

Ritesh Shah is an author of many technical article on Microsoft Technology, especially on SQL-Server, .NET technology like C# and ASP.Net etc. He is having 10+ year of experience in IT industry and working as Consultant Project Leader and Database Administrator.

Long way back, we had to take help from third party tools in order to encrypt and decrypt data. Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, we have improved and in-built facilities for Encryption and Decryption within SQL Server.

To encrypt data and decrypt it, it is really a very crucial task as one mistake and your data go out of your reach. It can become more difficult when you will encrypt some data in one database in one server and try to restore that database into different server.

In this Article, I am going to show you the script which can encrypt data in one database on one server, take its backup, and restore that encrypted database anywhere else and you will get your data with 100% security and no data risk.

We will now create one database which is going to be used in throughout this example.

Well this is sixth article in the series of Encryption and Decryption. It was really good to have good response from all of you about this series. I would like to give links about past 5 article in these series for those who directly landed to this page.

Ok, so above links are well enough, if you want to get the concept of encryption – decryption in Microsoft SQL Server 2008 from scratch, do read all articles given above too from part 1.

Anyway, let us move ahead with Symmetric key. This is at the very bottom level in hierarchy of Encryption-Decryption. Further you can refer hierarchy chart given in my first article. Symmetric key can either use same key for encryption and decryption or use different key for encryption and decryption but do keep in mind that both the keys are mathematically related via simple transformation. Symmetric key could be encrypted by Certificates or via Asymmetric key as per your need.

Well this is sixth article in the series of Encryption and Decryption. It was really good to have good response from all of you about this series. I would like to give links about past 5 article in these series for those who directly landed to this page.

Ok, so above links are well enough, if you want to get the concept of encryption – decryption in Microsoft SQL Server 2008 from scratch, do read all articles given above too from part 1.

Anyway, let us move ahead with Symmetric key. This is at the very bottom level in hierarchy of Encryption-Decryption. Further you can refer hierarchy chart given in my first article. Symmetric key can either use same key for encryption and decryption or use different key for encryption and decryption but do keep in mind that both the keys are mathematically related via simple transformation. Symmetric key could be encrypted by Certificates or via Asymmetric key as per your need.

Well, we are moving one step forward for data encryption and decryption, I have already described some of the essential topics in this area which are mandatory before implementing this step. If you want to go backward, have a look at series of article on this topic at below given link.

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

CREATECERTIFICATE ADV

ENCRYPTIONBYPASSWORD=‘$qlhub’

WITHSUBJECT=‘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.

BACKUPCERTIFICATE ADV

TOFILE=‘d:\ADV.CER’

WITHPRIVATEKEY

(

FILE=‘d:\ADV.PVK’,

ENCRYPTIONBYPASSWORD=‘$qlhub’,

DECRYPTIONBYPASSWORD=‘$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

DROPCERTIFICATE ADV

–restore from file

CREATECERTIFICATE ADV

FROMFILE=‘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’

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.

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!!!!

Installing MySQL Server in CentOS 7 As a part of some latest R&D, I wanted to replicate some data from SQL Server to MySQL. Since there is no native way to do this, I am preparing my own framework which suits my requirement. To work on the project, I have purchased, recently, cloud space with […] The post Installing MySQL Server in CentOS 7 appeared firs […]

I have started working with Microsoft SQL Server 7.0 in late 1999 and even today I become very excited with the new version of Microsoft SQL Server. SQL Server 2016 has been in market for quite some time but now finally Microsoft has released service pack 1 for SQL Server 2016 to download. This […] The post SQL Server 2016 Service Pack 1 appeared first on […]

SQL Server 2014 CTP 1 available to download SQL Server 2014 is recently announced in TechED Europe. I am really very excited to download and knew more about it. You can download SQL Server 2014 CTP 1 from here. Each new version of software needs compatible hardware and software. Those are mandatory before you try […] The post SQL Server 2014 CTP 1 available […]

I have very often seen that people are so used to with traditional methods and hence never think in terms of set based programming, they used to write code with evergreen traditional method which people used to use in sequential language like C or many be ASP etc. Let us see this practically with an […] The post Dynamic filter in WHERE Clause with CASE_WHEN_ […]

SQL Server Login permission to read queue message count of Service Broker Few weeks back, I have written one article “Find Service Broker Queue Count in SQL Server”. One of the blog reader has created the UDF I have given in that article and tried to fetch value of that UDF from his web application […] The post SQL Server Login permission to read queue messa […]

Count active Thread for Service Broker Queue in SQL Server Since we have big service broker setup, it is very important to know for us about thread running for target queue. Target queue is the queue which suppose to do the work of actual business logic and we used to give proper naming convention to […] The post Count active Thread for Service Broker Queue […]

Error Fix – Replication subscriber does not exist on the server anymore Recently one of my client has faced weird situation related to SQL Server Replication. Their main database goes down so they started running mirroring database as a primary database, removed subscriber and distributor. Once they have setup primary server back properly, they have […] The […]

Bug Fix – Show Result Pane not working in SQL Server 2012 with CTRL + R Once you are addicted with some keyboard short keys then you hate to use mouse for those things for sure. SQL Developer/DBA are, mostly, addicted with “CTRL + R” short key which is being used to show/hide result panel […] The post Bug Fix – Show Result Pane not working in SQL Server 2012 […]

Find disabled job in SQL Server I, generally, don’t tend to keep disabled job in my server. I would always like to remove disabled job after generating the script and keep the backup of script and this is the reason whenever someone intentionally or unintentionally disabled the job, my monitoring application shows the server name […] The post Find disabled j […]

Copy and archive SQL Server database backup Though you are working as SQL Server DBA, you have to think, at least sometime, beyond relational. We can schedule different types of backup, ie: full, differential, transaction etc., from within SQL Server but what if you want to set archiving policy also? There are so many different […] The post Copy and archive […]

Error Fix Database Mail XPs error in SQL Server Today I have received one email from one of the blog reader since he was facing an issue in his .NET application which is related to SMTP email in SQL Server. Here is the error he was facing: SQL Server blocked access to procedure ‘dbo.sp_send_dbmail’ of […] The post Error Fix Database Mail XPs error in SQL Ser […]

Deal with message queue conversation with state CD in Service Broker Each side of a Service Broker conversation is represented by a conversation endpoint and sys.conversation_endpoints catalog view contains a row per conversation endpoint in the database. Generally when service broker accept record in message queue, it used to process message and remove it a […]

Keep watch on Service Broker Queue Delay in SQL Server We have very large setup of Service Broker enabled in our live databases. Out of which, some of the nightly business processes create a huge target queue which we suppose to finish during the night time itself. If we can’t cleanup all these queue in […] The post Keep watch on Service Broker Queue Delay i […]

Milestone of 500+ article This post is 504th post in my blog. I have started sharing my knowledge in form of blog article three years back. Initially I, just, wanted to keep my script collection online so that I can find it easily as and when I need it but later on my blog has […] The post Milestone of 500+ article appeared first on Ritesh Shah (Extreme-Advi […]

Update XML attribute value based on condition with XQUERY Recently I had one article which shows how to update XML attribute value which doesn’t had any checking/condition while update. One of the reader asked me how can he update value based on condition. Here is the solution for it. INSERT INTO Orders (ClientID,TestDetails) SELECT ‘CHEM02′,'

Query to Find missing job in SQL Server While managing multiple instance of SQL Server, it is little bit difficult to keep watch on every jobs running in every SQL Server Instances we are managing. I have created one monitoring application which suppose to be watched by at least one DBA 24*7. I have one […] The post Query to Find missing job in SQL Server ap […]

Update XML attribute value with XQUERY in SQL Server Recently I have written article on Inserting attribute in existing XML Elements and Delete XML element. Today I am going to show how to update existing attribute value in XML elements. INSERT INTO Orders (ClientID,TestDetails) SELECT ‘CHEM02′,'’ UNION ALL SELECT ‘ACCU01′,'

XQuery in SQL Server to insert XML attribute in existing XML element Use of XML is being popular in SQL Server and it comes handy in many situation as I have explained in many of my past Xquery/XML related article. Today I come up with one more example which shows usage of XQuery to insert […] The post XQuery in SQL Server to insert XML attribute in existing […]

XQuery in SQL Server to delete XML element Use of XML is being popular in SQL Server and it comes handy in many situation as I have explained in many of my past Xquery/XML related article. Today I come up with one more example which shows usage of XQuery to delete element from XML data […] The post XQuery in SQL Server to delete XML element appeared first on […]

WHERE clause on XML column in SQL Server table Xquery is one of the wonderful feature I appreciate most in SQL Server. Due to lack of knowledge, people doesn’t tend to use it. I always prefer XML in case of I have to pass multiple values in one parameter of stored procedure. It is even […] The post WHERE clause on XML column in SQL Server table appeared firs […]