How .NET reads a connection string
A connection string is Key=Value pairs separated by semicolons. Keys are case-insensitive, most have synonyms (Server, Data Source and Address are the same key in SqlClient), and if a key appears twice the last one wins. A value that contains a semicolon, a quote, an equals sign or leading or trailing spaces must be quoted with " or '; a quote of the same kind inside is doubled.
Each driver then applies its own rules: which keys exist, which values they accept and how it writes the string back out. This page uses the exact rules of SqlClient, Npgsql, MySqlConnector and Microsoft.Data.Sqlite, taken from those libraries and checked against them on tens of thousands of connection strings.
The mistakes that cost an afternoon
- A semicolon in the password.
Password=p@ss;wordis read asPassword=p@ssfollowed by a key calledword;Encrypt, and the error says "Keyword not supported: 'word;encrypt'". Quote the value. Port=with SQL Server. SqlClient has no Port keyword:Server=myhost,1433.- The certificate error after upgrading SqlClient. Encrypt has defaulted to True since Microsoft.Data.SqlClient 4.0. A local or container SQL Server with a self-signed certificate then fails.
TrustServerCertificate=Truefixes it for development; do not ship it. - Require vs Required. PostgreSQL's Npgsql says
SSL Mode=Require, MySqlConnector saysSSL Mode=Required. Trust Server Certificatewith Npgsql. It no longer does anything;SSL Mode=Requirealready skips certificate validation.- yes and no. SqlClient and MySqlConnector accept
yes/nofor true/false; Npgsql and Microsoft.Data.Sqlite only accept true/false. - MySQL 8 and SSL Mode=None. Logins using caching_sha2_password then need
AllowPublicKeyRetrieval=True. - A relative SQLite path. It is resolved against the process's working directory, which is not always the project folder.
Where the connection string should live
Put it under ConnectionStrings in appsettings.json and read it with builder.Configuration.GetConnectionString("Default"). Keep passwords out of the file: use user secrets in development, and an environment variable (ConnectionStrings__Default) or a secret store in production. The output tabs above give you each form.
One page per database
Examples, common errors and the builder for one database:
- PostgreSQL connection string (Npgsql)
- SQL Server connection string
- MongoDB connection string
- MySQL connection string
- SQLite connection string
- Redis connection string (StackExchange.Redis)
FAQ
How do I specify a port in a SQL Server connection string?
SqlClient has no Port keyword. Put the port after the server name, separated by a comma: Server=myhost,1433. Adding Port=1433 fails with Keyword not supported: 'port'.
Why does my local SQL Server connection fail with a certificate error?
Since Microsoft.Data.SqlClient 4.0, Encrypt defaults to True, so the client validates the server certificate. A local or container SQL Server usually has a self-signed certificate. Add TrustServerCertificate=True for local development only, or install a trusted certificate.
How do I put a semicolon in a connection string password?
Wrap the value in double or single quotes: Password="p@ss;word". To include a double quote inside double quotes, double it. Without quotes, the text after the semicolon is read as the next key, which gives a confusing Keyword not supported error.
Is it SSL Mode=Require or Required?
It depends on the driver. Npgsql (PostgreSQL) uses Require; MySqlConnector (MySQL) uses Required. Each rejects the other's spelling.
Where does a relative SQLite Data Source point?
To the current working directory of the process, not the project folder. It can differ between dotnet run, the IDE and a published app, so many apps build an absolute path from AppContext.BaseDirectory.