SQL Server Authentication

Set-Up
I have an n-tier application, security is implemented in the business layer not in the database. So it is a pretty neat design :-)

I use integrated authentication to make sure we can audit database access and use triggers to store information about who last updated a certain record - this also helps debugging in Profiler, because you can track all sessions back to the user.

The technical set-up is two servers, one running IIS 6.0 and one running SQL Server 2000/2005 (I have the choice).

Problem
Since users are allowed to connect to the database and have access to all tables, they could circumvent security by connecting to the database directly, e. g. using MS Access, Excel, etc. As all security is in the application, this would give them full access to all information.

Potential Solutions
Put security into the database
Does not work for me for three reasons:

• the complex security model we have, if we could implement it on the database level, would impact performance considerably
• maintenance of security in the database is very hard, by putting it into the application we can use AzMan (Authorization Manager) to administer security, so it does not require a programmer to do this.
• I like clean design, and security just belongs into the business logic layer

Shutdown SQL Server network services and connect to the database using shared memory instead
This would work, but it requires IIS and SQL Server to run on the same machine, and my experience was that this slows down the machine considerably. Also, for administration, DBAs and programmers would always need to log on to the server itself, they could not use Enterprise Manager running on their own machines.

Use a dedicated application account and dump integrated authentication
This would prevent us from auditing and other things that I described above and which I value too high, I cannot dump this.

This proposal was then detailed further by the person who had suggested it, the application should call a stored procedure to tell the database who the user is.

I still did not find this solution convincing; a bug in the application could break the tracking mechanism, auditing and using Profiler would still not be possible.

Final Solution
I knew that Oracle uses something called "Proxy Authentication", which connects to the database with an application-specific account. Once connected it will run in the user's context, so the user name is known in the database.

So I was looking for something similar in SQL Server, and finally found that it does exist: you can create application roles in a database (not on the server level, but that's OK), these roles are protected by a password. Access to tables is not granted to users, but to the application role only. Users are only granted connect rights.

The application will connect to the database using integrated authentication, and before accessing any tables it will need to call sp_setapprole and pass the name and password of the application role.

After activating the application role, the variable USER will contain the role name, while SYSTEM_USER will still contain the real user's name.
Now users could potentially still connect to the database but would not see any information.

Implementation of this is extremely easy, just call sp_setapprole when you open the connection, which, if you designed your application well, is in a single place.

I am now even considering to grant connect rights to all Domain Users, so security can be controlled in a single place via AzMan.