Migration of Sql Server 2005 Encrypted databases

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
          = 'C:\CertsKeys\dataencryption.xxxxxxxxx.com.binary.cer'
          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?

Thanks
Sri
sriniramAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Check out the Backup and Restore scripts here:

http://social.msdn.microsoft.com/Forums/en-US/sqlsecurity/thread/9bcb4a32-af88-4df7-8649-b6c34c130ba0

kindly revert if you need additional help.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sriniramAuthor Commented:
Thank you. It helped me.

Now I am copying a database from the test server to my local machine where I have SQL Express 2005.

As I have windows XP on my local machine, I am using DESX algorithm instead of AES_128 which is not supported in windows XP.

The procedure I followed is:
Created a new database in my test server (windows 2003), created master key, certificate and symmetric keys. Then I added some data both raw and encrypted intoo a table.
Next I backuped the database, service master key, master key, certificate.

In the local machine I dropped the existing master key, certificate, symmetric key and restored the backup taken on my test server.

Without any restoration of the keys / certificates, I was able to decrypt the data and it worked fine.

Then I dropped the master key, certificate and symmetric key. while restoring the service master key I got the message "The old and new master keys are identical. no data re-encryption is required".

Now while restoring the master key I am getting an error invalid syntax. I am giving the commands I have used:

The steps followed are:

IN THE SOURCE MACHINE:
create table testtable (col1 varchar(9), lastname varchar(20), col1enc varbinary(100), lastnameenc varbinary(100))

Insert into testtable
    select top 5000 col1, lastname,
        EncryptByKey(Key_GUID('Sym_xxxxxxxxDataEncryptionkey1'), col1),
        EncryptByKey(Key_GUID('Sym_xxxxxxxxDataEncryptionkey1'), lastname)
    from xxxxxxxx_cobra_UAT_Copy.dbo.testenc


OPEN SYMMETRIC KEY Sym_xxxxxxxxDataEncryptionkey1 DECRYPTION BY CERTIFICATE xxxxxxxxEncryptionCertificate1;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxxx2010'

    -- Change the existing password if needed
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'yyyyyyyy10N4DMK';

    -- Add a second password if needed
ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'S2yyyyyyyy10N4DMK'

    -- Add Master Key to Service Master Key
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

CREATE CERTIFICATE xxxxxxxxEncryptionCertificate1
    FROM FILE = 'C:\CertsKeys\dataencryption.testtable.com.binary.cer'
    WITH PRIVATE KEY (FILE = 'C:\CertsKeys\dataencryption.testtable.com.pvk',
    DECRYPTION BY PASSWORD = 'xxxxxxxx2010')

CREATE SYMMETRIC KEY Sym_xxxxxxxxDataEncryptionkey1
WITH ALGORITHM = DESX
ENCRYPTION BY CERTIFICATE xxxxxxxxEncryptionCertificate1

open master key decryption by password = 'yyyyyyyy10N4DMK'
BACKUP MASTER KEY TO FILE = 'D:\Murthy\DATEncTest\20100412\MasterKey.dat' ENCRYPTION BY PASSWORD = 'yyyyyyyy10N4DMK';
BACKUP CERTIFICATE xxxxxxxxEncryptionCertificate1 TO FILE = 'D:\Murthy\DATEncTest\20100412\xxxxxxxxEncryptionCertificate1.dat';
BACKUP CERTIFICATE xxxxxxxxEncryptionCertificate1 TO FILE = 'D:\Murthy\DATEncTest\20100412\xxxxxxxxEncryptionCertificate1.dat'
    WITH PRIVATE KEY (FILE = 'D:\Murthy\DATEncTest\20100412\dataencryption.testtable.com.pvk', ENCRYPTION BY PASSWORD = 'xxxxxxxx2010')

BACKUP SERVICE MASTER KEY TO FILE = 'D:\Murthy\DATEncTest\20100412\S_Master_Key.dat' ENCRYPTION BY PASSWORD = 'yyyyyyyy10N4DMK';

CREATE/ALTER CERTIFICATE WITH PRIVATE KEY (FILE = ‘<pvk_backup_filename>’, DECRYPTION BY PASSWORD = ‘<pvk backup password>’)


IN THE DESTINATION MACHINE:
Restore the database back taken from the source machine.

When the data is selected, I was able to decrypt the encrypted data. Then I dropped the keys and certificates.

drop symmetric key Sym_xxxxxxxxDataEncryptionkey1
   
drop certificate xxxxxxxxEncryptionCertificate1

drop master key

Restoration of keys and certificates:

restore service master key from file = 'C:\xxxxxxxx\20100412\S_Master_Key.dat' decryption by password = 'yyyyyyyy10N4DMK';

restore master key from file = 'C:\xxxxxxxx\20100412\MasterKey.dat' DECRYPTION BY PASSWORD = 'yyyyyyyy10N4DMK'

Currently I am getting a syntax error while restoring the master key as "Incorrect syntax near 'yyyyyyyy10N4DMK'".

I am working on solving this.

Please let me know whether my approach is correct.

Thanks
Sri
   
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Regret for the delay as I missed this question..
Try doing this:

use ur_encrypted_db
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxxxxx2010'
0
sriniramAuthor Commented:
Yes. I used your suggestion and it is working for me.

Thanks
Sri
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.