Enable SSL Connection in MSSQL

Enable SSL Connection in MSSQL

Secure the AssetExplorer MSSQL database by setting up an SSL connection.
 
To create an SSL connection, you must obtain either a self-signed SSL certificate or purchase a verified SSL certificate from a vendor.
 
To generate a self-signed certificate, go to the command prompt and enter the following command:
New-SelfSignedCertificate -CertStoreLocation Cert:\LocalMachine\My -DnsName host.doman.com -KeySpec KeyExchange -FriendlyName SQLCert

The following are the basic steps to help you enable an SSL connection in MSSQL.
 
For more information on SSL connections, refer to this link.
 

Steps to Enable SSL Connection in MSSQL    

  1. On the SQL server-installed machine, go to Start and type mmc to open the Microsoft Management Console.
  2. Go to File > Add/Remove Snap-in.
  3. Select Certificates from the Available snap-ins and click Add to move them to the Selected snap-ins box.

 
  1. The Certificates snap-in window will pop up. Select Computer account, and then click Next.

 
  1. On the displayed window, click Finish by keeping the check boxes as per default settings (Local computer).

 
  1. Import the SSL domain certificate under Certificates > Personal > Certificates. To import the certificate, click Actions > Personal > More Actions > All tasks > Import.

 
  1. Import the certificate again under Certificates > Trusted Root Certification Authorities.
Info
To import a certificate chain, upload the respective certificates into Personal, Intermediate, and Trusted Root Certification Authorities. 
  1. Go to Start > Computer Management > SQL Server Configuration Manager.
  2. Under SQL Server Network Configuration, right-click Protocols of MSSQLSERVER and select Properties.
  3. On the Flags tab, choose Yes for Force Encryption.

 
  1. On the Certificate tab, choose the SSL certificate from the drop-down.
  2. Click Import, and then Apply.

  1. Restart the MSSQL server for the changes to take effect.
Info
To determine whether the AssetExplorer MSSQL database connection is secure,
  1. Connect Microsoft SQL Server Management Studio enabling only the Encrypt Connection check box and disabling the Trust server certificate check box.  
  2. Execute the following query under New Query in the Microsoft SQL Server Management Studio: select * from sys.dm_exec_connections;
The SSL connection details will be displayed.   
  1. In case of an untrusted, internal, or self-signed certificate, follow the steps below to import the SSL certificate into Java keytool.
    1. Go to <SDP SERVER_HOME>\jre\lib\security.
    2. If the jssecacerts file is unavailable, duplicate the cacerts file and rename the duplicated file to jssecacerts.
    3. On the command prompt, navigate to <SERVER_HOME>\jre\bin
    4. Execute the following command:
keytool -import -alias SQLServer-1 -keystore ..\lib\security\jssecacerts -file <path to the SQL certificate> 
    1. Enter the password as changeit when prompted.
    2. Restart AssetExplorer for the changes to take effect.

Info
The keytool -list -v -keystore ../lib/security/jssecacerts > cert.txt command can be used to see if the certificate is imported into jssecacerts.
  1. To enable SSL for the application, append ;encrypt=true;trustServerCertificate=false; to rodatasource.url,url in the <AE_HOME>/conf/database_params.conf file