SQL Server: Enforce Encrypted Connections

Tags: SQL Server, force, encryption, encrypted, connections

Friday, August 16, 2019 6:26:00 PM

To configure SQL Server to use a self-signed SSL certificate, follow below steps:

  1. Create a self-signed certificate
  2. Set permissions for this certificate
  3. Configure SQL Server to use this certificate.

      Create a certificate With IIS

     If you have IIS on your machine:

  • Start IIS Manager
  • Go to Server Certificates
  • Right Click > Create Self-Signed Certificate
Set permissions for the certificate Find the service account name for your SQL Server instance
 
  • Start SQL Server Configuration Manager
  • Go to SQL Server Services
  • Select your instance
  • Right-click Properties
  • Copy the account name for later re-use

Launch the Certificates snap-in

One way to manage your certificates is to

  • Launch MMC (type mmc.exe in the Windows search bar)
  • Go to File > Add / Remove Snap-in …
  • Select Certificates ( when prompted choose Computer Account)

Grant SQL Server rights to read the private key for the certificate.

  • In MMC, go to Certificates (Local computer) > Personal > Certificates
  • The certificate should be listed there. ( If  you created the certificate using makecert, you may have to import it.)
  • Right click > All Tasks > Manage Private Keys
  • Add the service account for your instance of SQL Server
  • Give the service account Read permissions

Configure SQL Server to use this certificate

  • Start SQL Server Configuration Manager
  • Go to SQL Server Network configuration
  • Select your instance
  • Right-click > Properties > Certificate tab
  • Choose the certificate you just created
  • Restart the service

Source:  http://support.pitneybowes.com/SearchArticles/VFP05_KnowledgeWithSidebarHowTo?id=kA180000000Ci7fCAC&popup=false;&lang=en_US

Source: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/enable-encrypted-connections-to-the-database-engine?view=sql-server-2017

1 Comment

  • researchadmin said Reply

    then check your session if encrypted by:

    SELECT encrypt_option
    FROM sys.dm_exec_connections
    WHERE session_id = @@SPID

    Sources:
    https://www.sqlservercentral.com/forums/topic/is-ssl-being-used

    https://dba.stackexchange.com/questions/117873/how-can-i-check-if-connection-to-sql-server-is-encrypted

    https://blogs.technet.microsoft.com/sqlman/2009/01/26/verifying-if-a-connection-to-sql-server-is-encrypted/

You must log on to comment.