8

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).

Improve this question
4
  • Are you using AD authentication? Commented May 19, 2010 at 15:48
  • 4
    Just a heads up but the program name isn't guaranteed to be accurate, for example if you using OLEDB to connect you can make it anything you like with ;Application Name=xxxxx the same applies for ODBC. Commented May 19, 2010 at 15:50
  • Mixed mode authentication - more recent stuff connects using windows authentication but there exists a large amount of applications etc... that connect using SQL Server authentication Commented May 19, 2010 at 16:14
  • In Sybase and Oracle I have had servers which we did not install the odbc catalogs stopping ODBC and tus Excel - however I am not certain ADO.Net and SQL Server can run without these Commented May 19, 2010 at 16:29

6 Answers 6

Reset to default
17

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks for this answer - this will acomplish exactly what we need at present which is to provide a temporary mechanism for denying access from existing Excel spreadsheets, and helping us to log where that access is coming from so that we can identify these existing spreadsheets and then move towards creating better ways for accessing this particular database (basically creating .net applications to replace the legacy functionality).
I read you other posts and this seems OK use for it, track down the legacy apps and files and eliminate them as they pop up. I choose my (rather abrasive) verbage just to make sure noone else comes back in two months and see this answer and says 'Hey, I can use this to secure my app!'
That is perfect. Needed it to deny mass access to a bunch of legacy app we are replacing. This helped us finding who was using them as we got email about it no working and we could update them quickly.
This query is missing a closing bracket. It should be: IF EXISTS (SELECT * FROM sys.dm_exec_sessions WHERE session_id = @@SPID AND program_name IN (N'Bad Program', N'Worse Program', N'Unmentionable'))
6

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

Improve this answer

1 Comment

Thanks. I agree that as a longer term solution this is probably the direction that we should be heading in. Currently the issue that we face is that we have an unknown set of excel addins that access a specific database, using an unknown set of credentials. We cannot disable the credentials since these other applications validly use them to connect. We wish to move towards a model of 'no excel access to database' as part of a wider initiative to reduce firmwide reliance on excel
4

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.

Improve this answer

Comments

3

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.

Improve this answer

6 Comments

But what happens if a user uses one of the approved programs and Excel ? How do you make only one work
@Mark: You don't. The server doesn't care what application is accessing it. It only cares what the user is and what their access rights are. Why do you want to stop excel?
Because that is the OP's question. I have see ODBC access from Excel locks out other applications
@Mark: somehow I mixed you up with the OP.;)
I have seen this issue in production - A user can use Excel to create can execute arbitrary SQl including massive cross joins which a) under odbc lock tables and b) take a lot of Server CPU. Whilst an app can constrain the queries
|
0

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.

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

It is part of a wider initiative to reduce reliance on Excel usage across the organisation, and also to meet certain compliance regulations that have come into effect. The main issue we face is that we are not able to clearly state what the current set of spreadsheets that access the database is (large organisation, many sites, etc...)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.