October 28, 2010 at 4:09 pm
Hi All. First post on this forum.
I am more of a developer than a DBA, but I think I am running across a double-hop problem. I am getting confused at some of the explanations presented in this forum on how to solve double-hops, and am hoping for some clarification.
For an overview of the problem, there are two SQL Servers at a client site, and I will call them Server A and Server B. Server A runs SQL Server 2005, Server B runs SQL Server 2008. Both are on a domain. Windows authentication.
Server A has commercial software installed on it as well that uses the SQL database. Server B has been used for a number of custom development projects revolving around SQL Server.
On Server A, the Insert trigger for one of the tables in the database for the commercial software has been modified to call a remote stored procedure on Server B. It passes the unique identifier for the record just added. The stored procedure on Server B receives this ID, does a bunch of stuff, but also performs a query back to server A to get all the additional information needed about the record just added.
Server A is linked to Server B on both servers. No impersonation is done and the setting on the linked server security page is "Be made using the login's current security context".
So... when I am logged in on Server A and run the commercial software and add a record, everything works like a charm. When I go to a workstation and run the commercial software off Server A and add a record, I get "Login failed for user NT AUTHORITY\ANONYMOUS LOGON".
Am I correct in assuming this is a double-hop issue?
Can I get around this by doing some kind of impersonation on Server A's link to server B?
This is a very high visibility project and they have nobody in-house that knows more than me about SQL Server Administration, which is a frightening thought!
Help?
John
October 29, 2010 at 6:44 am
I haven't a clue about this, so I'm going to call in some reinforcements. Stand by.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 29, 2010 at 6:45 am
Try this site
http://clintboessen.blogspot.com/2010/04/working-with-spns-and-sql-server.html
A screen shot of the results from the setspn (in CMD) and a screen shot of the SQL Service account in AD would be useful
October 29, 2010 at 6:51 am
Taken from http://www.sqlservercentral.com/blogs/robert_davis/default.aspx
Configuring Kerberos and Constrained Delegation
I've rambled on enough about what kerberos is. I want to get into actual problems and how to fix them, but there's one more area I must cover. The reason I'm going into all this information before-hand is that I feel kerberos, like boggarts, are easiest to handle when you understand what it is. One of your key tools for resolving kerberos issue is simply checking the configuration to make sure it is configured correctly. You'll need to understand how to configure kerberos and constrained delegation for setting it up and for figuring out why it doesn't work when it doesn't. In simple terms, here is what you need to know for setting up kerberos with constrained delegation:
* All SQL Servers involved must have valid SPN's
o SPN must be owned by the SQL Server service account
o SPN must match the servername being used to connect to the server
+ if using the FQDN, an SPN must exist for the FQDN
+ if using ServerName without FQDN, an SPN must exist without the FQDN
+ If using an instance name not on the default port, the port must be specified
+ If using a virtual name, an SPN must exist for the virtual name
o See Lesson 1 of this series for tips on troubleshooting invalid SPN's
* All SQL Servers involved and their service accounts must be in the same domain
o Kerberos cannot be configured cross domain
* The end user's account must not be marked as sensitive, unable to be delegated
* The relaying SQL Server's service acocunt must be granted privileges in AD to delegate credentials
When you experience kerberos failures, you typically won't realize it unless you try to perform a second hop. Generally, if kerberos fails, SLQ Server silently falls back to NTLM without raising an error or warning. NTLM does not allow credentials to be relayed, so when a double-hop is attempted, the credentials do not get relayed and the target SQL Server sees the user as anonymous. The following error will be seen in the SQL log of the relaying server if this occurs:
Error: 18456, Severity: 14, State: 11.
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: <end user's IP address>]
October 29, 2010 at 7:07 am
Just a little warning if you want to do this using a trigger :sick::crazy:
- a trigger is always executed "in transaction". If the trigger fails, your whole original transaction fails!!
This being said, if your Server B is down, your operations on Server A will be affected, unless you disable this trigger !! (If you enable this trigger later on, there is no way of "redo" the work it missed during this interruption time)
- I wonder how Server B will get to your data, modified in during your original transaction ?
((b)locking, isolation level, ...)
- Have a look how SQLServer Service Broker can help you provide all this needed information for your server B, without compromising Server A. SQLServer Service broker is a SQLserver to SQLServer message queuing system that can handle payloads and conversations. More info can be found in Books online.
Maybe even search SSC for some good article on SSB.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 29, 2010 at 7:30 am
Yes, this is a classic double-hop issue.
First, I recommend moving this out of a trigger and into a queued process. The trigger will cause problems because of transactionality. If you can, either move the trigger code into the insert proc, or move it into a queued process.
Second, I recommend setting up an SQL account that you can use specifically for the linked servers. Grant it the absolutely minimum rights to get the job done. That's going to be a LOT easier that setting up a kerberos solution, and easier to administer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 29, 2010 at 10:50 am
Wow. Thanks to all that responded. I think nobody at the client site will know how to implement Kerberos nor will they give me the authority.
I liked the idea about removing the remote procedure call from the trigger and putting it into a queue. In fact, I think I can write a stored procedure on Server B to check the commercial software's audit trail on Server A for new records every xx minutes using SQL Server Agent. I can queue it up there and the end result is that all processing will happen on Server B. Server A will just be queried by Server B. I believe this would solve the double hop.
Thanks guys for getting me over the roadblock!
October 29, 2010 at 10:53 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 1, 2010 at 10:43 am
GSquared (10/29/2010)
Yes, this is a classic double-hop issue.First, I recommend moving this out of a trigger and into a queued process. The trigger will cause problems because of transactionality. If you can, either move the trigger code into the insert proc, or move it into a queued process.
Second, I recommend setting up an SQL account that you can use specifically for the linked servers. Grant it the absolutely minimum rights to get the job done. That's going to be a LOT easier that setting up a kerberos solution, and easier to administer.
Kerberos isn't as bad to setup as it used to be.
At least now it's much easier to find good documentation.
A bigger challenge might be finding a Domain Admin and getting accounts to run SQL services under.
I agree with the other recommendations.
Maybe you might want to take a peek at this for future reference, just in case you might want to use it.
I also use Group Policy to cure 2 intermittent issues.
They set 2 keys in the registry to force Kerberos to use TCPIP, and another for the Max Token Size.
MaxPacketSize (1) and MaxTokenSize (65535)
It is nice once it's setup,I've found it very stable.
And when you say 'client', this might be a very valuable authentication method to know how to setup.
Or at least know when it could be one of the best options to use.
For example, using SharePoint / PerformancePoint / Reporting services consuming SSAS cubes, it might be one of the only secure methods to expose the cube data. You can easily have multiple servers in the mix, so 'double hop' doesn't even begin to describe how many hops are taken.
Greg E
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply