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

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 a开发者_开发百科ll Microsoft Office applications (especially Excel).

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.

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

Typically you go the other way around. Create a set of login's that have specific access rights then use those logins in the relevant applications.

If you just have a single login that is shared.. well, that's not a very secure environment and you end up with everyone having access to do whatever they want.

Just curious... why don't you issue different user IDs and passwords to each application and restrict access this way? I know this doesn't answer your question exactly, but I think this is the preferred approach.

If you want to restrict a User access through an application, use SSPI.

If you want to only restrict the application, use SQL Server impersonation and create an account for this very application.

This way, once you no longer wish this application to have access to your server, you just remove it from the role.

Assuming you create a role specific for applications, etc.

It might be helpful to know exactly why you want to do this. I'm assuming this isn't for security reasons, as any such scheme would be pretty easy to circumvent.

Are you concerned about Excel and other applications consuming a disproportionate amount of server resources? If so, take a look at the Resource Governor feature added in SQL Server 2008. The basic idea is that you create a function to classify new sessions into groups and then associate those groups with resource pools where the memory or CPU usage (or both) can be throttled when there is contention.