Configuring Linked Servers in Microsoft SQL Server 2005-2012, using the Microsoft OLE DB Provider for ODBC Drivers

The following steps are appropriate (with minimal adjustment) for Microsoft SQL Server 2005 and later.

1. Log in to the machine that hosts the Microsoft SQL Server 2005 instance.
   
2. In the Start menu, drill down to Programs -> Microsoft SQL Server 2005 (or similar) -> Microsoft SQL Server Management Studio.
   
3. Locate your Microsoft SQL Server instance, and drill down to Server Objects -> Linked Servers.
   
4. Right-click on Linked Servers, and select New Linked Server.
   
5. Supply the following information on the General tab:
   
   • Linked Server: Any name you like
     
   • (Check Other Data Source)
     
   • Provider Name: Microsoft OLE DB Provider for ODBC Drivers
     
   • Product Name: MSDASQL
     
   • Data Source: Valid ODBC DSN Name
     
   • Note: OpenLink ODBC DSNs do not store username and password data, so these should be set in the Security tab, below.
     
6. This is the minimum information which you must supply. Provider Name and Product Name must be exactly as shown above. Feel free to engage any server options on the bottom of the tab.
   
7. If your ODBC Data Sources requires Username and Password authentication, click to the Security tab and review your options. This is the only way to provide user credentials to the ODBC connection.
   
8. Click OK to create your server.
   
9. Exit the Linked Server dialog box, and the Microsoft SQL Server Enterprise Manager.
   
10. Open the Microsoft SQL Server Query Analyzer.
    
11. Run a query similar to the following, to test your Linked Server:
    
    

    
    select * from OPENQUERY (Your_LINKEDSERVER_Name, 'your SQL query here')