Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

Restricting Access to Database by Application Name and/or Host Name in SQL Server

, 16 May 2010 CPOL
Rate this:
Please Sign up or sign in to vote.
Restricting Access to Database by Application Name and/or Host Name in SQL Server

Have you ever thought of restricting access to the Database by Application Name or Host Name, that’s on top of the user credentials.

For example, you don’t want users to use SQL Query Analyser but instead they should use Microsoft Access or another 3rd party application, or you want that only a certain Workstation can only access your SQL Server so when the user connects to the database he will be kicked out. For any reason, you might have a solution. I was checking over the internet whether there is a SQL built in security property that I can use to handle this scenario, but to my luck there is none. Since I am not a DBA I then asked some of the DBAs I know but they don’t have an answer either. So I devised my own solution by using triggers.

There are 3 trigger types in SQL which are:

  • DML Triggers (Data Manipulation Language) – The trigger that can fire on UPDATE, INSERT, or DELETE.
  • DDL Triggers (Data Definition Language) – The trigger that fires on CREATE, ALTER, DROP, GRANT, DENY, REVOKE, and UPDATE STATISTICS statements.
  • Logon Triggers – The trigger that fires on Logon, this is what we will use. Thank God this was implemented on SQL 2005 SP2.

It's very simple. Here is a sample of the trigger I created to handle my scenario.

CREATE TRIGGER RestrictAccessPerApplication
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF
(PROGRAM_NAME() = ‘Microsoft® Access’ AND HOST_NAME() = ‘WORKSTATION_01')
BEGIN
ROLLBACK;
END
END

So what does the code do. Every time a user logs in, the Trigger Fires and this will happen on all database instances, I tried restricting it to per database but logon triggers are global, I even tried adding that to the condition (DB_NAME() = ‘MyTestDatabase’) which definitely would not work as when you logon there is no database instance yet. Now for the condition part, if it does satisfy both conditions a user on WorkStation_01 using Microsoft Access to run queries in your SQL Database, then he will be kicked out regardless of him having access to the server, that’s the Rollback part. He will see a message something similar to this one.

Now it's up to your imagination how you would want to extend this one but be very careful as this is a Login Trigger. If your account gets caught with the conditions then you are in deep sh!@!#$t! You can even not revoke access if you want but just start to log events when it happens (Insert something in a table).

For me, I even added a table of applications, hosts and super users like such:

ALTER TRIGGER RestrictAccessPerApplication on ALL SERVER
FOR LOGON
AS
BEGIN
IF
(
PROGRAM_NAME() IN (SELECT sApplicationName _
	from MyTestDatabase.dbo.AllowedApplications WHERE bIsEnabled = 1)
AND HOST_NAME() IN (SELECT sHostName _
	from MyTestDatabase.dbo.AllowedHosts WHERE bIsEnabled = 1)
AND ORIGINAL_LOGIN() NOT IN (SELECT sUserName _
	from MyTestDatabase.dbo.OverrideUsers WHERE bIsEnabled = 1)
)
BEGIN
ROLLBACK;
END
END

Here is how my table looks like:

This works for me really well, but be very careful again when you want to change the table structure. Do it with care as one error in the script the condition will always fire and you will always be kicked out.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Raymund Macaalay
Technical Lead
New Zealand New Zealand
http://nz.linkedin.com/in/macaalay
http://macaalay.com/
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
SuggestionBackup master PinmemberJan Steyn31-Aug-11 5:27 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.1411023.1 | Last Updated 17 May 2010
Article Copyright 2010 by Raymund Macaalay
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid