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:

1. 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.
2. Right click “Linked Server” under Server Objects in Management Studio.
3. Type a name for the linked server and select “Other data source.”
4. For the Provider, select “Microsoft OLE DB Provider for ODBC Drivers”
5. For Product name, type the name of the DSN you created in step 1.
6. Data source is the name of the DSN again.
7. The provider string I used is Data Source=<DSN>;Initial Catalog=tempdb;Integrated Security=SSPI;
8. Location and Catalog are both blank.
9. Under the security page, be sure the radio button is selected for “Be made using the login’s current security context”
10. Set the appropriate options on the Server Options page.
11. Click OK

That’s it!  You should now have a working linked server setup between SQL Server 2005 and SQL Server 7.