I have an application running in IIS which connects to a SQL Server 2008 R2 instance using windows integrated authentication. This application does simple read/write operations in the db using a set of stored procedures. I can restrict the privileges in SQL server quite well for this login/user combination.
But for a small subset of the application I need "elevated"/more powerful permissions in the database ... for example creating/dropping event notification and possibly queues and broker services.
So basically I have a single process with code running under the same user account connecting to SQL server, and I need two different sets of privileges ... one connection with very restricted permissions, one connection with more powerful privileges.
I would like to use windows integrated authentication ... sql authentication with two user/password combos is not an option.
Is there a recommended way to achieve this?
- Impersonation in the application code, connecting to SQL Server using integrated security from an impersonated context (different user account/sql login) don't like it since I have to manage a second login/password
- SQL server application roles (requires a application provided password I believe) - don't really like it since I need to store passwords
- SQL authentication with two users - not an option
- create dedicated stored procedures for the "elevated stuff" and use
execute as
(should work, however, not sure whether I can create stored procedures in the target db) - complicated idea: start the application process, connect to the db, restrict the application token and then open the less privileged connection (would that work?)
- use COM+ (a la Keith Brown's protocol transition), WCF service, or second process for the "elevated" parts of the application (too complex)
I suppose there is some simple and neat solution which I am missing ...