If you have an SAP system that you are planning to make a copy
of and you have TDE enabled on it, you will run into a problem when trying to
copy the database. This is because the
source system is protected from the exact thing you are trying to do.
In order to make this work, you will simply need to setup
your Target System to use the same encryption certificate and key as the
source. This should not be a problem, since you should have access to all the
necessary information and files.
The TDE keys should be found in the SQL Server directory structure
on the source system. For example, it may be stored here:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA
For simplicity in scripting, it may be wise to copy the
files to a different directory temporarily. I had to copy them to a local drive
for the query to work correctly.
You will need the following files: tdeCert.cer and
tdeCert.pvk
You should also use the same encryption password used to
originally set TDE in the source system.
Make absolutely sure you have the tdeCert.cer and
tdeCert.pvk files and they are located as stated in the script before running
the script. Otherwise encryption will be set without the necessary information
for the copy to work, and you will have to remove the encryption and start
again.
Script:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '[password that you
set]'
GO
CREATE CERTIFICATE tdeCert
FROM FILE = 'C:\tdeCert.cer'
WITH PRIVATE KEY (FILE =
'C:\tdeCert.pvk',
DECRYPTION BY PASSWORD = '[password
that you set]');
GO
For another example of how to do this see http://sql-articles.com/blogs/how-to-copy-move-a-database-that-is-encrypted-with-tde/
Hope this helps...
