How to configure a Linked Server using the ODBC driver

Microsoft Open Database Connectivity (ODBC) is an application programming interface (API) designed to access data from a different of database management systems (DBMS). ODBC is designed for relational data stores.

In this article, we will explain how to install the appropriate ODBC drivers for SQL Server, how to configure ODBC to connect to a SQL Server instance and how to create and configure a Linked Server using the ODBC driver and the MSDASQL provider to query tables on a SQL Server instance.

The diagram below explains the flow of connecting to a SQL Server database using the MSDASQL provider and a SQL Server ODBC driver.

MSDASQL allows OLE DB consumer applications to use ODBC drivers to connect to a data source. The provider transforms incoming OLE DB calls into ODBC calls, and transfers them on to the specified ODBC driver. After that, it retrieves results from the ODBC driver and formats those into OLE DB specific structures which the consumer has access to them.

Installing and configuring the Microsoft ODBC drivers for SQL Server

From this link, the Microsoft ODBC drivers for SQL Server can be downloaded and installed. Depending on which Windows operating system version (32-bit or 64-bit) is used, there are two versions of the Microsoft ODBC drivers for SQL Server that can be installed:

x64\msodbcsql.msi is for the Windows 64-bit version
x86\msodbcsql.msi is for the Windows 32-bit version

In case that the wrong version of the Microsoft ODBC drivers for SQL Server is trying to be installed (e.g. x86\msodbcsql.msi on the Windows 64-bit version), the following warning message will appear:

Let’s install the appropriate Microsoft ODBC drivers for SQL Server. The process of installation of the Microsoft ODBC drivers for SQL Server is very simple and straightforward. Double click on the (e.g. msodbcsql.msi ) file and the welcome screen will appear:

Click the Next button to proceed with installation. Under the License Agreement window read the license terms and if you agree, check the “I accept the terms in the license agreement” radio button and click the Next button:

On the Feature Selection window, select the Client Components item and click the Next button:

Click the Install button on the Ready to Install the Program window to begin the installation of the Microsoft ODBC drivers for SQL Server:

In a few seconds, the installation of the Microsoft ODBC drivers for SQL Server finishes. Click the Finish button to close the Microsoft ODBC drivers for SQL Server Setup window:

To confirm that the Microsoft ODBC drivers for SQL Server is installed, go to Control Panel and under the Program and Features find the Microsoft ODBC drivers for SQL Server:

Or open the ODBC Data Source Administrator dialog box:

Under the Drivers tab, check iif the ODBC Driver for SQL Server driver exists:

The ODBC Data Source Administrator dialog box is used to create and manage ODBC data sources. The ODBC Data Source Administrator dialog box is a Windows component.

To open the ODBC Data Source Administrator dialog box, go to the Start menu under Windows Administrative Tools, choose ODBC Data Sources:

In the Control Panel under Administrative Tools, choose appropriate ODBC Data Sources:

Or simpler, in the Windows search box, type the ODBC Data Sources word:

And from the search list, choose appropriate:

As you may notice, there are two versions of the ODBC Data Sources Administrator dialog boxes: one is the ODBC Data Sources Administrator (32-bit) and another is the ODBC Data Sources Administrator (64-bit) version. Since Windows 8 is the operating system, there are two the ODBC Data Sources Administrator dialog boxes, one is for 32-bit ODBC Data Sources and the other is for 64-bit ODBC Data Sources.

Note: If the ODBC Data Sources Administrator (64-bit) dialog for creating connection with 32-bit data source (e.g.32-bit SQL Server) is used, the following error may appear when a linked server is created:

