Configuring and troubleshooting SQL Server Connections

Article
10/30/2023

After installing and configuring Business Central, the Business Central Server may not be able to connect to the SQL Server. For example, you'll get error messages in Business Central Administration Shell or event log like the following :

The program could not create a connection to the server. Do you want to try again?

ServerInstance 'MicrosoftDynamicsNavServer$bc210' failed to reach status 'Running' on this server due to the following error: 'Cannot start the service.'. Additional information may be in the event log of the server.

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)

This article describes configuration changes that you can try to resolve SQL Server connection problems. For more help, see A network-related or instance-specific error occurred while establishing a connection to SQL Server.

Enable Protocols in SQL Server Configuration Manager

SQL Server Configuration Manager is a tool for managing SQL Server services and network connectivity. You can use SQL Server Configuration Manager to enable and disable network protocols. For Business Central to work correctly, the Named Pipes and TCP/IP protocols must be enabled for SQL Server.

Follow these steps to verify that these protocols are enabled or to enable them if they are currently disabled.

To enable Named Pipes and TCP/IP in SQL Server

Start SQL Server Configuration Manager.
Expand the SQL Server Network Configuration node in the tree view, and then choose Protocols for MSSQLSERVER.
Under Protocol Name in the right pane, verify that Named Pipes and TCP/IP have the status Enabled.
If either protocol is not enabled, right-click the protocol, and then choose Enable.

After exiting SQL Server Configuration Manager, start the Business Central Server again to see if the connection problem has been resolved.

Allowing SQL Server with Windows Firewall

If Windows Firewall is running on the computer running SQL Server, then you must explicitly specify the SQL Server executable file as an allowed exception before you can connect to the Business Central database.

To allow SQL Server with Windows Firewall

In Control Panel, open Windows Firewall, choose Change settings, and then click the Exceptions tab.
Choose Add program.
Choose Browse, and then browse to the location of the SQL Server executable file (sqlservr.exe). The default location on a computer running Windows Server 2008 R2 is:
```
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exe  
```

C:\Program Files\Microsoft SQL Server\MSSQL15.BCDEMO\MSSQL\Binn\sqlservr.exe
4. Double-click the executable file to select it and to exit the Browse window, and then choose OK to close the Add a Program window.

Choose OK to close Windows Firewall.

After exiting SQL Server Configuration Manager, start the Business Central client again to see if the connection problem has been resolved.

Starting SQL Browser Service

When using a named database instance for Business Central, and Business Central Server and the SQL server are on separate computers, then SQL Browser Service must be running on the SQL Server. For more information, see Start SQL Browser Service.

Integrating directly on SQL Server objects

Warning

During operations such as upgrade and app/extension synchronization, the Business Central Server synchronizes the metadata model defined in AL to physical tables on the SQL Server database.

While it's possible to integrate directly with SQL Server objects on the database (bypassing the Business Central Server), it's not recommended or even supported.

Altering SQL objects created by the Business Central Server directly on the database can break operations such as upgrade and app/extension synchronization.

Adding additional SQL objects such as triggers or stored procedures directly on the database can break operations like upgrade and app/extension synchronization, but will also break system integrations that depend on such additional objects if the table schema created by the Business Central Server synchronization changes.