Microsoft SQL Server connectivity issues

    • Summary:Steps to resolve Microsoft SQL Server connectivity issues.
    • 2 min read
    On this page

    Different versions of Microsoft SQL Server install with different defaults. This can cause the SQL Server to be unavailable for backing up databases by the SQL Server Agent. There are several ways to address this issue.

    Using Surface Area Configuration tool

    1. Run the SQL Server Surface Area Configuration Tool from the start menu.

    2. Choose the ‘Surface Area Configuration for Services and Connections’ near the bottom of the page.

    3. For each database in the list that you are intending to have backed up, the following will need to be configured:

      1. Choose the ‘Remote Connections’ component for the database being configured.

      2. Choose the ‘Using both TCP/IP and named pipes’ option under the ‘Local and remote connections’ option on this page.

      3. Click Apply on this page to save your settings.

      4. Acknowledge the warning message that the database engine must be restarted.

      5. Choose the ‘Service’ component for the database being configured.

      6. Click Stop on the Service page.

      7. After the service is stopped, click Start.

    4. Repeat the sub-steps for the step above for any other database server/engines that you want to have backed up.

    5. Choose the ‘Service’ Component under the ‘SQL Server Browser’.

    6. Make sure Startup type is set to Automatic.

    7. Click Apply to save the configuration.

    8. If needed, click Start to start the SQL Server Browser service.

    9. Click OK to close the ‘SQL Server Surface Area Configuration’ tool.

    Using Configuration Manager

    1. Run the ‘SQL Server Configuration Manager’ tool from the Start Menu.

    2. Set the ‘SQL Server Browser’ service to the start mode of ‘Automatic’ and the state of ‘Running’.

    3. For each database server to be backed up, enable the ‘Shared Memory’, ‘Named Pipes’ and the ‘TCP/IP’ protocol names.

    4. Double-check the client expressed protocols, by selecting the ‘Properties’ context menu on the ‘Client Protocols’ component.

    5. Make sure that the ‘Shared Memory’, ‘Named Pipes’ and ‘TCP/IP’ protocols are enabled.

    6. Click OK to save your changes.

    7. Close the ‘SQL Server Configuration Manager’.