question

mjharper avatar image
mjharper asked

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.
sql-server-2008cross-database
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Pavel Pawlowski avatar image
Pavel Pawlowski answered
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][1]. Additional sources: [Understanding Kerberos and NTLM authentication in SQL Server Connections][2] [Using Kerberos with SQL Server][3] [1]: http://www.sqlservercentral.com/articles/Security/65169/ [2]: http://blogs.msdn.com/b/sql_protocols/archive/2006/12/02/understanding-kerberos-and-ntlm-authentication-in-sql-server-connections.aspx [3]: http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Many thanks to you both for these answers.
0 Likes 0 ·
Kevin Feasel avatar image
Kevin Feasel answered
You've hit a [double hop issue][1], it sounds like. Instead of doing OPENDATASOURCE, you could create a [linked server connection][2]. 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. [1]: http://nizamettinozpolat.blogspot.com/2012/01/opendatasource-and-nt.html [2]: http://msdn.microsoft.com/en-us/library/ms190479.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.