Microsoft OLE DB Driver for SQL Server connection strings

SQL Server 2019

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.

SQL Server 2017

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.

SQL Server 2016

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.

SQL Server 2014

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.

SQL Server 2012

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.

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;