Linked Server permission question

  • A userA has excecute permission on a stored proc procA that runs on server1. In the procA there is a use of the linked server that talks to the server2

    The user was getting an error

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON

    To resolve this I had to go to the Security tab of the linked server on server1 and add the user so that his log in is mapped to the remote user LINKuser that we had setup when we created the linked server.

    Is there a better way of doing this? For .e.g. in the code itself somewhere in the procA ?

    Any suggestion would be greatly accpeted and thank you in advance.

  • It looks like you are using a website to execute the stored proc. Another option would be to setup the application pool in IIS to use a domain account and provide the domain account the appropriate permissions.

  • no, the application is a vb.net Winform apps

  • This probably falls under the Kerberos "Double Hop" problem. When you log into a server (connect with a client application) the server authenticates your login with the active directory. If you then try to connect to another server from there like an application server connecting to the database that is the "first hop". If you then try to use a linked server to connect to a 3rd windows server that second hop will fail due to the 3rd server being unable to determine if it trusts the login. The fix for this involves getting kerberos authentication configured correctly to allow impersonation of logins between servers.

  • A userA has excecute permission on a stored proc procA that runs on server1. In the procA there is a use of the linked server that talks to the server2

    The user was getting an error

    Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON

    Is it recurring? Do you get this error in SSMS?

    To resolve this I had to go to the Security tab of the linked server on server1 and add the user so that his log in is mapped to the remote user LINKuser that we had setup when we created the linked server.

    You don't need to map all users to Linked Server. Few users (Regular and Privileged) are sufficient. The owner of the procedure should have sufficient rights for Linked Server. The caller would still be able to get what they want from Linked Server.

    EXECUTE AS Clause (Transact-SQL)

    http://msdn.microsoft.com/en-us/library/ms188354.aspx

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply