Error: .Net SqlClient Data Provider: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.

Product:  BizInsight
Applies to:  MAS 90/200
Article Type:  Error 

Summary:
When dragging and dropping a BizInsight function into a worksheet, the following error occurs:

Error: .Net SqlClient Data Provider: 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) dataset [MAS90_Balances_All]

Details:
This error indicates that there is some kind of connectivity problem between the client workstation and the server where the linked servers have been created.

Resolution/Workaround:
To troubleshoot this issue, do the following:

1. Check basic connectivity to the server.
    a. Click on Start and then click on Control Panel.  
    b. Double-click Administrative Tools and then double-click Data Sources (ODBC)
    c. Click on the System DSN tab and click Add.
    d. Select SQL Server from the list of drivers.
    e. Enter a name in the Name field.  Any name will work.
    f. In the Server field, enter the servername\instance name of the SQL Server installation where the linked servers are located.  Click Next.
    g. On the next dialog, select a valid authentication method either Windows authentication or SQL authentication.  Click Next.
    h. If you are able to get to the next dialog where you’re able to choose the Default database, then you need to double-check the server information
    entered in the BizFunctions.xml and make sure that the servername\instance name you provided in the DataSource1 section  is the same as what you
    used for this data source.
    i. If you receive a Login Failed error, correct the credentials you’ve provided and try the ODBC data source again.
    j. If you get an error here like “SQL Server does not exist or access denied”, you have confirmed a basic connectivity problem to the server and
    should continue with the next troubleshooting step.

2. Confirm that you are using the correct servername\instance name.
    a. On the server, start SQL Server Management Studio
    b. When the Connect to Server dialog opens, check the contents of the Server Name field. This field displays the servername and instance name.  It
    is likely that this is the servername and instance name you should be providing in the ODBC data source but if you are using SQL Server for other
    applications, you will want to connect to this instance and confirm that the linked servers exist under Server Objects > Linked Servers.   If you do
    not see the linked server objects, then you are in the wrong instance.  Click on File > Disconnect Object Explorer.  Then click on File > Connect    
    Object Explorer to reopen the Connect to Server dialog.  Select the drop down list next to the Server Name field and select a different
    servername\instance name until you locate the instance in which the Linked Servers have been created.  Once you locate the correct instance, make
    a note of the servername\instance name and try that back on the client workstation in the ODBC data source from step 1.  If you continue to be
    unable to advance to the next dialog in the ODBC data source, continue with the next troubleshooting step.

3. Confirm that the SQL Server installation was configured to allow remote TCP/IP connections.
    a. On the server, click on Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server  Surface Area Configuration.
    b. Click on Surface Area Configuration for Services and Connections
    c. In the left pane, locate your SQL Server instance, expand Database Engine and select Remote Connections.
    d. In the right pane, make sure that the Local and remote connections radio button is selected and that the Using TCP/IP only radio button is
    selected.  
    e. If the instance is not configured for Local and remote connections, make those changes and click Apply.  You will get a message indicating that
    the change will not take effect until the database engine is restarted.  Click OK to that message and then in the left pane, click on Service under
    Database Engine and in the right pane, click Stop and then Start.  Test the ODBC data source again from step 1.  If you continue to have
    connectivity problems, proceed to the next troubleshooting step.

4. Confirm that the SQL Browser service is running.
    a. In the SQL Server Surface Area Configuration tool, expand the SQL Server Browser group in the left pane and click on Service.   If you closed
    the SQL Server Surface Area Configuration tool, reopen it again following steps 3a and 3b above.
    b. In the right pane, confirm that the Startup Type is set to Automatic and the service is started.
    c. If the service is not set to Automatic and/or is not started, correct these items and then test the ODBC data source again.  If you continue to have
    a problem with the ODBC data source, continue to the next troubleshooting step.

5.   Check the server configuration if it is Windows Server 2008.
    a. Windows Server 2008 has some additional security features that may need to be enabled in order for a client workstation to be able to connect to
    a SQL Server installation on that server.
    b. Launch Server Manager on the Window   s Server 2008 server.  
    c. Go to Features
    d. Check to make sure the following Features are enabled and if they aren’t, add them:
        i. .NET Framework 3.0 Features > XPS Viewer
        ii. Desktop Experience
        iii. Remote Server Administration Tools > Role Administration Tools > Web Server (IIS) Tools.
    e. If you had to make changes, check the ODBC data source again and see if the connectivity issue is resolved.

6. Confirm that the firewall is not blocking the connection.
    a. Most SQL connections are made via Port 1433.  The firewall could be blocking the port being used for the SQL calls. 
    b. To confirm if the problem is with the firewall, disable the firewall temporarily and test from the client again.  
    c. If the error does not occur, re-enable your firewall and then make the appropriate changes that will allow the SQL connection to be passed
    through the firewall.  Usually you need to add two port exceptions:  1 for TCP port 1433 for SQL Server and 1 for UDP port 1434 for SQL Browser.  It may
    also be necessary to add a program exception for sqlservr.exe.

If the provided Resolution/Workaround does not resolve your issues, please contact BizNet Software Support for further assistance.