Re: ALTER MASTER KEY REGENERATE Command

The database master key (DbMK) has an encryption by password and by default
it also has an encryption by the service master key (SMK). The latter
encryption allows a sysadmin easy access to any data encrypted by the SMK,
whether he knows the password or not. However, if you drop the SMK
encryption (ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY), then
the DbMK can only be used by people that know the DbMK password. The ALTER
statement that you mentioned below can be used by a sysadmin only if the
DbMK has a SMK encryption; otherwise, the sysadmin would need to know the
DbMK password to open the DbMK first, before he could execute the statement.

Note that using a password encryption for the DbMK doesn't really mean that
you lock out the sysadmin from accessing the data - it's just that he
doesn't have direct SQL Server access to the DbMK anymore. For example, in
most cases, a sysadmin is also a local administrator, so he could simply
debug the server process to get the DbMK password at the moment you execute
the OPEN MASTER KEY statement.

Thanks

--
Laurentiu Cristofor [MSFT]
Software Development Engineer
SQL Server Engine
http://blogs.msdn.com/lcris/

This posting is provided "AS IS" with no warranties, and confers no rights.

"et_ck" <etck@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7BD34F69-AF85-4057-9341-6E117B4FD532@xxxxxxxxxxxxxxxx
Hi,

If I forgot what was the password used to create the Database Master key,
I
could just use this command to regenerate a new one:

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'newpassword'

Then what is the use of the following command which requires me to provide
a
password?

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id =
101)
CREATE MASTER KEY
ENCRYPTION BY PASSWORD =
'23987hxJKL969#ghf0%94467GRkjg5k3fd117r$$#1946kcj$n44nhdlj'

Does this means that the Sys Admin (SA) will have divine rights to
regenerate the Database Master key without the need to know the original
password used to create the key?

Is there any proper guidelines as to how secure a database using the SQL
Server 2005 encryption capability?



.

Relevant Pages

  • Re: SQL 2005 Encryption with restored databases
    ... This is the only thing that you need to restore for encryption. ... server store the DbMK password in a credential, so that it can restore the ... SMK encryption automatically: ...
    (microsoft.public.sqlserver.security)
  • Re: SQL 2005 Encryption with restored databases
    ... DbMK = Database Master Key ... SMK = Server Master Key ... if you had such encryption originally (you might have chosen ...
    (microsoft.public.sqlserver.security)
  • RE: ICSF
    ... The z/OS encryption facility books aren't very helpful. ... And, of course, the Master Key should not be transported in the open. ... to whom they are addressed.If you have received this email in error please notify the system manager. ...
    (bit.listserv.ibm-main)
  • 2005-Problem restoring database with encrypted columns to diff ser
    ... I need to start encrypting several fields in a database and have been doing ... OPEN MASTER KEY DECRYPTION BY PASSWORD = 'testAppleA3'; ... ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; ... encryption by certificate test; ...
    (microsoft.public.sqlserver.security)
  • Re: Encrypted database deployment
    ... Use of included script samples are subject to the terms specified at ... An error occurred during decryption. ... it isn't the database master key so you now have two different master ... ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY ...
    (microsoft.public.sqlserver.security)