Linked server and Windows domain user.

I logon to SQL Server using windows user and I created a linked server to
another SQL Server in the same domain (My windows user can logon both server
with db_owner role). I tried to use "Be made using the login's current
security context"
However, I cannot connect to remote server
Error 18456: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I tried other options too either got the same error or the mapping windows
user says "User Domain\user cannot login". The only way I can access is to
create a log SQL Server account and mapping to it.
I also tried openrowset:
SELECT a.*
FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
Server};SERVER=server001;Database=pubs;t
rusted_connection=yes',
pubs.dbo.authors) AS a
ORDER BY a.au_lname, a.au_fname
and I got the following error:
Server: Msg 7303, Level 16, State 2, Line 1
Could not initialize data source object of OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server D
river][SQL
Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
Did I miss any steps to make remote linked server?The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000 and above, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation (2000)
or in the topic Configuring Linked Servers for
Delegation(2005)
-Sue
On Mon, 23 Jan 2006 11:46:03 -0800, "nick"
<nick@.discussions.microsoft.com> wrote:

>I logon to SQL Server using windows user and I created a linked server to
>another SQL Server in the same domain (My windows user can logon both serve
r
>with db_owner role). I tried to use "Be made using the login's current
>security context"
>However, I cannot connect to remote server
>Error 18456: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>I tried other options too either got the same error or the mapping windows
>user says "User Domain\user cannot login". The only way I can access is to
>create a log SQL Server account and mapping to it.
>I also tried openrowset:
>SELECT a.*
>FROM OPENROWSET('MSDASQL', 'DRIVER={SQL
> Server};SERVER=server001;Database=pubs;t
rusted_connection=yes',
> pubs.dbo.authors) AS a
>ORDER BY a.au_lname, a.au_fname
>and I got the following error:
>Server: Msg 7303, Level 16, State 2, Line 1
>Could not initialize data source object of OLE DB provider 'MSDASQL'.
>[OLE/DB provider returned message: [Microsoft][ODBC SQL Server
Driver][SQL
>Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
>
>Did I miss any steps to make remote linked server?