I have a problem. I configured a application role in my database. However, i need to do a query in other database in other server. I read in Books Online and i can do a query in another database on the same server if i grant access to user guest to destination database. But i need to do a query in another server (through linked server). The linked server is registered with "Be made using the login's current security context".
The user and role is registered on both server and databases with the same permissions.
Someone can help me?
I have two servers. Server A and Server B. On each server, i have a database named Corporative,the same users and logins with the same permissions and the same application role named 'controlsecurity'.
All users are not allowed to write data or execute objects. The permissions only are allowed to application role 'controlsecurity'.
When users connect server A, database corporative through query analyzer and try to do a query on server B, the following error message appears: Login Failed for user 'XXXX'
Scenario 1)User connect server A trought query analyzer 2)User execute sp_setapprole 'controlsecurity','password' to gain the role´s privilegies. 3)User execute select * from serverB.corporative.dbo.table 4)the following error message appears: Login Failed for user 'XXXX'
If user doesn´t execute the step 3, the query works correctly. It makes me sure that the problem is not linked server.
This is a problem with the combination of AppRoles together with your current Linked Server settings.
The linked server connection setting of "Be made using the login's current security context" can never work when a user's AppRole is ON, because the security context of an AppRole is database-only and it therefore suppresses or strips-out the Server-level security context, which is the "login".
In other words, AppRole user have no "login security context". Therefore than can not use a linked server through that setting.
Here is what I think your choices are to address this, you will have to decide which are acceptable for you:
Option #2 is typically what I would recommend a customer to try first, as it has the least collateral impact and side-effects if it can be made to work. Let me know if you want to pursue this approach...
(EDIT: Sorry, I missed that this was for SQL Server 2000. Options 3 & 4 will not work until SQL Server 2005.)
You may need to enable RPC for the linked server. This is on the server options page of the linked server properties.
answered Oct 15, 2009 at 05:30 AM
Melvyn Harbour 1 ♦♦