Auditing SQL Server Logins
March 08, 2010
During the recent internal auditing of our backend SQL Server, which is being used by the Finance team; it was identified that there is no auditing enabled for the SQL Server Logins. As the Finance backend SQL Server hosts organization wide critical data, it is mandatory to audit both failed and successful logins. In this article we will take a look at how a database administrator can easily enable auditing in SQL Server for logins. The steps mentioned below are applicable on SQL Server 2005 and higher versions.
1. Connect to the SQL Server 2005 Instance using SQL Server Management Studio and then right click the SQL Server Instance within the Object Explorer and choose Properties option from the pop-up menu as shown in the below snippet.
2. In Server Properties navigate to Security page as highlighted below snippet. Under Logging Auditing select the last option which is Both failed and successful logins and click OK to save the configuration changes.
There are four types of login auditing available in SQL Server:-
None – No logins will be audited i.e. neither successful nor any failed logins will be audited.
Failed logins only – Only failed logins will be audited, and at the same time all the all the successfully logins will be ignored.
Successful logins only – Only successful logins will be audited and at the same time all the failed logins will be ignored
Both failed and successful logins – Both failed and successfully logins will be audited.
In our case as the SQL Server host organization wide critical financial information, we had no choose but to enable both failed and successful logins so that we can monitor each and every single activity on the SQL Server.
Best Practices: Database Administrator
should always make sure that they are auditing at least failed
logins across production servers.
Once you have made a choice for the level of login auditing for the Server; click OK to save the change. Since this is a registry level change, in order to bring the new login audit setting into effect you need to restart the SQL Server Database Engine Service using SQL Server Configuration Manager.
Click on Next Page to continue reading rest of the article…
Continue Free Learning...