How to Configure Local Database Server to Access It from Skyvia

In order to connect directly to a database server, Skyvia requires it to be accessible from the Internet (using TCP protocol).

For this, several conditions must be met. The Firewall must allow access to the database server port. Additionally, the database server must have the corresponding login registered and must be accessible from Skyvia. SQL Server must allow access via the TCP/IP protocol and SQL Server authentication.

Additionally, if you are connecting to a computer in your local network, you should use port forwarding.

This topic contains some information about configuring databases, useful for creating direct connections from Skyvia. As an alternative, for local database servers you can also use agent connections. In most cases agent connections does not require any additional database or firewall configuration.

How to Configure Windows Firewall to Allow Access from Skyvia

Please note that this information is about configuring the standard Windows Firewall. If you use other firewall application or other operating system, read its documentation for the information on how to allow access.

Skyvia requires the 1433 port (default SQL Server port) to be open.

To open this port in Windows Firewall, perform the following steps:

  1. Open Windows Firewall with Advanced Security using the Start menu or Start screen.
  2. In the tree on the right part of the Windows Firewall with Advanced Security window, right-click the Inbound Rules node and then click New Rule on the shortcut menu.
  3. In the New Inbound Rule Wizard dialog box, click Port and then click Next.
  4. Specify the 1433 port number in the Specific local port box and click Next.
  5. Click Allow the connection and then click Next.
  6. Select the Public, Private, and Domain checkboxes and click Next.
  7. Enter Name and Description for the rule and click Finish.

Skyvia will access your server from the IP 40.118.246.204, 13.86.253.112, and 52.190.252.0.

How to Configure PostgreSQL Server for Remote Access

If your PostgreSQL server is not configured for remote access, you may need to modify its postgresql.conf and pg_hba.conf files.

In the postgresql.conf file, check the listen_addresses setting. You need to either set it to

listen_addresses = '*'

in order to allow connections from any IP addresses. Or you may list the necessary IPs separated by commas in this setting. In the latter case, add the Skyvia IPs (40.118.246.204, 52.190.252.0, and 13.86.253.112) to this list.

In the pg_hba.conf file, add the following lines to the end of the file:

1
2
3

host all all 40.118.246.204/32 md5
host all all 13.86.253.112/32 md5
host all all 52.190.252.0/32 md5

This allows access from the Skyvia IP for all the PostgreSQL users. For more information, please refer to PostgreSQL documentation.

After modifying these files, please restart the server.

How to Configure SQL Server for Connecting via TCP/IP

The TCP/IP protocol is usually enabled in SQL Server configuration by default, and you may omit the steps below. However, if your SQL Server has custom configuration with TCP/IP protocol disabled, you can enable it in the following way:

  1. Click the Start button and type SQL Server Configuration Manager. Then press Enter.
  2. In the tree on the right part of the SQL Server Configuration Manager window, expand the SQL Server Network Configuration node and click the Protocols for SQL node.
  3. If the TCP/IP protocol has status Disabled in the right pane of the SQL Server Configuration Manager window, right-click it and then click Enable on the shortcut menu.
  4. Right-click the TCP/IP protocol in the right pane of the SQL Server Configuration Manager window and then click Properties.
  5. On the Protocol tab of the TCP/IP Properties dialog box, make sure that the Enabled property is set to Yes. If not, set it to Yes. Then click the IP Addresses tab.
  6. On the IP Addresses tab, for the IP, corresponding to the external IP address of your server, set the Enabled property to true. Then click OK.
  7. Then you need to restart SQL Server in order for configuration changes to be applied. In the tree on the right part of the SQL Server Configuration Manager window, click the Protocols for SQL node, and then right click the corresponding SQL Server instance in the right pane and click Restart on the shortcut menu.

How to Create SQL Server Authentication Logins

You can use various applications or tools to manage authentication logins on SQL Server. Here we will show how to do it with Microsoft SQL Server Management Studio.

Since Windows Authentication cannot be used from outside the domain, logins that will be used for connecting from Skyvia must use SQL Server authentication.

To create SQL Server authentication logins with Microsoft SQL Server Management Studio, please perform the following steps:

  1. In SQL Server Management Studio Object Explorer, expand the node of the server instance you want to create a new login to.
  2. Right-click the Security node, then point to New and click Login.
  3. Enter the name of the new user in the Login name box.
  4. Select SQL Server authentication.
  5. Enter a password for the new login to the Password box and reenter it to the Retype Password box.
  6. In the Default database list, select a default database for the login or leave the default value master.
  7. Change other login settings if necessary.
  8. Click OK.

How to Configure SQL Server for Using Mixed Mode Authentication

As written above, SQL Server authentication must be used for connecting to SQL Server from the Internet. So, if your SQL Server is configured to use Windows Authentication only, you need to configure it to use Mixed mode authentication. Mixed mode authentication allows using both SQL Server Authentication and Windows Authentication. We will use Microsoft SQL Server Management Studio again in order to configure mixed mode authentication. Perform the following steps:

  1. In SQL Server Management Studio Object Explorer, right-click the node of the server instance you want to configure and then click Properties.
  2. In the Select a page pane, click Security.
  3. Select SQL Server and Windows Authentication mode and click OK.
  4. SQL Server Management Studio will display the message that SQL Server should be restarted. Click OK.
  5. In Object Explorer, right-click your server and then click Restart.