October 28, 2013 at 11:24 am
I also found the issue with trying to restore, the MS artcle does not say that you have to create a master key first. Once I did that I was able to get father. All I need to do now is repoint the mdf and ldf with move in the restore.
Thank you Gail, as always you are very helpful and insightful to my errors! 🙂
MCSE SQL Server 2012\2014\2016
January 13, 2017 at 9:22 am
Gail,
Can you help me please, I work in very restricted environment and I have granted the service account access to the data drive (where cert is stored) and to the drive where the encrypted database back up is stored. I'm trying to restore an encrypted database to new server. The code I'm using is posted below, I'm not sure if its the code, or if the service account needs access to the c drive besides the drive where the certificate or database is stored.
use master
go
create master key encryption by password = 'xxx'
go
create certificate TDE_DB_Encryptioncert
to file = 'f:\mssql\data\tde_db_encryptioncert'
with private key (file = 'TDE_DB_PrivateFile',
encryption by password = 'xxx')
go
use master
restore database xyz from disk = n'g:\mssql\backup from prod\xyz.bak'
with file = 1, nounload, replace, stat = 5
go
the error I continue to get is 15240 permissions.
thank you!
January 13, 2017 at 9:47 am
everykyto - Friday, January 13, 2017 9:22 AMGail,
Can you help me please, I work in very restricted environment and I have granted the service account access to the data drive (where cert is stored) and to the drive where the encrypted database back up is stored. I'm trying to restore an encrypted database to new server. The code I'm using is posted below, I'm not sure if its the code, or if the service account needs access to the c drive besides the drive where the certificate or database is stored.
use master
go
create master key encryption by password = 'xxx'
go
create certificate TDE_DB_Encryptioncert
to file = 'f:\mssql\data\tde_db_encryptioncert'
with private key (file = 'TDE_DB_PrivateFile',
encryption by password = 'xxx')
go
use master
restore database xyz from disk = n'g:\mssql\backup from prod\xyz.bak'
with file = 1, nounload, replace, stat = 5
go
the error I continue to get is 15240 permissions.
thank you!
https://msdn.microsoft.com/en-us/library/ff773063.aspx
I am not Gail but here is a link that will answer your question.
MCSE SQL Server 2012\2014\2016
January 15, 2017 at 1:05 pm
Can you post the full error message you are getting? The only thing I can think of is that the certificate thumbprint of the cert you created is different than the one that is protecting the database encryption key, but it would be nice to confirm that.
Are you sure that the certificate you created is the same one that was used to protect the DEK of the database you are trying to restore? If not you need to restore that cert on the target SQL Server. If you do not have it you need to unencrypt the source database and remove the DEK before trying a restore. If you do not have access to the source database where the backup was taken and do not have access to the cert/private key used to protect the DEK your backup will be regrettably useless.
Joie Andrew
"Since 1982"
January 16, 2017 at 9:06 pm
This was removed by the editor as SPAM
January 17, 2017 at 8:34 am
everykyto - Friday, January 13, 2017 9:22 AMGail,
Can you help me please, I work in very restricted environment and I have granted the service account access to the data drive (where cert is stored) and to the drive where the encrypted database back up is stored. I'm trying to restore an encrypted database to new server. The code I'm using is posted below, I'm not sure if its the code, or if the service account needs access to the c drive besides the drive where the certificate or database is stored.
use master
go
create master key encryption by password = 'xxx'
go
create certificate TDE_DB_Encryptioncert
to file = 'f:\mssql\data\tde_db_encryptioncert'
with private key (file = 'TDE_DB_PrivateFile',
encryption by password = 'xxx')
go
use master
restore database xyz from disk = n'g:\mssql\backup from prod\xyz.bak'
with file = 1, nounload, replace, stat = 5
go
the error I continue to get is 15240 permissions.
thank you!
Only use the encryption by password clause in the create cert command, if you wish to encrypt the certs private key with a password instead of the database master key. The correct syntax for the create cert command is
CREATE CERTIFICATE [MyNewCert] AUTHORIZATION [dbo]
FROM FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.cer'
WITH PRIVATE KEY (DECRYPTION BY PASSWORD = 'P@sswordt0encryptbackup',
FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.pky'
)
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 17, 2017 at 10:44 am
JasonClark - Monday, January 16, 2017 9:06 PMlkennedy76 - Monday, October 28, 2013 9:04 AMHey Guys, I am trying to move an encrypted database that some contractors installed over a year ago and they are now gone. When I try to backup the cert. with encrypted password it tells me I cannot write the file to the location I have selected. Yes I checked permissions and even added my login with god rights to the drive. I also created a folder on a local drive to the sql server and got the same error. I did read an article that says I have to use the same password that the database was encrypted with and I do not have that. I did want to reach out to you guys first before I come to conclusion that I am !@#$ and have to re-install the application. :crazy: Msg 15240, Level 16, State 1, Line 1 Cannot write into file ' \etwork drive\SQLPrivateKey'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.make sure the file name is unique. Let me know if changing the name of the file worked.
thanks for the reply Jason but this was 3-4 years ago, someone else is having issues.
MCSE SQL Server 2012\2014\2016
January 18, 2017 at 11:21 am
Thank you so much for the postings, I'm still having trouble after reading the link provided.
Yes I do have the original encrypted database available to me, and yes I have tried copying the certs from that server to the other server, where that database had previously been restored, but it fails with errors. I also tried un-encrypting the database in test environment, backing it up and copying backup over to the dev environment, I'm getting msg 33111, Level 16, State 3, cannot find server certificate with thumbprint "xxxx'.
Msg 3013, level 16, state 1, restore filelist is terminating abnormally.
January 19, 2017 at 2:18 am
everykyto - Wednesday, January 18, 2017 11:21 AMThank you so much for the postings, I'm still having trouble after reading the link provided.
Yes I do have the original encrypted database available to me, and yes I have tried copying the certs from that server to the other server, where that database had previously been restored, but it fails with errors. I also tried un-encrypting the database in test environment, backing it up and copying backup over to the dev environment, I'm getting msg 33111, Level 16, State 3, cannot find server certificate with thumbprint "xxxx'.
Msg 3013, level 16, state 1, restore filelist is terminating abnormally.
Your error messages indicate that you have not brought over the cert protecting the DEK to the new instance you are trying to run the restore on. Here is what I would suggest:
- On the original server with the encrypted database, backup the cert protecting the DEK using the BACKUP CERTIFICATE command, specifying that you are backing up the cert AND the private key to files:
BACKUP CERTIFICATE <CERTNAME>
TO FILE = '<CERT_FILE>.cer'
WITH PRIVATE KEY (FILE= '<PRIV_KEY>.pvk', ENCRYPTION BY PASSWORD = '<PASSWORD>');
- Copy the files over on the target server you want to do the restore on
- If not present create a master key in the master database on the target server
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<PASSWORD>';
- Create a certificate on the target server in the master database based on the cert and private key files you backed up
CREATE CERTIFICATE <CERTNAME>
FROM FILE = '<CERT_FILE>.cer'
WITH PRIVATE KEY (FILE= '<PRIV_KEY>.pvk', DECRYPTION BY PASSWORD = '<PASSWORD>');
- Retry your restore
If it succeeds backup your service master and database master keys and your certificate again.
Joie Andrew
"Since 1982"
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply