Systems Engineering and RDBMS

Auditing Login information

Posted by decipherinfosys on April 22, 2009

In SQL Server, you can either use the logon triggers to audit the login activities if you want more granular control or if you want to just audit the failure/success of the login, you can do that login auditing through configuration.  We had discussed logon triggers before in one of our posts here … do note that this functionality is available only from SQL Server 2005 SP2 onwards.  In this post, let’s take a look at the configuration changes that we can do to log the login failure/success to the error log.

If you right click on an instance and select properties and select the Security page, you will see this:

login_1

Let’s try to login using the wrong credentials:

login_x

Now, let’s take a look at the error log and see whether anything was logged there.  And surely enough, when we looked into the error log and filtered on the source of “Logon”, the error enteries were there:

login_3

So, this is an easy way to audit the login failure or success.  If you do change the option to something else, you will need to re-start the service before the changes take place.  And if you want more functionality than just simple audit capability presented by this configuration change, then look into the logon triggers functionality.

Resources:

  • Logon Triggers – BOL entry – here.
  • Login Auditing – BOL entry – here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: