About:

SQL Server Plug-in provides an automated database file detection mechanism based on a SQL Server user account login: this user account should be created as required then configured as indicated in the release notes, SQL Server Plug-in Configuration section.

Briefly, this mechanism works as follows when the File Filter Discovery task executes:

• extracts the installed SQL Server instance names (data source) from the Windows registry
• combines the detected data sources (SQL Server instance names) with the configured SQL Server user account and builds the connection string (done for each instance)
• connects to each SQL Server instance, queries and retrieves the database files, then adds them to the protected set

If for some reason the mechanism fails to extract the connection string parameters, or the extracted data source parameters are not working then the connection to the database fails with one of the warning messages described in the next section.

SQL Server Plug-in provides an emergency backdoor mechanism for bypassing the automated one. This should be applied only in the situations where the automated mechanism is not working.

Symptom:

File Filter Discovery task logs any of the following (or similar) warnings each time it executes.

• SqlServer: Failed to obtain the databases list using connection string: Initial Catalog=master;Data Source=MSSQLSERVER; User ID = neverfail; Password=******. A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

• File Filter Discovery: Plug-in task failed: Errors encountered. Make sure the plugin was configured correctly. Some data may not be protected. Please see the logs. (System.ApplicationException: Errors encountered. Make sure the plugin was configured correctly. Some data may not be protected. Please see the logs.
  at SqlServerNFPlugin.SqlServerNFPlugin.OnUpdateFileFilters())

This means that some data may not be protected as the connection is used for retrieving the SQL Server database files.

Workaround:

This procedure should be used only in the following cumulative conditions:

• SQL Server Plugin is configured correctly with a valid Windows User or SQL Server Login account as described in the plugins release notes, SQL Server Plug-in Configuration section
• SQL Server is running and healthy: all the installed instances can be accessed with the configured user account, using other client tools.
• File Filter Discovery task fails as described in the previous section

 Procedure:

• On the active server in C:\ProgramData folder create a file named (exactly) SqlServerConnectionStrings.xml.
• Edit C:\ProgramData\SqlServerConnectionStrings.xml exactly as described below: each protected SQL Server Instance should have the correct data source specified in the corresponding xml element.

  <?xml version="1.0"?>
  <!--Connection strings used by the Neverfail SQL Server Plugin.-->
  <connectionStrings>
  	<SQLServerInstance>
  		<name><!--DefaultInstance(MSSQLSERVER)--></name>
  		<connectionString>Initial Catalog=master;Data Source=<!--hostname--></connectionString>
  		<providerName>System.Data.SqlClient</providerName>
  	</SQLServerInstance>
  	<SQLServerInstance>
  		<name><!--NamedInstance--></name>
  		<connectionString>Initial Catalog=master;Data Source=<!--hostname\NamedInstance--></connectionString>
  		<providerName>System.Data.SqlClient</providerName>
  	</SQLServerInstance>
  </connectionStrings>

Notes:

• SQL Server Plugin will detect that C:\ProgramData\SqlServerConnectionStrings.xml exists and will automatically add a corresponding inclusion file filter
• If C:\ProgramData\SqlServerConnectionStrings.xml is deleted/renamed/moved, the inclusion file filter will be automatically removed
  • Important: in the situations when user decides not to use anymore the C:\ProgramData\SqlServerConnectionStrings.xml connection-based mechanism then the file should be removed from all the nodes. Neverfail Engine does this automatically all the nodes in the cluster are connected/replicating and the file is deleted on the active node. However, to be on the safe side, the user must assure the file is no longer present on the passive after its removal from the active.  

Applies to:

Neverfail for SQL Server Plug-in v201.5.14