December 28, 2015 at 2:56 am
Hi all,
We've recently migrated to SQL Server 2014 SP1 Enterprise edition and I've come across a bit of a headache. Let me describe the scenario: we have three servers ServerA, ServerB, and ServerC. All 3 servers have the same version of SQL Server 2014 and are not in a cluster or anything complicated. They are independent of one another and they have a "all services on one server" type of architecture. This means the database engine, SSIS, SSAS, SSRS etc are all on one server.
The only dependency, if that contradicts my above assertion of independence, is that each server has a Linked Server to its counterparts under Server Objects for querying purposes. That means that in ServerA, I have a Linked Server to ServerB and ServerC. In ServerB to ServerA and ServerC and so on. That means that on ServerA, I should be able to run the following queries:
1. Select *
From ServerB.Database.Schema.Table
2. Select *
From ServerC.Database.Schema.Table
However after a server restart (ServerA being restarted), I get the following error message for Query 1 (Query 2 runs without any problems) after running it on Server A:
OLE DB provider "SQLNCLI11" for linked server "ServerB" 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.".
OLE DB provider "SQLNCLI11" for linked server " ServerB " returned message "Client unable to establish connection".
Encryption not supported on the client.
I've checked all the services on both servers and they are all running. I've also checked the TCP/IP ports and all is enabled and the firewall isn't blocking anything. In addition to that, these are all default instances.
The solution to this problem is to restart the SQL Server (MSSQLSERVER) service on ServerA and then I can run Query 1 without any problems. Nonetheless my question is why does this happen? I mean the service is automatically started during the server reboot so why do I have to restart it again manually to make the Linked Server work? All of the following query combinations work without a service restart:
Run on ServerB:
Select *
From ServerA.Database.Schema.Table
Select *
From ServerC.Database.Schema.Table
Run on ServerC:
Select *
From ServerA.Database.Schema.Table
Select *
From ServerB.Database.Schema.Table
It's just the "Select * From ServerB.Database.Schema.Table" that needs a service restart. Am I missing something here during my server restart?
December 28, 2015 at 8:19 am
I would focus upon "Encryption not supported on the client" message, where the "client" is Server A. Inspect ServerA's system event log (at very least, during the restart of the OS). Test whether making a SQL Server service startup dependent upon the Cryptographic Services service avoids the errors. If that dependency helps, you should next determine why Cryptographic Services is starting slowly. If I remember correctly, Sysinternal's Process Explorer can be configured to collect metrics during a system startup.
January 4, 2016 at 5:04 am
Thanks for the suggestion, I will try it out and let you know.
January 4, 2016 at 6:27 am
what security context does the linked server use, sql or windows authentication.
It sounds very much like windows auth with an SPN issue
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 5, 2016 at 3:58 am
Unfortunately setting the SQL Server service dependent on Cryptographic Services didn't work.
Connections are always made using the login's current security context, which is Windows Authentication. It sounds like an SPN issue but why is it automatically fixed when I restart the SQL Server service and it works from ServerA -> ServerC and all other combos except for ServerA -> ServerB?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply