0

I am in a situation in which I should doubt everyone (even my developer team and database admin) so I want to Log all the logons made to my server.

I have two SQL Servers, say A and B. I want to log all logons using a logon trigger from server A in server B, and it is important that my database be able to insert the log into A, but it should not have delete or update access on B.

I want the log to be made, but I do not trust my sa or other people who have full access on A.

In brief, Server A can insert Logs but can not do anything more than that, and when I want to monitor the logons on A the only possible solution should be doing it on B.

How can I achieve this situation?

3
  • So what about when one of the SAs edits the logon trigger to not log anymore but does it through impersonation of another login that no longer exists? Or removes it? Or passes fake values to server b? Aug 9, 2014 at 14:44
  • @SeanGallardy I want to know who is manipulating my database, so if a SA is doing so, let him do as far as I can see the log on server b. If a SA disables the logon trigger, he is the last one being logged in server b, so I know who the thief is. Aug 9, 2014 at 14:56
  • The only problem is this can be done without your logon trigger knowing who did it or even executing. I'm not going to go through the entire way of bypassing it, but it can easily be done. You'd be better off using SQL Audit. Aug 9, 2014 at 15:24

2 Answers 2

2

I want to log all logons using a logon trigger from server A in server B.

Ideally, if you are using Enterprise edition, then as Shawn mentioned, you can use SQL Server Audit.

You can even set up a light weight server side trace and then define a WMI event notification - TRACE_FILE_CLOSE to automatically load the trace data into a table when it rollsover.

Another option is - you can use the script for Logon Trigger posted by me. Note that it wont work for the existing connections and it a limitation of Logon triggers. All new connections will go through the Logon Trigger.

it is important that my database be able to insert the log into A, but it should not have delete or update access on B.

You can use transactional replication by just replicating the LOGON Table to server B (in your case). Make sure you choose - Do not replicate DDL changes option.

when I want to monitor the logons on A the only possible solution should be doing it on B.

You can create a view using Linked server and just give select permission to the view. This way you can control who can access the info.

As a side note, make sure you do not return a full data set in the view, rather just limit it with last 10 -15 days of login audit, so when a user queries the data, it will return a small amount of data only.

0

SQL Server Audit is your best option if you are using Enterprise Edition, anything less (Standard) does not allow database level auditing.

However you could just as well setup Extended Event session to do the auditing for you at the database level. You can set the target to a file or ring buffer. Ring buffer would require period polling to persist the data to a table so for archive a file target would likely work better. Then you can set that up to roll over and then archive the file to a table for easier reporting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.