I recently came across an issue when working w/ linked servers in SQL and had to do a bit of digging to get it to work correctly so I figured I’d share it with the world.  The challenge was to setup a linked server for SQL that used an Alias instead of just the server name.  Although it may seem straight forward, the bit of the challenge was that the linked server was not the default instance of SQL, but a named instance.
Through various forums and posts, I was able to find the following process to correctly get this configured.  The main trick to the configuration is that instead of using SQL 2005’s “SQL Server Configuration Manager” for setting up the alias, use the legacy (earlier SQL 2000) utility of cliconfg.exe
The steps for this are as follows:

  1. setup the alias using cliconfg.exe (Start -> Run -> cliconfg.exe)
  2. Once cliconfg.exe is running, select the Alias tab and click the Add button
  3. Fill in the information (Server alias, Server name,Library type, and port (if applicable)
    1. For the server name, be sure to reference the correct instance name (unless default)
    4.  Click OK twice to apply and exit out of cliconfg.exe
Now that our Alias is setup, we can setup our Linked Server in SQL Management Studio.  To do that, open SQL Management studio and follow the steps below.
Branch out the Server Objects and right click on Linked servers to select New Linked Server
Using the New Linked Server wizard, fill out the information for your Linked Server.
Under General:

  1. Linked Server: your alias from above
  2. Server type: select Other data source
  3. Provider: SQL Native Client
  4. Product name: sql_provider
  5. Data source: reference the server (and instance if applicable)
  6. Catalog: If you’d like to reference a specific database directly, fill this in

Linked Server: your alias from above

Under Security:

Add the logins used to connect to your Linked server, or specify an alternate for non defined logins and click Ok
Under Server Options:

  1. Be sure to set the correct settings.  Recommended are:
    • Collation Compatible: False
    • Data Access: True
    • Rpc: True
    • Rpc Out: True
    • Use Remote Collation: True

Note: Adjust these to the proper security for your needs
Click OK and test out the new connection.
“Happy SQLing”