NOTE: This product was retired from sale and support as of 30 September 2021. Please refer to our communication for more information. If you have not already done so, please contact your Sage Business Partner to review alternative solutions. If you do not have a Sage Business Partner who supported you with this solution and would like to hear about available alternatives, please contact 0191 479 5988.

Disclaimer

This document is provided as a guideline for configuring Microsoft SQL Server Express 2005, for use with Sage 50 Manufacturing.

It is the responsibility of the end user to ensure that the settings applied to SQL are suitable to the environment where the software will be used and that it meets business requirements.

It is also the responsibility of the end user to ensure that no IT security issues are presented by installing SQL server in the manner described in this document.

If you are unsure about this installation process or any of the information described in this article please contact Sage Technical Support or your IT personnel.

Who is this document for:

This document is intended for use with the installation of SQL Server Express 2005 in conjunction with the following products:

2009 Manufacturing Controller
2009 Batch Controller
2009 Job Controller
2009 Bill of Materials

Pre-requisites

It is assumed that you have successfully completed the installation of SQL Express 2005 and that your instance is called SQLEXPRESS. . If it is not, please substitute your instance name for this one where reference is made to SQLEXPRESS.

For information on how to install SQL Server please consult the article 'Installing SQL Express 2005 for use with Sage 50 Manufacturing.' There are both detailed and summary versions of this article depending on your level of experience.

You need to have access to the server where SQL has been installed as all of the configuration options in this article relate to features which are only available on that particular machine. If you don’t have access to it please contact your IT Personnel.

Summary

Once you have installed SQL Server Express 2005, there are some configuration options which must be applied correctly in order for Sage 50 Manufacturing to function. If all settings are not correct you may encounter connectivity issues when trying to use Sage 50 Manufacturing. 
 
Please refer to this document as a guide for configuring SQL Server for first time use, or if you are encountering SQL related error messages when trying to access Sage 50 Manufacturing.

Protocol Configuration using SQL Server Configuration Manager

SQL Server needs to be told how it will communicate with other computers on the computer network, and also with the Sage 50 Manufacturing application.

SQL communicates using various protocols - TCP/IP and Named Pipes are used primarily with Sage 50 Manufacturing. Shared Memory also needs to be enabled.

The required protocols are not enabled by default and must be enabled using SQL Server Configuration Manager. This can be located by going to :

Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools

When the Configuration Manager opens, expand the SQL Server 2005 Network Configuration node on the left and select the option called ‘Protocols for SQLEXPRESS.’ The screen looks like this:

The Protocols to the right of the screen will all show a status of ‘Disabled.’ You must enable Shared Memory, Named Pipes and TCP/IP.

Each Protocol is easily enabled by right-clicking and choosing the option to ‘Enable.’

At this point you will receive a message as follows:

Click OK to this message. We need to re-start the SQL server service but will do so once all other protocols have been enabled. 

Once you have enabled all 3 Protocols the screen will look like this:

The SQL Server service must now be re-started to ensure that the newly enabled protocols are used. This can be done in the Services section of the SQL Server Configuration Manager. Refer to the next section for details of how to re-start SQL server to bring these changes into effect.

Re-starting the SQL Server Service

SQL Server must be re-started for various reasons throughout the course of this article. You can re-start SQL using the SQL Server Configuration Manager found in:

Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools

The required feature is in the SQL Services area as illustrated:

With the Services option selected from the left panel, right click on ‘SQL Server (SQLEXPRESS)’ in the right panel and choose re-start:

It may take a few moments for the service to re-start. You will see progress bars indicating the stopping and starting of the service. Please be patient. If you experience an error message during this process please contact your IT personnel.

Allowing Remote Connections to SQL Server

Microsoft SQL server must be configured to accept remote connections, from other computers, in order for Sage 50 Manufacturing to be able to store and retrieve data from the database.

The SQL Server Surface Area Configuration tool is used to manage these settings.

The tool may be found in the following location:

Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools

It looks like this:

