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 3^rd 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.