code security in SQL Server 2008 = = = Master Key

Encryption in SQL Server is handled in a hierarchical form to provide multiple levels of security. SQL Server contains two key types that are used to encrypt data. Such as:

1. Server Master Secret (Service master key), located at the top of the hierarchy and created automatically when SQL Server is installed, used to encrypt system data, linked server logins, and database master keys. When you first use the Service master key to encrypt a certificate, database master key, or linked server master password through SQL Server, the service master key is automatically generated and generated using the Windows Certificate of the SQL Server service account. If you have to change the SQL Server service account, Microsoft recommends using SQL Server Configuration Manager, because this tool will perform the appropriate decryption and encryption methods required to generate the new service master key, and can keep the encryption hierarchy intact. The Service Master key is also used to encrypt the database master key under it.

2. The database master key, which is used to encrypt the certificate, as well as the asymmetric key and the symmetric key. All databases can contain only one database master key, which is encrypted when it is created by the service master key. When you create an asymmetric key, you can decide whether the private key that corresponds to the encrypted asymmetric key contains a password. If the display contains a password, the private key is encrypted using the database master key.

Let's look at a set of examples:

Example one, backup and restore Service master key

Use the following two SQL commands:

BACKUP Service Master key exports the Services master keys. (http://msdn.microsoft.com/zh-cn/library/ms190337.aspx)

RESTORE Service Master key imports the Services master keys from the backup file. (http://msdn.microsoft.com/zh-cn/library/ms187972.aspx)

--The following statement backs up the service master key to C:\SqlBackup\SMK.bakBACKUPSERVICE MASTERKEYToFile='C:\SqlBackup\SMK.bak'Encryption byPASSWORD='Makeitagoodone!1ab'----Note that the password can use single quotation marksGo--Recovery Service Master keyRESTORESERVICE MASTERKEYFromFile='H:\SqlBackup\SMK.bak'decryption byPASSWORD='Makeitagoodone!1ab'Go

If the key does not actually change, and you perform a key recovery, you will receive a prompt:

--the old and new master keys is identical. No data re-encryption is required.

Example two, creating, regenerating, and deleting a database master key

Use the following two SQL commands:

Create master key creates database master keys (http://technet.microsoft.com/zh-cn/library/ms174382.aspx)

ALTER master key regenerates the database master key (http://msdn.microsoft.com/en-us/library/ms186937%28SQL.90%29.aspx)

Drop master Key Delete database master keys (http://msdn.microsoft.com/en-us/library/ms180071.aspx)

When the database master key is explicitly created, an additional generated security layer is automatically generated to encrypt the new certificate and asymmetric key in the database, and to further protect the encrypted data.

Ifnotexists (SELECTname fromsys.databasesWHEREName='Bookstore')BEGINCreateDatabase BookstoreENDGO UseBookstoreGO--Create a database master keyCREATEMASTERKEYEncryption byPASSWORD='Password'Go UseBookstoreGO--Regenerate the database master keyALTERMASTERKEY[ Force]Regenerate withEncryption byPASSWORD='Password'--To Delete a database master key UseBookstoreGODROPMASTERKEY

Note: If the database master key is still used by other database objects, it cannot be deleted, which is similar to the schema.

Once the database master key is created, it is a good practice to back it up immediately.

Example three, backing up, recovering a database master key

Grammar:

BACKUP Master key Exports the service master keys. (http://technet.microsoft.com/en-us/library/ms174387.aspx)

RESTORE Master Key imports the database master keys from the backup file. (http://msdn.microsoft.com/en-us/library/ms186336.aspx)

Here is a complete example:

--backing up the database master key UseBookstoreGOCREATEMASTERKEYEncryption byPASSWORD='magneticfields!'GOBACKUPMASTER Keytofile='H:\SqlBackup\BookStore_Master_Key.BAK'Encryption byPASSWORD='4d280837!!!'--Recovering the database master keyRESTOREMASTER Keyfromfile='H:\SqlBackup\BookStore_Master_Key.BAK'decryption byPASSWORD='4d280837!!!'Encryption byPASSWORD='magneticfields!'

Similar to the service master key, if there is no modification, you will receive the following prompt:

The old and new master keys is identical. No data re-encryption is required.

Example three, removing the service master key from the database master key

When a database master key is created, it is encrypted by default in two ways: the service master key and the password used in the Create Master Key command. If you do not want to encrypt the database master key using the service Master password ( in which case login with sysadmin privileges will not be able to access the encrypted data without knowing the database Master key), you can use the Alter MASTER KEY command to remove the service master key.

The abbreviated syntax is as follows:

ALTER MASTER KEY

ADD encryption by SERVICE MASTER KEY |

DROP encryption by SERVICE MASTER KEY

Because the service master key allows users with sufficient permissions (such as sysadmin) to automatically decrypt the database master key, once the encryption of the service master key is removed, and then you want to modify the database master key, you must access it using a new command. OPEN MASTER KEY, the syntax is as follows:

OPEN MASTER KEY decryption by PASSWORD = ' PASSWORD '

Here is an example:

ALTERMASTER Keydrop Encryption bySERVICE MASTERKEY--once executed, any changes to the database master key need to be accessed using the password of Open master key, in order to re-apply the service Master key encryptionOPENMASTERKEYDecryption byPASSWORD='magneticfields!'--Once the service master key is used to encrypt the database master key, the database master key no longer needs to be explicitly opened or closed. ALTERMASTER Keyadd Encryption bySERVICE MASTERKEY--Close the database master keyCLOSEMASTERKEY

Summary:

1, this article mainly describes the service master key backup and restore, the database master key creation, regeneration, deletion and backup, restore.

2. Once the master key is created, it is a good habit to back it up immediately.

Asymmetric key encryption (asymmetric key encryption) is mainly described below

code security in SQL Server 2008 = = = Master Key