June 10, 2013 at 6:12 am
Hi All,
I have a PRODDB which is encrypted with TDE .I have DMK and DBCert Key with me .I also have the password for the keys .Can you please help and suggest the Steps I should use to change the password for Keys ??
June 10, 2013 at 6:58 am
The database master key is independant of the certificate. The master DMk is merely used to protect the private key while the cert is stored on the SQL server.
The certificate would generally be created by specifying a subject, then a database encryption key is created in your TDE protected database using the cert to encrypt it. Usually the only time you'll have a password for the cert is when you take a backup of it.
What is it you are attempting to do exactly?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 10, 2013 at 7:06 am
So you are trying to say that the password would be for DMK???
Then how would I change the password for the existing DMK??
June 10, 2013 at 7:47 am
A typical create master key command
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
A typical create certificate command
USE master
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'
A typical create database encryption key
USE mydb
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
Have you specified anything different to these when creating your keys, certs, if so what?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 10, 2013 at 8:37 am
Actually It was done by an old DBA....I just have its password and Keys ....
Also how would I know whats the Subject of my certificate ??
Is there a Way we can change the password anyhow?Appreciate your support on the same
June 10, 2013 at 9:06 am
What do the following queries return?
selectname
, CASE is_master_key_encrypted_by_server
WHEN 0 THEN 'Encrypted by password only'
WHEN 1 THEN 'Encrypted by SMK'
END AS KeyEncBySrvr
from sys.databases
where name = 'master'
selectname
, pvt_key_encryption_type_desc
, issuer_name
, [subject]
from sys.certificates
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 10, 2013 at 9:10 am
name KeyEncBySrvr
------ --------------
masterEncrypted by SMK
namepvt_key_encryption_type_descissuer_namesubject
---- ---------------------------- ----------- -------
UDBCertENCRYPTED_BY_MASTER_KEYUDB CertificateUDB Certificate
June 10, 2013 at 10:07 am
Ok so have a stab, what can you learn from the results?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 10, 2013 at 4:40 pm
Jai-SQL DBA (6/10/2013)
name KeyEncBySrvr------ --------------
masterEncrypted by SMK
namepvt_key_encryption_type_descissuer_namesubject
---- ---------------------------- ----------- -------
UDBCertENCRYPTED_BY_MASTER_KEYUDB CertificateUDB Certificate
Your database master key is encrypted by the service master key and is opened\updated automatically.
You have a certificate called UDBCert which is encrypted by the DMK and this is the default. As such you will only need a password for this cert when backing it up to or restoring it from a file.
So, why do you want to change the DMK, has it become compromised?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 11, 2013 at 3:23 am
Yes its a regular activity to change DMK and password for Cert
Can you help me with the steps to change the DMK and password for Cert??
What precautions do I need to take before I should start ?
June 11, 2013 at 4:35 am
Jai-SQL DBA (6/11/2013)
Yes its a regular activity to change DMK and password for CertCan you help me with the steps to change the DMK and password for Cert??
As i pointed out previously, your certificates private key is encrypted by the DMK not a password. The result below indicates this
name pvt_key_encryption_type_desc issuer_name subject
---- ---------------------------- ----------- -------
UDBCert ENCRYPTED_BY_MASTER_KEY UDB Certificate UDB Certificate
Jai-SQL DBA (6/11/2013)
What precautions do I need to take before I should start ?
do you mean change the DMK?
As i said before your query results indicate the following
Perry Whittle (6/11/2013)
Your database master key is encrypted by the service master key and is opened\updated automatically.You have a certificate called UDBCert which is encrypted by the DMK and this is the default. As such you will only need a password for this cert when backing it up to or restoring it from a file.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply