Configuring a Microsoft SQL Server 2005 Linked Server with the Single-Tier "Lite" Edition OLE DB Provider for ODBC Data Sources

  1. Login to the machine that hosts the Microsoft SQL Server 2005 instance.
  2. Expand the Start and Programs menu.
  3. Expand the Microsoft SQL Server 2005 folder.
  4. Launch the SQL Server Management Studio.
  5. Expand the menu tree associated with your Microsoft SQL Server instance.
  6. Expand the Server Objects menu tree.
  7. Expand the Linked Servers menu tree.
  8. Expand the Providers menu tree.
  9. Right click the OpenLink Provider and set these options: 


    [*] Dynamic parameter
[*] Nested Queries
[ ] Level zero only
[*] Allow inprocess
[ ] Non transacted updates
[ ] index as access path
[ ] Disallow adhoc access
[*] Supports 'Like' operator

  10. Save your changes.
  11. Right click on Linked Servers.
  12. Select New Linked Server.
  13. Supply the following information on your General tab:
    • Linked Server: Any name you like.
    • (Check Other Data Source)
    • Provider Name: OpenLink OLE DB Provider for ODBC Drivers
    • Product Name: OpenLinkODBC
    • Data Source: Valid ODBC DSN
    • Note: OpenLink ODBC DSNs do not store username and password data, so these should be set in the Security tab, below.
  14. 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.
  15. 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.
  16. Click OK to create your server.
  17. Exit the Linked Server dialog box and the Microsoft SQL Server Enterprise Manager.
  18. Open the Microsoft SQL Server Query Analyzer.
  19. Run the following query to test your Linked Server: 


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