All Apps and Add-ons

how to do MS SQL security autdit

linker
Explorer

I have many sql server , manager want to audit sql operation secure, how to do it use splunk? have apps for mssql?

Tags (1)
0 Karma

khourihan_splun
Splunk Employee
Splunk Employee

For MSSQL 2008r2 You can monitor the SQL activity by adding a DB input for the Database log table (i.e. dbo.DatabaseLog). You will have to install the DB Connect App (DBX) first.

See this for general information on adding a database input.

I used these settings:

Input Type = Tail (Follow based on increasing Value)
Database = blah
Table Name = dbo.DatabaseLog

Rising Column = PostTime (note that if you go into SQL Management Studio, you can see the PostTime column, which Splunk will use for time indexing and also as a key to know when new it needs to tail new records)

Host Field Value = MSSQL1

^^ Note you should set this, otherwise it gets set to $decideOnStartup which I think is a bug
Output = Key-Value

and everything else I left blank.

Clicked save, and the audit records start flying in.

e.g.

DatabaseLogID=1597 PostTime=1333054356.407 DatabaseUser=dbo Event=CREATE_EXTENDED_PROPERTY Schema=Sales Object=StoreSurveySchemaCollection TSQL="EXECUTE [sys].[sp_addextendedproperty] N'MS_Description', N'Collection of XML schemas for the Demographics column in the Sales.Store table.', N'SCHEMA', [Sales], N'XML SCHEMA COLLECTION', [StoreSurveySchemaCollection], NULL, NULL;

I hope this works for you.

0 Karma