Solved

Audit 'SA' login connections to SQL Server

Posted on 2014-12-22
5
411 Views
Last Modified: 2014-12-23
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,
RICUser
C--Users-pchter01-Desktop-Create-Table--
0
Comment
Question by:RICuser
  • 3
  • 2
5 Comments
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 40513666
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.

longer:
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:
<EVENT_INSTANCE>
    <EventType>type</EventType>
    <PostTime>date-time</PostTime>
    <SPID>spid</SPID>
    <ServerName>name</ServerName>
    <LoginName>name</LoginName>
    <UserName>name</UserName>
    <DatabaseName>name</DatabaseName>
    <SchemaName>name</SchemaName>
    <ObjectName>name</ObjectName>
    <ObjectType>type</ObjectType>
    <TSQLCommand>command</TSQLCommand>
</EVENT_INSTANCE>

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')
and
@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.
0
 

Author Comment

by:RICuser
ID: 40514043
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,
RICUser
C--Users-pchter01-Desktop-Results-of-Scr
C--Users-pchter01-Desktop-Create-Table--
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40514152
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.
0
 

Author Comment

by:RICuser
ID: 40514813
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!

RICUser
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40514897
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.
0

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question