February 2, 2017 at 12:27 am
Hi Experts,
I have a TDE enabled database and i successfully restored it to another instance of SQL after restoring the certificate along with private key.
The certificate I restored is from another SQL Server and the certificate is encrypted by DMK of other server so now there is no relation between DMK and certificate in new server right,Please correct me if I am wrong.
USE Encrypt_DiffCert;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
GO
I tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.
Msg 15151, Level 16, State 1, Line 2
Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.
February 2, 2017 at 3:52 pm
Apparently the master key does not exists in the database which you are trying to alert.
February 3, 2017 at 10:32 am
VastSQL - Thursday, February 2, 2017 12:27 AMHi Experts,I have a TDE enabled database and i successfully restored it to another instance of SQL after restoring the certificate along with private key.
The certificate I restored is from another SQL Server and the certificate is encrypted by DMK of other server so now there is no relation between DMK and certificate in new server right,Please correct me if I am wrong.
USE Encrypt_DiffCert;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
GOI tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.
Msg 15151, Level 16, State 1, Line 2
Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.
No, it's telling you you haven't created a DMK on your new instance. You must create a DMK in master on your new server before attempting to restore the TDE certificate
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 4, 2017 at 10:26 pm
Perry Whittle - Friday, February 3, 2017 10:32 AMVastSQL - Thursday, February 2, 2017 12:27 AMHi Experts,I have a TDE enabled database and i successfully restored it to another instance of SQL after restoring the certificate along with private key.
The certificate I restored is from another SQL Server and the certificate is encrypted by DMK of other server so now there is no relation between DMK and certificate in new server right,Please correct me if I am wrong.
USE Encrypt_DiffCert;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
GOI tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.
Msg 15151, Level 16, State 1, Line 2
Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.No, it's telling you you haven't created a DMK on your new instance. You must create a DMK in master on your new server before attempting to restore the TDE certificate
Thanks Perry,
But when running select name from sys.symmetric_keys
getting below results.
##MS_DatabaseMasterKey##
##MS_ServiceMasterKey##
February 5, 2017 at 2:00 pm
I think you are referencing two different things. For TDE there needs to be a database master key in the master database of the instance. Your query is looking for a database master key in user database Encrypt_DiffCert.
The cert will have to exist in the new instance, otherwise the instance would not be able to access the DEK to decrypt the data files, making the database useless on the new instance.
Joie Andrew
"Since 1982"
February 5, 2017 at 9:22 pm
here is a video TDE demo, that i created while ago.
February 5, 2017 at 10:40 pm
Joie Andrew - Sunday, February 5, 2017 2:00 PMI think you are referencing two different things. For TDE there needs to be a database master key in the master database of the instance. Your query is looking for a database master key in user database Encrypt_DiffCert.The cert will have to exist in the new instance, otherwise the instance would not be able to access the DEK to decrypt the data files, making the database useless on the new instance.
Thanks Joie,
The Certificate do exists and the example is taken from msdn. Below they use AdventureWorks2012 DB as example.
USE AdventureWorks2012; ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003'; GO
February 5, 2017 at 10:47 pm
What example from MSDN? Can you provide the link?
Joie Andrew
"Since 1982"
February 6, 2017 at 8:24 am
VastSQL - Saturday, February 4, 2017 10:26 PMPerry Whittle - Friday, February 3, 2017 10:32 AMVastSQL - Thursday, February 2, 2017 12:27 AMHi Experts,I have a TDE enabled database and i successfully restored it to another instance of SQL after restoring the certificate along with private key.
The certificate I restored is from another SQL Server and the certificate is encrypted by DMK of other server so now there is no relation between DMK and certificate in new server right,Please correct me if I am wrong.
USE Encrypt_DiffCert;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
GOI tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.
Msg 15151, Level 16, State 1, Line 2
Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.No, it's telling you you haven't created a DMK on your new instance. You must create a DMK in master on your new server before attempting to restore the TDE certificate
Thanks Perry,
But when running select name from sys.symmetric_keys
getting below results.
##MS_DatabaseMasterKey##
##MS_ServiceMasterKey##
what do the results of these show
USE master
GO
select * from sys.symmetric_keys
select * from sys.certificates
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 7, 2017 at 12:57 am
Perry Whittle - Monday, February 6, 2017 8:24 AMVastSQL - Saturday, February 4, 2017 10:26 PMPerry Whittle - Friday, February 3, 2017 10:32 AMVastSQL - Thursday, February 2, 2017 12:27 AMHi Experts,I have a TDE enabled database and i successfully restored it to another instance of SQL after restoring the certificate along with private key.
The certificate I restored is from another SQL Server and the certificate is encrypted by DMK of other server so now there is no relation between DMK and certificate in new server right,Please correct me if I am wrong.
USE Encrypt_DiffCert;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
GOI tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.
Msg 15151, Level 16, State 1, Line 2
Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.No, it's telling you you haven't created a DMK on your new instance. You must create a DMK in master on your new server before attempting to restore the TDE certificate
Thanks Perry,
But when running select name from sys.symmetric_keys
getting below results.
##MS_DatabaseMasterKey##
##MS_ServiceMasterKey##what do the results of these show
USE master
GOselect * from sys.symmetric_keys
select * from sys.certificates
Thanks Perry.
name
##MS_DatabaseMasterKey##
##MS_ServiceMasterKey##
Name
##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_AgentSigningCertificate##
##MS_PolicySigningCertificate##
##MS_SmoExtendedSigningCertificate##
##MS_SchemaSigningCertificate990F36EF1B3577FE5687C7465F0A5135DE9E6834##
MyServerCertificate
My_TDE_Certificate
Please let know if full result set is required.
February 7, 2017 at 10:13 am
so have you actually managed to restore the TDE protected database to the instance?
Are you using the My_TDE_Certificate to encrypt the DEK in your TDE protected database.
Post the output of the following if the database has been restored and is online
USE [TDE_Database]
go
SELECT dek.encryption_state, c.name, c.subject
FROM master.sys.dm_database_encryption_keys dek
INNER JOIN master.sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint
WHERE dek.database_id = DB_ID()
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 7, 2017 at 10:21 pm
Perry Whittle - Tuesday, February 7, 2017 10:13 AMso have you actually managed to restore the TDE protected database to the instance?
Are you using the My_TDE_Certificate to encrypt the DEK in your TDE protected database.Post the output of the following if the database has been restored and is online
USE [TDE_Database]
go
SELECT dek.encryption_state, c.name, c.subject
FROM master.sys.dm_database_encryption_keys dek
INNER JOIN master.sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint
WHERE dek.database_id = DB_ID()
Thanks Perry.
encryption_state name subject
3 MyServerCertificate My TDE Certificate
February 8, 2017 at 6:23 am
So, why are you trying to use the following, what are you hoping it to do, given there is no database master key in the database [Encrypt_DiffCert]
VastSQL
USE Encrypt_DiffCert;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
GOI tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.
Msg 15151, Level 16, State 1, Line 2
Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.
Did you maybe want the alter database encryption key command as below-- Syntax for SQL Server
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
|
ENCRYPTION BY SERVER
{
CERTIFICATE Encryptor_Name |
ASYMMETRIC KEY Encryptor_Name
}
[ ; ]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 8, 2017 at 10:30 pm
Perry Whittle - Wednesday, February 8, 2017 6:23 AMSo, why are you trying to use the following, what are you hoping it to do, given there is no database master key in the database [Encrypt_DiffCert]VastSQL
USE Encrypt_DiffCert;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
GOI tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.
Msg 15151, Level 16, State 1, Line 2
Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.Did you maybe want the alter database encryption key command as below
-- Syntax for SQL Server
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
|
ENCRYPTION BY SERVER
{
CERTIFICATE Encryptor_Name |
ASYMMETRIC KEY Encryptor_Name
}
[ ; ]
Thanks Perry,
DMK is only for the master database right,correct me if I am wrong. Also what is the relation between an encrypted database from another server restored to a new server,we are only restoring the certificate right, there is no connection between DMK and the restored certificate right or am I missing something here?
February 9, 2017 at 5:25 am
VastSQLThanks Perry,
DMK is only for the master database right,correct me if I am wrong.
Wrong, that's why it's important to create a DMK in the master database when setting up TDE, it's the DMK in master that's used for decrpyting the private key of a certificate stored in master used to protect a TDE enabled database.
VastSQLAlso what is the relation between an encrypted database from another server restored to a new server,we are only restoring the certificate right, there is no connection between DMK and the restored certificate right or am I missing something here?
When restoring a TDE enabled database from server A to server B, you only need to transfer the backup of the database and the certificate used to encrypt the Database Encryption Key in the TDE enabled database.
However, to create a certificate on server B from a backup, you will first need to create a DMK in master on server B as these are not created automatically whereas the SMK is created automatically upon first usage.
You still haven't answered my question, why are you using the following command, what did you expect it to do?
USE Encrypt_DiffCert;
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
GO
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply