Initially my plan for this blog post was to write up a short explanation of how to copy SQL Server instance master key between SQL Server instances, but too many people around me keep saying “start with why”, so I decided to go through some practical example from K2 world to illustrate when you may need to perform this operation. We will consider scenario of migration of already existing K2 database to a new SQL Server instance. This scenario will serve us as an illustration and essential “why part”. I also feel that it may facilitate understanding of the process itself.
Let’s consider the following example. You just moved your K2 database to a new SQL Server instance, ensured that K2 service account has db_owner permissions on it (more information on required permissions can be found here) and trying to start K2 service. Assuming you are following the best practice of using SQL Server alias, accommodating this change from K2 side is a super easy thing: you just stop K2 service, edit your SQL alias adjusting Server name property using cliconfg.exe and start K2 service again to verify the results (no K2 reconfiguration whatsoever is required). What result you may expect?
Well, this one:
An error message which says System.Data.SqlClient.SqlException (0x80131904): Please create a master key in the session before performing this operation.
Why is that? This is because we are unable to open Database Master Key (DMK) which is necessary to work with encrypted data in K2 database. DMK is encrypted with use of Service Master Key (SMK) which is unique for each SQL Server instance and acts as a root of the SQL Server encryption hierarchy. It means that by default, after migrating your K2 database to another SQL Server instance you are unable to perform encryption/decryption operations within it as you are unable to open DMK required for that. DMK lives within your K2 database (so you have it already), but you cannot open it since it is encrypted with SMK from your old SQL Server instance which is missing.
Specifically, in K2 service startup case, immediately after service startup K2 it needs to work with encrypted data (think of necessity to decrypt encrypted passwords in connection strings, for example), but runs into exception on the SQL Server side which you can also see in SQL Server Profiler (providing you selected Exception and User Error Message Events):
Now let’s try to have a closer look at SQL Server encryption. Windows Operating System Level Data Protection API (DPAPI) protects the SMK, and SMK which is created by SQL Server Setup in combination with password protects DMK. The SMK and all DMKs are symmetric keys, as you can easily check using SQL query:
DMK is used to encrypt certificate and asymmetric key, and those can be used to encrypt symmetric key which is the key which used for data encryption. Symmetric key being used for data encryption as using it is less resource intensive than use of certificate or asymmetric key, i.e. it gives you better performance. In any case, access to the start of encryption hierarchy is additionally protected by password.
But I guess these explanations may not work well without picture summarizing this, so here you have one:
This is slightly modified image from Microsoft documentation – you can find original here.
Now we know, that root of SQL encryption hierarchy is SMK which stored within the master database of your SQL Server Instance:
You have one on your new instance, but by default it is different that the one you had on the old instance. You can easily verify this by executing the following SQL query against your old and new instances:
If you execute the same query you will see that SMK has different create date and GUID. Here is sample output from my “old” instance which demonstrates that:
This key is required to open/decrypt your K2 DMK (asymmetric key) which lives in K2 database. DMK cannot be seen in SSMS GUI which can show you only Symmetric Key used for actual data encryption:
You can see DMK with help of the following SQL script:
So now after giving some explanations on why K2 service was failing to start and how SQL Server encryption works in general we can do a little test to see if encryption works in our migrated K2 DB after moving it to new SQL Server instance which has different SMK. We can do this using this SQL script:
As we do not have SMK from the old instance you will see that this script will fail with the same error which we saw in K2 service console mode output and in SQL Server Profiler:
As you can see we just cannot perform encryption/decryption operations. And I hope you already clear on how we go about fixing this.
When DMK is encrypted by SMK it can be opened transparently on as needed basis. A DMK that is not encrypted by the service master key can be opened only by using the OPEN MASTER KEY DECRYPTION BY PASSWORD statement and a password (in K2 database case, you can obtain this password from K2 support). Once key opened in that way you can use ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY statement to make it encrypted by new instance SMK. This how this script will look like:
Execution of this script will enable you to start K2 service, but you need to contact K2 support to get the password (unless you have it at your disposal already), and then run this script after database restore. This may be not desirable, especially when you need to involve different person/department for every action on SQL Server side.
We can use different approach of copying and restoring instance SMK from an old SQL Server instance to the new one. This operation can be performed as a part of new SQL instance preparation process and has two obvious benefits: a) you don’t need to contact K2 support to get password and b) your restored K2 database is ready to use immediately after restore completion.
If you look at K2 documentation dedicated to configuration of SQL Server AlwaysOn you will see that main point there is that you need to copy SMK from your primary replica to all secondary replicas before adding databases otherwise you will have to use FORCE key to replace existing SMK and run the risk of potential data loss.
Here is how we go about copying SMK between SQL Server instances. We first need to backup SMK on our original/existing SQL Server instance using the following SQL script (be sure to use strong password of your choice):
Next, we need to copy over SMK backup file to the new SQL Server instance and restore it there. In case you didn’t do this operation before restoring K2 database you may see the following error message saying that you must use FORCE parameter:
But if you perform this operation before restoring K2 database it will complete without any errors:
If you perform these steps before restoring K2 database on new instance, database encryption will be working right off the bat and you won’t experience any issues with K2 service startup. Now if you combine this knowledge with using SQL Server alias in your environment you can migrate your K2 database to the different SQL Server instance with minimum downtime.
I hope this blog post was useful and informative and thank you for reading 😊 Below you can find some additional links to learn more about SQL Server Encryption and configuration of SQL AlwaysOn for automated failover of the K2 Database.
SQL Server Encryption Hierarchy
BACKUP SERVICE MASTER KEY (Transact-SQL)
RESTORE SERVICE MASTER KEY (Transact-SQL)
Configuring MS SQL for AlwaysOn Automated Failover of the K2 Database
In case you have any questions or comments feel free to post them below.
