I wrote about setting up a basic server audit recently. That showed about how a server level audit is set up, but that’s a just a shell. The audit itself is just a container in which you can store various audited items. Like a SQL Server Agent job, it doesn’t do anything until you add some details.
One of the details that I think is worth adding is the failed login audit at the server level. Finding a large number of failed logins can clue you in to some hacking going on, so it can be good to log these.
To add these, you first need to add a server audit specification, which is like a job step. It’s a detail at the server level. Using the SSMS GUI, you can do this by right clicking the Server Audit Specification under Security. Select “New Audit Specification”
That gives you a dialog where you can add a name:
And then select an audit to which you assign this particular detail.
For my purposes, I need to audit failed logins, so I select that change group. You can get all change groups in BOL.
Once this is done, your audit appears in the folder in SSMS.
Now when someone tries to log in and can’t, you can view this in the logs:
Search this blog
The Current Book – Never Split the Difference
16 Year MVP Awardee
Tags
- administration
- AI
- auditing
- Azure
- backup
- Backup/Recovery
- blogging
- business
- career
- career2
- car update
- Cloud Computing
- conferences
- containers
- continuous delivery
- continuous integration
- coping
- data
- data analysis
- database design
- databases
- Database Weekly
- data privacy
- dbatools
- DevOps
- disaster recovery
- encryption
- Flyway
- Friday Poll
- GDPR
- Git
- goals
- hardware
- high availability
- Humor
- life
- Linux
- Microsoft
- misc
- monitoring
- networking
- NoSQL
- PASS
- Performance
- powershell
- presentations
- Redgate
- republish
- sabbatical
- security
- software development
- software development
- speaking
- SQL Compare
- SQL in the City
- SQLNewBlogger
- SQL Prompt
- SQL Saturday
- sql server
- SQLServerCentral
- SSMS
- summit
- syndicated
- T-SQL
- T-SQL Tuesday
- Tesla
- testing
- travel
- tsqlt
- vacation
- version control
- webinar
- windows
- words
- work
Search this blog
Steve’s Tweets
Tweets by way0utwest- administration
- AI
- auditing
- Azure
- backup
- Backup/Recovery
- blogging
- business
- career
- career2
- car update
- Cloud Computing
- conferences
- containers
- continuous delivery
- continuous integration
- coping
- data
- data analysis
- database design
- databases
- Database Weekly
- data privacy
- dbatools
- DevOps
- disaster recovery
- encryption
- Flyway
- Friday Poll
- GDPR
- Git
- goals
- hardware
- high availability
- Humor
- life
- Linux
- Microsoft
- misc
- monitoring
- networking
- NoSQL
- PASS
- Performance
- powershell
- presentations
- Redgate
- republish
- sabbatical
- security
- software development
- software development
- speaking
- SQL Compare
- SQL in the City
- SQLNewBlogger
- SQL Prompt
- SQL Saturday
- sql server
- SQLServerCentral
- SSMS
- summit
- syndicated
- T-SQL
- T-SQL Tuesday
- Tesla
- testing
- travel
- tsqlt
- vacation
- version control
- webinar
- windows
- words
- work
Older Posts
Meta
I created a server audit to a file and I created a database server specification in order to capture create/alter/drop events in my "prueba" database:USE [master]GOCREATE SERVER AUDIT [DDL Audit]TO FILE ( FILEPATH = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2RTM\MSSQL\Log\' ,MAXSIZE = 0 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF)WITH( QUEUE_DELAY = 0 ,ON_FAILURE = CONTINUE ,AUDIT_GUID = '5aeeab6e-dc91-401b-99eb-44b678918ca3')GOALTER SERVER AUDIT [DDL Audit]WITH (STATE = ON);GOuse pruebagoCREATE DATABASE AUDIT SPECIFICATION [DDL Audit Database]FOR SERVER AUDIT [DDL Audit]ADD (SCHEMA_OBJECT_CHANGE_GROUP)WITH (STATE = ON)GOWell, when I execute this sentence:use pruebaCREATE VIEW VISTA_1 as select getdate() as fechaIt generates a correct entry in my file BUT…..when I execute this sentence:CREATE VIEW VISTA_2 as select getdate()SQL gave an expected error (I know) and surprising it generates a correct entry in my file too!!!!!!Idem with this sentence:CREATE VIEW VISTA_1 as select getdate() as fechaSQL gave an expected error (existing object) and surprising it generates a correct entry in my file too!!!!!!So, how can I distinguish the row corresponding to a correct CREATE VIEW???Thanks,
LikeLike
Post the note on a forum. I did see in the Connect site that there was an R2 bug about auditing entries when statements failed.
LikeLike