Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Audit SQL Server Logins Using SQL Profiler


By:   |   Read Comments   |   Related Tips: More > Auditing and Compliance

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

Database and data security is becoming an ever increasingly more important aspect of what a SQL Server DBA is responsible for. Details of who did or tried to access your database is no doubt something every DBA has had an auditor ask them to provide as a part of a security audit. This tip will look at how you could use SQL Profiler to provide an audit of the login activity on your SQL Server database instance.

Solution

There are many different methods for auditing the logins to your SQL Server instance. You could use the SQL Server Error Log or the newer Extended Events functionality, but for this tip we are going to focus on using SQL Profiler.

Requirements

As with any type of auditing your security team should define some requirements as to what needs to be collected. There is nothing worse than taking the time to set up some type of audit and then find out after the fact that you didn't have everything they required. For this example we will assume the following audit information is required for both successful and failed login attempts.

  • Event
  • Event Details
  • Username
  • Login/Logout Date/Time
  • Client Hostname/IP
  • Application
  • Server Hostname
  • Database

Luckily for us SQL Profiler can capture all of the required information above. The following table maps out each requirement to its associated event column in SQL Profiler.

Audit Item

Profiler Item

Description

Event Event Class "Audit Login" and "Audit Login Failed" Events
Event Details TextData Describes login setting on successful login and error message for failed logins
Username LoginName, NTUsername, NTDomainName SQL Server login name client used to make connection request. Depending on the type of login (SQL or Windows) all fields may not be populated
Login Date/Time StartTime Date/time the client login request hit the SQL Server instance
Client Hostname/IP HostName Server where user login request originated from
Application ApplicationName Application used by client to try to access database
Server Hostname HostName Server name that hosts SQL Server instance
Database DatabaseName Database that user tried to access

Now that we know what we need to collect in our SQL Profiler session we can go ahead and create our trace. If you've read my tutorial on SQL Profiler you have a pretty good idea of how to setup a trace and what each screen is used for so for this tip I'll just show the completed screenshots.

SQL Server Trace Setup

On the "General" tab we will setup the trace to write to a file and have the files rollover every 50MB. Once complete your tab should look like the screenshot below.

General Tab for SQL Server Trace Setup

On the "Events Selection" tab we can select the events and column data we identified above. On this tab you could also set some filters to exclude any data you don't require (ie. certain applications or users), but I always capture everything and remove it later if needed. Once done the tab should look like the screenshot below.

Events Selection for SQL Server Trace Setup

All that's left now is to click "Run" and start collecting data. Data will be populated both in the user interface as well as be saved to the file you configured on the "General" tab. If you plan on using this trace for an extended period of time you can also export the trace definition and then run the SQL script that was generated to have this run as a server side trace. More details on how to do this can be found here.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools