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 MS Access instead or another 3rd party application, or you want 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 I 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 DBA’s 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 fires 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.
Its 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, I tried restricting it to the database level 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 you dont know the database instance yet.
Now for the condition part if it does satisfy both conditions a user on WorkStation_01 using MS Access to run queries in your SQL Database then he will be kicked our regardless of him having access to the server, that’s the Rollback part. He will see a message something similar to this one.
Now its up to your imagination how would you want to extend this one but be very careful as this is a Login Trigger if your account get caught with the conditions then your in deep [email protected]!#$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.