Add linked server from SQL Server 11.0.5058 to SQL Server 10.50.4000 I tried to add a linked server running on SQL Server 2008 R2 (10.50.4000) from SQL Server 2012 (11.0.5058).

I keep getting this error:


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




Does anyone know how to solve this problem?

Are there any restrictions on building linked server between different versions of the database?

Okay, created this linked server using tsql code

EXEC sp_addlinkedserver
@server='xxxLinkedServer', -- here you can specify the name of the linked server
@srvproduct='',     
@provider='sqlncli', -- using SQL Server Native Client
@datasrc='xxxxxx',   -- add here your server name
@location='',
@provstr='',
@catalog='smpro5'  -- add here your database name as initial catalog (you cannot connect to the master database)
-- Add credentials and options to this linked server
EXEC sp_addlinkedsrvlogin
@rmtsrvname = 'xxxLinkedServer',
@useself = 'false',
@rmtuser = 'xxxxSqlUser',             -- add here your login on Azure DB
@rmtpassword = 'xxxxxxxx' -- add here your password on Azure DB


When open the server node and view all tables, get the "Failed to retrieve data for this request" error.



I can access the 2008r2 server via UI, without any trouble.


I do have one concern, that is my main database sits on Amazon cloud. Could this cause any problem?

Okay, thanks for all the answers guys. I think it is the problem caused by the firewall setting on the 2008r2 server. I am going to ask server admin to open that port for me.

Thanks to system administrator, it now works.



Special thanks to  Remus Rusanu, thank you for pointing me to the right direction !!!
As your administrator I'd like to confirm that the problem has been caused by firewall restriction. SQL port is opened so you can complete linked server setup.

Cheers, mate