How to Setup MSSQL Database SSL

Written by Adrian Yong


Hands
Adrian Yong
07 August 2018

How to prepare database for TLS1.2

Make sure all service pack has been downloaded and installed.

  1. SQL 2012 Service Pack 1 - https://www.microsoft.com/en-in/download/confirmation.aspx?id=35575
  2. SQL 2012 Service Pack 2 - https://www.microsoft.com/en-us/download/details.aspx?id=43340
  3. SQL 2012 Service Pack 3 - https://www.microsoft.com/en-us/download/details.aspx?id=49996
  4. SQL 2012 Service Pack 3 - Hotfix for TLS1.2 - https://www.microsoft.com/en-us/download/confirmation.aspx?id=50733
  5. SQL 2012 Service Pack 4 - https://www.microsoft.com/en-us/download/confirmation.aspx?id=50402

How to create database SSL certificate

Hands
  1. ** Pre-requisite. Please make sure you have Administrator access.
  2. Open Manage computer certificates by searching cert. Then right click and choose as Administrator.
  3. Drill down from Certificates - Local Computer > Personal > Certificates
  4. Select Certificates > Right click > All Tasks > Request New Certificate…
  5. Click Next twice. Select Computer template. Then click Enroll.
  6. Please note that a new certificate should appear in the right panel of the Certificate Manager user interface.
    Hands

How to configure database SSL certificate

Hands
  1. **Pre-requisite. Please make sure you have Administrator access.
  2. Type SQL Server Configuration Manager into the desktop search engine. Then right click and choose as Administrator.
  3. Drill down from SQL Server Configuration Manager (Local) > SQL Server Network Configuration > Protocols for MSSQLSERVER
  4. Right click Properties. Go to Flags tab. Select Force Encryption - Yes.
  5. Go to Certificate tab. Select a certificate. Click Apply.
  6. After click apply, schedule a database service restart at an appropriate time ( prefer non-working hours )

How to verify database SSL encryption enabled

Hands
  1. **Pre-requisite. Please make sure you have Administrator access.
  2. After server restart, search for SQL Management Studio. Right click at icon to open speedbar, and select Run as administrator.
  3. Login into the application and run the following query.

    SELECT session_id, connect_time, net_transport, encrypt_option, auth_scheme, client_net_address FROM sys.dm_exec_connections

  4. After execute the query, check the column encrypt_option for TRUE value to conclude successfully.

    Hands

How to test TLS 1.2 in web server enabled

  1. Go to the search bar. Type Internet Options. Or go to Control Panel > Internet Options.
  2. Select Advanced tab. Refer to the Security checkboxes. Untick all SSL and TLS except for TLS 1.0.
  3. Open internet explorer browser and try to browse the web page using the https url if error loading page is the result. This means TLS1.0 is disabled.
    Hands
  4. Repeat Step 1 to 3, to test different encryption (eg. SSL 3.0, TLS 1.0, TLS 1.1) that is supposed to be disabled.
  5. After testing all the disabled encryption, we proceed to test the PCI compliant encryption TLS 1.2. Please repeat step 1 and 2, except Untick all SSL and TLS except for TLS1.2.
  6. Open internet explorer browser and try to browse the web page using the https url is success. This means TLS1.2 is enabled.

Author(s) Shoutout

Written by Adrian Yong

Hi everyone, if you have any question, feel free to message me. If you like this article or would like me to extend more on the subject you can email me. Feedback is always appreciated. I will write more. If I get a two thumbs up. =)

License

This article, along with any associated source code and files, is licensed under The Microsoft Public License (Ms-PL).