- 0 minutes to read

SQL Server Connection Strings

A comprehensive guide to configuring SQL Server connection strings for Nodinite components. This page covers modern TLS certificate validation, Always On Availability Groups, authentication methods, troubleshooting, and security best practices.

Overview

Nodinite components connect to SQL Server databases using connection strings. Starting with Microsoft.Data.SqlClient v4+, default security settings have changed, requiring explicit configuration for certificate validation and encryption.

What changed:

  • Old default: Encrypt=False (no TLS), TrustServerCertificate=True (accept any cert)
  • New default: Encrypt=True (TLS required), TrustServerCertificate=False (validate cert chain)

Impact: Upgrading Nodinite components may cause "The certificate chain was issued by an authority that is not trusted" errors if SQL Server uses self-signed certificates.

Tip

This guide applies to all Nodinite components that connect to SQL Server: Core Services, Log Databases, Monitoring Agents, Logging Agents, and the Install and Update Tool.

Important

Connection strings are always encrypted: All connection strings stored in Nodinite are encrypted using certificate-based encryption. See the Secret Management guide for details on how Nodinite securely stores passwords, connection strings, and other sensitive data using automated certificate handling.

Common Connection String Examples

Basic Connection (Self-Signed Certificate) 

Data Source=localhost;Initial Catalog=DatabaseName;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;

Use when: Dev/test environments with self-signed certificates

Production Connection (Trusted CA) 

Data Source=SQL01\INSTANCE;Initial Catalog=DatabaseName;Integrated Security=True;Encrypt=True;TrustServerCertificate=False;

Use when: Production with properly configured SQL Server certificates from trusted CA

SQL Authentication 

Data Source=sql.contoso.com,1433;Initial Catalog=DatabaseName;User ID=nodinite_user;Password=***;Encrypt=True;TrustServerCertificate=True;

Use when: Windows Authentication not available (not recommended for production)

Connection String Settings

Setting Purpose Values Default (v4+)
Data Source SQL Server instance name or IP localhost, SQL01, SQL01\INSTANCE, 192.168.1.10,1433 Required
Initial Catalog Database name Database name Required
Integrated Security Windows authentication True (recommended), False False
User ID / Password SQL Server authentication Username and password (none)
Encrypt TLS encryption on/off True (recommended), False, Mandatory, Strict True
TrustServerCertificate Skip certificate validation True (self-signed), False (validate CA) False
MultiSubnetFailover Always On AG fast failover True (AG), False (standalone) False
ApplicationIntent Read-only routing ReadWrite (default), ReadOnly ReadWrite
Connect Timeout Connection timeout (seconds) 15, 30 (default), 60 30
Pooling Connection pooling True (default), False True
Min Pool Size / Max Pool Size Pool limits 0 / 100 (defaults) 0 / 100

Complete connection string reference

Certificate Validation Decision Guide

Choose the right certificate configuration for your environment:

Scenario Encrypt TrustServerCertificate Security Level Use Case
Production with trusted CA True False ✅✅✅ Highest SQL Server cert from public/enterprise CA, cert chain trusted by OS
Production with self-signed True True ✅✅ Medium SQL Server uses self-signed cert, pragmatic security
Dev/Test True True ✅✅ Medium Development environments, quick setup
Legacy (not recommended) False N/A ❌ Low Old applications, testing only
Strict mode (SQL 2022+) Strict False ✅✅✅✅ Highest Maximum security with TDS 8.0

Important

After upgrading Nodinite components, if you see certificate errors:

  1. Best practice: Install SQL Server certificate's issuing CA on the Nodinite host
  2. Quick fix: Add TrustServerCertificate=True to connection strings
  3. Not recommended: Set Encrypt=False (testing environments only)

Azure SQL Managed Instance

Azure SQL Managed Instance is a fully managed SQL Server platform-as-a-service (PaaS) in Azure. It requires SQL authentication and always uses encrypted connections.

Basic Azure SQL Managed Instance Connection 

Server=tcp:myinstance.database.windows.net,1433;Initial Catalog=DatabaseName;User ID=sqladmin;Password=***;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Use when: Hosting Nodinite databases in Azure SQL Managed Instance

