Problem

I recently hit upon an issue with trying to setup a linked server to an instance which was already a subscriber to a publication. When replication is setup, it actually creates a remote server for the subscriber. However that ‘remote server’ is not configured for data access. So if you try to use that server, you would end up with:

Msg 7411, Level 16, State 1, Line 1
Server 'foosub' is not configured for DATA ACCESS.

Also, any attempt to add a similarly named linked server would fail with the error message below:

EXEC master.dbo.sp_addlinkedserver @server = N'foosub', @srvproduct=N'SQL Server'

Msg 15028, Level 16, State 1, Procedure sp_addlinkedserver, Line 82
The server 'foosub' already exists.

Troubleshooting

Next, I tried to add a linked server (using the SQL Native Client) but with a different name (MYSRV) but pointing to the right server (foosub). My initial attempt yielded the following error:

The linked server has been created but failed a connection test. Do you want to keep the linked server?

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Named Pipes Provider: Could not open a connection to SQL Server [53].
OLE DB provider "SQLNCLI10" for linked server "MYSVR" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "MYSVR" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 53)

Solution

The problem turned out that I had not used the right ‘provider’ string. Here is the script which finally worked for me:

EXEC master.dbo.sp_addlinkedserver @server = N'MYSVR', @srvproduct=N'foosub', @provider=N'SQLNCLI10', @provstr=N'Server=foosub;Database=master;Trusted_Connection=yes'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MYSVR', @locallogin = NULL , @useself = N'True'
GO

Or if you are more comfortable using the SQLOLEDB provider, here’s a sample:

EXEC master.dbo.sp_addlinkedserver @server = N'MYSRV', @srvproduct=N'SQLOLEDB', @provider=N'SQLOLEDB', @datasrc=N'foosub', @provstr=N'Data Source=foosub;Initial Catalog=master', @catalog=N'master'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MYSRV', @locallogin = NULL , @useself = N'True'
GO

You can also do this from the SSMS GUI by using '‘SQLNCI10’ or ‘SQLOLEDB’ as the Provider.

With this, I can subsequently access remote tables as such:

SELECT * FROM MYSVR.master.sys.tables

Hope this is useful! Please leave a comment if you find it useful.