The linked server has been created but failed a connection test. Do you want to keep the linked server?
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider “MSDASQL” for linked server “SERVERX86”.
OLE DB provider “MSDASQL” for linked server “SERVERX86” returned message “[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”. (Microsoft SQL Server, Error: 7303)

To create a new SQL Server ODBC data source, press the Add button under the User DSN or System DSN tab. Both of these tabs are used to create a connection to ODBC data source, the difference is that the User DSN tab will show data source only for a currently logged user on this computer. While creating data sources under the System DSN, it will be available to any user that is logged on to this computer.

For the purpose of this article, the SQL Server ODBC data source will be created under the System DSN tab. To start creating connection, press the Add button:

This will open the Create New Data Source dialog, from the list, choose a driver for which you want to set up a data source. In our case, that will be ODBC Driver 13 for SQL Server:

After selecting a desired driver, click the Finish button, this will open the Create a New Data Source to SQL Server dialog:

Under the Name box, enter the connection name, the name can be whatever you like (e.g. TestServer). The Description box is optional and it’s for a description of the data source. In the Server field, enter a name of a SQL Server for which you want to connect to (e.g. WIN10\SQLEXPRESS):

If a SQL Server instance is the default instance, type the name of the computer that hosts the instance of SQL Server (e.g. WIN10). If SQL Server is a named instance, type the name of the computer and the name of the instance separated by a slash (e.g. WIN10\SQLEXPRESS). Otherwise, the following message will appear when testing the SQL Server ODBC data source connection and the name of the SQL Server instance is wrong:

After setting the appropriate Server and connection name, press the Next button. In this dialog, how to connect to SQL Server by using Windows authentication can be specified, including Active Directory authentication or using SQL Server authentication.

For the purpose of this article, SQL Server authentication will be used. Check the With SQL Server authentication using a login ID and password entered by the user radio button and, in the Login ID and Password field, enter appropriate credentials and press the Next button:

On this dialog, set the database that you want to connect to, by checking the Change the default database to check the box and from the combo box, choose a deserted database.

During the change of the default database the following message may appear:

This means that communication link between the driver and the data source to which the driver was attempting to connect failed.

To resolve this, close the ODBC Data Sources Administrator dialog box and try again to create connection to ODBC data source.

If that isn’t successful, check if Named Pipes under the Protocols for SQL Server for which you want to create a connection to (e. g. SQLEXPRESS) are enabled:

Don’t forget to restart SQL Server under the SQL Server Services after enabling Named Pipes in order to changes have effect.

Make sure that the SQL Server and Windows Authentication mode radio button under the Security tab of the Server Properties dialog is checked:

Also, check if the Allow remote connections to this server check box under the Connections tab is checked:

After these settings are done, under the Change the default database to combo box, available databases will appear, choose a database (e.g. AdventureWorks2014) and click the Next button:

This will open one more dialog with more options to set. These options will be left as it is and click the Finish button:

After, clicking the Finish button, the ODBC Microsoft SQL Server Setup window will appear with configuration information that we set for the ODBC data source. At the bottom of the ODBC Microsoft SQL Server Setup window, there is the Test Data Source button, by clicking on this button, it can be checked if the connection with the data source is established successfully or not:

In case that connection with data source is established successfully, on the SQL Server ODBC Source Test window, the “TESTS COMPLETED SUCCESSFULLY” message will appear:

To confirm that the ODBC data source connection is created, in the ODBC Data Source Administrator dialog box under the System DSN tab, the name of the ODBC data source that was created will appear:

Deleting an ODBC data source

To delete ODBC data source in the ODBC Data Source Administrator dialog box, select the desired item from User DSN or System DSN tab and click the Remove button:

As you may notice, under the System DSN tab, the LocalServer name is selected, but the Remove button is disabled. This happens because we are trying to delete a 32-bit System DSN from 64-bit ODBC Data Source Administrator dialog box. To delete 32-bit DSN, open the 32-bit ODBC Data Source Administrator dialog box and click the Remove button:

Now, when the ODBC data source is created, let’s create and configure a Linked Server using the ODBC.

Creating and configure a Linked Server using the ODBC driver via SQL Server Management Studio

In SQL Server Management Studio (SSMS) go to the Object Explorer, right click on the Linked Servers folder and, from the context menu, choose the New Linked Server command:

This will open the New Linked Server dialog:

Under the General tab of the New Linked Server dialog in the Linked server text box, enter a name of a new linked server (e.g. ODBC_SERVER). in the Provider combo box, choose the Microsoft OLE DB Provider for ODBC Drivers item. The Product name is an identifier and any appropriate value for this field (e.g. TestSarever) can be used. The data source must match a system data source defined in the ODBC Data Source Administrator dialog box System DSN tab:

Under the Security tab, choose the Be made using this security context radio button, then enter the username and password for a user account existing on the TestServer instance that was chosen as our data source:

For more about the Security and Options tab, see the How to create and configure a linked server in SQL Server Management Studio page.

After, the security configuration is done, press the OK button on the New Linked Server dialog to create a new linked server. A newly created linked server will appear under the Linked Servers folder:

To test if the connection with the ODBC data source is established successfully, right click on the ODBC_SERVER linked server and, from the context menu, choose the Test Connection command:

If a connection with the ODBC data source is established successfully, the following info message box will appear:

In case that the connection with a linked server is created successfully, but under the Catalogs folder of the linked server, only the default database is shown:

To resolve this, close SQL Server Management Studio and run it again, but this time as an administrator:

Another way is to go to the SQL Server Configuration Manager:

From the SQL Server Configuration Manager dialog, select SQL Server for which a linked server has been created:

From the context menu, choose the Properties option:

Under the Log on tab of the SQL Server Properties dialog, choose the Built-in account radio button and from the combo box, select the Local System item:

From the SQL Server Properties dialog, click the Apply button and press the Yes button on the Confirm Account Change warning message box:

Now, open SQL Server Manage Studio as administrator and under the Catalogs folder, the databases should appear:

If the problem still persists, contact MSDN troubleshooting and support

Creating and configuring a ODBC Linked Server using T-SQL

To create a linked server using T-SQL, execute the following code:

EXEC master.dbo.sp_addlinkedserver @server = N'ODBC_SERVER', @srvproduct=N'TestServer', @provider=N'MSDASQL', @datasrc=N'TestServer'

/* For security reasons, the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ODBC_SERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'Ben',@rmtpassword='########'

Note:

@rmtpassword is the password of the remote user used to connect to a remote server, for security reasons password is changed with ‘########’.

More about how to create and configure a SQL Server linked server using T-SQL can be found on the How to create, configure and drop a SQL Server linked server using Transact-SQL page.

Installing and configuring the Microsoft ODBC drivers for SQL Server

Deleting an ODBC data source

Creating and configuring a ODBC Linked Server using T-SQL

Related posts: