Configuring SSL to a Microsoft SQL Server source database involves the following steps:
Grant read permission to the private keys to the SQL Server service logon user
Configure ODBC data source or connection string for SQDR source or destination
The instructions below assume that a Microsoft Windows Certificate Server exists on your Windows domain, and that both the server system running SQL Server and the system running SQDR are members of the domain. Note that the Certificate Server can reside on any server in the domain, and does not need to be installed on a domain controller. Additional certificate setup work will be necessary if you are using another type of Certificate Authority, or if multiple Windows domains are involved.
Alternatively, you can specify trustServerCertificate=true (JDBC) or TrustServerCertificate=yes (ODBC) to instruct the driver to connect without verifying the server certificate. This is useful if the server certificate was issued by a Certificate Authority (CA) unknown to the client machine or the connection specifies an IP address or hostname other than the Common Name defined in the certificate.
In a command window or a Run window. type MMC
On the Console menu, click Add/Remove Snap-in....
Click Add, and then click Certificates. Click Add again.
You are prompted to open the snap-in for the current user account, the service account, or for the computer account. Select the Computer Account.
Select Local computer, and then click Finish.
Click Close in the Add Standalone Snap-in dialog box.
Click OK in the Add/Remove Snap-in dialog box. Your installed certificates are located in the Certificates folder in the Personal container.
Click to select the Personal folder in the left-hand pane.
Right-click in the right-hand pane, point to All Tasks, and then click Request New Certificate....
The Certificate Request Wizard dialog box opens. Click Next. Select Certificate type is "computer".
In the Friendly Name text box you can type a friendly name for the certificate or leave the text box blank, and then complete the wizard.
If the Certificate Server has been configured to automatically issue certificates (this is typically defined in the Certificate Template), the certificate is already available. If issuing the certificate requires approval from the administrator, you will need to wait for the administrator to approve the request.
On completion, you will see the certificate in the folder with the fully qualified computer domain name.
Use the Services control panel (Properties/Logon) to determine the name of the user account that the SQL Server service runs. This is typically something like NT Service\MSSQL$SQLEXPRESS for a named instance; it may also be a domain account.
In MMC using the Certificates snap-in, select
the certificate installed above, right-click, and choose Manage
Private Keys from the context menu.
Grant read permission to the SQL Server account.
Open the SQL Server Configuration Manager.
Expand SQL Server Network Configuration, right-click Protocols for <server instance>, and then select Properties.
On the Certificate tab, select the desired certificate from the Certificate drop-down menu, and then click OK.
On the Flags tab, select Yes in the ForceEncryption box, and then click OK to close the dialog box.
Restart the SQL Server service.
On the machine running SQL Server, use MMC and the Certificates snap-in to export the CA (Certificate Authority) certificate:
Navigate to the Trusted Root Certificate Authorities/Certificates folder.
Locate the certificate for the Certificate Authority (e.g. mydomain-MYCERTSRV)
Right click on the certificate and select All
Tasks/Export...
For format, choose Base-64 encode X.509 (.CER) and select a destination folder.
Copy the resulting text file to the system running SQDR.
On the machine running SQDR (or any machine with browser access to SQDR Control Center), import the certificate into the Java keystore used by SQDR Plus:
In SQDR Control Center, select Manage Certificates from the Databases menu.
Open the exported certificate in Notepad or other text editor and copy and paste the entire certficate (including -----BEGIN CERTIFICATE----- and -----END CERTIFICATE-----) into the contents window.
Enter any name e.g. MYSQLServer in the Alias text field.
Click the Add
Certificate button.
Create a SQL Server Agent as usual. Be sure to specify the fully qualified hostname of the SQL Server rather than localhost or an IP address.
Modify the Configuration of the agent: add encrypt=true to the sourceDbUrl property.
The resulting URL should look something like:
jdbc:sqlserver://myhost.mydomain.com\SQLEXPRESS;databaseName=MYDATABASE;encrypt=true
Saving the configuration will restart the Agent; check the Diagnostics for any errors or warnings.
Use the fully qualified name (e.g. myhost.mydomain.com\SQLEXPRESS), rather than localhost, an unqualified name, or an IP address.
If you are using an ODBC data source, select the checkbox for Use strong encryption for data.
If you are using an ODBC connection string, add Encrypt=Yes.
Use the Test Data Source button on the panel when configuring a Data Source in ODBC Administrator and verify that it displays:
INFO: Connection was encrypted with server certificate validation.
Note that if you do not specify encryption, the client can still connect but will display:
INFO: Data encryption was enforced by server or client
machine-wide setting.
INFO: Connection was encrypted without server certificate validation.
We recommend using the Test Data Source button on the panel when configuring a Data Source in ODBC Administrator, as it provides information on whether SSL is being used and whether or not a certificate was used.
Use Event Viewer to look in the System event log for errors with Source=Schannel.
Symptom: After enabling SSL in SQL Server Configuration Manager, the SQL Server service fails to start and the following error appears in the Event Log:
A fatal error occurred when attempting to access the SSL server credential private key. The error code returned from the cryptographic module is 0x8009030D. The internal error state is 10001.
Solution: Grant read access to private keys to the SQL Server logon user.
Symptom: An ODBC connection fails with:
[Microsoft][ODBC Driver 17 for SQL Server]SSL Provider:
The target principal name is incorrect.
The certificate received from the remote server does not contain the expected
name. It is therefore not possible to determine whether we are connecting
to the correct server. The server name we were expecting is .. The SSL
connection request has failed. The attached data contains the server certificate.
Solution: Make sure that the connection is using the fully qualified name (e.g. myhost.mydomain.com\SQLEXPRESS), rather than localhost, an unqualified name, or an IP address, as this name must match the name that appears as the name of the certificate.
Symptom:The following error appears in the SQDR Plus agent diagnostics:
com.microsoft.sqlserver.jdbc.SQLServerException: The driver
could not establish a secure connection to SQL Server by using Secure
Sockets Layer (SSL) encryption.
Error: "java.lang.RuntimeException: Unexpected error: java.security.InvalidAlgorithmParameterException:
the trustAnchors parameter must be non-empty".
Solution: Import the CA certificate from the Certificate Server into the Java keystore used by SQDR Plus.
Symptom:The following error appears in the SQDR Plus agent diagnostics:
com.microsoft.sqlserver.jdbc.SQLServerException: The driver
could not establish a secure connection to SQL Server by using Secure
Sockets Layer (SSL) encryption.
Error: "java.security.cert.CertificateException: Failed to validate
the server name in a certificate during Secure Sockets Layer (SSL) initialization.".
Solution: Be sure to use the fully qualified hostname in the URL, as this name must match the name that appears as the name of the certificate.
Alternatively, specify trustServerCertificate=true.
Symptom:The following error appears in the SQDR Plus agent diagnostics:
com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "sun.security.validator.ValidatorException: PKIX path validation failed: java.security.cert.CertPathValidatorException: validity check failed".
and the following error appears in the System Event Log from Schannel
Event ID 36881
The certificate received from the remote server has either expired or is
not yet valid. The SSL connection request has failed. The attached data
contains the server certificate.
Solution: This error could result if the certificate used by the SQL Server has expired. Renew or replace the certificate and restart the SQL Server service. If you replace the certificate, the SQL Server service will not start until you open the SQL Server Configuration Manager, select the server instance under Network Configuration, select Protocols/Properties, and select the new certificate.
https://support.microsoft.com/en-us/help/316898/how-to-enable-ssl-encryption-for-an-instance-of-sql-server-by-using-mi
How to enable SSL encryption for an instance of SQL Server by using Microsoft
Management Console