Resolving SQL auditing connection issues

Applies to

ApexSQL Audit

Summary

This article shows SQL auditing connectivity issues based on prerequisites required for application setup.

Description

The main struggle in small-scale environments is a lack of technicians that can work on dedicated areas regarding management and setup. In other words, it is sometimes expected that the same person should cover different technological aspects to get some jobs, like SQL auditing, up and running. One thing is installing and administering SQL auditing solutions like ApexSQL Audit, but another thing is to, without some previous experience, prepare prerequisites, mostly security-related, to get the application working properly. Additionally, even if there is a dedicated person that can resolve prerequisite issues, it can be difficult to communicate the issues between ApexSQL Audit operator and domain administrator.

This article intends to show what connectivity issues could be encountered during ApexSQL Audit setup due to a lack of security prerequisites and how to resolve them.

Since system interoperability generally requires a domain-controlled environment, further explanation will be related to domain accounts. According to the domain security best practices, it is recommended to create one dedicated account for ApexSQL Audit administration and operation authorization, which can be assigned to one operator and centrally managed. For demonstrating purposes, we will simply name it SQL Audit.

Through the course of this article, we will try to set up auditing without the main prerequisites fulfilled and see what issues may emerge and how to resolve them

Installing ApexSQL Audit central repository

The central repository is a SQL database that needs to be hosted with a SQL Server. When started, the installation for ApexSQL Audit will require the SQL Server where the central repository will be installed and an account that will authorize the application to use that SQL Server to create and manage the repository on it. Without setting proper permissions for the used account the situation shown in the screenshot below will occur when the Configure button (the configuration confirmation button) is clicked:

Installing the SQL auditing central repository

This means that the chosen “SQL Audit” account, seen on the screenshot, has to be added as login account for the chosen SQL Server. To resolve this part, we will use the SQL Server Management Studio and establish the connection to this server using a known account with administrative access to this server, the safest bet would be the built-in “sa” account:

Connect to the SQL Server hosting the SQL auditing central repository

With the connection established, we can continue with the account setup. From the Object Explorer, expand the Security node and then the Logins node which will show current accounts authorized for SQL Server operations. We can confirm here that the chosen account for auditing is missing:

Current SQL Server login list

To add the account, open the context menu for the Logins item and choose the New login… option:

Add new SQL Server login

In the shown window there will be a field to enter the account name. We should avoid entering the name manually to prevent mistakes (one of which will be demonstrated in this section), instead, it is recommended to use the Search option:

Add new account as SQL Server login

This will open the Select User or Group dialogue where we can simply type in the account name and with the Check names button, locate it:

Searching the local accounts

At this point we can see that something is wrong, the account that was found does not look like the one used in ApexSQL Audit install configuration:

Difference between local and domain accounts

This is explained by the fact that it is possible to have two accounts with the same name where one would be the local Windows account and the other is the domain account. As explained earlier, it is necessary to use a domain account and that one should be added as login for SQL Server, so, this solution will not work.

To correct that, let’s go back to the Select User or Group dialogue and use the Locations… button, which will open the network locations tree, where the domain hosting the required account can be selected:

Changing location to search for accounts

In case there is no domain name present here, it means that the machine hosting this SQL Server is either not a part of the domain or the Domain Controller is not available which should be examined internally.

Repeating the search for the desired account will now give a different result:

Searching the domain accounts

Confirming the account name with OK will return to the initial window with the correct account name added as login:

Added SQL auditing account for SQL Server login

This account will now have to be set with the Sysadmin role. This role gives unrestricted access to the SQL Server for the audit account.

Switch to the Server Roles tab and tick the sysadmin item in the server roles list and confirm the settings with OK:

Set account roles

We can see that the account is properly added to the logins list:

Updated SQL Server login list

The explained procedure can be done with the following T-SQL script:

USE [master]
GO
CREATE LOGIN [APEXDOMAIN\SQL Audit] FROM WINDOWS WITH DEFAULT_DATABASE = [master]
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [APEXDOMAIN\SQL Audit]
GO

