I just recently came in touch with MS SQL Encryption, where I used a Master Key, a Certificate and a Symmetric Key. When I tried to DEcrypt it, I've had quite the hard time getting through the key opening in the right order. Essentially I wrote something like this:
OPEN MASTER KEY BY PASSWORD ('Password')
OPEN CERTIFICATE BY MASTER KEY
OPEN SYMMETRIC KEY SSN_01 BY CERTIFICATE
which obviously didn't work, and no it's not exactly what I wrote it's just to give you an idea of what I did. Now I eventually figured out that I only needed to open the Master Key and then open the Symmetric Key by the Certificate. My question is why? Why have the Master Key and the Certificate been automatically linked?
Think of it this way: once you have the master key open, when you go to open the symmetric key, SQL Server will say "Hmm… the certificate that protects that is locked. Do I have the means to unlock it?". And, sure enough, it does! Assuming that the certificate's private key is encrypted with the database master key, of course You can check sys.key_encryptions to see what encrypts/protects what and sys.openkeys to see what keys you have open in this session.