-->APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse ![Certificate Certificate](https://www.siteground.com/tutorials/wp-content/uploads/2017/09/116-646x330.jpg)
![Key Key](/uploads/1/2/6/0/126068751/897415721.jpg)
Thanks for your feedback!There was an error submitting your feedback. Try again or send us a message.
Crt and key files represent both parts of a certificate, key being the private key to the certificate and crt being the signed certificate. It's only one of the ways to generate certs, another way would be having both inside a pem file or another in a p12 container. Jul 09, 2019 The private key gets generated along with your Certificate Signing Request (CSR). The CSR is submitted to the certificate authority right after you activate your certificate, while the private key must be kept safe and secret on your server or device. Later on, this key is used for installation of your certificate. Creating your privateKey.key file: Return to the certificate.txt file generated above. Copy the section starting from and including -BEGIN PRIVATE KEY- to -END PRIVATE. Create a new file using Notepad. Paste and save the information into the new Notepad file. Save the file as. Apr 09, 2020 CA Key and Certificate Creation. Generate a CA private key file using a utility (OpenSSL, cfssl etc) Create the CA root certificate using the CA private key. Server Certificate Creation Process. Generate a server private key using a utility (OpenSSL, cfssl etc) Create a CSR using the server private key. Generate the server certificate using CA key, CA cert and Server CSR. Jun 09, 2019 This article describes how to recover a private key after you use the Certificates Microsoft Management Console (MMC) snap-in to delete the original certificate in Internet Information Services (IIS). You delete the original certificate from the personal folder in the local computer's certificate store.
Adds a certificate to a database in SQL Server.
This feature is incompatible with database export using Data Tier Application Framework (DACFx). You must drop all certificates before exporting.
Syntax
Arguments
certificate_name
Is the name for the certificate in the database.
Is the name for the certificate in the database.
AUTHORIZATION user_name
Is the name of the user that owns this certificate.
Is the name of the user that owns this certificate.
ASSEMBLY assembly_name
Specifies a signed assembly that has already been loaded into the database.
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 is accessed in the security context of the SQL Server service account. This account must have the required file-system permissions.
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 is accessed in the security context of the SQL Server service account. This account must have the required file-system permissions.
Important
Azure SQL Database does not support creating a certificate from a file or using private key files.
BINARY = asn_encoded_certificate
ASN encoded certificate bytes specified as a binary constant.
Applies to: SQL Server 2012 (11.x) and later.
ASN encoded certificate bytes specified as a binary constant.
Applies to: SQL Server 2012 (11.x) and later.
WITH PRIVATE KEY
Specifies that the private key of the certificate is loaded into SQL Server. This clause is invalid when the certificate is being created from an assembly. To load the private key of a certificate created from an assembly, use ALTER CERTIFICATE.
Specifies that the private key of the certificate is loaded into SQL Server. This clause is invalid when the certificate is being created from an assembly. To load the private key of a certificate created from 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 is accessed in the security context of the SQL Server service account. This account must have the necessary file-system permissions.
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 is accessed in the security context of the SQL Server service account. This account must have the necessary file-system permissions.
Important
This option is not available in a contained database or in Azure SQL Database.
![Certificate Certificate](https://www.siteground.com/tutorials/wp-content/uploads/2017/09/116-646x330.jpg)
BINARY = private_key_bits
Applies to: SQL Server (Starting with SQL Server 2012 (11.x)) and Azure SQL Database.
Applies to: SQL Server (Starting with SQL Server 2012 (11.x)) and Azure SQL Database.
Private key bits specified as binary constant. These bits can be in encrypted form. If encrypted, the user must provide a decryption password. Password policy checks are not performed on this password. The private key bits should be in a PVK file format.
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 fails.
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 fails.
ENCRYPTION BY PASSWORD = 'password'
Specifies the password 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 is encrypted using the database master key. password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server. For more information, see Password Policy.
Specifies the password 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 is encrypted using the database master key. password must meet the Windows password policy requirements of the computer that is running the instance of SQL Server. 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 should be no more than 64 characters long, and this limit is enforced for SQL Server on Linux. For SQL Server on Windows, the subject can be up to 128 characters long. Subjects that exceed 128 characters are truncated when they are stored in the catalog, but the binary large object (BLOB) that contains the certificate retains the full subject name.
The term subject refers to a field in the metadata of the certificate as defined in the X.509 standard. The subject should be no more than 64 characters long, and this limit is enforced for SQL Server on Linux. For SQL Server on Windows, the subject can be up to 128 characters long. Subjects that exceed 128 characters are truncated when they are stored in the catalog, but the binary large object (BLOB) that contains the certificate retains the full subject name.
START_DATE = 'datetime'
Is the date on which the certificate becomes valid. If not specified, START_DATE is set equal to the current date. START_DATE is in UTC time and can be specified in any format that can be converted to a date and time.
Is the date on which the certificate becomes valid. If not specified, START_DATE is set equal to the current date. START_DATE is in UTC time and can be specified in any format that can be converted to a date and time.
EXPIRY_DATE = 'datetime'
Is the date on which the certificate expires. If not specified, EXPIRY_DATE is set to a date one year after START_DATE. EXPIRY_DATE is in UTC time and can be specified in any format that can be converted to a date and time. SQL Server Service Broker checks the expiration date. Backup with Encryption using certificates also checks the expiration date and will not allow a new backup to be created with an expired certificate, but will allow restores with an expired certificate. However, expiration is not enforced when the certificate is used for database encryption or Always Encrypted.
Is the date on which the certificate expires. If not specified, EXPIRY_DATE is set to a date one year after START_DATE. EXPIRY_DATE is in UTC time and can be specified in any format that can be converted to a date and time. SQL Server Service Broker checks the expiration date. Backup with Encryption using certificates also checks the expiration date and will not allow a new backup to be created with an expired certificate, but will allow restores with an expired certificate. However, expiration is not enforced when the certificate is used for database encryption or Always Encrypted.
ACTIVE FOR BEGIN_DIALOG = { ON | OFF }
Makes the certificate available to the initiator of a Service Broker dialog conversation. The default value is ON.
Makes the certificate available to the initiator of a Service Broker dialog conversation. The default value is ON.
![Key Key](/uploads/1/2/6/0/126068751/897415721.jpg)
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, a binary constant, or an assembly. This statement can also generate a key pair and create a self-signed certificate.The Private Key must be <= 2500 bytes in encrypted format. Private keys generated by SQL Server are 1024 bits long through SQL Server 2014 (12.x) and are 2048 bits long beginning with SQL Server 2016 (13.x). Private keys imported from an external source have a minimum length of 384 bits and a maximum length of 4,096 bits. The length of an imported private key must be an integer multiple of 64 bits. Certificates used for TDE are limited to a private key size of 3456 bits.
The entire Serial Number of the certificate is stored but only the first 16 bytes appear in the sys.certificates catalog view.
The entire Issuer field of the certificate is stored but only the first 884 bytes in the sys.certificates catalog view.
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 fails.
The
ENCRYPTION BY PASSWORD
option is not required when the private key is encrypted with the database master key. Use this option only when the private key is encrypted with a password. If no password is specified, the private key of the certificate will be encrypted using the database master key. If the master key of the database cannot be opened, omitting this clause causes an error.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.
A binary description of a certificate can be created by using the CERTENCODED (Transact-SQL) and CERTPRIVATEKEY (Transact-SQL) functions. For an example that uses CERTPRIVATEKEY and CERTENCODED to copy a certificate to another database, see example B in the article CERTENCODED (Transact-SQL).
The MD2, MD4, MD5, SHA, and SHA1 algorithms are deprecated in SQL Server 2016 (13.x). Up to SQL Server 2016 (13.x), a self-signed certificate is created using SHA1. Starting with SQL Server 2017 (14.x), a self-signed certificate is created using SHA2_256.
Permissions
Requires
CREATE CERTIFICATE
permission on the database. Only Windows logins, SQL Server logins, and application roles can own certificates. Groups and roles cannot own certificates.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.B. Creating a certificate from a file
The following example creates a certificate in the database, loading the key pair from files.
Important
Azure SQL Database does not support creating a certificate from a file.
C. Creating a certificate from a signed executable file
Alternatively, you can create an assembly from the
dll
file, and then create a certificate from the assembly.Important
Azure SQL Database does not support creating a certificate from a file.
Important
Starting with SQL Server 2017 (14.x), the 'CLR strict security' server configuration option prevents loading assemblies without first setting up the security for them. Load the certificate, create a login from it, grant
UNSAFE ASSEMBLY
to that login, and then load the assembly.D. Creating a self-signed certificate
The following example creates a certificate called
Shipping04
without specifying an encryption password. This example can be used with Parallel Data Warehouse.See Also
ALTER CERTIFICATE (Transact-SQL)
DROP CERTIFICATE (Transact-SQL)
BACKUP CERTIFICATE (Transact-SQL)
Encryption Hierarchy
EVENTDATA (Transact-SQL)
CERTENCODED (Transact-SQL)
CERTPRIVATEKEY (Transact-SQL)
CERT_ID (Transact-SQL)
CERTPROPERTY (Transact-SQL)
DROP CERTIFICATE (Transact-SQL)
BACKUP CERTIFICATE (Transact-SQL)
Encryption Hierarchy
EVENTDATA (Transact-SQL)
CERTENCODED (Transact-SQL)
CERTPRIVATEKEY (Transact-SQL)
CERT_ID (Transact-SQL)
CERTPROPERTY (Transact-SQL)
Important: This example is intended to provide general guidance to IT professionals who are experienced with SSL requirements and configuration. The procedure described in this article is just one of many available methods you can use to generate the required files. The process described here should be treated as an example and not as a recommendation.
When you configure Tableau Server to use Secure Sockets Layer (SSL) encryption, this helps ensure that access to the server is secure and that data sent between Tableau Server and Tableau Desktop is protected.
Looking for Tableau Server on Linux? See Example: SSL Certificate - Generate a Key and CSR.
Tableau Server uses Apache, which includes OpenSSL. You can use the OpenSSL toolkit to generate a key file and Certificate Signing Request (CSR) which can then be used to obtain a signed SSL certificate.
Steps to generate a key and CSR
To configure Tableau Server to use SSL, you must have an SSL certificate. To obtain the SSL certificate, complete the steps:
Generate Key File From Certificate Template
- Generate a key file.
- Create a Certificate Signing Request (CSR).
- Send the CSR to a certificate authority (CA) to obtain an SSL certificate.
- Use the key and certificate to configure Tableau Server to use SSL.
You can find additional information on the SSL FAQ page on the Apache Software Foundation website.
Configure a certificate for multiple domain names
Tableau Server allows SSL for multiple domains. To set up this environment, you need to modify the OpenSSL configuration file, openssl.conf, and configure a Subject Alternative Name (SAN) certificate on Tableau Server. See For SAN certificates: modify the OpenSSL configuration file below.
Set the OpenSSL configuration environment variable (optional)
To avoid using the
-config
argument with every use of openssl.exe, you can use the OPENSSL_CONF
environment variable to ensure that the correct configuration file is used and all configuration changes made in subsequent procedures in this article produce expected results (for example, you must set the environment variable to add a SAN to your certificate). Open the Command Prompt as an administrator, and run the following command:
set OPENSSL_CONF=c:Program FilesTableauTableau Serverpackagesapache.<version_code>confopenssl.cnf
Notes:
- When setting the Open SSL configuration environment variable, do not enclose the file path with quotation marks.
- If you are using a 32-bit version of Tableau Server on a 64-bit computer, run the
set OPENSSL_CONF=c:Program Files (x86)TableauTableau Serverpackagesapache.<version_code>confopenssl.cnf
command instead.
Generate a key
Generate a key file that you will use to generate a certificate signing request.
- Open the Command Prompt as an administrator, and navigate to the Apache directory for Tableau Server. For example, run the following command:
cd C:Program FilesTableauTableau Serverpackagesapache.<version_code>bin
- Run the following command to create the key file:
openssl.exe genrsa -out <yourcertname>.key 4096
Note: This command uses a 4096-bit length for the key. You should choose a bit length that is at least 2048 bits because communication encrypted with a shorter bit length is less secure. If a value is not provided, 512 bits is used.
Create a certificate signing request to send to a certificate authority
Use the key file you created in the procedure above to generate the certificate signing request (CSR). You send the CSR to a certificate authority (CA) to obtain a signed certificate.
Important: If you want to configure a SAN certificate to use SSL for multiple domains, first complete the steps in For SAN certificates: modify the OpenSSL configuration file below, and then return to here to generate a CSR.
- Run the following command to create a certificate signing request (CSR) file:
openssl.exe req -new -key yourcertname.key -out yourcertname.csr
If you did not set the OpenSSL configuration environment variable,OPENSSL_CONF
, you might see either of the following messages:- An error message about the config information being unable to load. In this case, retype the command above with the following parameter:
-config ..confopenssl.cnf
. - A warning that the
/usr/local/ssl
directory cannot be found. This directory does not exist on Windows, and you can simply ignore this message. The file is created successfully.
To set an OpenSSL configuration environment variable, see Set the OpenSSL configuration environment variable (optional) section in this article. - When prompted, enter the required information.Note: For Common Name, type the Tableau Server name. The Tableau Server name is the URL that will be used to reach the Tableau Server. For example, if you reach Tableau Server by typing
tableau.example.com
in the address bar of your browser, thentableau.example.com
is the common name. If the common name does not resolve to the server name, errors will occur when a browser or Tableau Desktop tries to connect to Tableau Server.
Send the CSR to a certificate authority to obtain an SSL certificate
Send the CSR to a commercial certificate authority (CA) to request the digital certificate. For information, see the Wikipedia article Certificate authority and any related articles that help you decide which CA to use.
Use the key and certificate to configure Tableau Server
When you have both the key and the certificate from the CA, you can configure Tableau Server to use SSL. For the steps, see Configure External SSL.
For SAN certificates: modify the OpenSSL configuration file
In a standard installation of OpenSSL, some features are not enabled by default. To use SSL with multiple domain names, before you generate the CSR, complete these steps to modify the openssl.cnf file.
- Open Windows Explorer and browse to the Apache conf folder for Tableau Server.For example:
C:Program FilesTableauTableau Server<version_code>apacheconf
- Open openssl.cnf in a text editor, and find the following line:
req_extensions = v3_req
This line might be commented out with a hash sign (#) at the beginning of the line.If the line is commented out, uncomment it by removing the # and space characters from the beginning of the line. - Move to the [ v3_req ] section of the file. The first few lines contain the following text:
# Extensions to add to a certificate request
basicConstraints = CA:FALSE
keyUsage = nonRepudiation, digitalSignature, keyEnciphermentAfter the keyUsage line, insert the following line:subjectAltName = @alt_names
If you’re creating a self-signed SAN certificate, do the following to give the certificate permission to sign the certificate:- Add the
cRLSign
andkeyCertSign
to the keyUsage line so it looks like the following:keyUsage = nonRepudiation, digitalSignature, keyEncipherment, cRLSign, keyCertSign
- After the keyUsage line, add the following line:
subjectAltName = @alt_names
- In the [alt_names] section, provide the domain names you want to use with SSL.
DNS.1 = [domain1]
DNS.2 = [domain2]
DNS.3 = [etc]The following image shows the results highlighted, with placeholder text that you would replace with your domain names. - Save and close the file.
- Complete the steps in Create a certificate signing request to send to a certificate authority section, above.
Additional information
If you prefer to use a different version of OpenSSL, you can download it from Open SSL for Windows.