locked
Prevent SQL queries from certain applications RRS feed

  • 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

    Friday, August 17, 2012 10:58 AM

Answers

  • 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


    Friday, August 17, 2012 11:14 AM
  • 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, esquel@sommarskog.se
    • Marked as answer by Shulei Chen Monday, August 27, 2012 10:20 AM
    Friday, August 17, 2012 9:40 PM

All replies

  • 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


    Friday, August 17, 2012 11:14 AM
  • 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.

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


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Shulei Chen Monday, August 27, 2012 10:20 AM
    Friday, August 17, 2012 9:40 PM
  • 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

    Monday, August 20, 2012 4:03 PM