Solved

Audit 'SA' login connections to SQL Server

Posted on 2014-12-22
5
384 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now