Distributed Query Using NT Auth

From: Dan H (DanH_at_discussions.microsoft.com)
Date: 08/31/05 

Date: Tue, 30 Aug 2005 16:50:03 -0700

I'm trying to get a distributed query to run from Query Analyzer on a client
workstation. I'm getting the following error:
Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.

See more details below..

Can you give me any advice on how to make this work?
Thanks,
Dan

Background:

Server1 (Middle): SQL Server 2000 SP 4 running on Win 2000
- Both SQL services running as serviceacct1

Server2 (Back End): SQL Server 2000 SP 4 running on Win 2003
- Both SQL services running as serviceacct2

Note that the distributed query works fine when run using QA via remote
desktop on Server1.

Have done the following:

For serviceacct1, set to "Trust this user for delegation to specified
services only", Use Kerberos only, Services to which this account...:
MSSQLSvc Server2 1433
MSSQLSvc Server2.xxx.xxx.xxx 1433

Ran the following:

setspn -l "serviceacct1"

setspn -A MSSQLSvc/server1:1433 "serviceacct1"

setspn -A MSSQLSvc/server1.xxx.xxx.xxx:1433 "serviceacct1"

setspn -l "serviceacct2"

setspn -A MSSQLSvc/server2:1433 "serviceacct2"

setspn -A MSSQLSvc/server2.xxx.xxx.xxx:1433 "serviceacct2"

For Server1 computer, set to "Trust this computer for delegation to
specified services only", User Kerberos only, Services to which this
account...:
MSSQLSvc Server2 1433
MSSQLSvc Server2.xxx.xxx.xxx 1433

SQL Server Linked server on Server1, named "Server2" is set as follows:
For a login not defined in he list above, connections will:
Be made using the login's current security context

Relevant Pages

  • Remote Indexing / Distributed Query
    ... I'm trying to access an Indexing Service located on a remote machine using ... On the first server, the Indexing Server is up & running; ... the Indexing Service is stopped. ... distributed query facility is limited to the server where both SQLServer and Indexing ...
    (microsoft.public.sqlserver.fulltext)
  • Re: faster joins
    ... A Distributed query is a query which involves databases on different ... With in the same server will the query be treated as ... > "Kevin Spencer" wrote in message ...
    (microsoft.public.dotnet.framework.aspnet)
  • Stored Procedure Using Remote Connection gives error in Agent Job
    ... It uses a distributed query through a remote server to a old Oracle RDB database. ... I want to use this query in a SQL Server Agent job step to control whether or not an incremental roll-up procedure kicks off each day or not. ...
    (microsoft.public.sqlserver.programming)
  • Re: faster joins
    ... With in the same server will the query be treated as ... distributed query. ... "Kevin Spencer" wrote in message ... >> I have two databases with in same sql server. ...
    (microsoft.public.dotnet.framework.aspnet)