Today I had the pleasure of creating a linked server on our main production SQL Server 2005 instance. The catch is that the server I needed to link was a SQL Server 7 instance. Here are the steps I took to get the linked server up and running in under 5 minutes flat:
- Create a new system DSN using the SQL Server (not native client) driver for the server you’re going to link to. I used version 6.01.7600.16385. Walk through the configuration options and modify those that are applicable to your situation.
- Right click “Linked Server” under Server Objects in Management Studio.
- Type a name for the linked server and select “Other data source.”
- For the Provider, select “Microsoft OLE DB Provider for ODBC Drivers”
- For Product name, type the name of the DSN you created in step 1.
- Data source is the name of the DSN again.
- The provider string I used is Data Source=<DSN>;Initial Catalog=tempdb;Integrated Security=SSPI;
- Location and Catalog are both blank.
- Under the security page, be sure the radio button is selected for “Be made using the login’s current security context”
- Set the appropriate options on the Server Options page.
- Click OK
That’s it! You should now have a working linked server setup between SQL Server 2005 and SQL Server 7.