Microsoft SQL Server

About the Device

Microsoft SQL Server is a relational database management system (RDBMS). Applications and tools connect to a SQL Server instance or database, and communicate using Transact-SQL (T-SQL). 

Device Information

Collection Method

Port Requirements

To facilitate secure communication and align with our best practice, we strongly encourage the use of Transport Layer Security (TLS) between your security devices and our Adaptive MxDR platform for event forwarding.

While we understand that TLS support may not be available on all devices, if your devices do support TLS communication, we recommend utilizing port 6514 for seamless integration.

In some cases, the upgraded version of the device might incorporate TLS support without prior notice. If you come across such a scenario or for further assistance in configuring TLS, we kindly ask you to reach out to your dedicated Adaptive MxDR Service Delivery Lead.

Device Configuration

Pre-requisite

Ensure the machine where SQL Server is configured is in UTC Timezone.

To Configure SQL Server auditing

The first step to set up SQL auditing is to create a Server Audit object which defines the audit destination, i.e., Application Log. Then add a Server Audit Specification object to start auditing server events, such as logon attempts or principal changes and Database Audit Specification object to audit SQL statements.

To Create a SQL Server Audit object

1. Open SQL Server Management Studio and connect to the relevant database instance.

2. Click Security, right-click on Audits, and select New Audit.

3. In Create Audit, configure the following:

• Enter a name for the audit object.

• In On Audit Log Failure, select Continue.

• In Audit destination, select Application log 

4. Create another Audit Object repeating the Step 2 with different name and in On Audit Log Failure, select Fail Operation. In Audit destination, select Application log.

5. Click OK to create the Server Audit object.

6. A red arrow will be displayed next to the new object’s name, which indicates a disabled object. Right-click on the audit object and select Enable audit. Once enabled, red arrow will be disabled as shown below

To Create a SQL Server Audit Specification

1. Open SQL Server Management Studio and connect to the relevant database instance.

2. Click Security, right-click on Server Audit Specifications, and select New Server Audit Specification.

3. Enter Name of the new Server Audit Specification.

4. Choose both the Server Audit objects created earlier and select the actions you want to audit.

Select Audit-ServerAudit1 for continue action

Select Audit-ServerAudit2 for Fail Operation action

5. Select either individual Audit Action Type or select All of the Audit Action type based on the requirements.

6. Click OK to create the Server Audit Specification object.

7. A red arrow will be displayed next to the new object’s name, which indicates the object is disabled. Right-click on the audit object and select Enable audit. 

To Create a SQL Server Database Audit Specification

1. Open SQL Server Management Studio and connect to the relevant database instance.

2. Click Databases > [database_name] > Security.

3. Right-click Database Audit Specifications and select New Database Audit Specification.

4. Enter Name for new Database Audit Specification. Choose both the Server Audit object created earlier and select the actions you want to audit.

 Select Audit-ServerAudit1 for continue action

Select Audit-ServerAudit2 for Fail Operation action

5. Select either individual Audit Action Type or select All of the Audit Action type based on the requirements.

6. Mention Object Class, Object Name and Principal Name based on the requirements.

7. Click OK to create the Database Audit Specification object.

8. A red arrow will be displayed next to the new object’s name, which indicates the object is disabled. Right-click on the audit object and select Enable audit. Once Enabled, Red arrow will get disappeared and will look like below

To Collect SQL Server audit logs

Once you enable SQL Server audit, you can configure NXLog to collect the logs.

NXLog Configuration

Pre-requisite – Generate certificate on CyberHub using the following steps.

1.Go to support user mode by executing following command su - support on CyberHub terminal and choose option 29 - Manage the TLS certificates.

2.Choose option 2 - View EA Server TLS certificate and this will print Private Key and Certificate content on console.

3.Copy certificate from the console output to a file and save it.

Configuring the NXLog Agent for log forwarding

1. Download and install the NXLog agent from the following location: https://nxlog.co/products/nxlog-community-edition/download.

2. Navigate to services.msc and stop the nxlog service.

3. Go to the folder C:\Program Files\nxlog\data and delete the file configcache.dat if it is present.

4. Navigate to the installed location C:\Program Files\nxlog\conf. Rename the attached NXLog_SQL.conf ( ) file to nxlog.conf and copy it into this folder.

5. System Provider is different with respect to each customer configuration. System provider value should be mentioned as per customer configuration in nxlog.conf file in line 42/43.

6. Please also provide the same System Provider Value into Syslog Signature while onboarding this device.

7. Replace the placeholder CyberHub IP with the actual CyberHub IP Address in the nxlog.conf file.

8. Copy the certificate which you obtained from Adaptive MxDR team to windows machine where nxlog agent is installed and mention this cert path in nxlog.conf against CAFile at line number 73.

9. Now, start the nxlog service from services.msc.

10. NXLog agent logs will be available at the location C:\Program Files\nxlog\data\nxlog.log.

11. The log flow should work, and you can check it using tcpdump with the command tcpdump -AA port 6514.

Integration Parameters

Parameters required from customer for Integration.