Backing up DPAPI keys in relation to SQL Server

Hi,

There alot of talk about Service Master and DB Master keys and the importance of backing them up seperately.

What about the DPAPI keys used to protect the Service Master keys. What procedures to I take to back those up during my database backup.

If I understand correctly, if I do a backup on one machine, and then try to restore in a different machine, I won't be able to decrypt data becuase the DPAPI keys don't "match".

This is assumming I don't use a password to protect my DB Master keys, I just rely on the service master to protect them.

Thanks for any help,

Database master keys(DbMK) are always protected by a password - you have no choice about that.

If you backup the service master key(SMK), there is no need to be concerned about DPAPI - those keys are managed by the OS and the database automatically uses them to encrypt a loaded SMK.

When you do a database backup, you actually don't need to backup anything else than the database - you just have to remember the database master key password. You don't need the SMK or the DPAPI keys to be able to restore the database.

Thanks

Laurentiu

|||

Thanks,

I understand that I can restore the database without the SMK or DPAPI keys, but I'm surprised that I will be able decrypt data without them. I thought this was the whole purpose of the encryption heirarchy.

If I can indeed restore a database, and see the decrypted data with just the DbMK, then why do I need the DPAPI and SMK?

I thought they were in place to add additional "seperation of duties" for keys - so if someone stole a database backup tape, they wouldn't be able to restore the tape on a 'bad guy' server, and see by encrypted data - because they didn't have the SMK and DPAPI key.

Thanks for your help!

Backing up DPAPI keys in relation to SQL Server

Hi,

There alot of talk about Service Master and DB Master keys and the importance of backing them up seperately.

What about the DPAPI keys used to protect the Service Master keys. What procedures to I take to back those up during my database backup.

If I understand correctly, if I do a backup on one machine, and then try to restore in a different machine, I won't be able to decrypt data becuase the DPAPI keys don't "match".

This is assumming I don't use a password to protect my DB Master keys, I just rely on the service master to protect them.

Thanks for any help,

Database master keys(DbMK) are always protected by a password - you have no choice about that.

If you backup the service master key(SMK), there is no need to be concerned about DPAPI - those keys are managed by the OS and the database automatically uses them to encrypt a loaded SMK.

When you do a database backup, you actually don't need to backup anything else than the database - you just have to remember the database master key password. You don't need the SMK or the DPAPI keys to be able to restore the database.

Thanks

Laurentiu

|||

Thanks,

I understand that I can restore the database without the SMK or DPAPI keys, but I'm surprised that I will be able decrypt data without them. I thought this was the whole purpose of the encryption heirarchy.

If I can indeed restore a database, and see the decrypted data with just the DbMK, then why do I need the DPAPI and SMK?

I thought they were in place to add additional "seperation of duties" for keys - so if someone stole a database backup tape, they wouldn't be able to restore the tape on a 'bad guy' server, and see by encrypted data - because they didn't have the SMK and DPAPI key.

Thanks for your help!

Autonumber or unidue id generation

Hi,
I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ?
Thanks
-SudhakarHi,

I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ?

Thanks
-Sudhakar

Make the datatype int and set the Identity constraint on that column.
Joydeep|||Your two best choices are either an Identity column (integer), or a GUID with the default set to NewID().|||I would STRONGLY recommend using the GUID if that option is available to you. IDENTITY values are fine for many purposes, and are easy for legacy code to manage, but GUID values are much simpler in the long run. They are easier to use, easier to generate, easier to manage, and very nearly foolproof, at least in my experience.

-PatP|||Well, I use GUIDs almost exclusively, but I don't know that I would recommend them as strongly as Pat. They do have some drawbacks, and I am not talking about their size or the fact they they are difficult to type (stupid arguments...). One of my pet peeves is that I can't apply aggregate functions such as MAX and MIN to GUIDs, which, believe or not, can be handy thing to do some times, such as when eliminating duplicates. You have to cast the GUID as char(36) first, which throws any indexes out the window.