July 10, 2012 at 1:35 pm
I have a query which pulls data from other linked servers. It was fine till yesterday. Suddenly since morning I have this linked server error. I tried to test the linked server connection which gave me the following error.
"SQLServer Network interfaces: Logon failed"
OLE DB provider "SQLNCLI10" for linked server "DataServerOne" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "DataServerOne" 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 65535, Level 16, State 1, Line 0
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
OLE DB provider "SQLNCLI10" for linked server "DataServerOne" returned message "Invalid connection string attribute".
July 10, 2012 at 5:19 pm
I have dropped and recreated the linked server. Still the same error no change.
July 11, 2012 at 7:21 am
Can you post your linked server configuration?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 11, 2012 at 11:05 am
/****** Object: LinkedServer [DataServerSeven] Script Date: 07/11/2012 10:02:40 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'DataServerSeven', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @provstr=N'PROVIDER=SQLOLEDB;SERVER=tcp:XXXXXXXXXXX\xxxXXXXXXXXXX,58660'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DataServerSeven',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DataServerSeven', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
July 11, 2012 at 1:01 pm
It worked until yesterday, so what has changed?
Were permissions changed on the remote server?
If being called through an Agent job did the Agent service account change?
If being called from a web application, did the app pool account get changed or could there be a Kerberos issue now?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply