December 10, 2015 at 12:05 pm
I have a feeling the response is going to be "google 'sql server' and 'double hop'" but for my own sanity let me pose the question anyway.
The objective: I'm trying to create a Linked Server over which I'm going to make a remote SP call using Windows Authentication.
Things I've already confirmed:
- The user account in question has permission on both sides
- I can telnet from one server to the other via port 1433
Create the Linked Server:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'SomeServer', @srvproduct=N'sql_server', @provider=N'SQLNCLI10', @datasrc=N'SomeServer.SomeDomain.com'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SomeServer',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
GO
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'collation name', @optvalue=NULL
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'SomeServer', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
Create Synonym to the remote SP:
USE SomeDB
GO
CREATE SYNONYM [dbo].[SomeSynonym] FOR [SomeServer].[SomeRemoteDB].[dbo].[SomeSP]
GO
Open a new SSMS window using a runas:
runas /noprofile /netonly /user:SomeDomain\SomeUser "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
Execute the SP:
USE SomeDB
GO
EXEC [dbo].[SomeSynonym]
GO
The Result:
OLE DB provider "SQLNCLI10" for linked server "SomeServer" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "SQLNCLI10" for linked server "SomeServer" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "SomeServer".
Adjust Linked Server config:
(switch from "without using a security context" to "using the login's current security context")
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'SomeServer', @locallogin = NULL , @useself = N'True', @rmtuser = N''
GO
The Result:
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
Any thoughts or ideas?
_____________________________________________________________________
- Nate
December 10, 2015 at 5:18 pm
Just guessing here but could this not be due to Kerberos not being configured correctly? If "SomeServer" is on a different domain to the calling server then I suppose "SomeServer" has no way of authenticating the calling user. So a combination of Kerberos and domain trusts could be the issues. That's assuming the servers are on different domains. If they are not then I really don't know! 🙂
December 11, 2015 at 1:53 am
Yes defiantly looks like a double hop issue.
Check with your domain admins, that the SQL Server accounts have trusted for delegation rights and that the correct SPN's have been set for the services to enable kerberos to pass tickets between hosts.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply