Before proceeding with SSL certificate configuration, ensure that SQL Server is properly installed and accessible. Two critical prerequisites must be satisfied:

Enable TCP/IP Protocol
Launch the Sql Server Configuration Manager tool that accompanies SQL Server installation. Navigate to SQL Server Network Configuration and ensure TCP/IP protocol is enabled for remote conenctions.

Network Accessibility
For cloud-hosted environments, verify VPC settings and security group rules permit connections from client machines. Test connectivity using:

curl -kv {ip}:{port}

Generating Self-Signed Certificates with SAN Extensions

SQL Server validates certificates using the Subject Alternative Name (SAN) extension to verify connection authenticity. The certificate must include all possible IP addresses and hostnames your SQL Server might use, including NAT scenarios.

Create OpenSSL Configuration File

Generate a configuration file named openssl.cnf with the following structure:

[req]
default_bits = 2048
prompt = no
default_md = sha256
x509_extensions = v3_req
distinguished_name = req_distinguished_name

[req_distinguished_name]
C = US
ST = California
L = San Francisco
O = TechCorp
OU = Database
CN = db-server-01

[v3_req]
basicConstraints = CA:FALSE
keyUsage = nonRepudiation, digitalSignature, keyEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names

[alt_names]
DNS.1 = db-server.internal
DNS.2 = db-server.company.local
IP.1 = 192.168.1.100
IP.2 = 10.0.0.50

Generate Private Key and Certificate Signing Request

Execute the following command to create a 2048-bit RSA private key and CSR:

openssl req -new -x509 -keyout sql_server.key -out sql_server.crt -days 365 -config openssl.cnf -extensions v3_req

Verify the generated certificate contains the expected SAN entries:

openssl x509 -in sql_server.crt -text -noout | grep -A 2 "Subject Alternative Name"

Export Certificate in PKCS#12 Format

Combine the private key and certificate into a PKCS#12 bundle for SQL Server import:

openssl pkcs12 -export -in sql_server.crt -inkey sql_server.key -out sql_server.pfx -name "SQL Server SSL" -password pass:SecurePass123

Convert to JKS Format for JDBC Clients

Java-based applications require Java KeyStore format. Use keytool to perform the conversion:

keytool -importkeystore -srckeystore sql_server.pfx -srcstoretype PKCS12 -srcstorepass SecurePass123 -destkeystore sql_server.jks -deststoretype JKS -deststorepass KeyStorePass456 -destkeypass KeyPass789

Installing Certificate on SQL Server Host

Press Windows Key + R, enter certlm.msc to open the Local Machine Certificate Manager.

Navigate to Personal Certificates → All Tasks → Import. Follow the certificate import wizard and select the PKCS#12 file generated in the previous step. Complete the import process with the specified password.

Configuring SQL Server to Use the Certificate

Launch Sql Server Configuration Manager and right-click on SQL Server services. Select Properties and navigate to the Certificate tab.

The dropdown list displays available certificates. Select the imported certificate if its Common Name matches the server hostname. Alternatively, use the Import button to manually browse and select the certificate file.

After selection, restart the SQL Server service for changes to take effect.

Verifying SSL Encryption on Connections

On client machines, import the root certificate into the Trusted Root Certification Authorities store by double-clicking the PFX file or using the Certificate Manager (certmgr.msc).

Connect using SQL Server Management Studio (SSMS). Enter the server IP address or hostname in the connection dialog. The specified IP must exist within the certificate's SAN extensions.

Click Options, then navigate to the Connection Properties tab. Check "Encrypt connection" while leaving "Trust server certificate" unchecked. This ensures SQL Server uses your configured certificate rather than the auto-generated service certificate.

After successful connection, execute the following query to confirm encryption is active:

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

The encrypt_option column should return "TRUE" indicating the connection is encrypted.

Connection String Examples

JDBC Connection String with SSL:

jdbc:sqlserver://db-server.company.local:1433;encrypt=true;trustServerCertificate=false;trustStore=/path/to/sql_server.jks;trustStorePassword=KeyStorePass456;

ADO.NET Connection String:

Server=192.168.1.100,1433;Database=ProductionDB;User Id=app_user;Password=AppPass789;Encrypt=True;TrustServerCertificate=False;