APP Security, cross database questions: Stored Proc + Execute As?

Question

I have an MS-Access Application with a SQL 2008R2 back-end.

The Application is attaching to database A for its data tables et. al. However, it's security authentication data tables are in Database B (on the same server instance).

I had intended to create a Stored Proc in database A to be used to query the security tables and return the application-specific login information to the Access application on startup, *AFTER* establishing a default connection to database A (this is a critical point as Access caches this initial connection internally along with its security credentials, allowing the use of "redacted"/"reduced" connection strings to use this cached connection without needing to store or reveal the security information of the initial connection in storing these connection strings subsequently in the application session, meaning that pass-thru query connection strings, dynamically added linked table connections, etc. don't reveal the userid/password info anywhere accessible).

The Application will be using SQL Server security for login to SQL Server and not Windows Authentication.

I had hoped to be able to use a user account on database B created "Without Login" to run the query on database B's authentication tables, but further reading on the subject of using Execute AS impersonation within the SP of database A suggests I'm barking up the wrong tree somewhat.

At this point my reading has me confused about how to setup the logins, database properties, trustworthy settings, etc.

I am a consultant working with the handicap of being "outside the IT firewall" here as well, so I have to make specific requests of what scripts to have other users who with SysAdmin rights run to achieve this desired authentication setup. We have significant lattitude with the Dev and Prod SQL Servers here, but still need to do this "quietly" so as not to awaken the sleeping IT giant in the room next door (who is a political monster in this case and will cause a great ruckus if disturbed/triggered).

Any/all advice/direction OTHER THAN "do it all in the same SQL Server database" (not an option for me here) is greatly appreciated.

Also, this procedure will be the template for a bunch of other existing Access DB apps which are about to be upgraded to use as well going forward.

---

Mark Burns, MCAD, MCP
Sr. Microsoft Access Analyst/Developer
Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)

Answer 1

So this article on my web site,
http://www.sommarskog.se/grantperm.html
should give you some ideas of the technical possibilities:
*  DB-chaining.
*  Certificate signing.
*  EXECUTE AS

Of these, I think certificate signing is one method where you can don't have to disturb the IT people, as long as your permissions are good for BACKUP CERTIFICATE. (And on SQL 2012, you would not even need that.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Erland,

I'm not seeing where that article directly answers my question though.

Perhaps I'm just not connecting the dots right in my head though.

Given DB-A and DB-B on the same server, if an authenticated connection to DB-A is made, but a DB-A stored Proc needs to query DB-B data, does the user (without login) need to be defined in DB-A, or DB-B, or both? If the same (identical) username is defined in both, does the ownership chaining pick up and connect the dots if the DB_Chaining is turned on between the two databases?

Or should there be another way to do this without DB_Chaining?

or...am I barking up the wrong tree entirely in thinking about using any DB User (without login) for these cross-Database Access/security configuration purposes (which is what I thought they were _for_)??

---

Mark Burns, MCAD, MCP
Sr. Microsoft Access Analyst/Developer
Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)

Answer 3

If you do

CREATE USER frits WITHOUT LOGIN

in two databases, that is two different users with no connection to each other.

I don't understand though how users without login comes into play here. You have users in DB-A and then you want them to be able to run a stored procedure in DB-B. Then you must add these logins to DB-B or enable the guest user in DB-B.

Ownership chaining could apply if the two databases have the same owner and DB_CHAINING is enabled. But you could also use certificates.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Erland,

Thanks for your insightful articles.

They did lead me, ultimately to the answer for my situation, though probably not in the way you thought/hoped.  :-)

http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/0415edf0-ecc1-491b-a74c-872c3d5c03e5

---

Mark Burns, MCAD, MCP
Sr. Microsoft Access Analyst/Developer
Manager LinkedIn.Com community: Professional Microsoft Access Developers Network (PMADN)

Answer 5

Seems like you found the best solution.

---

Erland Sommarskog, SQL Server MVP, [e-mail]