Part III - Best Practices for Handling Connection Strings in C#

Connection strings are a critical part of every database-driven application — they define how your application connects to a database. However, mismanaging connection strings can lead to security vulnerabilities, configuration issues, and deployment risks.

Previous parts

Part I: 🔐 Advanced Authentication Types For SQL Server and Other Database Systems
Part II: In-Depth Look at Advanced Authentication and Connection Strings for SQL Server and Databases (Part II)

This section highlights the best practices for securely managing connection strings in .NET (C#) applications.

1. Never Hard-Code Connection Strings

❌ Bad Practice

var conn = new SqlConnection("Server=localhost;Database=ShopDB;User Id=admin;Password=12345;");

✅ Good Practice

Store the connection string in a configuration file such as appsettings.json or web.config:

{"ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=ShopDB;Integrated Security=True;"}}

Then access it via:

var connString = Configuration.GetConnectionString("DefaultConnection");
var conn = new SqlConnection(connString);

📘 Why: Keeps credentials out of source code and supports environment-based configurations.

2. Use Windows Authentication Whenever Possible

If your application runs under a trusted Windows domain, prefer Windows Authentication:

"Server=MyServer;Database=SalesDB;Integrated Security=True;"

✅ No credentials stored, and authentication is managed via Active Directory.

3. Use Azure Key Vault or User Secrets

For cloud-based or production apps, sensitive values like passwords or tokens should never be stored in plain text.

Option A: .NET User Secrets (for local dev)

dotnet user-secrets set "ConnectionStrings:DefaultConnection" "Server=prod;Database=ShopDB;User Id=sa;Password=StrongPass@123;"

Option B: Azure Key Vault (for cloud)

Store secrets centrally and load them using the Azure.Identity and Azure.Security.KeyVault.Secrets packages.

🔒 Why: Eliminates risk of credential leaks in repositories or logs.

4. Use Environment Variables in CI/CD

During deployment, inject connection strings through environment variables:

setx ConnectionStrings__DefaultConnection "Server=ProdServer;Database=MainDB;Integrated Security=True;"

In Program.cs:

builder.Configuration.AddEnvironmentVariables();

🧩 Why: Keeps secrets separate from application artifacts.

5. Validate Connection Strings

Before use, validate the structure and parameters:

var builder = new SqlConnectionStringBuilder(connString);
if (string.IsNullOrEmpty(builder.InitialCatalog))
    throw new InvalidOperationException("Database name is missing.");

⚙️ Ensures configuration issues are caught early.

6. Avoid Excessive Privileges

Use the least privilege principle

Create a SQL user with only the required permissions for your app (e.g., read/write to specific tables, not full admin).

🚫 Never use sa or admin accounts for application-level connections.

7. Encrypt Connection Strings in Configuration

For on-premises apps using web.config, encrypt the <connectionStrings> section:

aspnet_regiis -pef "connectionStrings" "C:\MyApp"

🔐 Prevents unauthorized reading of sensitive data on disk.
Reference: Encrypt Web Config Sections Using aspnet_regiis.exe Securely

8. Enable Encryption for SQL Server Connections

Force encryption between client and server:

"Server=myserver;Database=AppDB;User Id=AppUser;Password=StrongPass@123;Encrypt=True;TrustServerCertificate=False;"

✅ Helps protect credentials during transmission.

Summary

Practice	Description	Benefit
Avoid Hard-Coding	Store strings in config files	Secure, maintainable
Use Windows Auth	Leverage domain credentials	Password-less auth
Use Key Vault	Centralized secret management	Cloud-grade security
Environment Variables	Inject at deploy time	CI/CD friendly
Validate Connections	Check structure early	Reduces runtime errors
Least Privilege	Limit DB access	Minimize attack surface
Encrypt Config	Secure at-rest data	Prevent config theft
Use TLS/Encryption	Protect in transit	Data confidentiality

SQL Databases

SQL Server

Windows Authentication

string connectionString = 
    "Server=MY-SERVER-NAME;Database=MyDatabase;Trusted_Connection=True;";

SQL Server Authentication

string connectionString = 
    "Server=MY-SERVER-NAME;Database=MyDatabase;User Id=myUser;Password=myPassword;";

With Encrypt and Trust Settings

string connectionString =
    "Server=sqlserver.example.com,1433;Database=MyDatabase;User Id=myUser;Password=myPassword;Encrypt=True;TrustServerCertificate=False;";

Azure SQL Database

string connectionString =
    "Server=tcp:myserver.database.windows.net,1433;Initial Catalog=MyDatabase;Persist Security Info=False;User ID=myUser;Password=myPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";

MySQL

string connectionString =
    "Server=myserver;Database=mydb;User=myuser;Password=mypassword;";

With SSL

string connectionString =
    "Server=myserver;Database=mydb;User=myuser;Password=mypassword;SslMode=Required;";

PostgreSQL

string connectionString =
    "Host=myserver;Port=5432;Database=mydb;Username=myuser;Password=mypassword;";

With SSL Mode

string connectionString =
    "Host=myserver;Database=mydb;Username=myuser;Password=mypassword;Ssl Mode=Require;";

Oracle

string connectionString =
    "User Id=myuser;Password=mypassword;Data Source=MyOracleDB;";

Using TNS

string connectionString =
    "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=myserver)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=myservice)));User Id=myuser;Password=mypassword;";

SQLite

string connectionString = "Data Source=mydatabase.db;Version=3;";

With Password

string connectionString = "Data Source=mydatabase.db;Version=3;Password=mypassword;";

NoSQL Databases

MongoDB

string connectionString = 
    "mongodb://myUser:myPassword@localhost:27017/MyDatabase";

MongoDB Atlas (Cloud)

string connectionString =
    "mongodb+srv://myUser:[email protected]/MyDatabase?retryWrites=true&w=majority";

Redis

string connectionString = "localhost:6379";

With Password and SSL

string connectionString = "myserver.redis.cache.windows.net:6380,password=MyPassword,ssl=True,abortConnect=False";

Cassandra

string connectionString =
    "Contact Points=myserver;Port=9042;Default Keyspace=mykeyspace;Username=myuser;Password=mypassword;";

Azure Cosmos DB (SQL API)

string connectionString =
    "AccountEndpoint=https://mycosmosaccount.documents.azure.com:443/;AccountKey=myPrimaryKey;";

Amazon DynamoDB

var config = new AmazonDynamoDBConfig
{
    RegionEndpoint = RegionEndpoint.USEast1
};
var client = new AmazonDynamoDBClient("accessKey", "secretKey", config);

Tips

Never hardcode credentials — store in:
- appsettings.json
- Environment variables
- Azure Key Vault or AWS Secrets Manager

Use IConfiguration for retrieval:

var connectionString = configuration.GetConnectionString("DefaultConnection");

Always enable encryption (Encrypt=True;TrustServerCertificate=False)
Use managed identities for cloud connections instead of credentials.