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:
Let’s try to login using the wrong credentials:
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:
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.