Re: SQL 2005 Encryption with restored databases

Just for my clarification:

DbMK = Database Master Key
SMK = Server Master Key

Correct? I'm just asking, because it gets really hard to read stuff that
has abbreviations and acronyms strewn all over the place instead of just
putting the stuff in plain English.

--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.


"Laurentiu Cristofor [MSFT]" <laur@xxxxxxxxxx> wrote in message
news:eA59iA$XGHA.4684@xxxxxxxxxxxxxxxxxxxxxxx
Yes, after restoring the database, you only need to reencrypt the DbMK
with the SMK, if you had such encryption originally (you might have chosen
to drop it). This is the only thing that you need to restore for
encryption.

Also see the following article for a procedure that you can use to let the
server store the DbMK password in a credential, so that it can restore the
SMK encryption automatically:

http://msdn2.microsoft.com/en-US/library/ms182754(SQL.90).aspx

Thanks

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

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

"Jim Youmans" <jdyoumans@xxxxxxxxx> wrote in message
news:1145033991.704851.180870@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
I am creating a 2005 database with encrypted columns in a few tables.
This database will need to be backed up and restored to a reporting
server and DR server on a nightly basis. In order for the reporting
and DR groups to have access to the encrypted fields, what do I need to
do?

From reading this group and some great blogs on MSDN, it seems that if
I reencrypt the DbMK with the SMK after each restore, that should be
it? Then the DbMK will be re-encrypted with the SMK and then usable.
Is that right?

I am also putting together a cluster (one active and one fail over)
with replication. I assume that the encrypted data will be transferred
to the fail over box from the active box with no changes. So I would
have to re-encrypt the DbMK at set up so that it is ready to go.

Thanks!!

Jim Youmans





.

Relevant Pages

  • Re: ALTER MASTER KEY REGENERATE Command
    ... The database master key (DbMK) has an encryption by password and by default ... encryption allows a sysadmin easy access to any data encrypted by the SMK, ...
    (microsoft.public.sqlserver.security)
  • 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)
  • [NT] Multiple Vulnerabilities in HP Web JetAdmin (Read, Write, Execute, Path Disclosure, Password De
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... HP Web JetAdmin is an enterprise management system for large amounts of HP ... The web server is a modular service ... HP Web JetAdmin uses it's own encryption. ...
    (Securiteam)
  • Re: Proposal for Lite Encryption for Login Form without SSL
    ... the form uses javascript to hash the password ... This way the password is not sent to the server ... This would be the equivalent to a public key in public key encryption ...
    (comp.lang.php)
  • Re: Printing Problems (2nd request)
    ... lpstat: Unable to connect to server: Connection refused ... # Encryption: whether or not to use encryption; ... got two printers connected, both of which used to work fine. ... Deny From All ...
    (Fedora)