June 18, 2009 at 9:19 pm
Good afternoon
I am trying to query tables in several production servers from my DataWarehouse.
Example:
On Server1 is the warehouse, this server has linked servers Prod1, with catalog DB2, Prod2, with catalog DB2
My (attempted) query: (pseudocode - syntax is not the issue)
Insert Into Server1.WareHouse.dbo.ResultofTwoTables
SELECT *
FROM
Prod1.DB2.dbo.SourceTable1 a
JOIN
Prod2.DB2.dbo.SourceTable2 b
ON
a.PimaryID=b.PrimaryID
I have log on rights to all 3 DB's, write access to Warehouse and read access to the others.
When I run the query - from my PC, using SQL Server Management studio (all 2005) I get:
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I get that somehow something is passing the anonymous logon to Prod1&2 DB 1 and 2 - how do I "tell" these servers (pass my token) that it is "me" who is trying to query them?
I does not matter if I am logged into these servers in my instance of Sql Server Management studio - I have tried that. And to make it very clear: I can log into them, using Windows authentication, and run queries - no problem.
I would appreciate some help very much!
Regards
Paul
(In case you were wondering: I am NOT a DBA - I am a financial analyst...)
Do or do not. There is no try...
June 19, 2009 at 3:05 am
Hello Paul,
I suspect it is the way the Security is set-up for your Linked Server(s).
Possibly they are configured as (Login will) “Be made without using a security context”?
If so, could you try changing them to “Be made using the login’s current security context”?
If they are already set-up to use the login’s current security context then please check the SQL Server Books Online Topic “Configuring Linked Servers for Delegation” for details of the requirements to successfully pass through the credentials.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
June 22, 2009 at 7:26 pm
Hi John
Thanks for that!
I had a look at the online documentation and had a crack at setting up the linked servers to use Kerberos delegated authorization.
Although it is already all set up as in the guidelines when I query the DataWarehouse (first hop/"middle") server
with "select auth_scheme from sys.dm_exec_connections where session_id=@@spid" I get "NTLM".
Any ideas where I could be going wrong?
Possibly they are configured as (Login will) “Be made without using a security context”?
If so, could you try changing them to “Be made using the login’s current security context”?
It is and was setup as "....current security context"
Other requirements, Same Domain, TCP-IP as protocol, DataWarehouse SPN registered with domain controller, SQL server is running under sysadmin, so trusted for delegation.
I think it may be time to call in a pro, and pick up the tab!
Thanks and regards
Paul
Do or do not. There is no try...
June 22, 2009 at 8:09 pm
There is a very good article on this site about setting up Kerberos. Here are a few of my thoughts on your issue:
1) Running as sysadmin does not guarantee that it is trusted for delegation
a) SQL Server should be running as domain user (not admin), where that domain user has been setup with trusted for delegation.
2) You need to make sure you are connecting to SQL Server using the FQDN, and that the FQDN is what was defined with the SPN record.
For example, if you have the following SPN record:
ServerA.Company.Domain.com
Then, your linked server needs to connect to 'ServerA.Company.Domain.com' and not be connecting to just ServerA. The other option is to add the SPN record for just the server name also.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply