June 21, 2007 at 5:49 am
Another thing I'm wondering if anyone's noticed. I'm part of a DBA Windows group. This group (We'll call it Win_DBA for now) is mapped to all our servers and set up as SysAdmin on all the servers.
Yet, when I create a Linked Server to Server B on Server A and choose the "Login using the user's current security context", then try to use that linked server in my queries, it won't let me connect. I keep getting the error message: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
But if I change the security to "Be made using this security context" and put in a SQL Login & Pwd, the connection will work.
It's worth noting that I can't put in my personal Windows login name & pwd because I don't have a user / login account mapped to SQL Server except through the Win_DBA. And if I try, my login will fail every time. So, the questions are:
Does Linked Servers not allow pass-through security for Windows User Groups?
Has it ever allowed this behavior?
Does this behavior frustrate anyone else? I'm interested in hearing everyone's opinions. Thanks!
June 21, 2007 at 5:55 am
This is a HOP problem. might be one of your servers are running under local system account or server are running under local computer account than domain users account. Change your sql server services to run under domain user account then you will be able to use windows authentication in linked server with current security context mode. Look for double hop in this website you will find an interesting article.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 21, 2007 at 6:09 am
Actually, all the servers are running under a domain user account and they should be (I'll have to check) running under the same one. We have to use domain user accounts for our services because we do so many cross-server things.
June 21, 2007 at 6:39 am
If you use different account then its not possible to have currenty security context with windows mode. Or check if you can impersonate your domain service accounts so that they validate against each other.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 21, 2007 at 7:46 am
I double-checked. Only one of my servers is using a different domain account than the other 3, and that wasn't the server I was trying to link to. The other servers all use the same domain user account. So this can't be the problem.
And Corporate won't give us the password to the DU account the services are running on, so I can't use that in the linked server setups.
Any other thoughts?
June 21, 2007 at 10:53 pm
If you are confident that all your servers to be linked runs under the same domain account and permissions are there and still want to use 'current security context mode' you can raise a ticket with microsoft and get this one resolved. Whats the type of connection its trying to make NTLM or kerberos, if NTLM you will get this error.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 22, 2007 at 4:27 am
I can hardly find anything in BOL about either NTLM or Kerberos. What I can find seems to indicate this is a server setting, not a SQL setting. Any thoughts on how I can verify which of these my servers are using?
Also, why would NTLM error out? I can't find anything in BOL regarding Linked Servers and NTLM not liking each other. The closest I can find is this quote "If security account delegation is not available on the client or sending server, or the linked server/provider does not recognize Windows Authentication Mode, self-mapping will not work for logins that use Windows Authentication."
This does NOT read to me as if it's a network method causing the problem. And since our servers are all set up for Mixed Mode authentication and they are SQL servers (not Oracle / DB2 / Progress, etc.), they should be able to recognize Windows Auth Mode. Am I missing something obvious?
June 22, 2007 at 7:36 am
Brandie, read the link below to know thing about HOPPING in sql
http://www.sqlservercentral.com/columnists/bkelley/qa2k_1.asp
in sql 2005 yu can use sys.dm_exec_coonection to find what's the type of connection is being made. It can be NTLM or KEREBROS.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 24, 2007 at 11:40 pm
Brandie,
Check if SPN is set correctly for this computer.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply