April 3, 2015 at 9:19 am
Everyone,
Very interesting issue popped up and I'm curious to get some ideas/opinions on this.
I have 2 SQL 2012 servers using AlwaysOn. Let's call them sql101 and sql102 I've created everything identically on both servers, including the linked servers. There are 3 on each 2012 server - 1 to a SQL 2000 server (I know, I know....) and 2 to SQL 2005 instances. The other day, one of the 2005 linked servers on sql101 stopped working. The error being generated when I drill down in Management Studio is:
------------------------------
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 [1326]. (Microsoft SQL Server, Error: 1326)
Now, before I go to my networking guy, I want to see if there is something I'm missing here. I can ping the server. I can log in via Management Studio from sql101. It doesn't appear to be a network thing, but I'm out of logical options at this point. We changed nothing on the SQL Server side. When the error was reported and I couldn't come up with an answer, I dropped the linked server on sql101 and used the script from sql102 to create it. I received the same errors.
If I drop the linked server and re-create it using an ODBC DSN, it works. I don't know if would matters that one server uses the OLE DB provider and the other uses ODBC. They're the same name. I would hate to see one behave differently than the other in a given circumstance and we end up with incorrect/varying results. I suppose I could drop the good one and make them both ODBC, but why would the linked server to our other 2005 instance work fine?
Either I'm missing something here or I need to engage our networking guy. Oh, below is the code for the linked server creation. I've changed some company-specific info to protect the guilty....
thanks...
USE [master]
GO
/****** Object: LinkedServer [SQL2005INST] Script Date: 4/3/2015 10:39:55 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'SQL2005INST', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'SQL2005INST.domain.com'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL2005INST',@useself=N'False',@locallogin=NULL,@rmtuser=N'hrp_reader',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'SQL2005INST', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
April 4, 2015 at 12:07 am
Quick thought, having a Named Pipe as a protocol there looks odd, have the client network configurations changed on the server?
😎
Named Pipes Provider: Could not open a connection to SQL Server [1326]. (Microsoft SQL Server, Error: 1326)
April 4, 2015 at 12:33 am
Bought from stocked up even more when they had the fifty percent of steel one the use mom see hope without another why do I love lower I letter not really I V ball key again welcome Bella Vita Anti-Aging Serum[/url] at my age and all today and someone who can answer before get into the video here and is not I have switched my Instagrama cap so a few followed me on Mazurka hell and I kea I teach I L I am no longer using yet dot Integra I have switch the name that mines scat I %eh I L in open up a new one way and I V kite I %eh I L and how this is super confusing but as they let me hear I'm because I wanted she'll look I know respect the privacy other people own I was always been staring at my friends and family and open up a separate one they could do more.
For information presentation topics --> > >> > > > http://www.tripleeffectseyeserum.org/bellavita-anti-aging-serum/
April 4, 2015 at 12:33 am
Bought from stocked up even more when they had the fifty percent of steel one the use mom see hope without another why do I love lower I letter not really I V ball key again welcome Bella Vita Anti-Aging Serum[/url] at my age and all today and someone who can answer before get into the video here and is not I have switched my Instagrama cap so a few followed me on Mazurka hell and I kea I teach I L I am no longer using yet dot Integra I have switch the name that mines scat I %eh I L in open up a new one way and I V kite I %eh I L and how this is super confusing but as they let me hear I'm because I wanted she'll look I know respect the privacy other people own I was always been staring at my friends and family and open up a separate one they could do more.
For information presentation topics --> > >> > > > http://www.tripleeffectseyeserum.org/bellavita-anti-aging-serum/
April 4, 2015 at 12:34 am
Bought from stocked up even more when they had the fifty percent of steel one the use mom see hope without another why do I love lower I letter not really I V ball key again welcome Bella Vita Anti-Aging Serum[/url] at my age and all today and someone who can answer before get into the video here and is not I have switched my Instagrama cap so a few followed me on Mazurka hell and I kea I teach I L I am no longer using yet dot Integra I have switch the name that mines scat I %eh I L in open up a new one way and I V kite I %eh I L and how this is super confusing but as they let me hear I'm because I wanted she'll look I know respect the privacy other people own I was always been staring at my friends and family and open up a separate one they could do more.
For information presentation topics --> > >> > > > http://www.tripleeffectseyeserum.org/bellavita-anti-aging-serum/
April 6, 2015 at 11:39 am
No, we're not using Named Pipes to connect. Not sure why that is coming back in the message.
Just for giggles, with the same set up, I tried to run something via OPENQUERY. Here's a little more from the error message:
select * from openquery(SQL2005INST, 'exec sp_who')
OLE DB provider "SQLNCLI11" for linked server "NTSQL010" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "NTSQL010" 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.".
Msg 1326, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [1326].
Again, the timeout thing is bizarre to me. I can ping that server, log in using SSMS from the server I am run OPENQUERY from. I'm really leaning towards some odd networking thing, but I'm hoping someone here may have seen this odd behavior.
April 6, 2015 at 11:43 am
Have you disabled Named Pipe and made certain TCP/IP is enabled? The error is typical of such an issue.
😎
April 6, 2015 at 11:54 am
I did not disable Named Pipes on sql101 because it is not disabled on sql102. From sql102, I can get to SQL2005INST no problem. In fact, from sql101, I can get to another SQL 2005 instance without issue, just not to the one I use in my linked server.
I just turned off Named Pipes on sql101, to see what would happen. Doing that, I can no longer get to either of my SQL 2005 instances.
Eirikur Eiriksson (4/6/2015)
Have you disabled Named Pipe and made certain TCP/IP is enabled? The error is typical of such an issue.😎
April 6, 2015 at 12:26 pm
pveilleux (4/6/2015)
I did not disable Named Pipes on sql101 because it is not disabled on sql102. From sql102, I can get to SQL2005INST no problem. In fact, from sql101, I can get to another SQL 2005 instance without issue, just not to the one I use in my linked server.I just turned off Named Pipes on sql101, to see what would happen. Doing that, I can no longer get to either of my SQL 2005 instances.
Eirikur Eiriksson (4/6/2015)
Have you disabled Named Pipe and made certain TCP/IP is enabled? The error is typical of such an issue.😎
You definitely have a networking issue if that is the case, look closer into the configurations and the OS's network config.
😎
April 6, 2015 at 12:29 pm
I also went ahead and disabled Named Pipes on sql102, tried the queries, enabled Named Pipes, tried queries again. Everything on sql102 is behaving just fine. As I said, I can no longer get to a 2005 instance from sql101. I'm going to get a networking guy involved now.
thanks....
April 6, 2015 at 12:33 pm
pveilleux (4/6/2015)
I also went ahead and disabled Named Pipes on sql102, tried the queries, enabled Named Pipes, tried queries again. Everything on sql102 is behaving just fine. As I said, I can no longer get to a 2005 instance from sql101. I'm going to get a networking guy involved now.thanks....
Another thought, have you checked if the SQL Server Browser Service running on the 2005 instance?
😎
April 6, 2015 at 12:34 pm
Eirikur Eiriksson (4/6/2015)
pveilleux (4/6/2015)
I also went ahead and disabled Named Pipes on sql102, tried the queries, enabled Named Pipes, tried queries again. Everything on sql102 is behaving just fine. As I said, I can no longer get to a 2005 instance from sql101. I'm going to get a networking guy involved now.thanks....
Another thought, have you checked if the SQL Server Browser Service running on the 2005 instance?
😎
It is not running on the SQL 2005 instance. I can turn it on, see what happens, but remember, I have 1 server (sql102) that can still get to the SQL 2005 instances, even with the Browser off.
April 6, 2015 at 12:41 pm
pveilleux (4/6/2015)
Eirikur Eiriksson (4/6/2015)
pveilleux (4/6/2015)
I also went ahead and disabled Named Pipes on sql102, tried the queries, enabled Named Pipes, tried queries again. Everything on sql102 is behaving just fine. As I said, I can no longer get to a 2005 instance from sql101. I'm going to get a networking guy involved now.thanks....
Another thought, have you checked if the SQL Server Browser Service running on the 2005 instance?
😎
It is not running on the SQL 2005 instance. I can turn it on, see what happens, but remember, I have 1 server (sql102) that can still get to the SQL 2005 instances, even with the Browser off.
Was it running before? Just thinking of what could have changed|.
😎
April 6, 2015 at 12:44 pm
Eirikur Eiriksson (4/6/2015)
Was it running before? Just thinking of what could have changed|.
😎
It was not running. The 2005 server is a production server and the 2012 instances (sql101, sql102) are still in development. Me and one other person are the only ones actively using them. The other person is the one who reported the problem. I think she only has access to SQL, not to the server itself, so she wouldn't have changed anything.
April 6, 2015 at 12:53 pm
pveilleux (4/6/2015)
Eirikur Eiriksson (4/6/2015)
Was it running before? Just thinking of what could have changed|.
😎
It was not running. The 2005 server is a production server and the 2012 instances (sql101, sql102) are still in development. Me and one other person are the only ones actively using them. The other person is the one who reported the problem. I think she only has access to SQL, not to the server itself, so she wouldn't have changed anything.
Is this a physical box or a VM?
😎
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply