How Can We Help?
You are here:
Print

SQL Configuration

The AmZetta zPortal Controller requires a SQL database for storing data. If you do not have an instance of a SQL server running, you can install an instance of SQL Express.

Microsoft SQL Compact Installation

Ensure that Microsoft SQL Compact is installed on the server that will host the zPortal Controller. If this is not installed, or you are not sure if it is installed, you can download the installer from here.

Configure SQL Server to Listen on Static Port

Static ports are being encouraged and are a must in zPortal Configurations for security and ease of manageability reasons.
Follow the below steps to configure SQL Server to use static TCP port:

  1. Open SQL Server Configuration Manager
  2. Click on SQL Server Services in the left pane
  3. In right pane, note down the Process ID of the SQL Server instance, such as 116 in the screenshot above
  4. Open Command Prompt and run the following command:
    netstat -ano | findstr /i <PID of SQL server instance>
    example: netstat -ano | findstr /i
    4604
  5. If the command does not show any result (as shown in the screenshot above), that means the SQL server instance is not using any static TCP port and should be configured to use one
  6. To enable SQL Server to use static port, in SQL Server Configuration Manager
    1. Click on SQL Server Network Configuration in the left pane
    2. Right-click on TCP/IP protocol
    3. Click on Enable

  7. Restart SQL Server service and identify the process ID assigned to SQL service.
  8. In the command prompt execute command to verify that SQL services are listening on any TCP port:
    netstat -ano | findstr /i <PID of SQL server instance>

    1. To configure a specific port – In SQL Server Configuration Manager:
      1. Click on SQL Server Network Configuration in the left pane
      2. Right-click on TCP/IP protocol
      3. Select option Properties
    2. Go to IP Addresses tab
    3. Scroll down to IPAll section
    4. Remove the value for TCP Dynamic Ports (do not enter zero (0)!!)
    5. Enter 1433 for TCP Port
  9. Restart SQL Server service, identify new process ID assigned to SQL service and in the command prompt execute command to verify that SQL Service is now listening on configured TCP Port 1433
    netstat -ano | findstr /i <PID of SQL server instance>
  10. At this stage, SQL Express is configured to listen on standard port 1433
NOTE: If you are using firewall you need to add an exception for the 1433 port to allow TCP/IP traffic on port 1433.

Configure a Windows Firewall for Database Engine Access

  1. To add a windows firewall exception for the port 1433, open Administrative Tools and select Windows Firewall with Advanced Security option.
  2. In the Windows Firewall with Advanced Security dialog box click on the Inbound Rules options and select the New Rule command.
  3. In the New Inbound Rule wizard select the Port option and click Next
  4. In the Protocols and Ports window specify the protocols and ports to which a rule applies. Select the TCP option, in the Specific local ports text box enter the 1433 port, and click Next
  5. In the Action window select the Allow the connection to specify the action to be taken when a connection matches the conditions specified in the rule and click Next
  6. Specify the profiles for which the rule applies in the Profile window, and click Next
  7. In the last window specify the name of the created rule and click the Finish button
  8. You can now see the created rule in the list of inbound rules

Turn On SQL Server Browser Service

Follow the below steps to enable the SQL Server browser service

  1. Open SQL Server Configuration Manager and click on SQL Server Services in the left pane, right-click SQL Server Browser service and select Properties
  2. Go to Service tab and for Start Mode option change start type to Automatic and click Apply
  3. Click Start button to start SQL Browser service
  4. Confirm that SQL Server Browser service is up and running

Enable Remote Connection on SQL Server Instance

  1. Open SQL Management Studio – Connect to SQL server to be configured as database server for zPortal Controller
  2. Right-click server name in the left pane and select Properties
    <sql20.png>
  3. Select Connections in the left pane
  4. Make sure the checkbox Allow remote connections to this server  is selected as it is shown in the screenshot below
    <sql21.png>
  5. To make sure that changes comes in effect, open SQL Server Manager. In the left pane of SQL Server Configuration Manager click SQL Server Services, right-click SQL Server<instance_name>, and click Restart
    <sql22.png>
Was this article helpful?
4.8 out Of 5 Stars

2 ratings

5 Stars 50%
4 Stars 50%
3 Stars 0%
2 Stars 0%
1 Stars 0%
5
How can we improve this article?
Please submit the reason for your vote so that we can improve the article.
Table of Contents
Top