Skip to content

ConnectionString

Jump to bottom
Simon Hughes edited this page Aug 30, 2026 · 5 revisions

Connection strings

Settings.ConnectionString is the one setting you cannot leave alone. Get it right and everything else follows.

Two connection strings, two different jobs

New users trip over this constantly, so it is worth being explicit. There are two settings with similar names, used at completely different times.

Setting Used by When Must be
Settings.ConnectionString The generator Design time, when you save Database.tt A full, working connection string
Settings.ConnectionStringName Your application Run time A key that exists in appsettings.json / app.config / web.config

The generator never reads connection strings out of your config files. You have to hand it the whole thing.

// Used to reverse engineer your database when you save this file
Settings.ConnectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True;Encrypt=false;TrustServerCertificate=true";

// Just a key. Placed into the generated DbContext constructor. Never used by the generator.
Settings.ConnectionStringName = "MyDbContext";

Depending on Settings.OnConfiguration, your design-time connection string may also be copied into the generated DbContext.OnConfiguring(). If you would rather it did not end up in source control, use OnConfiguration.Omit or OnConfiguration.Configuration and configure the provider yourself. That is the usual choice for anything deployed.

SQL Server

Settings.DatabaseType     = DatabaseType.SqlServer;
Settings.TemplateType     = TemplateType.EfCore10;
Settings.GeneratorType    = GeneratorType.EfCore;
Settings.ConnectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True;MultipleActiveResultSets=True;Encrypt=false;TrustServerCertificate=true";

Other shapes you will meet:

// Named instance, Windows authentication
"Data Source=.\\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True;Encrypt=false;TrustServerCertificate=true"

// LocalDB, the one Visual Studio installs
"Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Northwind;Integrated Security=True"

// SQL authentication
"Data Source=sql01.example.com,1433;Initial Catalog=Northwind;User ID=readonly_user;Password=***;Encrypt=True"

// Azure SQL Database
"Server=tcp:myserver.database.windows.net,1433;Initial Catalog=Northwind;User ID=readonly_user;Password=***;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30"

// Azure SQL with Entra ID (formerly Azure AD)
"Server=tcp:myserver.database.windows.net,1433;Initial Catalog=Northwind;Authentication=Active Directory Default;Encrypt=True"

Why Encrypt=false;TrustServerCertificate=true? Microsoft.Data.SqlClient encrypts by default from version 4.0 onwards. A local development SQL Server usually has a self-signed certificate your machine does not trust, and without one of those two settings you get:

A connection was successfully established with the server, but then an error occurred during the login process.

Do not carry TrustServerCertificate=true into production. There, install a real certificate and use Encrypt=True on its own.

MultipleActiveResultSets=True is only needed if you turn on lazy loading.

More detail, including what the generator can read: SQL Server

PostgreSQL

Settings.DatabaseType     = DatabaseType.PostgreSQL;
Settings.TemplateType     = TemplateType.EfCore10;
Settings.GeneratorType    = GeneratorType.EfCore;
Settings.ConnectionString = "Server=127.0.0.1;Port=5432;Database=Northwind;User Id=testuser;Password=***;";

Other shapes:

// Host= is a synonym for Server=, Username= for User Id=. Npgsql accepts both.
"Host=localhost;Port=5432;Database=Northwind;Username=testuser;Password=***"

// TLS to a managed instance (AWS RDS, Azure Database for PostgreSQL, Supabase, Neon, ...)
"Host=db.example.com;Port=5432;Database=Northwind;Username=readonly_user;Password=***;SSL Mode=Require;Trust Server Certificate=true"

// Through pgbouncer, where server-side pooling is already doing the job
"Host=db.example.com;Port=6432;Database=Northwind;Username=readonly_user;Password=***;Pooling=false"

The default schema is public, and every schema your user can see is read in one pass.

