Login failed in Linked Server - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-08 : 03:22:40

Suppose server1 is linked to server2 and the linked name is ‘server2’. I login to server1 via my windows account through windows authentication, when I try to read data from server2 through: select * from server2.server2.table1
I’m given this error:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Any idea is appreciated

lepeniotis
Yak Posting Veteran

75 Posts

Posted - 2008-10-08 : 04:49:46

Have a look at this
http://support.microsoft.com/kb/839569

When you use linked servers to access external data sources, you should pay special attention to the security context for the external connection. You can Configure the linked servers to use one of the following three security modes:
Self mapping when a linked server is created, this mode is added for all local logins, so SQL Server tries to connect to external data source using the current user’s login credentials. The same login and password must exist on the remote Server. This is the default behaviour.
Delegation – This mode impersonates the windows local credentials; the connection forwards the credentials of an authenticated windows user to the linked server. The windows user account and password must exist on the linked Server.
Remote Credentials – This mode lets you map local logins on the external data source.
Delegation of operating system logins is the securest mechanism.

MSc Advanced Computing Science
MSc Database Professional
Sheffield Hallam University
MCP (70-229, 70-228)
Industrial IT Engineer

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-08 : 07:59:52

For windows account to work, you should set up Security account delegation and they should be trusted. Try with sql login which is what we use for linked server.

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-11 : 03:03:03

Well...I wish I could try SQL login but IT department doesn't let me do that. All servers in our company are available through windows account.
By the way, I'm trying set up delegation under windows account
any further comment is appreciated

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-13 : 08:00:34

quote:
Originally posted by lepeniotis

Have a look at this
http://support.microsoft.com/kb/839569

When you use linked servers to access external data sources, you should pay special attention to the security context for the external connection. You can Configure the linked servers to use one of the following three security modes:
Self mapping when a linked server is created, this mode is added for all local logins, so SQL Server tries to connect to external data source using the current user’s login credentials. The same login and password must exist on the remote Server. This is the default behaviour.
Delegation – This mode impersonates the windows local credentials; the connection forwards the credentials of an authenticated windows user to the linked server. The windows user account and password must exist on the linked Server.
Remote Credentials – This mode lets you map local logins on the external data source.
Delegation of operating system logins is the securest mechanism.

MSc Advanced Computing Science
MSc Database Professional
Sheffield Hallam University
MCP (70-229, 70-228)
Industrial IT Engineer

Dear lepeniotis

Your comments led me to setup delegated Linked server through this article: [url]http://www.databasejournal.com/features/mssql/article.php/3696506[/url]
But I encountered another problem:
all of our servers are connected as workgroup, while it seems delegate requires domain. I talked to IT manager to see if it's possible to change servers from workgroup to domain but he refused.
Is there any other solution for my problem?

lepeniotis
Yak Posting Veteran

75 Posts

Posted - 2008-10-13 : 09:30:52

Hi Peace2007 did you use Kerberos? I think that you need intergraded authentication or NTLM.

MSc Advanced Computing Science
MSc Database Professional
Sheffield Hallam University
MCP (70-229, 70-228)
Industrial IT Engineer

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-14 : 01:19:55

I need to choose Kerberos via Active Directory but Active Directory cannot be installed cause we have workgroup. I'm not so skilled in OS, that's what I'm said.
Is there any other way to set it to use kerberos?

Thanks for your replies lepeniotis

lepeniotis
Yak Posting Veteran

75 Posts

Posted - 2008-10-14 : 04:05:47

No problem mate the thing is to find the solution

!

When the authentication type of an endpoint is specified as integrated, the endpoint will respond with either NTLM, depending on which type the client uses in requesting authentication. No other authentication method will be used if authentication fails.
Kerberos authentication is supported by windows 2000 and later. It is not supported by windows 98 and Windows NT 4.0.
NTLM authentication is supported by Win 98 and Windows NT 4.0. It is not as secure as Kerberos but Windows as I said before it is not supported by windows 98 and Windows NT 4.0.
Digest authentication is more secure than basic authentication but is not as secure as NTLM or Kerberos.
Basic authentication uses easily decoded base64-encoding and should only be used as a last resort unless the user that is granted permissions to the endpoint is a local user on the server computer itself.

I don't know if some of the above are fit to the workgroup network but if you have time you can try.

Regards,

MSc Advanced Computing Science
MSc Database Professional
Sheffield Hallam University
MCP (70-229, 70-228)
Industrial IT Engineer

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-10-14 : 04:08:07

I couldn't apply any change but when trying to read data from the linked server, I'm getting this error now:
OLE DB provider "SQLNCLI" for linked server "srv2" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "srv2" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "srv2".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources