Enabling SSL/TLS Encryption for SQL Server in SharePoint Environments

Introduction

In enterprise-grade SharePoint environments, securing the communication between SharePoint servers and SQL Server is paramount. By default, data exchanged between SQL Server and its clients is not encrypted, leaving it vulnerable to interception on the network.

To comply with security standards such as ISO 27001, HIPAA, GDPR, or LGPD, it is highly recommended to implement SSL/TLS encryption for SQL Server connections—particularly when deploying SharePoint farms across subnets, virtual networks, or hybrid topologies.

This article explains, in detail, how to configure SQL Server to enforce encryption using SSL/TLS, and how to validate that encryption is active in SharePoint.

Why Use SSL/TLS for SQL Server?

• Data Confidentiality: Prevents packet sniffing and man-in-the-middle (MITM) attacks.
• Compliance: Required by industry standards and internal audit policies.
• Authentication: Validates the SQL Server identity using digital certificates.
• Secure Administration: Encrypts communication during management via SSMS or PowerShell.

Prerequisites

Before configuring encryption:

1. Obtain a valid X.509 certificate:
   • Issued by an internal or external Certificate Authority (CA).
   • Must have the FQDN of the SQL Server in the Subject or Subject Alternative Name (SAN).
   • Key usage should include Server Authentication (EKU=1.3.6.1.5.5.7.3.1).
2. Install the certificate:
   • Place it in the Local Computer > Personal store on the SQL Server machine.

Configuring SSL/TLS Encryption for SQL Server

Step 1: Bind the Certificate

1. Open SQL Server Configuration Manager.
2. Go to SQL Server Network Configuration > Protocols for [Instance Name].
3. Right-click Properties > Go to the Certificate tab.
4. Select the installed certificate from the dropdown.
5. On the Flags tab:
   • Set Force Encryption to Yes.
6. Restart the SQL Server service.

Note: Ensure the SQL Server service account has read access to the private key of the certificate.

Step 2: Configure Client Encryption in SharePoint

SharePoint communicates with SQL Server using .NET clients. If you enforce encryption, clients must also be configured to trust the SQL Server certificate.

Edit the SQL Native Client or .NET connection string to include:

Encrypt=True;TrustServerCertificate=False;

For SharePoint:

1. Open Central Administration > Manage Servers in this Farm.
2. Ensure all servers can validate the SQL Server’s certificate chain.
3. If needed, deploy the CA certificate to Trusted Root Certification Authorities on SharePoint servers.

Alternatively, use TrustServerCertificate=True if the certificate is self-signed—but this is not recommended for production.

Verifying Encrypted SQL Connections

Option 1: SQL Server DMV Query

Run the following query on SQL Server:

SELECT session_id, encrypt_option, net_transport
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

• encrypt_option should return TRUE.

Option 2: Wireshark Packet Capture

Capture network traffic on port 1433:

• Look for TLS handshake.
• Plaintext SQL queries should not be visible.

PowerShell Script to Deploy Certificate and Verify

Here is a PowerShell snippet to check certificate presence and test binding:

$cert = Get-ChildItem -Path Cert:\LocalMachine\My | Where-Object {
    $_.Subject -like "*your-sql-fqdn.domain.com*" -and
    $_.EnhancedKeyUsageList.FriendlyName -contains "Server Authentication"
}

if ($cert) {
    Write-Output "Certificate found and valid for SSL."
} else {
    Write-Warning "Valid certificate not found."
}

Troubleshooting Tips

Summary Table: SSL/TLS Configuration Steps

References

• Microsoft Docs: Enable Encrypted Connections to SQL Server
• Wikipedia: Transport Layer Security
• Wikipedia: SQL Server
• Wikipedia: SharePoint