Is it possible to deny access to SQL Server from specific programs?

Question

Currently one of our databases (SQL Server 2008) is accessed via a number of different mechanisms: .Net SqlClient Data Provider; SQL Server Management Studio; various .Net applications and 2007 Microsoft Office system (basically Excel).

I see that in the sys.dm_exec_sessions DMV it is possible to see the program name of the program accessing the database for the current sessions. My question is: is it possible for one to deny access from a particular named program? First prize would be if this could be done for any named program, but we would gain a great deal of benefit from being able to deny access to this specific database from all Microsoft Office applications (especially Excel).

Answer 1

It is NOT possible and all claims to contrary are snake oil.

While is true that you can check the application name and create login triggers that deny logins based on this property, the application name is not a secure property and can be easily forged by anybody. Reliance on it for security (ie. login denial) is #fail.

So as long as you lower your bar and remove terms as 'deny access' from you question, it is possible to provide a Logon Trigger that inspects the session's program_name in sys.dm_exec_sessions:

CREATE TRIGGER application_limit_trigger
ON ALL SERVER WITH EXECUTE AS '...'
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT *
   FROM sys.dm_exec_sessions
   WHERE session_id = @@SPID
   AND program_name IN (N'Bad Program', N'Worse Program', N'Unmentionable')
    ROLLBACK;
END;

The program_name is set by some applications, I don't know is Office suite sets this property to something usefull or leaves it default. And you have to understand that this can be circumvented by anybody by simply changing the ApplicationName property in the connection string.

Answer 2

The mechanism you could use for this is "Application Roles". When connecting from an application you assume a particular role and that role is granted privileges. So all apps connect via this mechanism and don't give out SQL or NT logins for any unauthorised use.

See http://technet.microsoft.com/en-us/library/ms190998.aspx

-Krip