x

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Hi

I have a cross server view that is currently setup on SQL2000 and SQL2005 boxes that run fine. The SELECT of the view looks like this:

SELECT *
FROM OPENDATASOURCE('SQLOLEDB',
    'Data Source=MyServerName\sql2005;Integrated Security=SSPI').MyDatabaseName.dbo.MyTableName 

However when I transfer this to a SQL2008 server I get the following error:

Msg 18456, Level 14, State 1, Line 1
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Any suggestions why this doesn't work?

If I use openrowset as below instead it works ok - but a solution that means having the credentials in the view isn't an option:

SELECT *
FROM OPENROWSET('SQLOLEDB', 'server=MyServerName\sql2005;uid=MyUserId;pwd=MyPassword;database=MyDatabaseName',
    'select * from dbo.MyTableName')
Many thanks for your time.
more ▼

asked Nov 13, 2012 at 05:55 PM in Default

mjharper gravatar image

mjharper
1.4k 2 4 7

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

As @Kevin mentioned, you have the double hop issue as you are not using Kerberos authentication.

One option is the linked server with de default user and password (requires SQL Server authentication).

Other option is to properly setup Kerberos authentication so SQL Server can utilize it. There is post on SQLServerCentral - Configuring Kerberos Authentication.

Additional sources:
Understanding Kerberos and NTLM authentication in SQL Server Connections
Using Kerberos with SQL Server

more ▼

answered Nov 13, 2012 at 09:34 PM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

Many thanks to you both for these answers.
Nov 14, 2012 at 01:34 PM mjharper
(comments are locked)
10|1200 characters needed characters left

You've hit a double hop issue, it sounds like. Instead of doing OPENDATASOURCE, you could create a linked server connection. That way, you could have a default user ID and password put into the linked server, but the credentials wouldn't be visible to anybody.

more ▼

answered Nov 13, 2012 at 06:03 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1833
x10

asked: Nov 13, 2012 at 05:55 PM

Seen: 2137 times

Last Updated: Nov 14, 2012 at 01:34 PM