Solved

Audit 'SA' login connections to SQL Server

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server encoding for storing Indian languages 9 36
SQL Server - Slabs 9 38
Add '#' to end of file 2 30
SQL Server Update Query Streamline 4 13
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
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.

920 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

17 Experts available now in Live!

Get 1:1 Help Now