• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 540
  • Last Modified:

Audit 'SA' login connections to SQL Server

Hello Experts,
I have a requirement where I need to audit whenever the 'sa' login account logs into an SQL Server instance (mostly SQL 2008R2, but also some SQL2005 and SQL2012 shop).
Additionally, I need to know to which database (and podssibly the statement) is used by the captured connection.
I did some research and vetted some options and I chose to go with server logon trigger. I founds some advice online and decided to use the attached script to 1. create a table to store the captured logins and 2. To create the actual trigger.
The attached script works perfectly well. The only thing that I cannot firgure out is how to modify this script to also capture and store in the table also the database name (I must have this info) where the login is connecting to and\or the statement (could go without that) of the code being issued.

Any help would be greatly appreciated.

Thank you,
  • 3
  • 2
1 Solution
Olaf DoschkeSoftware DeveloperCommented:
Edit: In short, storing @xml in the NativeXML xml column already stores all info you have about the event, so you could only log that.

Well, referring to http://msdn.microsoft.com/en-US/library/ms173781(v=sql.100).aspx
EVENTDATA has values according to the schema in C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2006\11\events\events.xsd
Your mileage may vary by SQL Server version.

Since this should work from SQL2005 onwards I should rather refer to http://msdn.microsoft.com/en-US/library/ms173781(v=sql.90).aspx which shows this XML structure:

There you have DatabaseName and TSQLCommand, but for example no ClientHost.

As you log the @xml value in the NativeXML column you have the info for all captured logins and in the future simply need to get the single value in the same way as other detail values:

@xml.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname')
@xml.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(MAX)')

sysname isn't always the appropriate type for system names, eg client names have to be short, up to 15 chars only. FQDN (full qualified domain names) can be as long as 255 bytes, though. See what is valĂ­d in your domain. No need to save bytes today, so you could make it varchar(256).

But also see my edit before all this. All info is in the full @xml value.

Bye, Olaf.
RICuserAuthor Commented:
Thank you for the fast reply Olaf, I appreciate your time helping with this.
Obviously I am doing something wrong here. I added the two extra columns to the table definition then added them to the trigger definition using the syntax you provided above. The trigger is working still, and loading the data into the table, however the DB name and statement are all NULLs - see new attachment.

Can you check my new script ans see what am I doing wrong ?

Thank you,
Olaf DoschkeSoftware DeveloperCommented:
You did nothing wrong, obviously DatabaseName and TSQLCommand are not data of any event.

I can only quote once more:
To obtain the schema returned for an event, use Index or Search to locate the topic for the event in SQL Server Books Online

And voila: http://msdn.microsoft.com/en-us/library/hh213611.aspx
So unfortunately database name and statement is not part of Logon events.

There's nothing we can do about it, so you will need to log more events or use some other way. Eg use login auditing in the security options of the server properties.

Bye, Olaf.
RICuserAuthor Commented:
Note: Even though the above has been selected as a solution it does not accomplish 100% of the objective. An additional process will need to be implemented to be able to capture\audit database scope  and  statement issued.

Thank you Olaf!

Olaf DoschkeSoftware DeveloperCommented:
You're welcome.

Just be aware, further event triggers mostly cover DDL, but of course also DML inserts and updates. You won't get an event for data reading, though.

If using login auditing, you can audit all logins, successful or not, but not limited to sa. You can read from the log via xp_readerrorlog.

Bye, Olaf.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now