March 31, 2017 at 9:37 am
Hello experts,
I need to create a linked server from a SQL 2012 server to a SQL 2016 server and I am running into some problems. Here is a summary, hoping someone can point me in the right direction.
1. The new SQL 2016 server is running on a non-default port.
2. I have gotten the linked server to work when connecting from a SQL 2016 server (i.e., SQL 2016 -> SQL 2016) with @provider=N'SQLNCLI11' or @provider=N'SQLNCLI' - doesn't seem to make a difference.
3. On the servers where the linked server doesn't connect -- (a) SQL 2012 -> SQL 2016 and (b) another SQL 2016 -> SQL 2016 setup -- I get this response:
- The linked server appears to be created correctly, but when I expand its nodes, I see 'default' for the catalog instead of the actual destination database name.
- When I try to expand the 'default'catalog, I end up getting this error:
TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.
(Microsoft SQL Server, Error: 10060)
I'm just wondering if there is a checklist of things to try in this case so I don't get bogged down with trial and error. Below I have scripted out one of the linked servers I tried (details made generic to avoid specific server names etc.). Thanks for any help on this.
- webrunner
USE [master]
GO
/****** Object: LinkedServer [LINKEDSERVER] Script Date: 3/31/2017 11:29:43 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'LINKEDSERVER', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'remoteserver,port#', @catalog=N'databasename'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKEDSERVER',@useself=N'False',@locallogin=NULL,@rmtuser=N'user',@rmtpassword='pword'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVER', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
March 31, 2017 at 10:57 am
That looks right to me.
A few things to check though:
1 - remote desktop into the physical/virtual machine hosting the 2012 instance (the machine that you are trying to make the remote server connection on), start SSMS and try to connect to the 2016 machine you are making the link for
2 - try connecting to the 2016 machine using the username and password pair you are using
3 - make sure the appropriate firewall ports are open (hardware and software)
step 1 will ensure that you can actually connect from the machine hosting the 2012 instance to the 2016 instance. It could be something is blocking that connection
step 2 will ensure that the username and password are correct (I'd recommend copy-pasting them as that will rule out typos in them)
step 3 goes with step 1. Just because you can connect from one machine to another doesn't mean that a firewall somewhere might be blocking the connection. Short term, you could turn off the firewall on both machines and see what happens. If it connects successfully, then you know it is firewall and you need to open the ports.
I do NOT recommend running without a firewall.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply