Connect to Microsoft SQL Server (FireDAC)
Go Up to Database Connectivity (FireDAC)
This topic describes how to connect to Microsoft SQL Server.
Contents
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.
Client Software
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.
- ODBC Driver 13-18 as the connectivity for SQL Server 2008, 2008 R2, 2012, 2014, 2016, 2017, and 2019 and SQL Azure. We recommend using ODBC Driver 18.
If the SQL Server ODBC driver has not been installed properly, an exception is raised when you try to connect:
[FireDAC][Phys][ODBC][Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
macOS Client Software
FireDAC requires:
- The UnixODBC (libodbc.dylib) x86 ODBC driver manager library.
- The FreeTDS (libtdsodbc.dylib) x86 ODBC driver.
FreeTDS can be transferred from CVS into a folder in your home directory (more 1) (more 2). To install FreeTDS on macOS, use the commands:
cvs -z3 -d:pserver:[email protected]:/cvsroot/freetds checkout -P freetdsc cd freetds ./autogen.sh ./configure --with-tdsver=8.0 --with-unixodbc=/usr/local make sudo make install echo [FreeTDS] > tds.driver.template echo Description=v0.82 with protocol v8.0 >> tds.driver.template echo Driver=/usr/local/lib/libtdsodbc.so >> tds.driver.template odbcinst -i -d -f tds.driver.template cd ..
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.
Linux Client Software
FireDAC requires the unixODBC 64-bit ODBC driver manager library and the Microsoft ODBC Driver 11 for SQL Server on Linux.
To install the Microsoft ODBC Driver 11 for SQL Server on Linux:
-
On Ubuntu Server 16.04 LTS, run the following commands:
sudo apt-get install openssl libkrb5-3 libc6 e2fsprogs sudo ln -s /lib/x86_64-linux-gnu/libcrypto.so.1.0.0 /usr/lib/x86_64-linux-gnu/libcrypto.so.10 sudo ln -s /lib/x86_64-linux-gnu/libssl.so.1.0.0 /usr/lib/x86_64-linux-gnu/libssl.so.10 wget http://download.microsoft.com/download/B/C/D/BCDD264C-7517-4B7D-8159-C99FC5535680/RedHat6/msodbcsql-11.0.2270.0.tar.gz tar -xavf msodbcsql-11.0.2270.0.tar.gz sudo bash msodbcsql-11.0.2270.0/install.sh install --accept-license --force
- On Red Hat Enterprise Linux 7, download the corresponding installer and follow the official installation instructions.
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 that you specify the DriverID, Server, Database, OSAuthent, User_Name, and Password parameters (see Defining Connection (FireDAC) for details).
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. Note: The alternative TPC/IP port may be specified after a server name, separated by the comma.
Note: To connect to a named instance on Linux, the TPC/IP port must be specified after a server name, separated by the comma. Do not specify the instance name.
|
|
Port | Only for macOS. 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 sets) support in a connection:
MARS is a feature supported by SQL 2005 and later, or by SQL Azure. Note that enabling the MARS feature might result in performance degradation. For information about MARS, see: Using Multiple Active Result Sets (MARS) |
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 |
StringFormat |
|
Choose |
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 |
MetaCaseInsCat |
Controls the SQL Server catalog names case sensitivity. This parameter can take one of the following values:
|
True |
Use Cases
Using a Secure Connection
To use TLS/SSL encryption, pass the key-value pair TrustServerCertificate=yes
on the value of the ODBCAdvanced parameter:
ODBCAdvanced=TrustServerCertificate=yes
Other Use Cases
- Connect to the local SQL Server instance using SQL Server authentication:
DriverID=MSSQL Server=127.0.0.1 Database=Northwind User_Name=sa MetaDefSchema=dbo MetaDefCatalog=Northwind
- Connect to SQL Express 2005 using Windows authentication:
DriverID=MSSQL Server=DA\SQLEXPRESS Database=Northwind OSAuthent=Yes MARS=no
- Connect to SQL Azure. Note the "@<server>" suffix in User_Name and the "tcp:" prefix in the Server parameters:
DriverID=MSSQL Server=tcp:nasdfert6.database.windows.net Database=Northwind User_Name=addemo@nasdfert6 Password=asd123zxc Encrypt=Yes MetaDefSchema=dbo MetaDefCatalog=Northwind
- Connect to LocalDB:
DriverID=MSSQL Server=(localdb)\\v11.0 Database=master OSAuthent=Yes
- Connect to LocalDB and attach a database file:
Server=(localdb)\\v11.0 DriverID=MSSQL ODBCAdvanced=AttachDbFileName=C:\\Users\\Alex\\ADDemo.mdf
See Also
- Common Connection Parameters
- FAQs
- How to configure FireDAC Drivers
- How to manage FireDAC Connection Definitions
Samples
- FireDAC MS SQL sample