Prevent SQL queries from certain applications

Question

Dear community,

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

Answer 1

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

---

Thanks, Andrew

Answer 2

As Andrew Suggested, Create Logon trigger to block access for certain applications.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

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.

Answer 3

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
http://www.sommarskog.se/grantperm.html#Othermethods
(This is the end of a longer article.)

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Also, 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