SQL Server Audit empty fields

Question

Post reply

SQL Server Audit empty fields

hiram.osiris

Ten Centuries

Points: 1380
More actions
December 5, 2013 at 9:03 am

#285744

Hello Everyone
I was requested to research about a good way to audit our prod databases. I started creating an Audit and an Audit Server specification to capture first Login successful and Logouts and saving the audit to a flat file. I made first tests by connecting to my test instance with a SQL User, then just doing a few selects and disconnecting from the instance and disable the audit server specification. When I check the audit file I see an odd number of rows.
I got 31 rows for LOGIN SUCCEEDED (17) & LOGOUT (14). I also noticed that in some rows, the value for session_server_principal_name is empty but the server_principal_name has the value of my sql user and some other rows has value for both columns.
Could you please explain me how could I interpret this. I only made one connection and one logout for the instance, so I don't know why is this getting multiple records.
I'm new on SQL Server Audit, and I really appreciate your help 🙂
Thanks
BTW I'm using SQL Server 2012 😉

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

durai nagarajan SSCoach Points: 15778 More actions · Answer 1

by any change have you opened new querywindow(S).

what are the parameteres and ideas you implemented in your audit?

Regards
Durai Nagarajan

hiram.osiris Ten Centuries Points: 1380 More actions · Answer 2

Hello Durai

One of the things I will be asked to check is to track all changes that a Login made in an specific date or investigate unauthorized changes, for example: Give a report of all the dml operations DBATest SQL Login made yestedary between 8am and 8pm under dummy database and how many times did he connect and disconnect from the SQL Instace.

Also, if we see an unauthorized change, an alter table for example we need to be able to demonstrate which user established a connection to the SQL Instance and what time did the user disconnect.

I'm starting with:

SCHEMA_OBJECT_CHANGE_GROUP

SCHEMA_OBJECT_ACCESS_GROUP

LOGOUT_GROUP

SUCCESSFUL_LOGIN_GROUP

I'd appreciate any feedback

Best Regards 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 3

you can and will find that login and logout events could be missing, due to connection pooling.

this stackoverflow post explains it, with the linky to the microsoft article as well:

http://stackoverflow.com/questions/279401/sql-connection-pooling-and-audit-login-logout

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

your audit will have who did what at what time, but i think trying to find their login and logout events might not work all the time.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!