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
ENDSo 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.
Access Denied
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 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
ENDHere 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.
CREATE TRIGGER RestrictAccessPerApplication
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF
(PROGRAM_NAME() = ‘Microsoft® Access’ AND HOST_NAME() = ‘WORKSTATION_01’)
BEGIN
ROLLBACK;
END
END
Be careful about relying upon the program name as it can be set to anything the user wants. All you have to do is add the following to your connection string. Application Name=MyAppName
Good tip Jeremy
I am trying to implement a logon trigger for the following:
Based on a table of logons and associated application, if a monitored user logs on I need to monitor (hopefully within the logs) any SQL activity, i.e. updates/inserts/deletes and queries. Any advice ???
I think this is too costly for the database if you want to perform triggers on updates, inserts and deletes. There are a lot of ways to do this.
1. You can still do it in triggers if you really wanted but definitely not recommended
2. You can run profiler at the background and just filter the items you need and save it on a a SQL Table
3. Now my suggestion is to use the SQL Auditing Functions I will be creating a blog post for this one here –> http://anyrest.wordpress.com/2010/06/29/auditing-events-on-sql-server-2008/
Thanks for the quick response. Unfortunately, we are not in a position to move from SQL 2005 to SQL 2008 (we are testing as we speak).
Also, I may not have explained the situation properly. What I am trying to do is more of an audit at the user level. When user X (defined in a table) logs in using application Y (defined in a table) log any SQL activity (insert/update/delete).
The audit solution would audit any transaction by any user on the specified audit tables.
I think I am going to have to go the SQL Trace route. Any suggestions as to the best method of doing this ?
This will give you a head start, here is a script to create a trace and I had added comments to further explain.
USE Master
GO
CREATE PROCEDURE spAuditProcedure
AS
BEGIN
SET NOCOUNT ON;
DECLARE @iTraceID int
DECLARE @iFileSizeLimit bigint
DECLARE @bEnabled bit
DECLARE @sAuditFileName nvarchar(100)
SET @iFileSizeLimit = 5
SET @bEnabled = 1
SET @sAuditFileName = N'C:SQLTraceAuditTrace - ' + CONVERT(NVARCHAR(25), GETDATE()) + '.trc'
/*
Create the trace
More info on http://msdn.microsoft.com/en-us/library/ms190362.aspx
Parameters
1 - Trace ID - is the Output ID of the trace creates
2 - Options
2 Specifies that when the max_file_size is reached, the current trace file is closed and a new file is created,
4 Means Specifies that if the trace cannot be written to the file for whatever reason
8 Specifies that a record of the last 5 MB of trace information produced by the server will be saved by the server
In here you can add 2 and 4 so you both have the option and you will put 6 as the paremeter instead
3 - Trace File Location - Location where you want to save
4 - The Max File - size limit before new file created
5 - Stop Time - we dont intend to stop so null
6 - File Count - It will store a maximum of 100 Files and roll over
*/
EXEC sp_trace_create @iTraceID OUTPUT, 2, @sAuditFileName, @iFileSizeLimit, NULL
Print @iTraceID
/*
Set the trace event
More info on http://msdn.microsoft.com/en-us/library/ms186265.aspx
Parameters
1 - Trace ID - The trace ID output from the created trace above
2 - Event ID - 13 for SQL:BatchStarting this logs when a Transact-SQL batch has started, this checks for CRUD Events
3 - Column IF - 11 for LoginName, 1 for the Text data and 14 for Start Time. You can add more but I think this is enough for your needs.
4 - on - Event is turned ON
*/
EXEC sp_trace_setevent @iTraceID, 13, 7, @bEnabled
EXEC sp_trace_setevent @iTraceID, 13, 1, @bEnabled
EXEC sp_trace_setevent @iTraceID, 13, 14, @bEnabled
/*
0 - Stops the specified trace, 1 - Starts the specified trace, 2 - Closes the specified trace and deletes its definition from the server
*/
EXEC sp_trace_setstatus @iTraceID, 1
END
But please bear in mind that when the server is shut down or restarted or SQL Service is also restarted then you need to restart the trace which is a bit of hassle, but you can automate it by creating a stored procedure in the master database that contains the trace definition, with that you need to create the filename dynamically and the best way to do that is use the time as the filename that’s why our filename above is dynamic.
Now the last thing is to activate it on start up by running this script
Use Master
GO
/*
Parameters
1 - Stored Procedure - The SP you want to autorun
2 - Option Name - Is the name of the option to set. The only value for option is startup.
3 - Value - Is whether to set the option on (true or on) or off (false or off)
*/
sp_procoption spAuditProcedure, StartUp, True
Question: This appears to be the closest solution I have been able to find for something similar I’m trying to do. I need to be able to restrict access per SQL instance to a specific hostname – so only one host (and the localhost SQL Server) can access any particular SQL instance (ie. each App Server I have has a one-to-one relationship with a SQL instance – but all SQL instances are on the same server. So to prevent people from making stupid mistakes I want to force them to access the databases through SSMS from each specific associated App Server). I hate to ask something stupid but can you give some pointers on how I could modify this code above to just look at source hostname and allow traffic only for a specific host? Any help would be VERY much appreciated.
Sorry I am quite confused with your question, do you mean if you have for example Server1 and Server2 only the SSMS in Server1 can only connect to Server1 and that goes the same for Server2? So if Server2 SSMS connects to Server1 it will be denied?
Here is an example:
SQL Server SQL01 has an instance on it called DEVSYS
App Server named DEVSYS needs to connect to its SQL instance on SQL01
SQL Server SQL01 has another instance on it called WEBSITE
App Server named MYWEBSITE needs to connect to its SQL instance on SQL01
Neither App Server should be able to connect to any SQL instance other than its own assigned instance (so I should not be able to connect to the Windows App Server MYWEBSITE and use SSMS to connect to the SQL instance SQL01DEVSYS – I should only be able to use SSMS to connect to the instance assigned to that App Server which would be SQL01WEBSITE). I should be able to connect to all SQL instances if I log into SQL01 directly (obviously I do not want to remove access from the localhost for SSMS).
Does this make better sense? The attempt is to create a one-to-one relationship between Windows servers and their assigned named SQL instances so that there is no cross-access between Windows servers and a SQL instance not assigned to it (so someone doesn’t connect to the wrong SQL instance and make changes in the wrong database(s)).