SSL Provider Error: [error:1416F086] with ODBC Driver 18 for SQL Server

Problem Statement

When attempting to connect to SQL Server using ODBC Driver 18, particularly from Linux environments, you may encounter the following SSL error:

[Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate]

This error typically occurs when the client cannot verify the server's SSL certificate, often because:

• The SQL Server instance uses a self-signed certificate
• The certificate chain is incomplete or not trusted
• The client's OpenSSL configuration has security restrictions
• The connection string doesn't properly handle certificate validation

The issue is most commonly reported on:

• Ubuntu 20.04 and later versions
• PHP environments with SQLSRV or PDO_SQLSRV extensions
• Python applications using pyodbc
• .NET applications on Linux

Solutions

1. Modify Connection String Parameters

The most straightforward solution is to adjust your connection string to handle certificate validation appropriately.

Option A: Trust Server Certificate

Add TrustServerCertificate=yes to your connection string:

// PHP example
$sql_info = array(
    'UID' => 'your_sql_user',
    'PWD' => 'your_password',
    'Database' => 'your_DB_name',
    'TrustServerCertificate' => 1,
);
sqlsrv_connect('your_host_or_ip', $sql_info);

# Python example
conn_str = f'DRIVER=ODBC Driver 18 for SQL Server;SERVER={server};DATABASE={database};UID={username};PWD={password};TrustServerCertificate=yes'

' VB.NET example
Dim conStr As String = "Driver={ODBC Driver 18 for SQL Server};Server=your_servername;Database=your_databasename;Uid=your_username;Pwd=your_password;TrustServerCertificate=yes;"

Option B: Disable Encryption (Development Only)

For development environments where encryption isn't required:

# Python example - disable encryption
conn_str = f'DRIVER=ODBC Driver 18 for SQL Server;SERVER={server};DATABASE={database};UID={username};PWD={password};Encrypt=no'

SECURITY NOTE

Disabling encryption (Encrypt=no) should only be used in development environments. Never use this in production as it transmits data in clear text.

2. Framework-Specific Implementations

Laravel (PHP) Configuration

In config/database.php, add the trust_server_certificate parameter:

'sqlsrv' => [
    'driver' => 'sqlsrv',
    'host' => env('DB_HOST', 'localhost'),
    'port' => env('DB_PORT', '1433'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'prefix_indexes' => true,
    'trust_server_certificate' => 'true'
]

Django with mssql-django

DATABASES['default'] = {
    'ENGINE': 'mssql',
    'NAME': 'your_database',
    'USER': 'your_username',
    'PASSWORD': 'your_password',
    'HOST': 'your_host',
    'OPTIONS': {
        'driver': 'ODBC Driver 18 for SQL Server',
        'extra_params': "TrustServerCertificate=yes"
    },
}

SQLAlchemy (Python)

import sqlalchemy

conn_string = "mssql+pyodbc://username:password@server/database?driver=ODBC+Driver+18+for+SQL+Server"
engine = sqlalchemy.create_engine(
    conn_string,
    connect_args={
        "TrustServerCertificate": "yes"
    }, 
    echo=False
)

3. OpenSSL Configuration Adjustment

On some Linux distributions, you may need to adjust the OpenSSL security level:

sudo nano /etc/ssl/openssl.cnf

Find the [system_default_sect] section and modify:

[system_default_sect]
# Change from:
# CipherString = DEFAULT:@SECLEVEL=2
# To:
CipherString = DEFAULT:@SECLEVEL=0

SECURITY WARNING

Lowering the OpenSSL security level reduces cryptographic strength. Only use this as a temporary workaround and explore proper certificate management for production environments.

4. SQL Server Command Line Tools

When using sqlcmd, add the -C flag to trust the server certificate:

sqlcmd -S <server> -U <username> -P <password> -C

Or try connecting using the IP address instead of hostname:

sqlcmd -S 127.0.0.1 -U sa -P 'YourPassword' -C

Best Practices for Production Environments

While the above solutions work for development, production environments should implement proper certificate management:

1. Install a valid certificate from a trusted Certificate Authority on your SQL Server
2. Ensure proper certificate chain installation
3. Verify hostname matching between the certificate and your connection string
4. Maintain appropriate OpenSSL security levels without downgrading

Troubleshooting Steps

1. Verify your ODBC driver version: odbcinst -q -d
2. Test basic connectivity: openssl s_client -connect your_server:1433
3. Check SQL Server certificate validity and configuration
4. Ensure you're using the correct driver name in connection strings

Conclusion

The SSL certificate verification error with ODBC Driver 18 typically resolves by:

• Adding TrustServerCertificate=yes to your connection string
• Ensuring proper driver specification (ODBC Driver 18 for SQL Server)
• Adjusting framework-specific configuration as needed

For production environments, always prioritize proper certificate management over security workarounds to maintain data security and compliance.