Objective  

This article focuses on how to add SQL servers in EventLog Analyzer and receive the reports of SQL instances.

Prerequisites  

• Have access to the EventLog Analyzer console as an administrator

• Have access to the SQL server for manual configuration

• Have a SA account or a similar Windows authentication user account or a service account with control server permission to enabled advance auditing. Refer Permission Required for SQL Auditing for detailed information of permission requirement for each Advanced Auditing reports in SQL auditing. 

Steps to follow  

Step 1: Login to EventLog Analyzer console.

Step 2: Navigate to Settings >> Database Audit >> SQL server.

Step 3: Select + Add Instance.

Step 4: Select the Instance Name from the auto discovery or choose + Add Manually. 

Step 5: Under Windows Server Configuration, add the server name manually or choose the SQL server if you need to add it to the Windows log collection.

Step 6: Enter the credentials to perform log collection from the Event Viewer of the Windows machine.
Refer to the Service Account Permission to know about permission. You can also enable the Use Default Credential check box if the user account provided in the Domains and Account page has sufficient permission.

Step 7: Under SQL Server Instance Configuration, add the Instance Name and Port number used by SQL server. Refer to How to find port number to learn more.

Step 8: Set the Instance Authentication type and enter the credentials.

Note 1: The credentials mentioned above are the same as the user logon credentials for the SQL instance.
Note 2: Enabling advanced auditing will create an audit policy, and disabling advanced auditing will remove the audit policy on this SQL Server instance. Click here to know more.

Step 9: Choose Add to include the SQL instance.

 Tips  

• If you perform Agent based log collection, it is mandatory to have access between EventLog Analyzer or Log360 server and the SQL server over the SQL instance's TCP/IP port to get Advanced Auditing report.

• When Advanced Auditing is enabled for an instance, the EventLog Analyzer server automatically runs SQL queries every night at 11 PM to fetch these events directly from the MS SQL Server. The agent is used only to collect other types of logs, such as DDL/DML monitoring, which are available in the Event Viewer.

• EventLog Analyzer connects to SQL Server using TCP/IP, so ensure that TCP/IP is enabled for the instance.

• In the SQL Server Configuration Manager console, click SQL Server Network Configuration.

• Select Protocols for <instance name>. In the center pane, under the Protocol Name column, ensure TCP/IP is Enabled.

• If TCP/IP is Disabled, double-click TCP/IP, toggle the Enabled status from No to Yes and click OK.

• Restart the SQL Server instance.

•  The SQL Server instance should not use a dynamic port.  

• In the SQL Server Configuration Manager console, click SQL Server Network Configuration.

• Select Protocols for <instance name> In the center pane, under the Protocol Name column, double-click TCP/IP.

• In the TCP/IP Properties pop-up, click the IP Addresses menu, and go to the IPAll section at the bottom.

• Ensure that the TCP Dynamic Ports field is empty.

 Related topics and articles  

• No data in DDL/DML report