Check for SQL Server Connectivity

My company is revamping its domain structure and tightening up network security. I am one of the guinea pigs and therefore have been put into the new domain and OU groups. Obviously, we are experiencing some teething problems. As a result I can't access things, namely SQL Server instances that I used to be able to connect to from my work station.

I needed to provide the network team a list of the instances I could no longer access and I certainly wasn't going to try to connect to each one manually. We have a very large estate and it would have taken me ages.

Query To Find SQL Audit Details

I've been deploying a SQL Server auditing solution for our new SQL 2014 estate. I know, we're a few years behind. But if you saw some of the legacy systems I work with you'd understand that migrating to SQL 2014 is quite a coup!

Anyway, I found a really great audit solution that I have used as a base on Colleen Morrow's blog. I will detail what I have added to her solution in a later post. But, for now, I would like to share a query that displays some basic information about existing audits, both at the server and database level.

I put this query together for the purposes of documentation. Our 2014 estate is getting large fast. And with so many installations, we need to keep track of what is installed and configured on all our servers.

Monitoring out of hours DB access

Recently a client has asked to monitor staff usage of a desktop application out of hours and out of the office. So the specified criteria for tracking db access were:

• Between 17:00 and 09:00 hours on weekdays
• Weekends
• Computers from outside company offices

 The client uses SQL Server 2005 so the two solutions that immediately came to mind were:

• Run a job with a query interrogating the sys.dm_exec_sessions dmv checking for sessions within the specified criteria
• Create a logon trigger that fires at each logon event and populates a table with the required data when a logon event occurs within the specified criteria

In this case I felt that creating a logon trigger would be the more efficient option as it would fire only when a user logged on and would execute the query only during the specified times. Where as a job would need to be scheduled and would run even when not needed, i.e. when no one is accessing the database within the specified times.

A while back I posted about Logon Triggers when I first learned about them. I gave the example found in BOL about limiting the number of connections a user can have at any one time. Since then I haven't had an opportunity to put such triggers into practice. So it was, sadly, quite exciting to finally jump into it.

First I created a table in my AdminDB: 

USE [AdminDB] CREATE TABLE dbo.tblLogonTracking
(
    stblLogonTrackingID int primary key clustered identity (1,1) NOT NULL,
    login_time datetime NOT NULL,
    [host_name] varchar(50) NOT NULL,
    [program_name] varchar(100) NOT NULL,
    [EventData] VARCHAR(15) NULL
) ON [PRIMARY]

Then the trigger itself:

USE [master]
ALTER TRIGGER trLogonTracking
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF    DATEPART(HH, getdate()) NOT BETWEEN 9 AND 17
    OR (DATEPART(DW,GETDATE()) IN (1,7))
    BEGIN
        INSERT INTO AdminDB.dbo.tblLogonTracking (login_time,[program_name],[host_name],[EventData])
        SELECT GETDATE(),APP_NAME(),HOST_NAME(),
        EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(15)')
        WHERE ORIGINAL_LOGIN() IN ('AppLogin1','AppLogin2')
        AND (APP_NAME() LIKE '%app1_name %' OR APP_NAME() LIKE '%app2_name%')
        AND NOT EXISTS (SELECT 1 FROM AdminDB.dbo.tblLogonTracking as a
                        WHERE DATEDIFF(mi,GETDATE(),a.login_time) > 60
                        AND APP_NAME() = a.[program_name]
                        AND HOST_NAME() = [host_name])
        AND EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(15)') NOT LIKE '10.1.%'
    END
END

I've created the trigger 'ON ALL SERVER' since I need to use it for several databases/applications. But it could be created just for a single database by running it on the required db and using 'ON DATABASE'. Due to the nature of the applications I've also included a NOT EXISTS statement so I don't record the same user logging in and out successively. The goal here is to know that someone is accessing the database in out of hours periods, not necessarily how many times they are accessing the system. But that should be relatively easy to modify based on requirements.
I also needed to access the IP address of the incoming connection - to filter out tthe company IP range. So I'm using the EVENTDATA() function. Event data is held as XML and therefore an XML statement was required. For more info on EVENTDATA() see MSDN.

With the above there is now a table to be accessed for usage/auditing reports. It would probably be a good idea to archive and/or cleanup the LogonTracking table just to keep this tidy and efficient.

It would be nice to hear what other people have used logonn triggers for as well.