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. Now let’s try to have a closer look at SQL Server encryption. 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): 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. 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 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.
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. What result you may expect?Īn error message which says (0x80131904): Please create a master key in the session before performing this operation. 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). 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. I also feel that it may facilitate understanding of the process itself. This scenario will serve us as an illustration and essential “why part”. We will consider scenario of migration of already existing K2 database to a new SQL Server instance.
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. StarWind Virtual Tape Library (VTL) OEM.
0 kommentar(er)
