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


    • Related Articles

    • Import SSL Certificate

      An SSL (Secure Sockets Layer) certificate is a digital certificate that verifies a website's identity and allows for an encrypted connection. If AssetExplorer operates on an HTTPS port or uses HTTPS mode for communication with Endpoint Central ...

    • Prerequisites for configuring LDAP SSL

      Role Required: SDAdmin By default, any LDAP communication between client and server applications are not encrypted, making it vulnerable to network monitoring jump devices/software. AssetExplorer employs LDAP SSL to secure the communication between ...

    • FAQs Related to Database, HTTPS, and SSL

      How to change the Endpoint Central database from Postgres to MSSQL? By default, Endpoint Central agents come with bundled PGSQL but also supports MSSQL. Click here for a list of MSSQL versions supported by Endpoint Central. For detailed instructions ...

    • FIPS Compliance

      What is FIPS? Federal Information Processing Standards (FIPS) is established by the US government to enhance the security posture of organizations. It provides guidelines and best practices for securing data, employing strong cryptographic methods, ...

    • Agent Configuration

      Agent-based scanning is supported for Windows, Linux, and Mac machines. Before proceeding with an agent-based scan, Endpoint Central has to be installed. Click here to learn more. Configure Endpoint Central agent settings in AssetExplorer under Admin ...