Setting Value Why
Server tcp:yourinstance.database.windows.net,1433 Azure SQL Managed Instance FQDN
User ID / Password SQL authentication credentials Windows authentication not supported
Encrypt True Required — Azure SQL enforces encryption
TrustServerCertificate False Azure uses trusted CA certificates
Connection Timeout 30 or higher Account for network latency to Azure
Persist Security Info False (default) Security best practice
MultipleActiveResultSets False (default) Use True if needed by application

Full Example with Best Practices 

Server=tcp:nodinite-prod.database.windows.net,1433;Initial Catalog=Nodinite_Prod_Config;Persist Security Info=False;User ID=nodinite_admin;Password=ComplexP@ssw0rd!;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Azure SQL Database vs Managed Instance

Feature Azure SQL Database Azure SQL Managed Instance
Nodinite Core Services Support ❌ Not supported ✅ Supported (>=6.1)
Connection String Similar format tcp:instance.database.windows.net
Authentication SQL auth only SQL auth only
Encryption Always encrypted Always encrypted
Compatibility Limited T-SQL Near 100% SQL Server compatibility
Linked Servers Not supported ✅ Supported
SQL Agent Not available ✅ Available

Important

Nodinite requires Azure SQL Managed Instance, not Azure SQL Database. Managed Instance provides the SQL Server features Nodinite depends on (linked servers, SQL Agent, full T-SQL compatibility).

Azure SQL Managed Instance Checklist

  • ✅ Use Azure SQL Managed Instance, not Azure SQL Database
  • ✅ Create SQL login with appropriate permissions
  • ✅ Set Encrypt=True (enforced by Azure)
  • ✅ Keep TrustServerCertificate=False (Azure uses trusted certificates)
  • ✅ Configure Azure firewall to allow Nodinite server's IP address
  • ✅ Consider using private endpoint for secure connectivity
  • ✅ Increase Connection Timeout=60 if experiencing network latency
  • ✅ Store passwords in secure secret management (Azure Key Vault, etc.)

Common Azure SQL Issues

Symptom Cause Solution
"Cannot open server" Firewall blocking connection Add client IP to Azure SQL firewall rules
"Login failed for user" Invalid credentials Verify username/password, check SQL login exists
Connection timeout Network latency to Azure Increase Connection Timeout=60 or higher
"Server was not found" Incorrect server name Verify FQDN format: tcp:instance.database.windows.net,1433

Always On Availability Groups (AOAG)

Why Use AOAG?

Always On Availability Groups provide high availability and disaster recovery for SQL Server databases. When Nodinite connects to AOAG, the AG listener handles automatic failover—ensuring minimal downtime during SQL Server maintenance or failures.

Minimum AOAG Connection String 

Data Source=AG-Listener;Initial Catalog=DatabaseName;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;

This works, but reconnection after failover may take 10-20 seconds.

Data Source=AG-Listener;Initial Catalog=DatabaseName;Integrated Security=True;Encrypt=True;TrustServerCertificate=True;MultiSubnetFailover=True;

Optimized for fast failover (2-3 seconds vs 10-20 seconds).

Key AOAG Settings

Setting Required? Purpose Impact if Missing
Data Source (listener) ✅ Required Connect via AG listener, not individual nodes Connection fails or points to wrong replica after failover
MultiSubnetFailover=True ⚡ Recommended Parallel connection attempts for faster failover Slower reconnection (10-20s vs 2-3s)
ApplicationIntent Optional ReadWrite ensures writes go to primary Rare: may route to read-only secondary
Connect Timeout Optional Increase to 60s if slow failovers Connection may timeout during failover

When is MultiSubnetFailover=True Critical?

  • Multi-subnet deployments (different datacenters/availability zones) — Critical for acceptable failover times
  • Single-subnet deployments — Helpful but not required; reduces failover from ~15s to ~3s
  • Production workloads — Recommended; improves perceived reliability during maintenance