With this problem resolved we will try to repeat the installer configuration step, but the same message may appear. The only possible cause for that is that the installer was not started using the “SQL Audit” account but some user account currently logged on the Windows session (e.g. APEXDOMAIN\SQL). The installer requires that this account is also added as SQL Server login with the Sysadmin role. We can use one of the following resolution steps:

  1. Add the account in question as SQL Server login using the explained method (if this is security liability skip this method)
  2. Log on to Windows using the “SQL Audit” account and start the installation again
  3. Start the installer using the Run as different user option (press keyboard Shift Right-click on the installer and this option will appear in the context menu)

Run installation with different account

And in the popup dialogue enter credentials for the central SQL auditing account:

SQL auditing account credentials to run installation

For this to work, the audit account will have to be a member of the local administrators group.

To resolve that, from the Windows Start menu find the Edit local users and groups console and open it. From this console select the Groups item and double-click the Administrators item to open its properties:

Manage local system users and groups

From the properties window, click the Add button and find the audit account name (make sure the correct domain is chosen):

Add SQL auditing account as localadministrator

Confirm the account choice and addition to the Administrators group and proceed with the ApexSQL Audit installation:

Local administrators list

Note that the installer cannot be in the folder associated with the currently logged-in user (Documents, Downloads…) as it will not allow access to a different user.

Starting the application GUI

Now we will explain the differentiation between account authorized to use SQL Server for central repository and account authorized to use the application GUI. During the installation process, we authorized the “SQL Audit” account to use the SQL Server (in the further text we will refer to it as the “master” account), but we used option #3 to resolve installation startup and after the installation is finished we are still logged on the Windows session with the APEXDOMAIN\SQL account. Starting the application GUI will result in the following error:

Account unable to access application GUI

This could possibly mean one of three things for the APEXDOMAIN\SQL account:

  • The account is not part of the local “Administrators” group – If this is the case, repeat the procedure explained for the master account to add a new account as the local administrator
  • The account started the GUI or was logged on the Windows while Domain Controller, i.e. Active Directory was temporarily unreachable – in these cases, the easiest resolution is to reboot the system and log on to it again
  • The account is not added as ApexSQL Audit user

If adding this account as a local administrator is not an option for organizational and security reasons, the resolution is to add this account as the application GUI user, enabling further operations.

To do that we need to run the GUI using the master account first, from the Start menu, use the right-click on the ApexSQL Audit icon, then expand the More option and use the Run as different user option:

Run installed application with different account

The credentials dialogue will pop up where the master credentials should be inserted:

Run the ApexSQL Audit GUI with the main SQL auditing account

This will start the GUI and establish the connection with the central server. From the main menu, click the Options button and in the Options window, from the Manage accounts tab, click the Add button to introduce new user. In the shown dialogue, enter the account name (use the domain prefix) and choose the role for this account:

Add an account for GUI access

Three types of roles are available:

  • Administrator – enables full access to auditing administration
  • Power-user – restricts adding additional users
  • Reader – enables only creation of audit reports

Now the APEXDOMAIN\SQL account can start the GUI even if it is not one of the local administrators:

Current account using the application GUI

This will not produce additional permissions issues as the access to local and SQL Server resources is done via the Central process which is run under the master account:

Main SQL audit account running the Central process

Adding SQL Server instances for auditing

With the application GUI started, we can start with adding SQL Servers for auditing.

Go to the Configure tab and browse or enter the full SQL Server name in the Server name field and click the Add button. The Windows connection dialogue will appear:

Server side components remote installation

The credentials required here will enable inter-machine authorization for resources access, in this case, to automatically install the ApexSQL server-side components service that will manage auditing agent on remote instances. So, if we enter some user credentials or even master audit credentials the following message will appear:

Server side components remote authorization

This means that the most elevated account should be used to enable such authorization across the network to perform such installation. By default, this is the main domain administrator account:

Enabling server side components remote authorization

It is expected now that the installation will be successful and as a result, the auditing agent configuration window will be shown:

SQL auditing agent properties window

If the main domain administrator account is not available, as a workaround a manual installation of server-side components service can be performed.

