Resolving SQL auditing connection issues - "User "****\**" doesn't have permissions to access audit data" error encountered within the ApexSQL Audit (4369119)

This article is a Part 2 of the Resolving SQL auditing connection issues series.

Here, 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:

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:

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

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:

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:

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:

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:

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:

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:

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

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

• The instructions for this operation can be found in the article: ApexSQL Audit – How to audit remote SQL Server instances

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:

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.

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:

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

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).