0 SQL Server Auditing

By default, logon auditing is disabled. You can configure this either through SQL
Server Enterprise Manager or by changing the registry.

SQL Server log files are by default located in the following directory. They are textbased
and can be read with any text editor such as Notepad.

C:\Program Files\Microsoft SQL Server\MSSQL\LOG

To enable logon auditing with Enterprise Manager

1. Start Enterprise Manager.
2. Select the required SQL Server in the left hand tree control, right-click and then
click Properties.
3. Click the Security tab.
4. Select the relevant Audit level – Failure, Success or All.

To enable logon auditing using a registry setting

1. Create the following AuditLevel key within the registry and set its value to one
of the REG_DWORD values specified below.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\AuditLevel
2. Set the value of this key to one of the following numeric values, which allow you
to capture the relevant level of detail.
3—captures both success and failed login attempts
2—captures only failed login attempts
1—captures only success login attempts
0—captures no logins

It is recommended that you turn on failed login auditing as this is a way to determine
if someone is attempting a brute force attack into SQL Server. The performance
impacts of logging failed audit attempts are minimal unless you are being
attacked, in which case you need to know anyway.
You can also set audit levels by using script against the SQL Server DMO (Database
Management Objects), as shown in the following code fragment.

Sub SetAuditLevel(Server As String, NewAuditLevel As SQLDMO_AUDIT_TYPE)
Dim objServer As New SQLServer2
objServer.LoginSecure = True 'Use integrated security
objServer.Connect Server 'Connect to the target SQL Server
'Set the audit level
objServer.IntegratedSecurity.AuditLevel = NewAuditLevel
Set objServer = Nothing
End Sub