0

I have two instances of SQL Server 2016 Enterprise, each running on a different server. They are standalone servers. There is a shared drive on ServerA which can be accessed from ServerB. I have set up log shipping from a database (TESTDB) on MAIN_DB to one on MAIN_UAT. I did this by creating the same local user profile on each server (SQLService) with the same password and changing the SQL Agent and SQL Server jobs to run under this profile on each server using SQL Server Configuration Manager. This seems to work quite happily. I then tried to implement TDE on the databases. In the first instance, I stopped log shipping. I then created a second database (TESTDB2) on MAIN_DB and set up log shipping to that from the primary database on MAIN_UAT. Then I used this script to set up TDE. Once the backup and restore cycle had run, TESTDB2 was also encrypted. Again, this worked without problems.

use master
go
create master key encryption by password='something cryptic'
go 
create certificate TDECertificate with subject='Log Shipping certificate'
go 
create database encryption key with algorithm=AES_256 encryption by server  certificate TDECertificate  
alter database TestDB set encryption on

I then tried to reproduce this on the secondary server – MAIN_UAT. On the primary server I ran this script. 

backup certificate TDECertificate to file='D:\Certificates\myCertificate.crt'
with private key (file='D:\LANSHARE\Certificates\LogShippingKey.key',encryption by password='something cryptic')

backup master key to file='D:\Certificates\MasterKey.key' encryption by password='something cryptic'

I copied the three resulting files into a folder on the secondary server and gave SQLService (the local user) full control over the folder and files. I then tried to install the certificate on the secondary server with this script. 

use master
go
restore master key from file='D:\Certificates\MasterKey.key' decryption by password='something cryptic'encryption by password='something cryptic'
go
create certificate TDECertificate from file='D:\Certificates\myCertificate.crt' with private key (file='D:\Certificates\LogShippingKey.key',decryption by password='something cryptic',encryption by password='something cryptic')
go

use TESTDB2
go
create database encryption key with algorithm=AES_256 encryption by server certificate TDECertificate

This gave me this error, which is where I’m stuck. 

Msg 15151, Level 16, State 1, Line 14
Cannot find the certificate 'TDECertificate', because it does not exist or you do not have permission.

In a nutshell, how do I implement TDE with log shipping on servers on different domains? Thanks for any help you can give.

alan murphy
  • 11
  • 2
  • That last error has me suspecting where the certificate got created on your UAT server. What does a `select * from master.sys.certificates where name = 'TDECertificate';` show? – Ben Thul May 08 '17 at 22:40
  • Hi @Ben, the certificate does exist in the Master database - perhaps I should have mentioned that earlier. The encryption type is PW and its principal Id is 1. It is also active for begin dialog. – alan murphy May 09 '17 at 10:59
  • Weird. The error message is fairly clear here - it can't find that certificate where it expects to find it. It *could* be a permissions issue, I suppose (I've been assuming that you're running this as some sort of administrator account so ruled that out). Here's an interesting idea - who owns the TESTDB2 database? It could be that it's that principal's permissions that matter (I'd be mildly surprised, but it's worth a check). – Ben Thul May 09 '17 at 12:30
  • All the databases are owned by sa, except the primary on Main_DB which is owned by the Windows admin account. – alan murphy May 09 '17 at 14:08
  • I fear I've run out of possible explanations. I'm interested to see if someone has a different angle of attack! – Ben Thul May 09 '17 at 14:37

0 Answers0