Encrypting and storing sensitive data in SQL Server 2008
Consider the following image that shows the encryption hierarchy used in SQL Server. Please note the first blue block, that says the SMK is encrypted using DPAPI. The DPA开发者_JAVA百科PI uses a currently logged-in user credentials (+ more) to encrypt data, so it's machine-specific. This means that SMK (as well as DMK and any derived password) will be machine-specific (actually it's generated by SQL Server's setup). OTOH, I can create/backup an X.509 certificate in SQL Server (using CREATE CERTIFICATE, BACKUP CERTIFICATE and so on).
The scenario/question:
I'm developing a Web App that needs to encrypt and store CC information in a database column. I need to access those data, later on another machine so the db backup should be actually readable when restored on another machine (albeit, for someone who has got access to the above-mentioned certificate).
I'm wondering how am I supposed to restore a backup on another machine when the SMK is specific to the current instance of SQL Server? What should I do to access those encrypted data once they are restored on another machine?
UPDATE: Correct me if I am wrong!
We could use the BACKUP SERVICE MASTER KEY TO FILE
command to back the currently used SMK. This key, however, can be restored on any other SQLServer (on/out of the same machine) using the RESTORE SERVICE MASTER KEY FROM FILE
command. When the SMK is restored, it's being encrypted once again using DPAPI so that the key itself can be stored somewhere on the machine.
Any help would be highly appreciated,
The diagram shows that a certificate can be protected either by the DMK or via a password. If you protect it with just a password, it should be portable.
精彩评论