I am working on the data encryption procedure for the first time.
I have encrypted the data in my database and want to migrate the data into a different server.
The steps I used to encrypt the data is:
-- Master Key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxx2010'
-- Change the existing password if needed
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'encrypt10N4DMK';
-- Add a second password if needed
ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'S2encrypt10N4DMK'
-- Add Master Key to Service Master Key
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CREATE CERTIFICATE xxxxxxEncryptionCertificate FROM FILE
WITH PRIVATE KEY (FILE = 'C:\CertsKeys\dataencryption.xxxxxxxxx.com.pvk',
DECRYPTION BY PASSWORD = xxxxxx2010')
CREATE SYMMETRIC KEY Sym_xxxxxxDataEncryptionkey
WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE xxxxxxEncryptionCertificate
Using the encryptbykey function, I have encrypted the data.
Now I have backed up this database and also the master key.
backup service master key to file = 'C:\service.key' encryption by password = 'S2encrypt10N4DMK';
In the destination server, I restored the database and restored the master key
restore service master key from file = 'C:\service.key' decryption by password = S2encrypt10N4DMK';
While opening the symmetric key, I am getting the error "Either no algorithm has been specified or the bit length and the alogithm specified for the key are not available in this installation of windows".
It will be helpful if I can get the sequence of instructions to migrate a SQL Server 2005 encrypted database.
After restoring the master key, do I need to drop and recreate the certificate and symmetric keys? Do I need to copy the certificate file?