It 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
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:
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:
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:
To add the account, open the context menu for the Logins item and choose the New login… option:
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:
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:
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:
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:
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:
Confirming the account name with OK will return to the initial window with the correct account name added as 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:
We can see that the account is properly added to the logins 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:
And in the popup dialogue enter credentials for the central SQL auditing account:
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:
From the properties window, click the Add button and find the audit account name (make sure the correct domain is chosen):
Confirm the account choice and addition to the Administrators group and proceed with the ApexSQL Audit installation:
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.
For more information please visit the Resolving SQL auditing connection issues article
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Términos de uso Privacidad Cookie Preference Center