A network-related or instance-specific error occurred while establishing a connection to SQL Server

This article will discuss troubleshooting steps when encountering this error when creating or opening a SQL database.

When you attempt to create or open a SQL database, you receive the following:

System.Data.SqlClient.SqlException: A network-related or instance-specific error occurredd 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 remoter connecitons.

This message is an error returned by SQL Server, not SureSync. SureSync is simply reporting this error back to you. The error indicates that something is wrong with either the details provided on the SQL Database Open dialog or with the configuration of the SQL Server instance itself.

Step 1: Is your SQL Server remote from the SureSync Machine?

If you are trying to access a SQL Server instance on the same machine, skip to step 2.

If you are trying to create or open a database on a remote SQL Server, that SQL Server must be configured to allow remote TCP connections.

To confirm this, you will need to go to your Start menu. Go to Microsoft SQL Server [YearHere]. For example, Microsoft SQL Server 2022. Expand that folder and select SQL Server [YearHere] Configuration Manager. For example, SQL Server 2022 Configuration Manager.

The SQL Server Configuration Manager will load. Expand SQL Server Network Configuration in the left-hand tree view. Click on Protocols for [InstanceName]. For example, Protocols for MSSQLSever or Protocols for SQLExpress.

SQLConfigurationManager

Ensure that TCP/IP is set to "Enabled." If not, right-click on TCP/IP and select "Enable." You will need to restart the SQL Server service for this change to take effect.

Step 2: Confirm Permissions

The SureSync Desktop will be accessing the SQL Server as the user account you're logged into the machine as. This account must have permissions granted in the SQL Server to allow access. If creating a database, the account must have sys_admin rights.

If you do not have sys_admin rights to the SQL Server as the account you're logged into, a create database will fail. As an alternative, you can create a blank database on SQL Server using SQL Management Studio running as an account with permissions to do so. You can then open that blank database in SureSync.

The user account running the Desktop must have a minimum of db_owner rights on the SureSync SQL database.

Step 3: Confirm the SQL Details On the Open / Create Dialog are Accurate

From the SureSync Desktop, click on Home. Click on Database and then Create a New Database (or Open Existing Database depending).

CreateANewDB1

Click on the appropriate button for what you're attempting to do. For this example, we will use "Create a new database."

CreateANewDB2

Enter the following information to create the SureSync database:

Name of the SQL Server and any Instance Name: Enter the name of the SQL server and an instance name if used. For example, dev40\sqlexpress.
Name of any Failover Partner (mirrored) SQL Server: This is an optional field that allows you to use a SQL Failover Partner. This functionality has been deprecated from SQL but is currently still available. Should be defined as machinename\instancename.
Name of SQL Database: Enter the name of the SQL database that should be created.
Full path and file name of the new SQL database: Enter the path on the SQL server where the database files should be created. This folder must already exist. For example, G:\Databases\SureSync.mdf.

If you want to use Windows authentication with the SQL database, you can click ‘Continue’ to create the database.

If you want to use SQL authentication with the SQL database, you will click on the ‘Set SQL Database Credentials’ button to provide the credential. After that, you can click ‘Continue’ to create the database.

Once you have provided the correct details, click the "Continue" button to proceed. As long as the details provided are correct, the database will open without this error.