November 22, 2009 at 3:48 pm
hi
I have a Linked Server query which needs to be run under the context of a Service Account.
So I created a Stored Proc 'WITH Execute AS Owner' and tried executing the proc logging as a different user.
The Executing User and Owner (login) are sysadmin at the source server and the server where the proc resides.
I am getting Error
OLE DB provider "SQLNCLI10" for linked server "SourceLinkServer" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "SourceLinkServer" 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 getting enabled protocols list from registry [xFFFFFFFF].
The following is the Proc Code
CREATE PROCEDURE [dbo].[TestProc]
WITH EXECUTE AS OWNER
AS
BEGIN
EXEC('SELECT * FROM SourceLinkServer.dbAccountPlanningStagingPublish.sys.tables')
END
November 22, 2009 at 3:57 pm
From the error message,it seems you are using SQLNCLI10 as OLE DB Provider.
To better understand your problem, have you run any query against the linked server and tested it?
Is it working?
My initial assumption is that it is not related to the Execute as owner clause or security issue,
it is related to the Provider and test creating the linked server with the Provider as SQL Native Client.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 23, 2009 at 11:30 am
I tried with
EXEC master.dbo.sp_addlinkedserver @server = N'TestServer1', @srvproduct=N'SQL_SERVER', @provider=N'SQLNCLI10', @datasrc=N'xxxxxxx'
But still getting the same error
OLE DB provider "SQLNCLI10" for linked server "TestServer1" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "TestServer1" 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 getting enabled protocols list from registry [xFFFFFFFF].
November 23, 2009 at 12:53 pm
harinarayan-414614 (11/23/2009)
But still getting the same errorSQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF].
Did you check the protocols that are enabled on your Linked Server?
Is the SQL Server Browser enabled and started?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 23, 2009 at 1:14 pm
Shared Memory, TCP, Named Pipes are enabled. SQL Server Browser service is running.
November 23, 2009 at 1:20 pm
I am Clueless !
Try creating the Linked Server from GUI and use the ServerName,Port No..
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
May 19, 2010 at 12:44 pm
Re-install/ update your SQL native client.
November 12, 2010 at 4:34 am
Did this get resolved by reinstalling the SNAC? I am having the same problems querying a linked server using a Windows Domain account that has SA on both servers. When I configure the linked server to authenticate using a SQL Server Login it works.
I don't want to go down the reinstall route if I can avoid it as the server has strict uptime SLA's
September 10, 2011 at 5:32 am
RUN THIS:
EXEC master.dbo.sp_addlinkedserver @server = N'myserver', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'myserver',@useself=N'False',@locallogin=NULL,@rmtuser=N'mySqlAuthAcctOnTargetSvr',@rmtpassword='mySqlAuthAcctOnTargetSvrPassword'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'myserver',@useself=N'False',@locallogin=N'NT AUTHORITY\SYSTEM',@rmtuser=N'domain\svcAcct',@rmtpassword='DomainsvcAcctPassword'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'myserver',@useself=N'True',@locallogin=N'domain\svcAcct',@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'myserver', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
Jamie
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply