Detailed SQL Server 2005 symmetric key encryption technology

Introduced

An exciting feature of SQL Server 20,051 is the built-in encryption feature. In this new version of SQL Server, the development team has added cryptographic tools, certificate creation, and key management capabilities directly to T-SQL. This is a good gift for someone who needs to encrypt the data in a table because of legal requirements or business needs. Making decisions is easier for those who hesitate to use encryption to secure data. This article describes how the new encryption function works, how to use it.

TSQL now supports the use of symmetric keys and asymmetric keys, certificates, and passwords. This article describes how to create, manage, and use symmetric keys and certificates.

On the basis of the content involved, I decided to divide this article into three sections:

First part: Service Master key and database master key

Part II: Certificate

Part III: Symmetric key

1. Service master key and database master key

Figure: SQL Server 2005 Encryption hierarchy

1.1 Service Master Key

The Service master key is automatically generated when the first time you need to encrypt a linked server password, credential, or database master key using the service master key. The Service master key is the root of the SQL Server encryption hierarchy. The Service Master key protects all other keys and confidential content in the tree, directly or indirectly. The Service master key is encrypted using the local computer key and the Windows data protection API. The API uses a key derived from the Windows credentials of the SQL Server service account.

Because the service master key is automatically generated and managed by the system, it requires very little administration. The Service master key can be backed up by the backup Service master KEY statement in the following format:

BACKUP SERVICE MASTER KEY to FILE = ' path_to_file ' encryption by PASSWORD = ' PASSWORD '

' Path_to_file ' specifies the full path (including the file name) of the file to which you want to export the service master key. This path can be either a local path or a UNC path to a network location.