ERMAHBLERG

Currently at our business, the need for auditing Logons to a server, and some basic activity has came up recently. While the solution below was not implemented in our corproration. This is an elegant solution for a small SQL Server farm, with only a few instances of SQL Server. Some people may chose to do these on a larger scale, using policies, but we came up with an alternative solution, which will probably be detailed in later posts.

In the meantime, for a simple Logon Auditing, I found the following site: http://beyondrelational.com/modules/2/blogs/88/Posts/18008/logon-trigger-in-sql-server.aspx to use as a reference of what I needed.

There, Paresh included all the information that I wanted (within the context of this trigger) in the table plan he designed. Essentially the session ID, the time of the login, what computer was used, what application was used, the login name, and the client host of the user. His code is below:

CREATE TABLE LogonAuditing
(
	SessionId int,
	LogonTime datetime,
	HostName varchar(50),
	ProgramName varchar(500),
	LoginName varchar(50),
	ClientHost varchar(50)
)

Once the auditing table has been created, a trigger must be created on the Master Database to do something. This table either needs public write access, or this trigger needs to be executed as a user/account that has access. In this case, I’ve modified his code to execute as SA.

use master

CREATE TRIGGER [LogonAuditTrigger]
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS

BEGIN
DECLARE @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@ClientHost varchar(50),
@LoginType varchar(50),
@HostName varchar(50),
@AppName varchar(500)

SET @LogonTriggerData = eventdata()

SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
SET @HostName = HOST_NAME()
SET @AppName = APP_NAME()

INSERT INTO DATABASEWHEREAUDITTABLEIS.dbo.LogonAuditing
(
SessionId,
LogonTime,
HostName,
ProgramName,
LoginName,
ClientHost
)
SELECT
@@spid,
@EventTime,
@HostName,
@AppName,
@LoginName,
@ClientHost

END

GO

You can see an example of how this works over at his page.

One of the reasons we wanted this, was so we could monitor, and eventually block service accounts from access SQL through Management Studio. This is easy, as you can capture the LoginName, and the AppName, and if they match a criteria, you can issue a ROLLBACK instead of actually letting a transaction complete. To do this, you would insert the following, which restricts a specific user from using Management Studio for anything (browsing or querying)

IF @LoginName = 'SERVICEACCOUNT' AND @AppName like 'Microsoft SQL Server Management Studio%'
BEGIN
ROLLBACK;
END

You can implement this in the trigger, so that it logs the connection attempt, or you can do many different actions from this point. Now, if you have savy users, they can get around this by using alternative connections to the SQL Server, and if they have the correct access, they can view, and even disable these triggers. Also if you want this solution to be transparent, it is not possible, as when a login fails, it will fail due to a triger execution.

Our company is looking for a multi server solution, with a single repository, but we also want information as to some of the actions taken by the users, but not detailed enough to list every item changed, but that they did make a change.

Paresh was very helpful with his article, I learned a lot from it, and other articles on his blog. I beg you to pay him a visit at http://beyondrelational.com/modules/2/blogs/88/Posts/18008/logon-trigger-in-sql-server.aspx.