Auditing Failed Logins in SQL Server
How-To Write SQL Server Audit Events To Windows Security Log
After following these steps, you should successfully start seeing your Microsoft SQL Server Audit Logs in the Windows Security Log. They’re usually listed as Event Code 33205, so you can filter your Security Logs to only show those events.
SQL Server Audit (Database Engine)
All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions
Any authenticated user can read and write to the Windows Application event log. The Application event log requires lower permissions than the Windows Security event log and is less secure than the Windows Security event log.
Attaching a Database with an Audit Defined
Attaching a database that has an audit specification and specifies a GUID that does not exist on the server will cause an orphaned audit specification. Because an audit with a matching GUID does not exist on the server instance, no audit events will be recorded. To correct this situation, use the ALTER DATABASE AUDIT SPECIFICATION command to connect the orphaned audit specification to an existing server audit. Or, use the CREATE SERVER AUDIT command to create a new server audit with the specified GUID.
You can attach a database that has an audit specification defined on it to another edition of SQL Server that does not support SQL Server audit, such as SQL Server Express but it will not record audit events.
Auditing Administrators
Members of the sysadmin fixed server role are identified as the dbo user in each database. To audit actions of the administrators, audit the actions of the dbo user.
Creating and Managing Audits with SQL Server Management Studio
Audit-related nodes are located as follows in the Object Explorer hierarchy in Management Studio.
SQL Server Instance
---- Security
-------- Audits
-------- Server Audit Specifications
SQL Server Instance
---- Databases
-------- Database Name
------------ Security
---------------- Database Audit Specifications
Create a Server Audit and Server Audit Specification
Create a Server Audit and Database Audit Specification
SQL Server Audit Action Groups and Actions
Write SQL Server Audit Events to the Security Log
SQL Server Audit Support in Different Editions and Versions ( info for version prior to sql 2016)
Sat, 23 Nov 2013 14:02:05 GMT
Here is a summary of the SQL Server Audit support in the different editions:
| click me | click me | click me |
|---|---|---|
| Edition | SQL Server 2008 and 2008 R2 | SQL Server 2012 and 2014 |
| Enterprise | Server- and database-level | Server- and database-level |
| Evaluation | Server- and database-level | Server- and database-level |
| Developer | Server- and database-level | Server- and database-level |
| Datacenter | Server- and database-level | N/A |
| Business Intelligence | None | Server-level |
| Standard | None | Server-level |
| Web | None | Server-level |
| Express | None | Server-level |
sql server ports 445 and 1433
445 is not a SQL port, is a SMB port. It is involved in SQL Server only if you use named pipes protocol, as named pipes are over SMB and this in turn uses 445 for 'SMB over IP', aka. as SMB 'NETBIOSless' as opposed to the old NetBIOS based SMB, which uses 137-139.
To configure the server to listen on specific protocols, use SQL Server configuration Manager. To configure the client allowed protocols, see Configuring Client Network Protocols.
