code security in SQL Server 2008 (iv) master key _mssql2008

The following figure:

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

2. Database master key (DB master key) for encrypting certificates, and asymmetric keys and symmetric keys. All databases can contain only one database master key, which is encrypted by the service master key when it is created. When you create an asymmetric key, you can determine whether the private key that corresponds to encrypting the asymmetric key contains a password. If the password is included, 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

The following two SQL commands are used:

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

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

Copy Code code as follows:

--The following statement backs up the service master key to C:\SqlBackup\SMK.bak
BACKUP SERVICE MASTER KEY
to FILE = ' C:\SqlBackup\SMK.bak '
Encryption by PASSWORD = ' MAKEITAGOODONE!1AB '----Note that the password can be used in single quotes
Go
--Restore Service Master key
RESTORE SERVICE MASTER KEY
From FILE = ' H:\SqlBackup\SMK.bak '
Decryption by PASSWORD = ' Makeitagoodone!1ab '
Go

If the key does not actually change, and the key recovery is performed, you receive a prompt:

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

Example two, creating, then generating, and deleting a database master key

The following two SQL commands are used:

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

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

Drop Master Delete Database master key (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 at the same time to encrypt the new certificate and asymmetric key in the database to further protect the encrypted data.

Copy Code code as follows:

IF not EXISTS (SELECT name
From sys.databases
WHERE name = ' Bookstore ')
BEGIN
CREATE DATABASE Bookstore
End
Go

Use Bookstore
Go
--Create DATABASE master key
CREATE MASTER KEY Encryption by PASSWORD = ' PASSWORD '
Go

Use Bookstore
Go
--Rebuild the database master key
ALTER MASTER KEY
[FORCE] Regenerate with encryption by PASSWORD = ' PASSWORD '

--Delete Database master key
Use Bookstore
Go
DROP MASTER KEY

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

And once you create the database master key, it's a good habit to back it up right away.

Example three, backup, restore a database master key

Grammar:

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

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

The following is a complete example:

Copy Code code as follows:

--Backing up the database master key
Use Bookstore
Go
CREATE MASTER KEY Encryption by PASSWORD = ' magneticfields! '
Go
BACKUP MASTER KEY to FILE = ' H:\SqlBackup\BookStore_Master_Key.BAK '
Encryption by PASSWORD = ' 4d280837!!! '

--Restoring the Database master key
RESTORE MASTER KEY from FILE = ' H:\SqlBackup\BookStore_Master_Key.BAK '
Decryption by PASSWORD = ' 4d280837!!! '
Encryption by PASSWORD = ' magneticfields! '

Similar to the service master key, if not modified, you receive the following prompt:

The old and new master keys are 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 use the service master password to encrypt the database master key ( 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 a user with sufficient permissions (such as sysadmin) to automatically decrypt the database master key, once the encryption of the service master key is removed, and 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:

Copy Code code as follows:

ALTER master key DROP encryption by SERVICE MASTER key
-Once executed, any database master key modifications need to be accessed using the password of the open master key to reapply the service master key encryption
OPEN MASTER KEY decryption by PASSWORD = ' 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.
ALTER master key ADD encryption by SERVICE MASTER key
--Close the database master key
Close MASTER KEY

Summary:

1, this article mainly introduces the service master key backup and restore, the database master key creation, rebuild, delete and backup, restore.

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

The following is a major introduction to asymmetric key cryptography (asymmetric key encryption)

Invite the Month