SQL Server Certificates and Asymmetric Keys
Public Key Cryptography (PKI) is a form of message secrecy in which a user creates a public key and a private key. The private key is kept secret, whereas the public key can be distributed to others. Although the keys are mathematically related, the private key cannot be easily derived by using the public key. The public key is used to encrypt data and the private key is used to decrypt data. A message that is encrypted by using the public key can only be decrypted by using the correct private key. Since there are two different keys, these keys are asymmetric.
Certificates and asymmetric keys are both ways to use asymmetric encryption. Certificates are often used as containers for asymmetric keys because they can contain more information such as expiry dates and issuers. There is no difference between the two mechanisms for the cryptographic algorithm, and no difference in strength given the same key length. Generally, you use a certificate to encrypt other types of encryption keys in a database, or to sign code modules.
Certificates and asymmetric keys can decrypt data that the other encrypts. Generally, you use asymmetric encryption to encrypt a symmetric key for storage in a database.
A public key does not have a particular format like a certificate would have, and you cannot export it to a file.
Note
SQL Server contains features that enable you to create and manage certificates and keys for use with the server and database. SQL Server cannot be used to create and manage certificates and keys with other applications or in the operating system.
Certificates
A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server. You can use externally generated certificates or SQL Server can generate certificates.
Note
SQL Server certificates comply with the IETF X.509v3 certificate standard.
Certificates are useful because of the option of both exporting and importing keys to X.509 certificate files. The syntax for creating certificates allows for creation options for certificates such as an expiry date.
Using a Certificate in SQL Server
Certificates can be used to help secure connections, in database mirroring, to sign packages and other objects, or to encrypt data or connections. The following table lists additional resources for certificates in SQL Server.
| Topic | Description |
|---|---|
| CREATE CERTIFICATE (Transact-SQL) | Explains the command for creating certificates. |
| Identify the Source of Packages with Digital Signatures | Shows information about how to use certificates to sign software packages. |
| Use Certificates for a Database Mirroring Endpoint (Transact-SQL) | Covers information about how to use certificates with Database Mirroring. |
Asymmetric Keys
Asymmetric keys are used for securing symmetric keys. They can also be used for limited data encryption and to digitally sign database objects. An asymmetric key consists of a private key and a corresponding public key. For more information about asymmetric keys, see CREATE ASYMMETRIC KEY (Transact-SQL).
Asymmetric keys can be imported from strong name key files, but they cannot be exported. They also do not have expiry options. Asymmetric keys cannot encrypt connections.
Using an Asymmetric Key in SQL Server
Asymmetric keys can be used to help secure data or sign plaintext. The following table lists additional resources for asymmetric keys in SQL Server.
| Topic | Description |
|---|---|
| CREATE ASYMMETRIC KEY (Transact-SQL) | Explains the command for creating asymmetric keys. |
| SIGNBYASYMKEY (Transact-SQL) | Displays the options for signing objects. |
Tools
Microsoft provides tools and utilities that will generate certificates and strong name key files. These tools offer a richer amount of flexibility in the key generation process than the SQL Server syntax. You can use these tools to create RSA keys with more complex key lengths and then import them into SQL Server. The following table explains shows where to find these tools.
| Tool | Purpose |
| makecert | Creates certificates. |
| sn | Creates strong names for symmetric keys. |
Related Tasks
Choose an Encryption Algorithm
CREATE SYMMETRIC KEY (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)
See Also
sys.certificates (Transact-SQL)
Transparent Data Encryption (TDE)