SQL 2000: Application Role x Linked Server

Friends

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?

Edit

Lets go!

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.

asked Oct 14 '09 at 04:10 PM in Default

Felipe
262 ● 21 ● 23 ● 23

Can you give us a few more details on what's actually going wrong? I've got a certain amount of experience of this (SQL Server Central has two linked database servers that query databases on each other).

Oct 14 '09 at 04:16 PM Melvyn Harbour 1 ♦♦

Felipe: what do you mean by "If user doesn´t execute the step 3, the query works correctly"? Step 3 is the query.

Oct 18 '09 at 03:13 PM RBarryYoung

(comments are locked)

4 answers:

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:

Change that Linked Server setting. I think that you can get that to work (about 80% sure).
Keep the linked server "current login security context" as the general default, but add a specialized setting just for your AppRole users. I am less sure if this can be made to work, say about 60% sure.

Set the DB to "Trustworthy" and then use stored procedures with certificates to access the linked server, using the certificates to give the sProc a temporary server-level security context (I am about 40% sure on this one).

Temporarily REVERT the AppRole, access the linked server, then reapply it. I am about 90% sure that this will work security-wise, however, it's pretty restrictive for your users, so it may not be acceptable for you. For instance I doubt that a query that joins a local table to a linked-server table could be made to work with this technique, other than by manually copying the remote table to a local #temp table first.

Use Replication to present the remote tables rather than a linked server. I am sure that this can work, but obviously it is a lot of work to set it up and brings a huge amount of baggage with it (like 2x-4x the diskspace).

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.)

answered Oct 18 '09 at 03:34 PM