With the successful installation of server-side components, the next part is to configure the auditing agent and assign it to the SQL Server that was added for auditing. This is done in the configuration window shown in the previous screenshot. The main condition for this configuration is to enter credentials that will be used to access the selected SQL Server and the temporary files location. This does not have to be the same as the master audit account, but it is recommended to use it again for easier administration.

For this configuration to work some requirements will also have to be met. If not, confirming the configuration will result in the following message:

Permissions issue for remote SQL Server access

As the message states, the account cannot access the targeted SQL Server. This part is resolved by adding the account in SQL Server logins as Sysadmin and the procedure for that was explained earlier in the article for the central repository installation.

If the account is added in SQL Server logins, but the message appears again, we should check for one more symptom.

In the configuration window, the Technology dropdown box will show the supported data collection technologies on the targeted SQL Server. If this box shows only the ”Auto” value, it means that the SQL Server version was not read to provide this information. The main reason for this behavior is that SQL Server is very busy with the current transaction load and did not make itself available for reading.

SQL auditing technology listing

In that case, we should try forcing access to the target SQL Server by invoking the configuration window several consecutive times. If this fails, then we need to explore the possibility that the SQL Server is not available for other reasons, e.g. it is stopped or in some maintenance mode, which should be checked with the dedicated SQL Server administrator.

Note that, if SQL auditing is established on the target SQL Server, and the instance has periods of high loads with heavy queries, its readability may be intermittent in the future and cause symptoms in the application GUI like this:

Remote SQL Server connection lost

Forcing the refresh by invoking the agent configuration using the Fix button and just confirming with OK should resolve the view in the GUI:

Refreshing remote SQL Server connection

This behavior could be the signal that the hardware for this particular SQL Server is not optimized as could be the case with the query executed against it (note: storage drive speed can be an important factor).

SQL auditing session access

After the connection issues were resolved with the previous explanation, we should be expecting that the SQL auditing works, and we should be able to see some events collected in the reports. But there is one more prerequisite that needs to be fulfilled.

When auditing agent properties were configured, the temporary files location was set. This is the location where the SQL Server will write the auditing session files and will log the audited events in them:

Temporary storage for SQL auditing data

The account that was used to access the SQL Server via the auditing agent will need permissions to read and write to this location, and more specifically, to modify the session files.

To check the permission settings for the session files, open the Properties for one of the files, and from the Security tab click the Advanced button:

SQL auditing session file security properties

In the Advanced window we can see that the ownership for these files is set to the account running the SQL Server and by default full permissions are allowed to that account and local administrators:

Permission settings for SQL auditing session files

Since this file is dynamically managed by the SQL Server, it does not inherit permissions from the parenting folder. As a result, those files will remain there indefinitely due to the inability to be deleted by the auditing agent when they expire, which will result in a pileup and large storage space occupation.

Resolving the access to these files for the auditing gent account cannot be resolved by setting permissions for it on the temporary files location but should be done by adding this account to local administrators on the system hosting the SQL Server. The procedure was explained previously for the central instance account.

Alternatively, instead of adding the auditing agent account, it is possible to use it as the SQL Server Log On account. However, it is necessary to mention that this method will have to be repeated for every SQL Server on this system that requires auditing in comparison to the previous method which resolves the issue for the entire system.

  • Warning: The following method will require SQL Server restart

To do that, open the Services window, find the service for the desired SQL Server by its name and open the Properties window for it. When opened, switch to the Log On tab and click the Browse button:

SQL Server Log On properties

Similar to the earlier explanation, when browsing for the SQL Server login, in this dialogue the domain account used for auditing agent can be found and used (again, make sure that the correct location is chosen):

Searching account for SQL Server Log On

The OK button will return to the Log On properties where the password for this account should be entered and click on the OK button from here will show a message indicating that the service has to be restarted to apply this change:

Credentials for SQL Server Log On

With these final settings, SQL auditing will work without further issues. Note that the procedure for auditing agent permissions will have to be applied for every SQL Server when concerning logins and for every system when concerning file system access.