Watch the case. PostgreSQL folds unquoted identifiers to lower case, so CREATE TABLE MyTable really creates mytable. That matters for Filtering, because filters match the database name, not the generated class name.

More detail: PostgreSQL

MySQL and MariaDB

Settings.DatabaseType     = DatabaseType.MySql;
Settings.TemplateType     = TemplateType.EfCore10;
Settings.GeneratorType    = GeneratorType.EfCore;
Settings.OnConfiguration  = OnConfiguration.Omit;   // see below
Settings.ConnectionString = "Server=localhost;Port=3306;Database=Northwind;User Id=root;Password=***;";

Other shapes:

// Uid/Pwd are accepted as synonyms for User Id/Password
"Server=localhost;Port=3306;Database=Northwind;Uid=readonly_user;Pwd=***;"

// TLS to a managed instance (AWS RDS, Azure Database for MySQL, PlanetScale, ...)
"Server=db.example.com;Port=3306;Database=Northwind;User Id=readonly_user;Password=***;SslMode=Required;"

// Unix socket
"Server=/var/run/mysqld/mysqld.sock;Database=Northwind;User Id=readonly_user;Password=***;"

Database= is not optional. In MySQL a database is a schema. Whatever you name here is the only schema the generator reads, and tables in other databases on the same server are invisible to it, including through a foreign key that points at them.

Set OnConfiguration.Omit for MySQL. The EF Core provider is Pomelo, whose UseMySql() takes a mandatory ServerVersion argument that the generator does not emit. Configure it where you register the context:

var cs = builder.Configuration.GetConnectionString("MyDbContext");
builder.Services.AddDbContext<MyDbContext>(o => o.UseMySql(cs, ServerVersion.AutoDetect(cs)));

More detail: MySQL

Oracle

Settings.DatabaseType     = DatabaseType.Oracle;
Settings.TemplateType     = TemplateType.EfCore10;
Settings.GeneratorType    = GeneratorType.EfCore;
Settings.ConnectionString = "User Id=northwind;Password=***;Data Source=localhost:1521/pdb1;";

Other shapes:

// Easy Connect: host:port/service_name. Simplest, no tnsnames.ora needed.
"User Id=northwind;Password=***;Data Source=db.example.com:1521/ORCLPDB1;"

// TNS alias, resolved from tnsnames.ora in the folder named by the TNS_ADMIN environment variable
"User Id=northwind;Password=***;Data Source=MYALIAS;"

// Full descriptor inline
"User Id=northwind;Password=***;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLPDB1)));"

// Autonomous Database with a wallet. Point TNS_ADMIN at the unzipped wallet folder first.
"User Id=northwind;Password=***;Data Source=mydb_high;"

In Oracle the user is the schema. There is no Database= or Schema= part. The generator reads the current schema of whoever you connect as, and only that one schema, so connect as the owner of the schema you want to reverse engineer. Connecting as SYSTEM and expecting to see NORTHWIND will not work.

Oracle folds unquoted identifiers to upper case, so your filters need to match ORDER_LINE_ITEM, not Order_Line_Item.

More detail: Oracle

SQLite

Settings.DatabaseType     = DatabaseType.SQLite;
Settings.TemplateType     = TemplateType.EfCore10;
Settings.GeneratorType    = GeneratorType.EfCore;
Settings.ConnectionString = @"Data Source=C:\data\Northwind.db";

Other shapes:

// Relative to your .tt file. Settings.Root is the folder containing it.
"Data Source=" + Path.Combine(Settings.Root, "Northwind.db")

// Read-only. A sensible default for a generator.
@"Data Source=C:\data\Northwind.db;Mode=ReadOnly"

// Encrypted with SQLCipher or SQLite SEE
@"Data Source=C:\data\Northwind.db;Password=***"

Use an absolute path, or build one from Settings.Root. A relative Data Source resolves against the process working directory, which is not where you think it is when the generator runs. SQLite then cheerfully creates a new, empty database there, and you get no tables and no error. If your generated file comes out empty, check the path first.

More detail: SQLite

What permissions does the generator need?

It only ever reads, with one exception noted below. Give it the least it needs.

Database Minimum
SQL Server db_datareader plus VIEW DEFINITION on the database. db_ddladmin also works, and is the blunt instrument if you are stuck
PostgreSQL CONNECT on the database and USAGE on the schemas you want. The catalogue is readable by default
MySQL SELECT on the database, which is what filters information_schema for you
Oracle CREATE SESSION, plus visibility of the objects. Connecting as the schema owner is simplest
SQLite Read access to the file

The exception. On SQL Server, stored procedure return models are discovered by executing each procedure with SET FMTONLY ON, which returns column metadata without running the body. That needs EXECUTE permission on the procedures. If you cannot get it, turn the whole thing off:

FilterSettings.IncludeStoredProcedures      = false;
FilterSettings.IncludeTableValuedFunctions  = false;
FilterSettings.IncludeScalarValuedFunctions = false;

That is also the single biggest speed-up available on a large database.

Keeping the connection string out of source control

Database.tt is a source file. Anything you type into it gets committed. Three ways round that, in increasing order of effort.

1. An environment variable

Click the Start button, type env, press Enter, and click Environment Variables at the bottom right of the dialog. Add a User variable, for example EFRPG_NORTHWIND.

image

Notice that you don't add quotes around the connection string in the environment variable.

And to pick that up in the <database>.tt file, use:

Settings.ConnectionString = Environment.GetEnvironmentVariable("EFRPG_NORTHWIND", EnvironmentVariableTarget.User);

Restart Visual Studio after adding or changing it. Visual Studio caches the environment it was started with, so a new variable is invisible to a session that was already running.

2. A file outside the repository

Settings.ConnectionString = File.ReadAllText(
    Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.UserProfile), ".efrpg", "northwind.txt")).Trim();

3. Read it from your own appsettings.Development.json

Database.tt is ordinary C#, so it can parse the file itself. There is no JSON library available inside T4, so a small regex is the pragmatic answer:

var appsettings = File.ReadAllText(Path.Combine(Settings.Root, "..", "appsettings.Development.json"));
Settings.ConnectionString = System.Text.RegularExpressions.Regex
    .Match(appsettings, "\"MyDbContext\"\\s*:\\s*\"([^\"]+)\"").Groups[1].Value;

Whichever you choose, also set Settings.OnConfiguration = OnConfiguration.Omit; so the string is not copied into the generated .cs either.

How v4 handles it internally

Worth knowing, because it is a real improvement over v3. The template passes your connection string to the efrpg tool over stdin, never as a command-line argument. Command-line arguments are captured by process listings and, more to the point, by command-line audit logging (Sysmon event 1, EDR telemetry, ETW) which forwards them to a SIEM and to everybody who can read it. Passing it over stdin stops a password in your .tt file from spreading into your organisation's security logging.

--connection and --connection-base64 still exist on the tool for interactive and CI use, and are documented as visible in process listings. Base64 is transport encoding to survive shell quoting, not protection.

Testing a connection string quickly

The fastest way to tell "my connection string is wrong" from "my settings are wrong" is to bypass the template entirely and run the tool by hand:

efrpg --database SqlServer --connection "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True;Encrypt=false;TrustServerCertificate=true"

A wall of XML means your connection string and permissions are fine, and the problem is in Database.tt. An error on stderr means you have found it in one step.

For anything with a real password, prefer stdin:

'<Secrets><Connection>Host=localhost;Database=Northwind;Username=testuser;Password=***</Connection></Secrets>' |
    efrpg -d PostgreSQL --secrets-stdin > schema.xml

See Upgrading from v3 to v4 for the full tool command line.

See also

Start here

Databases

Settings

Modelling

Customising

Bigger jobs

Licence and support

Clone this wiki locally