To audit SQL Server logins, we need to configure SQL Server Auditing settings, which were introduced in SQL Server Database 2008.
- Connect the SQL server instance via SQL Server Management Studio.
- Navigate to Security → Right-click “Audits” and select “New audit” → Type in an name for the audit and select the location where the SQL Server audit logs will be stored → Click “OK” → Right-click the newly created audit and select “Enable audit”.
- Right-click “Server Audit Specification” and select “New Server Audit Specification” → Type in an appropriate name → Select the new audit from the audit drop-down menu → In the “Audit action type” column, select “Failed Login Group” and “Successful Login Group” → Click “OK” → Right-click on the newly created server audit specification and select “Enable server audit specification”.
- To view the SQL Server audit login trail, navigate to Security | Audits → Right-click the newly created audit and select “View Audit Logs”
Now you can read all login audit events in your SQL database.
- Open Netwrix Auditor → Navigate to Reports | Predefined | SQL Server | All SQL Server Logons → Click “View”.
Now you can view all users that access your SQL database.
Enable SQL Server Login Auditing to Protect Your Sensitive Databases from Unauthorized Access
Monitoring successful logins to SQL Server is essential for getting information about who is accessing your database. For example, when a suspicious user logs on to a sensitive database, you need perform an security investigation immediately. You also need to monitor failed logon attempts. While a certain number of failed logon attempts are to be expected during normal business operations, an unusually large number of failed login attempts can indicate that an attacker or malicious software is attempting to get inside your database by guessing user names and passwords. Enabling SQL login auditing will help you detect insider and outsider threats in time to protect your SQL databases against data breaches. These capabilities are included in SQL Server audit services, but this is not a very convenient solution because truly suspicious events get buried in the huge amount of noise data.
Netwrix Auditor for SQL Server provides complete visibility into and control over changes and access events in SQL databases, including auditing of successful and failed logon attempts from Active Directory or locally. The application comes with a broad set of predefined reports, including the “All SQL Server Logons” report, which enables you to easily audit both failed and successful login attempts. It provides all the critical who-what-when-where details you need to streamline auditing of attempts to log on to the database or SQL Server Management Studio so you can minimize the risk of a security breach. Plus, you can store your complete SQL Server audit trail for years in the cost-effective two-tiered (SQL database + file-based) storage.