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.
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
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 People are following this question.
