1

I don't have access to the Active Directory settings, nor do I have access to change anything on the linked server.

From everything I've read, it seems like this means I cannot use Kerberos - which is a big problem, because I don't know how to use a linked server without it.

Is there any way to connect to a linked server without Kerberos? (or some way to enable Kerberos without admin in AD?)

Exact problem description

When I connect to the linked server while sitting in front of my server, it works fine; but when I try to connect to the linked server from any other computer (delegating through my server), it gives the error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

It seems that this is the "double-hop problem," and the usual solution is to enable Kerberos, which requires access to AD and the linked server.

I get the same error when I set security to "Be made using the login's current security context," and I can't use "Be made using this security context" because that appears to use SQL-authentication (which is not enabled on the linked server) instead of NTLM

Improve this question
2
  • can you post how your linked server is set up?
    – Thirster42
    Apr 27, 2010 at 15:48
  • @Thirster: What do you mean? I added a linked server in SQL Management Studio, but no matter what options I choose, it refuses to work due to the double-hop issue.
    – BlueRaja
    Apr 27, 2010 at 16:08

4 Answers 4

Reset to default
4

This is indeed a case of constrained delegation (ie .'double hop'). There is no setting you can change which would make this work. If one would exist, it would be by definition a bug, as it would break the domain policy on constraining delegation only to trusted accounts. So unless you make the AD change to mark the SQL Server 'in the middle' as trusted for delegation, you won't succeed doing a double hop. And any other work around I would give you would put me in a position to help you violate your domain policies. Since it seems you understand the technical problem, I'd recommend you knock at the right doors: the masters of your domain and/or the stakeholders of your requirements.

Improve this answer
5
  • Well that's goofy - I could easily write a script to query the server under my account. This security policy doesn't protect anything at all - it only needlessly makes perfectly valid things more difficult for users.
    – BlueRaja
    Apr 27, 2010 at 18:19
  • Wrong. You'd query the server under your account, you could not fool some user to authenticate with you and then, w/o anyone's approval, impersonate that user and go on the network, like open his mailbox account. Which is what the policy protects. 'Trusted for delegation' means exactly that: the process foo running as user bar on machine foobar is trusted to impersonate any user and forward these accounts to a specific resource.
    – Remus Rusanu
    Apr 27, 2010 at 22:40
  • I don't care about impersonating the user, I just want to query the server. Why can't I just give it my authentication?
    – BlueRaja
    Apr 28, 2010 at 16:52
  • 'Give it my authentication' means, in technical terms, impersonating the client. You better consult with a professional since your understanding of this domain is shaky at best. Enabling trusted delegation is not exactly the end of the world, any admin know how to do it and can fix it in 1 minute for you.
    – Remus Rusanu
    Apr 28, 2010 at 17:24
  • "You better consult with a professional" - Er, isn't that what I'm doing? :)
    – BlueRaja
    Dec 4, 2011 at 9:22
0

You can use sp_addlinkedsrvlogin to map a Windows login on the host SQL Server to a SQL login on the remote server. This does not work for Windows groups.

Improve this answer
2
  • As I said, the linked server does not have SQL logins enabled, and I don't have access to change that. Isn't there someway I can just provide my normal AD credentials to SQL Server and have it use those to log into the linked server?
    – BlueRaja
    Apr 27, 2010 at 14:45
  • Sorry, missed the bit about SQL logins being disabled.
    – NULLgarity
    Apr 29, 2010 at 21:13
0

In the linked server dialog box, on the security section, do you have "Be made using the login's current security context" selected?

Is your windows account set up on the server you're trying to connect to?

Improve this answer
2
  • As I said already, that option does not work, and we are using Active Directory to handle logins.
    – BlueRaja
    Apr 27, 2010 at 16:52
  • is your windows accoutn set up on the second server?
    – Thirster42
    Apr 28, 2010 at 16:48
0

to use a linked server without kerberos the only option is SQL Logins. if that's not an option you should really try to fix up kerberos auth.

You have to figure out which account runs the sql server service. if it is a domain account, you should ask for someone to register the SPN for the user.

if you have access to any windows 2008 server on the domain with a domain user, you should run this CMD command to check what is register already (read access is allowed).

setspn -l [sqlservicedomainaccount]

you should check for setspn -l sqlmachinename and see if it is registered, and the sqlserviceaccount. Since this registries should not have duplicated items you first need to remove and then register the new one.

Then ask for someone in the network to register this spn's (as the service needs to be restarted after the register you should make this only on a time window)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .