SQL Server 2019
Standard security
Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;
ADO to map new data types
For ADO to correctly map SQL Server new datatypes, i.e. XML, UDT, varchar(max), nvarchar(max), and varbinary(max), include DataTypeCompatibility=80; in the connection string. If you are not using ADO this is not necessary.
Provider=MSOLEDBSQL;DataTypeCompatibility=80;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;
Trusted connection
Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;
Connecting to an SQL Server instance
The syntax of specifying the server instance in the value of the server key is the same for all connection strings for SQL Server.
Provider=MSOLEDBSQL;Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=yes;
Using a non-standard port
If your SQL Server listens on a non-default port you can specify that using the servername,xxxx syntax (note the comma, it's not a colon).
Provider=MSOLEDBSQL;Server=myServerName,myPortNumber;Database=myDataBase;UID=myUsername;PWD=myPassword;
Enable MARS
Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;MARS Connection=true;
Encrypt data sent over network
Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;Encrypt=yes;
Attach a database file on connect to a local SQL Server Express instance
Provider=MSOLEDBSQL;Server=.\SQLExpress;AttachDBFilename=c:\asd\qwe\mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;
Database mirroring
If you connect with ADO.NET or the SQL Native Client to a database that is being mirrored, your application can take advantage of the drivers ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string and the failover partner server.
Provider=MSOLEDBSQL;Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;
Availability group and failover cluster
Enable fast failover for Always On Availability Groups and Failover Cluster Instances. TCP is the only supported protocol. Also set an explicit timeout as these scenarios might require more time.
Provider=MSOLEDBSQL;Server=tcp:AvailabilityGroupListenerDnsName,1433;MultiSubnetFailover=Yes;Database=MyDB;Integrated Security=SSPI;Connect Timeout=30;
Read-Only application intent
Use a read workload when connecting. Enforces read only at connection time, and also for USE database statements.
Provider=MSOLEDBSQL;Server=tcp:AvailabilityGroupListenerDnsName,1433;MultiSubnetFailover=Yes;ApplicationIntent=ReadOnly;Database=MyDB;Integrated Security=SSPI;Connect Timeout=30;
Read-Only routing
You can either use an availability group listener for Server OR the read-only instance name to enforce a specific read-only instance.
Provider=MSOLEDBSQL;Server=aKnownReadOnlyInstance;MultiSubnetFailover=Yes;ApplicationIntent=ReadOnly;Database=MyDB;Integrated Security=SSPI;Connect Timeout=30;