Creating an ODBC linked server without configuring a data source

Recently, a prospective customer expressed an interest in connecting from SQL Server to a different DBMS by using ODBC, but didn't want the burden of having to create an ODBC data source on each machine on which the solution was deployed.

An ODBC data source is a set of configuration parameters that enables an ODBC driver to establish a connection to a database. On Windows, ODBC data sources are configured through a GUI provided by Microsoft and are stored in the registry.

Normally, when setting up an ODBC-based linked server in SQL Server, you specify an ODBC data source. However, you don't have to. SQL Server supports DSN-less connections, where all the connection details are specified in a connection string. Here's an example DSN-less connection string for our Salesforce ODBC driver:

DRIVER={Easysoft Salesforce ODBC Driver};User=myuser@easysoft.com;Password=mypassword;
TOKEN=ABCDEFGH123456;

The connection string:

• Tells the ODBC Driver Manager which ODBC driver to use.
• Supplies the ODBC driver with the authentication details for the Salesforce connection.

To use a DSN-less connection to create a linked server, in the New Linked Server dialog box in SQL Server, choose Microsoft OLE DB Provider for ODBC Drivers, leave the Data source field blank, and enter your connection string in the Provider string field.

Note You may need to create an ODBC data source first to find out what the parameter names are for the connection string. The parameter names aren't necessarily the same as the label for the parameter in the ODBC driver's configuration dialog box. If you create a test data source and then look for that data source under this registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI, you'll find out the parameter names to use.

Note When testing with our Salesforce driver, passing UID=myuser;PWD=mypassword on the connection string failed. Replacing these parameters with the driver defined equivalents succeeded (for example, User=myuser;Password=mypassword).

So, although you need to install an ODBC driver on each SQL Server machine where you want to use an linked server, you don't need to configure an ODBC data source on every (or any) SQL Server machine.