SUMMARY
When adding a SQL Server client, the appliance may detect the presence of a SQL instance but not be able to list active databases.
ISSUE
The Unitrends UI lists a SQL instance present on a windows client containing an agent for backup, but when expand, the server, it shows no databases.
RESOLUTION
Below are the identified causes Unitrends has encountered that could cause this and also steps that can be tried
A) SQL writer is not healthy.
A most common issue is the Microsoft SQL VSS service is not running on the windows machine, and the resolution is to start it. You may find the Microsoft Service management page to start the SQL VSS Writer and set to Automatic.
1) Start the Services Microsoft Management Console (services.msc)
2) Click on SQL Server VSS Writer.
3) Click Start to start the writer. Ensure the Startup Type has changed to Automatic.
4) On the Unitrends system click on the symbol (top right) and select INVENTORY SYNC.
5) Proceed with creating the Job. When you select the SQL option, you will see the database are now listed.
B) If this does not help getting the DB’s listed Check for SQL permissions.
SQL default permissions or other restricted permissions are in place for a 2012+ SQL engine preventing the NT Authority/System account from talking to SQL and they will require modification to allow Unitrends to talk to SQL.
1. Open SQL Management with admin credentials.
2. Within SQL Management, you need to open the Security folder within the Nav Tree, then click on Login folder.
3. Double click on NT AUTHORITY\SYSTEM Credentials and then check the DBs that are connected to it. Under Server Roles, verify that 'sysadmin', 'dbcreator', 'serveradmin' and 'public' is selected so that it has the proper access.
Also, under User Mapping, make sure that the DB in question has DBO (Database Owner) selected for all DB's under User Mapping.
4. Click save
C) Microsoft software failure leading Unitrends support to fixing issue with SQL MOF configuration
The following instructions are provided as a reference for a Microsoft supported process that "may" resolve some issues with the SQL MOF configuration that can cause WMI to fail to list SQL as a service properly.
Open the command prompt as an administrator and run this command:
mofcomp "C:\Program Files (x86)\Microsoft SQL Server\130\Shared\sqlmgmproviderxpsp2up.mof"
The command above is different for different SQL server versions. In the above command we have 130 highlighted as it is for 2016 server. Below screenshot will help you get the correct path that can be crosschecked on the SQL server itself.
Microsoft kb : https://learn.microsoft.com/en-us/troubleshoot/sql/tools/error-message-when-you-open-configuration-manager
Once the steps are completed on the server, we need to connect to the Unitrends UI and run a manual sync. Then resave the SQL asset and check if the databases are listed. This can sometimes take up to 5 to 10 mins to sync so we would need to monitor it.
D) Namespace in SQL preventing SQL instance to appear within the UI.
If using the mofcomp doesn't resolve the issue, perform the following.
Have the customer locate MSSQL install media to install the management studio (if not installed already) and run the query below within SQL to determine if there are any spaces in the database names or tables.
"select '#' + name +'#' from sys.databases"
E) Disabling TLS 1.0 causes SQL Instance and databases to no longer display in UI
Unitrends Client Agent uses ODBC client software to access all supported versions of MS SQL Server. On Windows, ODBC software is provided as part of the operating system setup. In order to support TLS 1.2 with Unitrends agent installation customer must use minimum SQL Server Native Client 11.0 client software to access all supported versions of MS SQL Server along with ODBC. Find out the SQL Server Native Client version string installed on the client by using ODBC Data Source Administrator (see figure 1).
Open C:\PCBP\master.ini as seen in Fig 1 (Master.ini must be opened using notepad or equivalent app using ADMINISTRATOR permissions to edit) and add following parameters in the referenced section:
[SQL Server]
ODBCEncrypt=Yes
ODBCDriver=SQL Server Native Client 11.0
Once done, we can save the file. Then come to Unitrends UI, Run an inventory sync, and resave the asset from Configure> Protected assets and the databases should be listed.