July 10, 2002 at 7:13 am
Hello,
I currently have two servers: test and production. Test is linking to production with the connections to be made using the login's current security context.
On the test server, I have my own Windows login, which is a SQL Server System Admin. I am also a member of a Windows group, which is also a SQL Server System Admin. On the production server, only the Windows group has a login and is a System Admin. I do not have my own Windows login in production.
The problem I am having is that when I am connected to test in Query Analyzer and try to access a table in production, I receive this error:
Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I did some research but am not 100% sure that I found the reason for the error. I am thinking that in order for me to access production through test, I need to have Security Account Delegation, which means I must have Active Directory.
Am I correct? If not, can someone please shed some light on what is happening here.
Thank you,
Melanie
July 10, 2002 at 7:29 am
I think thats exactly right. Havent run through it all the way myself, usually use a sql login when I need a linked server for testing. I think BOL says you have to create the principal name, but I saw something later that said SQL2K creates it automatically.
Andy
July 10, 2002 at 7:42 am
You are running Query Anaylzer from a client, yes? In other words, your workstation and not directly on the test server?
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 10, 2002 at 8:08 am
Hello,
I am running Query Analyzer from my workstation, which is not the test server. I did, however, log into the test server (via Terminal Services) and run the same query to access the table in production. I was able to obtain the resultset.
Does this mean that my problem is not the linked server permissions?
Thank you,
Melanie
July 10, 2002 at 8:28 am
If you are on an NT 4 domain, it's a limitation of NTLM, the security model that's in place with NT 4.
NTLM, by design, prevents what is known as a double-hop. For instance:
I authenticate via an NT login on Computer A.
I can then connect to Computer B using that authentication. That's a single hop:
A -> B
However, what I can't do is connect to Computer B from Computer A, then try and connect to computer C from Computer B. That's a double hop:
A -> B -> C
Like I said, this is by design in NT 4 and NTLM. Kerberos allows the delegation, which means you can do the A -> B -> C thing.
Under NT 4 there are a couple of workarounds. The first is to run QA on the server, either by logging on at the console or by doing the equivalent, such as with Terminal Services. The second is to write the query and stick it in a SQL Server job. In both cases we're dealing with a single hop, which is why it works.
Hope this helps.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 10, 2002 at 10:32 am
Hi Brian,
We are not an NT domain. So would this mean that the initial thought of Security Account Delegation is correct? I need this in order to perform a double hop in a 2000 domain?
Thank you,
Melanie
July 10, 2002 at 10:38 am
Ok, now I'm confused, but that happens easily, so please forgive me.
Are you running under Active Directory? If not and you are using Windows authentication, you are almost certainly under an NT domain (or a domain structure of multiple domains).
There are a couple of articles on setting up security delegation. I'll see what I can find for links.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 10, 2002 at 12:10 pm
This makes sense. The localsystem account has administrative rights to the local server and nothing more. To be able to authenticate to a network resource, a domain account is required. For instance, to copy files to or from a network share, an account the network share can validate is required. Localsystem isn't such an account.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 10, 2002 at 7:20 pm
Hi Brian,
I apologize... You are not confused. I was the confused one, as I failed to see that we are still in an NT domain and NOT 2000 (as we are not running AD). Thank you for your follow up.
In great appreciation,
Melanie
July 10, 2002 at 9:08 pm
We're in the same situation. NT 4 Domain Controllers, but with a mix of Windows 2000 and NT 4 Servers. Every new server we purchase is Win2K.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 12, 2002 at 12:23 pm
Boy now I am confused... At first I thought this was rather straight forward now I am not so sure. I am in a windwos 2000 domain running SQL2K. On my server I set up a link to another server and specify SQL credentials on the second page of the linked server properties. I am then able to query the linked server from my desk. I use an alias and can fully qualify the server as ALIAS.DBNAME.DBOwner.Table. Works great and have had little problems making it work.
If one was using Windows Authentication I would expect there could be a lot of issues. If I was using Windows Authentication I would definitely have the concept of a Domain Service Account on the servers and run the services under this account for all the servers.
Bruce Szabo, MCSE+I, MCDBA, MCSD
Bruce Szabo, MCSE+I, MCDBA, MCSD
July 12, 2002 at 5:02 pm
Under NTLM, double hop is prohibited by design. One of the work arounds is to set up the linked server connection using a SQL Server login. In this case, you aren't trying to apply NT authentication across two hops. Works like a charm.
The only catch is if the second server is NT authentication only. Then there is a problem if Kereberos and delegtion isn't in the picture. At that point, the query has to be executed locally to the first server, not through a client. It's not the services and the accounts they run under, rather, it is the choice of NT vs. SQL Server login for the linked server.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply