Edit

Share via

SQL Server Certificates and Asymmetric Keys

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Public Key Cryptography 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 can be used to encrypt data which only the corresponding private key will be able to decrypt. This can be used for encrypting messages to the owner of the private key. Similarly the owner of a private key can encrypt data which can only be decrypted with the public key. This use forms the basis of digital certificates in which information contained in the certificate is encrypted by the owner of a private key, assuring the author of the contents. Since the encrypting and decrypting keys are different they are known as asymmetric keys.

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 shows where to find these tools.

Tool Purpose
New-SelfSignedCertificate Creates self-signed certificates.
makecert Creates certificates. Deprecated in favor of New-SelfSignedCertificate.
sn Creates strong names for symmetric keys.

Choose an Encryption Algorithm

CREATE SYMMETRIC KEY (Transact-SQL)

CREATE CERTIFICATE (Transact-SQL)

See Also

sys.certificates (Transact-SQL)
Transparent Data Encryption (TDE)