CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
EvilDr
EvilDr

Reputation: 9610

Creating a certificate to use on multiple machines

I created a certificate and key using the following method on Machine A. This was to encrypt a password column into varbinary, and then decrypt it back when users logged in.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ReallyLongPasswordHere';
CREATE CERTIFICATE MyCertificate01 WITH SUBJECT = 'My Certificate 01';
CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyCertificate01 ;

I then (unexpectedly) needed to duplicate the database onto a cloud VM (Machine B) for testing a new schema change. After a struggle I realised that I had to export Machine A's master key and use it to overwrite Machine B's master key in order for decryption to work.

I therefore used this code on Machine B to achieve successful decryption:

RESTORE SERVICE MASTER KEY FROM FILE = 'C:\......\BackedUpServiceMasterKey' 
DECRYPTION BY PASSWORD = 'BackupPassword' FORCE;

However I realise now that whenever I spin up new test machines, the master key will always need replacing. This comes with a danger of it preventing any other database encrypted values from being able to be decrypted.

In terms of re-using certificates/keys and decrypting data that already exists, should I have created the certificate using a password instead of the master key? For example,

CREATE CERTIFICATE ... ENCRYPTION BY PASSWORD = 'password'

...and used the same password value on each new VM to create a replica certificate/key?

Upvotes: 1

Views: 508

Answers (1)

Roger Wolf
Roger Wolf

Reputation: 7722

Probably yes, encrypting certs with passwords is no less secure as with master keys.

You see, master key is a symmetric (means: weak) key, which in turn is protected with password (service master key also comes into play here, sure, but since it's a constant on any given instance, we can ignore it). So, one way or another, you end up using some kind of password anyway, which to me makes master keys rather redundant.

Upvotes: 1

Related Questions