Is there a way to use Active Directory pass-through security to SQL Server 2005 and disallow types of connectivity, such as ODBC

Question

We have an internal application that is using a service account and fine-grained table and field control is controlled by permissions tables in the application.

If we wanted to use pass-through security with Active Directory, we have a problem in that a user with odbc access could modify tables outside of business rules that exist in the application.

Is there a way to enable pass-through security, but disable odbc access.

Answer 1

You could use stored procedures and only give the rights to the stored procedures, ensuring either that the basic business rules cannot be violated (if they are simple and unchanging, they can go in the SPs) or at the very least (if they are complex or changing, they don't work well in SPs), all modifications have to go through stored procs. If they make an ODBC connection to the database, they could execute the stored procs to make changes, but if you give select-only rights to the tables or views, then they cannot do it through an Access linked-table for instance.

Answer 2

Logon triggers could be possible, if it's SQL 2005 +.

• Use HOST_ NAME or APP_NAME to limit who the connection to a server or application
• Use sys.dm_ exec_ sessions to test "client_ interface_name"

Answer 3

You could use application roles http://msdn.microsoft.com/en-us/library/ms190998.aspx in order allow you to block modify access unless the application role is set.