CREATE CERTIFICATE (Transact-SQL)
Adds a certificate to a database.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ]
{ FROM <existing_keys> | <generate_new_keys> }
[ ACTIVE FOR BEGIN_DIALOG = { ON | OFF } ]
<existing_keys> ::=
ASSEMBLY assembly_name
| {
[ EXECUTABLE ] FILE = 'path_to_file'
[ WITH PRIVATE KEY ( <private_key_options> ) ]
}
<generate_new_keys> ::=
[ ENCRYPTION BY PASSWORD = 'password']
WITH SUBJECT = 'certificate_subject_name'
[ , <date_options> [ ,...n ] ]
<private_key_options> ::=
FILE = 'path_to_private_key'
[ , DECRYPTION BY PASSWORD = 'password' ]
[ , ENCRYPTION BY PASSWORD = 'password' ]
<date_options> ::=
START_DATE = 'mm/dd/yyyy' | EXPIRY_DATE = 'mm/dd/yyyy'
Arguments
- certificate_name
Is the name by which the certificate will be known in the database.
- AUTHORIZATION user_name
Is the name of the user that will own this certificate.
- ASSEMBLY assembly_name
Specifies a signed assembly that has already been loaded into the database.
- [ EXECUTABLE ] FILE ='path_to_file'
Specifies the complete path, including file name, to a DER-encoded file that contains the certificate. If the EXECUTABLE option is used, the file is a DLL that has been signed by the certificate. path_to_file can be a local path or a UNC path to a network location. The file will be accessed in the security context of the SQL Server service account. This account must have the required file-system permissions.
- WITH PRIVATE KEY
Is the PRIVATE KEY clause specifies that the private key of the certificate is loaded into SQL Server. This clause is only valid when the certificate is being created from a file. To load the private key of an assembly, use ALTER CERTIFICATE.
- FILE ='path_to_private_key'
Specifies the complete path, including file name, to the private key. path_to_private_key can be a local path or a UNC path to a network location. The file will be accessed in the security context of the SQL Server service account. This account must have the necessary file-system permissions.
- DECRYPTION BY PASSWORD = 'key_password'
Specifies the password required to decrypt a private key that is retrieved from a file. This clause is optional if the private key is protected by a null password. Saving a private key to a file without password protection is not recommended. If a password is required but no password is specified, the statement will fail.
- ENCRYPTION BY PASSWORD ='password'
Specifies the password that will be used to encrypt the private key. Use this option only if you want to encrypt the certificate with a password. If this clause is omitted, the private key will be encrypted using the database master key. The password must comply with password complexity policy. For more information, see Password Policy.
- SUBJECT = 'certificate_subject_name'
The term subject refers to a field in the metadata of the certificate as defined in the X.509 standard. The subject can be up to 128 characters long. Subjects longer than 128 characters will be truncated when they are stored in the catalog, but the binary large object (BLOB) that contains the certificate will retain the full subject name.
- START_DATE ='mm/dd/yyyy'
Is the date on which the certificate becomes valid. If not specified, START_DATE will be set equal to the current date.
- EXPIRY_DATE ='mm/dd/yyyy'
Is the date on which the certificate expires. If not specified, EXPIRY_DATE will be set to a date one year after START_DATE.
- ACTIVE FOR BEGIN_DIALOG = { ON | OFF }
Makes the certificate available to the initiator of a Service Broker dialog conversation. The default value is ON.
Remarks
A certificate is a database-level securable that follows the X.509 standard and supports X.509 V1 fields. CREATE CERTIFICATE can load a certificate from a file or assembly. This statement can also generate a key pair and create a self-signed certificate.
Private keys generated by SQL Server are 1024 bits long. Private keys imported from an external source have a minimum length of 384 bits and a maximum length of 3,456 bits. The length of an imported private key must be an integer multiple of 64 bits.
The private key must correspond to the public key specified by certificate_name.
When you create a certificate from a container, loading the private key is optional. But when SQL Server generates a self-signed certificate, the private key is always created. By default, the private key is encrypted using the database master key. If the database master key does not exist and no password is specified, the statement will fail.
The ENCRYPTION BY PASSWORD option is not required when the private key will be encrypted with the database master key. Use this option only when the private key will be encrypted with a password. If no password is specified, the private key of the certificate will be encrypted using the database master key. Omitting this clause will cause an error if the master key of the database cannot be opened.
You do not have to specify a decryption password when the private key is encrypted with the database master key.
Note
Built-in functions for encryption and signing do not check the expiration dates of certificates. Users of these functions must decide when to check certificate expiration.
The START_DATE and EXPIRY_DATE arguments do not enable you to specify times for these dates. However, you can use the date and time functions provided by SQL Server to add specific times to certificate start and expiry dates. For more information, see the following topics:
Permissions
Requires CREATE CERTIFICATE permission on the database.
Examples
A. Creating a self-signed certificate
The following example creates a certificate called Shipping04. The private key of this certificate is protected using a password.
USE AdventureWorks;
CREATE CERTIFICATE Shipping04
ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
WITH SUBJECT = 'Sammamish Shipping Records',
EXPIRY_DATE = '10/31/2009';
GO
B. Creating a certificate from a file
The following example creates a certificate in the database, loading the key pair from files.
USE AdventureWorks;
CREATE CERTIFICATE Shipping11
FROM FILE = 'c:\Shipping\Certs\Shipping11.cer'
WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk',
DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');
GO
C. Creating a certificate from a signed executable file
USE AdventureWorks;
CREATE CERTIFICATE Shipping19
FROM EXECUTABLE FILE = 'c:\Shipping\Certs\Shipping19.dll';
GO
Alternatively, you can create an assembly from the dll file, and then create a certificate from the assembly.
USE AdventureWorks;
CREATE ASSEMBLY Shipping19
FROM ' c:\Shipping\Certs\Shipping19.dll'
WITH PERMISSION_SET = SAFE;
GO
CREATE CERTIFICATE Shipping19 FROM ASSEMBLY Shipping19;
GO
See Also
Reference
ALTER CERTIFICATE (Transact-SQL)
DROP CERTIFICATE (Transact-SQL)
BACKUP CERTIFICATE (Transact-SQL)
EVENTDATA (Transact-SQL)