Does anyone know if a field encrypted with an Symmetric key would be the exact same output on different servers given the same cert master key and key credentials?
I want to do a bulk copy from dev to test servers and want to know if the encryption would carry over.
Create Symmetric Key on MSDN
Thanks!
For those that can't see I forgot a comma, to clear it up, a MASTER KEY, a CERTIFICATE and a KEY
given the same cert master key and key credentials
There is no such concept as 'cert master key'. Do not invent your own terms, stick to the existing nomenclature so everyone understands what you're saying. There is a master key, there are certificates.
If you followed good practices and you deployed a correct encryption hierarchy then your data should be encrypted with one or more symmetric keys and these in turn would be encrypted with a certificate that is either encrypted with the database master key or a password.
Copying data encrypted with a symmetric key between databases is possible. In order to succeed you need to follow exactly the steps described in Create Identical Symmetric Keys on Two Servers which will result in a pair of keys that have the same identity and the same key material. Data encrypted with either one of the two servers can be decrypted on the other server.
That being said, any organization that is serious enough about data as to encrypt it in production and at the same time is willing to create a copy of the encryption key on dev machines, not to mention copy actual sensitive data to the dev boxes, is a joke imho. Technology is never the weak link.