Configure SQL Server to Allow Remote Connections - Powered by Kayako Help Desk Software

Knowledgebase

2Accounting 5Accounts Payable 28Accounts Receivable 3Backups 3Backups 5Checks 3Commissions 1Consignments 1Conversions 9Credit Card Processing 1Customer Management 22Customers 2Employees 16Errors 7General 10General Ledger 1Help File 5Internet Orders 79Inventory 49Invoices & Invoicing 6Labels 15Layaways 6Multi-Site for POSitive 14Networking 6New Features 19Orders 15POSitive Commerce - Magento 22Printers & Printing 19Purchase Orders 4Quotes 21Reports 1Service Appointments 14Service Orders 13Setup POSitive (General) 14SQL Database 4Stations 7Taxes 3Tools 11Troubleshooting 6Upgrades 10Vendors

Knowledgebase: Errors

Configure SQL Server to Allow Remote Connections

Posted by Sean W, Last modified by Sean W on 06 March 2018 11:12 AM

When you try to connect to an instance of Microsoft SQL Server from a remote computer, you may receive an error message. This problem may occur when:

SQL Server Instance not configured to accept remote connections
SQL Browser service is not running
SQL Server and SQL Browser service it blocked by a firewall

--- PROJECT SUMMARY---

!!! WARNING !!!
The following article describes configuration settings which may increase your security risk, but also may be necessary for POSitive to run smoothly and with ease. Before making these changes, we recommend that you evaluate the risks that are associated with their implementation in your network environment, and do so at your own discretion.

Step 1: Enable remote connections
Step 2: Enable the 'SQL Server Browser' service
Step 3: Configure the Windows Firewall to allow remote SQL Server connections

------------------------------
STEP 1: ENABLE REMOTE SQL CONNECTIONS
------------------------------

1. Click the Windows Start button and start typing "configuration Manager"
2. Choose the 'Configuration Manager' in the search results list
3. Expand 'Services and Application' in the left-pane, then expand 'SQL Server Configuration Manager'
4. Next, expand each instance of 'SQL Native Client xx Configuration' and 'SQL Server Network Configuration' in the left-pane
5. In the left-pane, select each instance of 'Client Protocols' and 'Protocols for <InstanceName>' and make sure the following settings are applied in the right-pane by right-clicking on the protocol name and choose 'Enable' or 'Disable' where applicable:

--- Correct Settings ---
'Named Pipes' = Disabled
'TCP/IP' = Enabled

On the right-pane make sure the SQL Server and SQL Server Browser services are Running under the State column. If either service is not running then open Services.msc from the Windows search box, right-click on the service and choose Properties. Change the Startup type to Automatic, Start the service then choose Apply > Ok, and then close the Services screen.

NOTE:
When you click the Automatic option, the SQL Server Browser service starts automatically every time that you start Microsoft Windows.

3. On the SQL Server Configuration Manager expand all options on the left-pane. For each Protocol you click on the left make sure the following settings are applied on the right-pane (Right-click on the protocol Name and choose Enable or Disable to change its current status).

--- Correct Settings ---
Named Pipes = Disabled
TCP/IP = Enabled

NOTE:
IF YOU MADE ANY CHANGES, just click OK when you receive the message "Any changes made will be saved; however, they will not take effect until the service is stopped and restarted," and then open Services.msc from the Windows search box, right-click on the service for which you made changes to and choose 'Stop'. Wait until the service stops, then right-click on the service again and choose 'Start' to restart the service.

------------------------------
STEP 2: ENABLE THE SERVER BROWSER SERVICE
------------------------------

The SQL Server Browser service only needs to be enabled one time, regardless of how many instances of SQL Server you are running. To enable the SQL Server Browser service, follow these steps.

1. Click the Windows Start button and start typing "configuration Manager"
2. Choose the 'Configuration Manager' in the search results list
3. Click on 'Services' in the left-pane
4. In the right-pane, right-click on 'SQL Browser Server' and choose Properties
5. On the 'General' tab, change the 'Startup type:' is to 'Automatic' and click 'Apply', if it is not already set to that
6. Click 'Start' if the service is not already running
7. Click OK exit the Properties page

NOTE:
When you set the 'SQL Server Browser' service to the 'Automatic' option, the service starts automatically after you start or restart your computer.

NOTE:
When you run the SQL Server Browser service on a computer, the computer displays the instance names and the connection information for each instance of SQL Server that is running on the computer. This risk can be reduced by not enabling the SQL Server Browser service and by connecting to the instance of SQL Server directly through an assigned TCP port. Connecting directly to an instance of SQL Server through a TCP port is beyond the scope of this article. For more information about the SQL Server Browser server and connecting to an instance of SQL Server, see the following topics in SQL Server Books Online:

* SQL Server Browser Service
* Connecting to the SQL Server Database Engine
* Client Network Configuration

------------------------------
STEP 3: CONFIGURE THE WINDOWS FIREWALL TO ALLOW REMOTE SQL SERVER CONNECTIONS
------------------------------

Firewall systems help prevent unauthorized access to computer resources. If a firewall is turned on but not correctly configured, attempts to connect to SQL Server might be blocked. To access an instance of the SQL Server through a firewall, you must configure the firewall on the computer that is running SQL Server to allow access.

The firewall is a component of Microsoft Windows. You can also install a firewall from another company. This topic discusses how to configure the Windows firewall, but the basic principles apply to other firewall programs.
See: http://kb.gopositive.com/firewallconfiguration

Updated 3/6/2018

Help Desk Software by Kayako