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;

Equivalent key-value pair: "Integrated Security=SSPI" equals "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;

The default SQL Server port is 1433 and there is no need to specify that in the connection string.

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;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

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;

There is ofcourse many other ways to write the connection string using database mirroring, this is just one example pointing out the failover functionality. You can combine this with the other connection strings options available.

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;

MultiSubnetFailover will perform retries in parallell and do it faster than default TCP retransmit intervals. This can not be combined with mirroring, e.g. Failover_Partner=mirrorServer.

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;

The result of using ApplicationIntent depends on database configuration. See read-only routing. The default for ApplicationIntent is ReadWrite.

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;

An availability group must enable read-only routing for this to work.

SQL Server 2017

Standard security

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

ADO to map new data types

Provider=MSOLEDBSQL;DataTypeCompatibility=80;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

Trusted connection

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Equivalent key-value pair: "Integrated Security=SSPI" equals "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;

The default SQL Server port is 1433 and there is no need to specify that in the connection string.

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;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Database mirroring

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;

MultiSubnetFailover will perform retries in parallell and do it faster than default TCP retransmit intervals. This can not be combined with mirroring, e.g. Failover_Partner=mirrorServer.

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;

The result of using ApplicationIntent depends on database configuration. See read-only routing. The default for ApplicationIntent is ReadWrite.

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;

An availability group must enable read-only routing for this to work.

SQL Server 2016

Standard security

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

ADO to map new data types

Provider=MSOLEDBSQL;DataTypeCompatibility=80;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

Trusted connection

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Equivalent key-value pair: "Integrated Security=SSPI" equals "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;

The default SQL Server port is 1433 and there is no need to specify that in the connection string.

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;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Database mirroring

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;

MultiSubnetFailover will perform retries in parallell and do it faster than default TCP retransmit intervals. This can not be combined with mirroring, e.g. Failover_Partner=mirrorServer.

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;

The result of using ApplicationIntent depends on database configuration. See read-only routing. The default for ApplicationIntent is ReadWrite.

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;

An availability group must enable read-only routing for this to work.

SQL Server 2014

Standard security

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

ADO to map new data types

Provider=MSOLEDBSQL;DataTypeCompatibility=80;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

Trusted connection

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Equivalent key-value pair: "Integrated Security=SSPI" equals "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;

The default SQL Server port is 1433 and there is no need to specify that in the connection string.

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;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Database mirroring

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;

MultiSubnetFailover will perform retries in parallell and do it faster than default TCP retransmit intervals. This can not be combined with mirroring, e.g. Failover_Partner=mirrorServer.

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;

The result of using ApplicationIntent depends on database configuration. See read-only routing. The default for ApplicationIntent is ReadWrite.

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;

An availability group must enable read-only routing for this to work.

SQL Server 2012

Standard security

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

ADO to map new data types

Provider=MSOLEDBSQL;DataTypeCompatibility=80;Server=myServerAddress;Database=myDataBase;UID=myUsername;PWD=myPassword;

Trusted connection

Provider=MSOLEDBSQL;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Equivalent key-value pair: "Integrated Security=SSPI" equals "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;

The default SQL Server port is 1433 and there is no need to specify that in the connection string.

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;

Why is the Database parameter needed? If the named database have already been attached, SQL Server does not reattach it. It uses the attached database as the default for the connection.

Database mirroring

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;

MultiSubnetFailover will perform retries in parallell and do it faster than default TCP retransmit intervals. This can not be combined with mirroring, e.g. Failover_Partner=mirrorServer.

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;

The result of using ApplicationIntent depends on database configuration. See read-only routing. The default for ApplicationIntent is ReadWrite.

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;

An availability group must enable read-only routing for this to work.

Azure SQL Database

SQL authentication

Provider=MSOLEDBSQL;Data Source=myServer;Initial Catalog=myDatabase;Authentication=SqlPassword;User ID=myUserName;Password=myPassword;Use Encryption for Data=true;

Integrated Windows authentication using SSPI

Provider=MSOLEDBSQL;Data Source=myServer;Initial Catalog=myDatabase;Authentication=ActiveDirectoryIntegrated;Use Encryption for Data=true;

Azure Active Directory username and password authentication

Provider=MSOLEDBSQL;Data Source=myServer;Initial Catalog=myDatabase;Authentication=ActiveDirectoryPassword;User ID=myUserName;Password=myPassword;Use Encryption for Data=true;

Azure Active Directory integrated authentication

Provider=MSOLEDBSQL;Data Source=myServer;Initial Catalog=myDatabase;Authentication=ActiveDirectoryIntegrated;Use Encryption for Data=true;

Azure Active Directory authentication with access token

Provider=MSOLEDBSQL;Data Source=myServer;Initial Catalog=myDatabase;Access Token=myAccessToken;Use Encryption for Data=true;

Azure Active Directory interactive authentication

Provider=MSOLEDBSQL;Data Source=myServer;Initial Catalog=myDatabase;Authentication=ActiveDirectoryInteractive;User ID=myUserName;Use Encryption for Data=true;

Azure Active Directory MSI authentication

Provider=MSOLEDBSQL;Data Source=myServer;Initial Catalog=myDatabase;Authentication=ActiveDirectoryMSI;User ID=myObjectId;Use Encryption for Data=true;