Choose the option entitled ‘Surface Area Configuration for Services and Connections’, highlighted above in red. The resulting screen is as follows:

Remote connections can be configured by using the left screen to access the Remote Connections section of the Database Engine settings.

Once Remote Connections has been selected, the right side of the screen should be used to select ‘Local and remote connections’ and ‘using both TCP/IP and named pipes’

Click OK to save these changes. The following error message will appear:

Please refer to section ‘Restarting the SQL Service’ on page 4 of this article and use this to restart SQL to complete the process.

Starting the SQL Server Browser service

The SQL Server Browser service must be running, as well as the main SQL Server service.

If the Browser is not running SQL will not be visible to other computers on the network.

A typical symptom of the Browser service being stopped is exhibited when configuring Sage 50 Manufacturing- you are prompted to select which instance of SQL to use to store your data but the instance (SQLEXPRESS) does not appear in the drop down list of available options.

Starting the Browser service usually rectifies the problem, although this may not be the only cause of this problem.

The process is very similar to restarting the SQL server service and is located in the SQL Server Configuration Manager tool which can be found at:

Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools

The options to concentrate on are highlighted in red in the following image:

Ensure the SQL Server 2005 Services option is select from the left panel then right click on the SQL Server Browser option from the right panel and choose ‘Start’

The Browser service may have been disabled. This prevents the option to start the service from appearing when you right click on the service in SQL Server Configuration Manager. You will find that all of the options except Properties are greyed out if this is the case.

You can re-enable the service by following these steps:

Right-click the browser service and choose Properties. The following screen will appear:

Use the drop down box to change the Start Mode from ‘Disabled’ to ‘Automatic’

Click Ok.

You may now right click on the Browser service and choose ‘Start’ as described on page 7 of this article.

SQL Server and the Windows Firewall

The Windows Firewall can prevent SQL server from communicating with other computers on the computer network and therefore render the database inaccessible from client computers.

If you are using the Windows Firewall on your server, you may experience problems in this area.

You are more likely to be experiencing problems with this if your ‘server’ is not a dedicated server operating system such as Windows Server 2003, but instead is a Windows XP machine. The standard Windows Firewall doesn’t tend to be used on a Windows Server.

The solution to any problem in this area is to add SQL server as an exception to the firewall, thereby allowing it to communicate with other computers.

Please be aware that changing firewall settings could cause network security issues. If you are in any doubt about what action needs to be taken please contact your IT support personnel.

The Windows Firewall is located in Control Panel and looks like this:

To allow exceptions, firstly ensure the Don’t Allow Exceptions tick box is not ticked, then browse to the Exceptions tab.

Click Add program to add SQL server as an exception.

Click Browse to locate the SQL server executable file (SQLSERVR.EXE). This is normally stored in the following location:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn

Select the file as follows:

Now Click Open. The file will be added to the Exceptions list for you to select as an exception as follows:

Click Ok with SQLSERVR.EXE selected. It will be added to the exceptions list.
 

With sqlservr.exe selected click OK. SQL Server has now been added to the Windows Firewall as an exception. You should no longer have any issues with regard to SQL and the Windows Firewall.

SQL Server port number

SQL, by default, uses port number 1433 to communicate with other computers. SQL will not be able to communicate with other computers on the network if port 1433 is blocked by your firewall.

The image below illustrates the following method for unblocking a port in your firewall. These settings are all applied on the Exceptions tab of the Windows Firewall, as detailed above:

1.Click Add Port.
2. The Add a Port screen is displayed.
3. Enter a Name of SQL (though you can call this anything you want)
4. the Port Number must be 1433
5. Ensure TCP is selected.
6. Click OK, then OK again and Port 1433 has been unblocked on your firewall.

Additional Firewalls

Firewalls are provided by a wide variety of Hardware and Software vendors.

Sage can not provide any support on configuring these firewalls owing to complexity and potential security issues.

The main things to consider when dealing with any firewall are that sqlservr.exe and port 1433 should not be blocked. If they are blocked, you will experience problems.