- Odbc Drivers Missing
- Odbc Drivers For Windows 7
- Microsoft Sql Server Odbc Drivers
- Odbc Driver 17 For Sql Server Download
ODBC connector to SQL Server high-performance and feature-rich connectivity solution for ODBC-based applications to access SQL Server databases from Windows, Linux, both 32-bit and 64-bit. SNAC was introduced all the way back in SQL Server 2005. 'SQL Server Native Client combines the SQL OLE DB provider and the SQL ODBC driver into one native dynamic-link library (DLL).'
SQL Server ODBC driver as the connectivity for SQL Server 2000. Most likely, the ODBC driver is already installed on your workstation. If not, see details. SQL Native Client as the connectivity for SQL Server 2000 and 2005. We strongly recommend that you have SQL Native Client installed, if your application has to work with SQL Server 2005.
The ODBC drivers for Linux and macOS support AlwaysOn Availability Groups. For more information about AlwaysOn Availability Groups, see:
You can specify the availability group listener of a given availability group in the connection string. If an ODBC application on Linux or macOS is connected to a database in an availability group that fails over, the original connection is broken and the application must open a new connection to continue work after the failover.
The ODBC drivers on Linux and macOS iterate sequentially through all IP addresses associated with a DNS hostname if you are not connecting to an availability group listener, and multiple IP addresses are associated with the hostname.
If the DNS server's first returned IP address is not connectable, these iterations can be time consuming. When connecting to an availability group listener, the driver attempts to establish connections to all IP addresses in parallel. If a connection attempt succeeds, the driver discards any pending connection attempts.
Note
Because a connection can fail due to an availability group failover, implement connection retry logic; retry a failed connection until it reconnects. Increasing connection timeout and implementing connection retry logic increases the chance of connecting to an availability group.
Connecting With MultiSubnetFailover
Always specify MultiSubnetFailover=Yes when connecting to a SQL Server 2012 (11.x) availability group listener or SQL Server 2012 (11.x) Failover Cluster Instance. MultiSubnetFailover enables faster failover for all Availability Groups and failover cluster instance in SQL Server 2012 (11.x). MultiSubnetFailover also significantly reduces failover time for single and multi-subnet AlwaysOn topologies. During a multisubnet failover, the client attempts connections in parallel. During a subnet failover, the driver aggressively retries the TCP connection.
The MultiSubnetFailover connection property indicates that the application is being deployed in an availability group or Failover Cluster Instance. The driver tries to connect to the database on the primary SQL Server instance by trying to connect to all the IP addresses. When connecting with MultiSubnetFailover=Yes, the client retries TCP connection attempts faster than the operating system's default TCP retransmit intervals. MultiSubnetFailover=Yes enables faster reconnection after failover of either an AlwaysOn Availability Group or an AlwaysOn Failover Cluster Instance. MultiSubnetFailover=Yes applies to both single- and multi-subnet Availability Groups and Failover Cluster Instances.
Use MultiSubnetFailover=Yes when connecting to an availability group listener or Failover Cluster Instance. Otherwise, your application's performance can be negatively affected.
Note the following when connecting to a server in an availability group or Failover Cluster Instance:
Specify MultiSubnetFailover=Yes to improve performance when connecting to a single subnet or multi-subnet Availability Group.
Specify the availability group listener of the availability group as the server in your connection string.
You cannot connect to a SQL Server instance configured with more than 64 IP addresses.
Both SQL Server Authentication or Kerberos Authentication can be used with MultiSubnetFailover=Yes without affecting the behavior of the application.
You can increase the value of loginTimeout to accommodate for failover time and reduce the application's connection retry attempts.
Distributed transactions are not supported.
If read-only routing is not in effect, connecting to a secondary replica location in an availability group fails in the following situations:
If the secondary replica location is not configured to accept connections.
If an application uses ApplicationIntent=ReadWrite and the secondary replica location is configured for read-only access.
A connection fails if a primary replica is configured to reject read-only workloads and the connection string contains ApplicationIntent=ReadOnly.
Specifying Application Intent
The keyword ApplicationIntent can be specified in your connection string. The assignable values are ReadWrite or ReadOnly. The default is ReadWrite.
When ApplicationIntent=ReadOnly, the client requests a read workload when connecting. The server enforces the intent at connection time, and during a USE database statement.
The ApplicationIntent keyword does not work with legacy read-only databases.
Targets of ReadOnly
When a connection chooses ReadOnly, the connection is assigned to any of the following special configurations that might exist for the database:
- A database can allow or disallow read workloads on the targeted Always On database. This choice is controlled by using the ALLOW_CONNECTIONS clause of the PRIMARY_ROLE and SECONDARY_ROLE Transact-SQL statements.
If none of those special targets are available, the regular database is read from.
The ApplicationIntent keyword enables read-only routing.
Read-Only Routing
Read-only routing is a feature that can ensure the availability of a read-only replica of a database. To enable read-only routing, all of the following apply:
You must connect to an Always On Availability Group availability group listener.
The ApplicationIntent connection string keyword must be set to ReadOnly.
The Availability Group must be configured by the database administrator to enable read-only routing.
Multiple connections each using read-only routing might not all connect to the same read-only replica. Changes in database synchronization or changes in the server's routing configuration can result in client connections to different read-only replicas. You can ensure that all read-only requests connect to the same read-only replica. Ensure this sameness by not passing an availability group listener to the Server connection string keyword. Instead, specify the name of the read-only instance.
Read-only routing may take longer than connecting to the primary. The longer wait is because read-only routing first connects to the primary, and then looks for the best available readable secondary. Due to these multiple steps, you should increase your login timeout to at least 30 seconds.
ODBC Syntax
Two ODBC connection string keywords support AlwaysOn Availability Groups:
ApplicationIntent
MultiSubnetFailover
For more information about ODBC connection string keywords, see Using Connection String Keywords with SQL Server Native Client.
The equivalent connection attributes are:
SQL_COPT_SS_APPLICATION_INTENT
SQL_COPT_SS_MULTISUBNET_FAILOVER
For more information about ODBC connection attributes, see SQLSetConnectAttr.
An ODBC application that uses AlwaysOn Availability Groups can use one of two functions to make the connection:
| Function | Description |
|---|---|
| SQLConnect Function | SQLConnect supports both ApplicationIntent and MultiSubnetFailover via a data source name (DSN) or connection attribute. |
| SQLDriverConnect Function | SQLDriverConnect supports ApplicationIntent and MultiSubnetFailover via DSN, connection string keyword, or connection attribute. |
See Also
Go Up to Database Connectivity (FireDAC)
This topic describes how to connect to Microsoft SQL Server.
Supported Versions
The FireDAC native driver supports Microsoft SQL Server Standard and Express editions version 2000 and later, and Microsoft SQL Azure. For a description of how to connect to Microsoft SQL Server Compact Edition, see Connect to Microsoft SQL Server Compact Edition.
Windows Client Software
FireDAC requires one of the Microsoft SQL Server x86 or x64 ODBC drivers to be installed on the workstation:
- SQL Server ODBC driver as the connectivity for SQL Server 2000. Most likely, the ODBC driver is already installed on your workstation. If not, see details.
- SQL Native Client as the connectivity for SQL Server 2000 and 2005. We strongly recommend that you have SQL Native Client installed, if your application has to work with SQL Server 2005. See Microsoft SQL Server Native Client.
- SQL Server Native Client NN.N as the connectivity for SQL Server 2000, 2005, 2008, 2012, and SQL Azure. We strongly recommend that you have SQL Server Native Client NN.N installed, if your application has to work with SQL Server 2008, 2012, or SQL Azure. See Microsoft SQL Server 2008 Native Client.
- SQL Server Native Client 11.0 as the connectivity for LocalDB.
Note: SQL Server Native Client 10.0 (SQL Server 2008) may fail to call a stored procedure, when it is connected to SQL Server 2000. The symptom of this issue is the error message 'Incorrect Syntax near to {'. In this case, use the ODBC driver from the SQL Server 2000 or 2005 distribution.
If the SQL Server ODBC driver has not been installed properly, an exception is raised when you try to connect:
Mac OS X Client Software
FireDAC requires:
- the UnixODBC (libodbc.dylib) x86 ODBC driver manager library.
- the FreeTDS (libtdsodbc.so) x86 ODBC driver.
FreeTDS can be transferred from CVS into a folder in your home directory (more 1) (more 2). To install FreeTDS on Mac OS X, use the commands:
Note that you may also need to install the gawk utility. At this point, you can configure a ODBC DSN or test the FireDAC connection.
Note that the FreeTDS ODBC driver is not that efficient and stable as the original Microsoft ODBC driver.
Driver Linkage
To link the driver:
- drop a TFDPhysMSSQLDriverLink component from the 'FireDAC Links' palette page
- or include the FireDAC.Phys.MSSQL unit in the uses clause.
Connection Definition Parameters
To connect to the Microsoft SQL Server DBMS, most applications require you to specify DriverID, Server, Database, OSAuthent, User_Name, and Password.
DriverID=MSSQL
| Parameter | Description | Example value |
|---|---|---|
| Server | Name of a server running SQL Server on the network. The value must be either the name of a server on the network or the name of a SQL Server Client Network Utility advanced server entry. When you connect to the SQL Azure, you have to prepend the server name with the 'tcp:' prefix.
|
|
| Port | Only for Mac OS X. Specifies the port where the SQL Server is listening. For the Windows platform the port may be specified after a comma in the Server parameter value. For example, Server=host, port. The default port is 1433. | |
| Database | Name of the default database for the connection. If the Database is not specified, the default database defined for the login is used. | Northwind |
| OSAuthent | Controls the authentication mode:
| No |
| User_Name | The SQL Server login name, if OSAuthent=No. When you connect to the SQL Azure, you have to append the '@<server>' suffix to your user name. |
|
| Password | The SQL Server login password, if OSAuthent=No. Note that the passwords with both '{' and '}' are not supported. | |
| Network | Name of a network library dynamic-link library. The name does not need to include the path and must not include the .dll file name extension. | dbnmpntw |
| Address | Network address of the server running an instance of SQL Server. Address is usually the network name of the server, but can be other names, such as a pipe, or a TCP/IP port and socket address. | |
| MARS | Controls the MARS - multiple active result set support in a connection:
MARS is a feature supported by SQL 2005 and later. It is not supported by SQL Azure. The enabled MARS may lead to fetch performance degradation. For more details, read the following: | No |
| Workstation | Workstation ID. Typically, this is the network name of the computer on which the application resides (optional). If specified, this value is stored in the master.dbo.sysprocesses column hostname and is returned by sp_who and the Transact-SQL HOST_NAME function. | Bookkeeper1 |
| Language | SQL Server language name (optional). When connecting to a SQL Server with multiple languages, Language specifies which set of messages are used for the connection. | |
| Encrypt | Controls the network traffic encryption:
| Yes |
| LoginTimeout | Controls the amount of time, in seconds, before an application times out while attempting to establish a connection. 0 specifies an infinite wait (default value). | 30 |
| VariantFormat | Controls the SQL_VARIANT data type representation:
| Binary |
| ExtendedMetadata | Controls the extended description of the query result sets:
| True |
| ApplicationName | Name of the application. If specified, this value is stored in the master.dbo.sysprocesses column program_name and is returned by sp_who and the Transact-SQL APP_NAME function. | AllBooks |
| ODBCAdvanced | Allows you to specify any other additional ODBC connection parameter value. | MARS_Connection=no;Regional=yes |
| MetaDefCatalog | Default database name. The Design time code excludes the catalog name from the object name if it is equal to MetaDefCatalog. | Northwind |
| MetaDefSchema | Default schema name. The Design time code excludes the schema name from the object name if it is equal to MetaDefSchema. | dbo |
| MetaCaseIns | Controls the metadata case-sensitivity:
| True |
Usage Cases
- Connect to the local SQL Server instance using SQL Server authentication:
Odbc Drivers Missing
- Connect to SQL Express 2005 using Windows authentication:
- Connect to SQL Azure. Note the '@<server>' suffix in User_Name and the 'tcp:' prefix in the Server parameters:
- Connect to LocalDB:
Odbc Drivers For Windows 7
- Connect to LocalDB and attach a database file:
Microsoft Sql Server Odbc Drivers
See Also
Odbc Driver 17 For Sql Server Download