SQL Server column level encryption consists of five types of keys. They are Service Master Key, Database Master Key, Asymmetric key, Certificate and Symmetric key. Whenever you implement column level encryption, a complete hierarchy of keys needs to be created and able to decrypt the other keys along the hierarchy. All keys are encrypted in the system at all times on disk and only decrypted in memory when they are in use. There are many diagrams of the SQL Server key hierarchy, which is based on the ansi X.917 standard. For your specific hierarchy, I'll describe it like this:
- the symmetric key can only be used after it is decrypted by the
private key of the certificate that protects it.
- The private key of the certificate that protects the symmetric key can only be used if it is decrypted by the database master key that protects it
- the database master key which protects the private key of the certificate can only be decrypted by the service master key that protects it
- the service master key which protects the database master key can only be decrypted by the Data Protection API, which is part of the Windows OS, that protects it
When a database master key is created, it is encrypted using two methods. First, it is encrypted by the password that you provided, which in this case is "StrongPassword". Second, it is automatically encrypted by the service master key, which allows you to use the encryption without putting the password in procedures and other code on the server. When you opened the symmetric key, you specified to decrypt it using the private key of the certificate but did not need to specify that the DMK needed to be open to decrypt the private key needed. The system performed that action using the service master key for you. You can see this in the system tables by using the sql statement below:
from sys.symmetric_keys sk
join sys.key_encryptions ke
Also, when you create a symmetric key, you can specify the argument key_source, which forms the basis of creating the actual key, but if you don't the database engine will create a random key for you. The symmetric key is protected by the certificate, not a derivative of it. It would be very dangerous if the symmetric key were able to be derived from the certificate or it's private key. The Open Master Key command is redundant since it is already been opened so that the private key from the certificate can be used.
I would also highly advise against using the master database for column level encryption for your user data.
I hope that the above description was clear because I wanted you to understand why you are having a problem before providing the resolution. The problem is that the Service Master Key on your local SQL server instance can't decrypt the Database Master Key. You can fix this in one of three ways. Back up the SMK from production and restore it on your local SQL Server or backup the DMK for the production database and restore it on the database on your local SQL Server or move the command to open the database master key by password before the open symmetric key command. Backing up the DMK would be the better and less impactful choice because restoring an SMK could be resource intensive. I would advise one of the first two resolutions since you don't want to put passwords in your code for security reasons.