Configuring SSL for Microsoft SQL Server

To secure the connection between the i2® Analyze application server and the database instance, you must change the configuration of both. Microsoft SQL Server stores its associated certificates and you must create or obtain certificates for the Microsoft SQL Server to use.

About this task

In i2 Analyze, SSL connections that involve SQL Server require i2 Analyze to trust the certificate that it receives from SQL Server. SQL Server stores certificates in the operating system's certificate stores. In a production deployment, you must use a certificate that is signed by a trusted certificate authority. To demonstrate a working configuration, you can create and use a self-signed certificate.

Ensure that you understand the details that are provided in the SQL Server documentation to configure SSL for your SQL Server. For more information, on Windows see Enable Encrypted Connections to the Database Engine or Linux® see Server Initiated Encryption.

Procedure

Create a self-signed certificate for SQL Server.

For example, on Windows you can use the MakeCert utility. For information about installing MakeCert, see MakeCert.
Run the following command to create a certificate by using MakeCert:
```
makecert -r -pe -n "CN=hostname" -e 12/12/2020 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12
```
Important: Ensure that you set the value of CN to the hostname of the server where SQL Server is located.

For example, on Linux you can run the following commands by using OpenSSL:

openssl req -x509 -nodes -newkey rsa:2048 -subj '/CN=hostname' -keyout sql-server-key.key -out sql-server-certificate.pem -days 365 
sudo chown mssql:mssql sql-server-certificate.pem sql-server-key.key 
sudo chmod 600 sql-server-certificate.pem sql-server-key.key
sudo mv sql-server-certificate.pem /etc/ssl/certs/
sudo mv sql-server-key.key /etc/ssl/private/

Important: Ensure that you set the value of CN to the hostname of the server where SQL Server is located.

Export the self-signed certificate.

On Windows:
1. Use the Certificates snap-in in the Microsoft Management Console to export the certificate from the Local Computer user's certificates.
2. Locate the self-signed certificate in the Personal certificate store.
3. Right-click the certificate, and click All Tasks > Export. Complete the Certificate Export Wizard to export the certificate without the private key as a DER encoded binary X.509 file. Set the file name to i2-sqlserver-certificate.cer.
For more information about exporting the certificate, see To export the server certificate.
On Linux:
1. Extract the DER certificate from the PEM file by using OpenSSL:
```
openssl x509 -outform der -in sql-server-certificate.pem -out i2-sqlserver-certificate.cer
```
  After you create the CER file, ensure that the file permissions are the same as the original PEM file.

Configure SQL Server to encrypt connections.

On Windows:
1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, and right-click Protocols for <instance> and click Properties.
2. In the Properties window on the Certificate tab, select your certificate from the Certificate list and click Apply.
3. On the Flags tab, select Yes from the Force Encryption list.
4. Click OK and restart the SQL Server instance.
  
  Note: You must start the SQL Server instance as a user that trusts the certificate you specified.
For more information about encrypted connections, see To configure the server to force encrypted connections.

On Linux:

Run the following commands to specify your certificate and key, and configure SQL Server:

systemctl stop mssql-server
cat /var/opt/mssql/mssql.conf
sudo /opt/mssql/bin/mssql-conf set network.tlscert /etc/ssl/certs/sql-server-certificate.pem
sudo /opt/mssql/bin/mssql-conf set network.tlskey /etc/ssl/private/sql-server-key.key
sudo /opt/mssql/bin/mssql-conf set network.tlsprotocols 1.2
sudo /opt/mssql/bin/mssql-conf set network.forceencryption 1

Restart the SQL Server instance.