AOAG Deployment Checklist

  • ✅ Use the AG listener name as Data Source—never individual SQL Server names (Required)
  • ✅ Verify database is in the AG and synchronized (Required)
  • ⚡ Add MultiSubnetFailover=True for faster failover (Recommended for multi-subnet)
  • ⚡ Test failover: stop primary replica, verify automatic reconnection
  • ⚡ Increase Connect Timeout=60 if intermittent timeout errors during failover
  • ⚡ Configure AG listener with static IP addresses for predictable DNS resolution

Common AOAG Issues

Symptom Cause Solution
Slow reconnection (10-20 seconds) Missing MultiSubnetFailover=True Add MultiSubnetFailover=True
"Login failed for user" after failover SQL logins not synchronized Use Windows auth OR sync logins to all replicas
Timeout errors during failover Default timeout too short Increase Connect Timeout=60
Connects to read-only secondary Incorrect routing configuration Add ApplicationIntent=ReadWrite
Connection hangs (multi-subnet) Sequential IP attempts with timeouts Add MultiSubnetFailover=True (critical)

Tip

Always test AG failover in non-production first. Monitor reconnection behavior and verify components resume automatically after the new primary replica is online.

Troubleshooting Certificate Errors

"The certificate chain was issued by an authority that is not trusted"

Cause: SQL Server uses a self-signed certificate or certificate from untrusted CA

Solutions:

  1. Best practice (Production):

    • Export SQL Server certificate's root CA certificate
    • Install CA certificate in Trusted Root Certification Authorities on Nodinite host
    • Keep Encrypt=True;TrustServerCertificate=False in connection string

  2. Pragmatic (Dev/Test):

    • Add TrustServerCertificate=True to connection string
    • Keeps encryption but bypasses certificate validation

  3. Not recommended:

    • Set Encrypt=False in connection string
    • Disables TLS encryption entirely

"The target principal name is incorrect"

Cause: Certificate CN/SAN doesn't match the Data Source name

Solutions:

  • Use DNS name from certificate in Data Source setting
  • OR update SQL Server certificate with correct SAN entries
  • OR add TrustServerCertificate=True (bypasses name check)

TLS Handshake Failures

Cause: TLS protocol version mismatch

Solutions:

  • Enable TLS 1.2+ in Windows (via registry)
  • Enable TLS 1.2+ in SQL Server configuration
  • Update .NET Framework to support modern TLS

Connection Timeout During Failover (AOAG)

Cause: Default 30-second timeout too short for AG failover

Solution:

  • Add Connect Timeout=60 to connection string
  • Verify MultiSubnetFailover=True is present

Security Best Practices

✅ Do This

  • Use Encrypt=True everywhere — Encrypts all data in transit
  • Prefer TrustServerCertificate=False with proper CA trust — Validates certificate chain
  • Use Windows Authentication (Integrated Security=True) — No passwords in connection strings
  • For AOAG, use listener name and MultiSubnetFailover=True — Ensures fast failover
  • Use least-privilege SQL accounts — Grant only necessary permissions
  • Rotate SQL passwords regularly — If SQL auth is required
  • Enable SSL/TLS 1.2+ — Disable older protocols

❌ Avoid This

  • Encrypt=False in production — Sends data unencrypted over network
  • Embedding passwords in connection strings — Use Windows auth or secure secret storage
  • SQL Authentication without encryption — Passwords sent in cleartext if Encrypt=False
  • Self-signed certificates in production without documentation — Creates security debt
  • Individual SQL Server names with AOAG — Breaks automatic failover

Connection String Security Levels

Configuration Security Rating Production Ready?
Encrypt=True;TrustServerCertificate=False + trusted CA ✅✅✅✅ Excellent ✅ Yes
Encrypt=True;TrustServerCertificate=True + Windows auth ✅✅✅ Good ✅ Yes (pragmatic)
Encrypt=True;TrustServerCertificate=True + SQL auth ✅✅ Fair ⚠️ Use with caution
Encrypt=False + Windows auth ✅ Poor ❌ Dev/test only
Encrypt=False + SQL auth ❌ Very Poor ❌ Never use

External References