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) Check WBPS logs
1. Login to the Protected Asset and open the master.ini file located in C: Drive -> PCBP
If you are not logged in as an admin, you'll need to open it via Notepad run as Administrator(Otherwise any changes can't be saved). In this path, you do need to change the file types to all:
The reason for this is because the master.ini is not considered a regular text file and it won't appear unless changed.
2. Go to the [debugging] section in the master.ini:
If you don't have a WBPS line in there, add it and have it equal 3:
This will increase the WBPS log to check for the error that verifies the problem being with the component or WMI which have been the main two errors found with these situations.
Once you have added that line, save the changes, restart the BP Agent in Services (services.msc), and re-save the asset.
3. Check the new WBPS file that should've created, and if you see the below messages:
May 24 15:33:53 : CSqlVssOdbc::QuerySQLInstancesMngt: Query StartupType of SQL Service
May 24 15:33:53 : CSqlVssOdbc::ExecuteWMIQuery: Connecting to WMI namespace root\Microsoft\SqlServer\ComputerManagement13
May 24 15:33:53 : CSqlVssOdbc::ExecuteWMIQuery: Could not connect WMI namespace 'root\Microsoft\SqlServer\ComputerManagement13' failed 0x8004100e <Unknown error code>.
May 24 15:33:53 : CSqlVssOdbc::QuerySQLInstancesMngt: Query StartupType of SQL Service
This proves what could be the component or WMI service issue noted above. Leave the WBPS line for now, this will be set to 0 later once the agent doesn't show this error again.
4. Open the Services Microsoft Management Console (services.msc). Restart the WMI process, named Windows Management Instrumentation in services.
After performing this, wait a couple minutes and re-attempt either a save of the Protected Asset or Inventory Sync. If no databases show, check the WBPS log once more. In the event the error is still present, there is a chance a reboot will be needed for the Windows Machine's WMI service to work with the Unitrends Agent.
If this resolves the issue, bring up the master.ini file again and set WBPS to equal 0 now.
WBPS=0
This is to prevent large logs from creating because a WBPS logged is normally created whenever the asset is re-saved, inventory sync, or a job is kicked off. Once the changes are saved, restart the BP Agent in Services and the machine is good to setup SQL jobs now.
In the case that it still won't resolve, set WBPS to 0, save changes, and restart the BP Agent in Services. Proceed to the next steps below.
E) 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"
F) 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.