“Unable to open SQL Server connection” when installing application with repository database on a remote server

Applies to
ApexSQL Monitor, ApexSQL BI Monitor, ApexSQL VM Monitor

Symptoms

The “Unable to open SQL Server connection” error appears in the Configuration step of the ApexSQL Monitor installation when the remote SQL Server instance is specified in the Server name field of the Central repository database section

The error appears during the installation in case  that the remote SQL Server instance was specified in the Server name field

Problem

During the ApexSQL Monitor installation process, the Configuration step requires selecting the SQL Server instance that should host the repository database. Additionally, this step requires entering the credentials for the Windows account that be used by the monitoring service.

The configuratiion step of the ApexSQL Monitor installer

When the user choses to host the repository database on a remote SQL Server instance in the Central repository database section, setting the adequate Windows account name and password in the Monitoring service section and pressing the Next button, after some 30 seconds the “Unable to open SQL Server connection” error message pops up.

Cause

The Windows account used in the Monitoring service section might be the correct one and with sufficient permissions on remote SQL Server, but the ApexSQL Monitor installer does not use that account to create the repository database on the remote SQL Server instance. The installer uses that account only for the ApexSQL Monitor service. Once the ApexSQL Monitor service is installed, it runs under that Windows account entered in the Monitoring service section

The installer itself is the one that creates the repository database on the remote SQL Server directly. The installer itself running under the Windows account that initiated the ApexSQL Monitor installation, which is the user that Windows OS is running under. If that Windows account does not have adequate permissions required for creating the repository database on the remote SQL Server instance, the “Unable to open SQL Server connection” error message appears

Solution

Make sure that the user that initiates the installation process has sufficient privileges on the remote SQL Server instance to create the repository database.

The following solutions are available:

Solution 1:

If it is possible, create the SQL login on remote SQL Server for the Windows user that should perform the ApexSQL Monitor installation. Make sure that the SQL login has CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE permissions. Run the installation again

Solution 2:

In case the solution 1 is not possible or not allowed, then run the installer as a different user.

To do that:

  1. Hold the Shift button and the mouse right click on the ApexSQL Monitor.exe installation file

    Context menu initiated with Shift and right mouse click on the ApexSQLMonitor.exe intallation file

  2. Chose the Run as a different user command from the context menu. The Windows security Run as a different user dialog pops up

    Run as different user dialog

  3. Now enter the user name and password for a Windows account with adequate permissions on target SQL Server and press the OK button

    Run as different user dialog

  4. After the installation starts under the specified user, complete the installation as usual. The error should not appear again

Solution 3:

Log out from the current Windows session and log in to Windows again using the Windows account that has sufficient privileges on the remote SQL Server instance. Rerun the installation, and it will complete without error, and the repository database will be installed on the selected remote SQL Server