Overview
Microsoft SQL Server is a relational database management system that offers a wide range of features and services. However, this also creates a large surface area for potential attacks and vulnerabilities. To address these concerns, SQL Server auditing is used to meet compliance requirements, diagnose database issues and investigate any suspicious activity.
1-Enabling logging for logins
Enable Audit logs in MSSQL server with the steps below:
Open Microsoft SQL Management Studio with the appropriate credentials.
In Object Explorer, right-click on the database server and select Properties.
In the Properties panel, select Security in the Select a page section.
In Login auditing, select Both failed and successful logins.
2-Enabling server auditing
Open Microsoft SQL management studio with appropriate credentials.
In Object Explorer, expand the Security tab to view Audits and Server Audit Specifications options.
3-Creating Audits
Right-click Audits to select New Audit..
In Audit Properties, provide appropriate audit name and set audit destination as application log. The configured Audit properties pane is shown below:
Click OK to apply settings.
4-Creating Server Audit Specifications
Right-click Server Audit Specifications and select New Server Audit Specification…
In Server Audit Specification Properties, provide an appropriate specification name and choose an earlier created audit name from the drop-down menu.
Configured Server Audit Specification Properties pane is shown below:
Click OK to apply settings.
Right-click on earlier created audit and select Enable.
Right-click on earlier created Server Audit Specification and select Enable Server Audit Specification.
To view audit logs, enable login auditing and click on the 'View Audit Logs' button.
The outcome will show login success.
Verification of configuration
Verification of configuration can be done in two ways:
From the Collector-Syslog Server (CCE): This can involve logging into the CCE and checking the configuration settings, testing connectivity and functionality of the various components, and comparing the actual results against the expected or desired outcomes.
From the UI: This can involve logging into the user interface and checking the configuration settings, monitoring the logs and flows, and comparing the actual results against the expected or desired outcomes.
Both methods can be used to ensure that the system is properly configured and working as intended.
Using UI
STEP 1:Log in to UI >> SYSTEM
STEP 2: >> Logs and flows collection status
STEP 3: >>To verify the source device IP from the UI:
Log in to the user interface
Navigate to the "SYSTEM" section
Look for the "SOURCE DEVICE IP"
Check the IP address that is displayed
Compare the IP address displayed against the expected source device IP
This will allow you to ensure that the system is properly identifying the source device IP and that it matches the expected IP address..
