Friday, August 17, 2012 10:58 AM
Is it possible to prevent certain applications to query on a SQL database ? They need access to the database via a AD Security group due to a application they use. The thing is some of the users also create excel queries, some of them not correct and they slow down the SQL server and Terminal server. Now i want to block Excel to query a certain database on one of our SQL servers but still connect tot he database via the application that is allowed.
Application 1 -> Allow access to SQL DB
Application 2 ->| Block access to SQL DB
Friday, August 17, 2012 11:14 AM
We've implemented a logon trigger which detects whether the user is accessing the server via Excel or Access. If they are, it checks whether they are a member of a particular AD group which permits excel/access as the application, if not, it blocks them.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER[Logon_Trigger] ON ALL SERVER WITH EXECUTE AS 'mydomain\elevatedacc' FOR LOGON AS BEGIN BEGIN IF APP_NAME() LIKE '%MICROSOFT OFFICE%' OR APP_NAME() LIKE '%EXCEL%' OR APP_NAME() LIKE '%ACCESS%' IF UPPER(ORIGINAL_LOGIN()) LIKE 'mydomain\%' BEGIN DECLARE @WindowsGroupMembers TABLE(AccountName VARCHAR(255), TYPE VARCHAR(50),privilege VARCHAR(50),Mapped_Login_Name VARCHAR(255),permission_path VARCHAR(255)); --declare var for groupname and connection user DECLARE @GroupName VARCHAR(255) SET @GroupName = 'mydomain\myadgroup' DECLARE @user VARCHAR(50) SET @user = ORIGINAL_LOGIN() -- populate table with results of xp_login (members) INSERT INTO @WindowsGroupMembers([AccountName],[TYPE],[privilege],[Mapped_Login_Name],[permission_path]) EXEC xp_logininfo @GroupName, 'members' -- SELECT * FROM @WindowsGroupMembers IF (SELECT COUNT(*) FROM @WindowsGroupMembers WHERE AccountName = @user) = 0 ROLLBACK END END END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [Logon_Trigger] ON ALL SERVER GO
Friday, August 17, 2012 2:23 PMAs Andrew Suggested, Create Logon trigger to block access for certain applications.
Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)
Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
Friday, August 17, 2012 9:40 PM
The trigger that Andrew posted will lock out the truly naïve and those who understand the sign that says "no trespassing".
However, it is a simple matter to change the connection string to include something like "Appname=Trapmeifyoucan".
There is no secure way to grant access per application. If you want users to be able to access the database through the application only, you must have some sort of a three-tier solution. I have some text on
(This is the end of a longer article.)
Erland Sommarskog, SQL Server MVP, email@example.com
- Marked As Answer by Shulei ChenModerator Monday, August 27, 2012 10:20 AM
Monday, August 20, 2012 4:03 PMAlso, your comment "some of the users also create excel queries, some of them not correct" indicates that your application is written to allow unintended queries. You may not be able to easily fix this, but if the only access allowed only runs stored procedures, and the users only have permission to execute those procedures, then they won't be able to create and run their own queries. When you have an opportunity, you should correct how your application works with the database.
Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty