Connect to Microsoft SQL Server
Microsoft SQL (MS SQL) Server is a popular Relational Database Management System (RDBMS) used by small and large businesses.
This post gives you an example of how you can configure Microsoft SQL Server and create a connection to it from Holistics.
High-Level
There are a few configurations you’ll need to make to use Holistics with a SQL Server:
- To enable connection from Holistics to SQL Server database:
- Allow the remote connection and configure “SQL Server Authentication” on your MSSQL server.
- TCP/IP Protocol is enabled with TCP port 1433.
- To enable connection from Holistics to the host machine(s) by using
Direct Connection:- Allow access to SQL Server via the Windows firewall.
- Whitelist the Holistics’ IP address if your SQL instance is behind the firewall
- If you're unable to whitelist our IP or allow remote connection, consider using Connection via Reverse Tunnel instead.
- A dedicated user with Read & Write permission.
Step-by-step Instructions
Step 1: Configure SQL Server to allow remote access.
1. Configure remote access
Run this script to enable remote access on your MSSQL server.
EXEC sys.sp_configure N'remote access', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
2. Configuring server authentication
Holistics requires “SQL Server Authentication” on your MSSQL server. If your MSSQL server is configured as “Windows Integrated Authentication” only, change the server configuration to “Windows Integrated Authentication and SQL Server Authentication on the Security page.
Step 2: Config SQL Server to enable TCP/IP connection
You need to configure the MS SQL Server instance with a specific fixed port to enable Holistics to connect to your MS SQL Server. If enabled, the default instance of the SQL Server Database Engine listens on TCP port 1433.
1. On Windows, you can use SQL Server Configuration Manager:
- In the SQL Server Configuration Manager, expand SQL Server Network Configuration and click Protocols for
instanced name - In the list of protocol names, right-click TCP/IP and select Properties.
- On the Protocol tab, from the Enabled list, select Yes.
- On the IP Addresses tab, find the interface that your workstation uses and see the connection port. By default, the port is 1433. Verify that other running applications do not use the same port. To configure for all IP, scroll down to IPAII section, and set the value 1433 for the TCP port.
If you changed any settings, restart the server. For most situations, the restart resolves connection problems.
2. On Linux, you can run mssql-conf script to configure SQL Server
The network.tcpport setting changes the TCP port where SQL Server listens for connections. By default, this port is set to 1433. To change the port, run the following commands:
2.1. Run the mssql-conf script as root with the "set" command for "network.tcpport":
sudo /opt/mssql/bin/mssql-conf set network.tcpport <new_tcp_port>
2.2. Restart the SQL Server service:
sudo systemctl restart mssql-server
For more information, see Sets the network configuration of the target instance of SQL Server, Install PowerShell on Windows, Linux, and macOS, Install the SQL Server PowerShell module, Configure SQL Server on Linux with the mssql-conf tool.
Step 3: Allow access to SQL Server through the firewall
To access an instance of the SQL Server through a firewall, you must configure the firewall on the computer that is running SQL Server.
- On Windows, see Configure a Windows Firewall for Database Engine Access and Configure the Windows Firewall to Allow SQL Server Access
- On Linux, you can use tools such as UFW (Uncomplicated Firewall) or Firewall-cmd to configure rules for SQL Server ports.
Step 4: Whitelist the Holistics’ IP address
If your SQL Server instance is behind the firewall, you need to whitelist the Holistics’ IP Address as per your region to allow Holistics to connect to your database. Use Direct Connection as the Connection type when you create the data source.
1. On Windows, you need to configure Windows Firewall to allow these IP Address.
Let’s select the Inbound rule that you’ve created in Step 3, click on the Properties at the right pane. Then, under the Scope tab, specify the IP address to connect your database remotely.
2. On Linux, you can set whitelist IP addresses through tools as UFW (Uncomplicated Firewall) or Firewall-cmd. See guideline of each tool for more information.
Step 5: Create a dedicated User for the Holistics connection.
To create this user, please follow this instruction.
Step 6: Connect MS SQL Server to Holistics as a data source.
- Click New Data Source button, and select Microsoft SQL Server as the database type. Then fill in the form:
- Display Name - Give the connection a name.
- Connection Mode - Direct connection.
- Host - The MS SQL server local or public IP Address.
- Port - The default port number is 1433.
- Database Name - The database that you wish to replicate.
- Username - user name for dedicated Holistics user.
- Password - password for dedicated Holistics user.
- Click the button Test Connect, if the connection status is successful, click Save to finish.
Connecting to an MS SQL Server Named Instance
Microsoft SQL Server supports multiple instances of a SQL Server database running concurrently on the same server. An instance is identified by an instance name, eg host_name\\instance_name
Holistics need to know the TCP port number of a SQL Server instance. By default, SQL Server listens on TCP port number 1433, but, for named instances, the TCP port is dynamically configured.
- So, the first step is you need to find the port the named instance is running following this guide.
- Then, in the Data Source Connection Form, you could try to connect normally by replacing the port number
1433with the dynamic port number that you got in the above step, for example:
The host shouldn’t include the instance name
Since the ports will be changed every time the server is rebooted, to avoid this issue in the future, you can try to assign static ports for named instances following this guide Configure a Server to Listen on a Specific TCP Port - SQL Server | Microsoft Docs