October 12, 2009 at 10:19 am
I use a SQL Server account "AppOrders" for my application to connect to a SQL2005 database on "ServerA" and execute stored procedures. AppOrders has Execute only permissions on the application's schema in the local database, but does have impersonation rights to another SQL account "AppProxy" which does have full rights to the schema objects, and all of the application SPs are compiled including With Execute As 'AppProxy'
In one particular SP, a select query is run against a linked SQL2005 server "ServerB". Identical AppOrders and AppProxy accounts also exist on ServerB with identical passwords to those on ServerA. The Linked Server is defined on ServerA (to ServerB) with the "Be made using the login's current security context" option.
When I remove the With Execute As 'AppProxy' directive from the SP (and add the necessary security permissions for AppOrders), it runs just fine, but with the directive in the SP, it fails with an error stating Login failed for user 'AppProxy'
If the passwords are identical on the two boxes, you should not have to set up any custom mapping in the linked server correct? What would generate this error message? When running the SP "with execute as", what security credentials are passed to the linked server? Any help or suggestions would be appreciated!
October 12, 2009 at 8:29 pm
That's an interesting issue. I'd run profiler against the linked server and see what credentials are being passed.
Can you post your linked server setup?
I don't have system to test on right now, but will try to test tomorrow when I am in the office.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 13, 2009 at 7:59 am
Thanks for the reply...
The LinkedServer script run on "ServerA" was:
EXEC sp_addlinkedserver 'ServerB', N'SQL Server'
EXEC sp_serveroption 'ServerB','data access','True'
EXEC sp_serveroption 'ServerB','rpc','True'
EXEC sp_serveroption 'ServerB', 'rpc out','True'
EXEC sp_addlinkedsrvlogin 'ServerB', 'True'
As for the Profiler Trace, it does appear that the select statement accessing the linked server is being run as "AppProxy", and in a trace on the linked server, the LoginFailed event is logged as "AppProxy", so it appears this "impersonated" account is being used on both sides.
Now why is it not "auto-mapping" when I've verified that the password is identical on both servers?
October 13, 2009 at 2:42 pm
Okay, I did a little bit of testing and I can't seem to get it to work without specifically mapping the local login to the remote login as part of the linked server setup when using a SQL Server login. When I did this:
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'Linked Server Name',@useself=N'False',@locallogin=N'linked_server_test',@rmtuser=N'linked_server_test',@rmtpassword='########'
It worked.
It appears, and I don't have any links for this, that, when using EXECUTE AS, all that is needed for login using a SQL login is not passed across to the linked server, so you need to set it up explicitly.
I have tried with a Windows login as well and, since I am not a domin admin, I can't get that to work either as delegation has to be setup.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 13, 2009 at 2:48 pm
Thanks again Jack. You've been a big help in confirming that I'm not just doing some little thing wrong.
I've come to the same conclusion today - that when using "execute as" in your SP, the auto-mapping of SQL accounts does not seem to work properly. I still don't understand why the explicit mapping is necessary, but it is working in my setup with the mapping declared as well, so I will chalk it up to being a "with execute as" limitation.
October 13, 2009 at 7:58 pm
I wonder if either signing the procedure or using a synonym would help. I doubt it and I can't try it until Thursday at the earliest as I will be in a seminar tomorrow.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply