Switching execution context inside logon trigger

Question

I have a database called MyDB in a Microsoft SQL Server 2008 Express instance using mixed mode authentication. The application using the database MyDB currently connects using Windows Authentication, using the current user's Windows credentials. This login is a member of the 'public' server role, and has a user mapped to it in the MyDB database. This database user is a member of the db_datareader and db_datawriter database roles.

What I would like is that when the application connects, it has permissions to read and write in MyDB. But when another application connects using the same login, it should only be allowed to read.

My thought was that I would create a logon trigger, which would check the application name part of the connection string, and based on that decide if the execution context should be switched. (For the record, I know that it is in no way secure to rely on the application name of the connection string, and that it is very easy to circumvent. The purpose here is not to secure the database, but to help users avoid changing data when they connect using another application, such as Microsoft Excel)

I have created a new login called 'myapp_reader' mapped to a user in the MyDB database, which is a member of db_datareader.

I then tried creating a logon trigger with the following TSQL:

CREATE TRIGGER CheckUser
ON ALL SERVER
AFTER LOGON AS
BEGIN
IF APP_NAME() <> 'My Application Name'
    BEGIN
        EXECUTE AS LOGIN = 'myapp_reader' WITH NO REVERT
    END
END

But unfortunately, it doesn't work. When I try to connect I get the following error:

Logon failed for login 'MyComputer\MyWindowsUsername' due to trigger execution.
Changed database context to 'master'.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

And when I look in the errorlog it says:

Error: 15590, Severity: 16, State: 1.
Can only use the 'No Revert' or 'Cookie' options with the 'Execute As' statement at the adhoc level.
Error: 17892, Severity: 20, State: 1.
Logon failed for login 'MyComputer\MyWindowsUsername' due to trigger execution. [CLIENT: xxx.xxx.xxx.xxx]

Does this error mean I cannot permanently change the execution context in the logon trigger?

Answer 1

I don't think its possible to change the execution context for the entire session. You could create a DML trigger for INSERT, UPDATE, and DELETE for every table/view in your database which does a rollback for a certain app_name(). You could write a procedure to automate the creation of all of these trigger.

Alternatively, if you had the option of having applications such as Excel connecting via a linked server then you could change the execution context at this point. And create a logon trigger that rolls back the connection if users try connecting via Excel or other apps directly to the server.

Answer 2

Assuming that you have control of the application and can modify it, then application roles will do exactly what you want. See sp_setapprole in Books Online to get started.

Answer 3

You can't do this in the fashion you want.

• A user connection has the same credentials throughout except for some specific scopes using EXECUTE AS.
• You have realised you can't rely on APP_NAME() or HOST_NAME() to detect when someone connects differently, which means a rollback trigger per table can't rely on there
• Your app relies on direct table write access

Some options I can think of...

• Your app uses stored procs, users have read access only to the tables
• Use SET CONTEXT_INFO in your app to set a "secret" key for rollback triggers
• Change your app to use a service account/is a windows service/etc and proxies the username in (as a web page would do)
• ... or some permutations therof

Answer 4

You really need to make up your mind about how to organize and manage credentials first.

If you use sp_setapprole that will circumvent Windows authentication and allow access through that app for any user. If that's what you really want to do then if the app is a server create user account for that app and run it under that user's credentials.

If it's a client app then make a web service which will only read and send particular data that the app needs and run that web service under new account. Then, in IIS7 you can put an ACL on the web service itself so that it's still protected.

Also if that app is not trusted to be clean and know what it's doing then request that it has to be code reviewed before it's allowed to touch SQL server. If it's your own app then start trusting yourself :-)