Data Integration
      Back to home
      1. Datagration Knowledge Base
      2. Getting Started
      3. Data Integration

      Connecting to a local SQL Server (Internal Article)

      This guide will be relevant to everyone who has SQL Server running on their local machines (e.g. their laptops) and would like to create a data connection in PetroVisor from the web app (app.petrovisor.com) to their laptop.

      The process can be broken down into 3 main parts:
      1. Network traffic tunneling
      2. SQL Server settings and configuration
      3. Firewalls
      This document was made using a Windows machine, but it can be done with Macstoo1.

      Network traffic tunneling

      • Go to https://ngrok.com/and create an account. After login you will be prompted to download for your machine
      • After downloading and extracting the ZIP you will have an executable

       
      • Copy the authtoken and issue this command on the machine that you installed ngrok on
      • In order to start a tunnel, we will issue the following command:
        • .\ngrok.exe <protocol> <port>
        • .\ngrok.exe tcp 1433
          • This is the command we will use to tunnel traffic to our SQL Server
      • The “forwarding address” is what we are interested in0.tcp.ngrok.io:19826–this is a one-time URL. Whenever ngrok is stopped and started it will be with a new address
       

      SQL Server Configuration Settings

      There are a couple parts to this one. You will need to create local sql accounts to the database you want to connect to for Petrovisor (don’t use Windows authentication), and you will need to enable remote connections on the sql server
      • Local SQL Accounts
        • Create user accounts using the following commands:

       

      CREATE LOGIN REPLACE_ME WITH PASSWORD = 'REPLACE_ME';

      create user REPLACE_ME for login REPLACE_ME;

      exec sp_addrolemember 'db_owner', ‘REPLACE_ME';


      • Make sure SQL Authentication is enabled for the server by right clicking the server -> Properties -> Security -> top question
       
      • Go to Connections and make sure “Remote Connections” are allowed

       

      Allow remote connections

      • Go to “SQL Server Configuration Manager” and enable TCP/IP for your SQLServer
       

      Firewalls

      • You will need to open up port 1433 on your machine’s firewall. You can do this on Windows by searching for “Windows Defender Firewall with Advanced Security”
      • Add an inbound rule for port 1433 for TCP connections. Select “Allow Connections” when asked.
       

      Testing

      MAKE SURE TO REPLACE COLON( : ) TO COMMA ( , ) IN SERVER NAME  in0.tcp.ngrok.io,19826
       
       
       

      Troubleshooting

      Try connecting from both PetroVisor and SSMS. Sometimes there are issues with SSMS when the app may work. Try these steps (from Stack Overflow - Where Developers Learn, Share, & Build Careers)
       
       

       

      • If you get the following error: "error: 10054 an existing connection was forcibly closed by the remote host" then go to the SQL Server Configuration Manager and navigate to the server's TCP/IP properties and change the "TCP Port" to 1433

       
      • If the TCP Port under the IPAll section is set to 1433, then in the previous sections of IP Addresses (such as IP1-IP9), the TCP Ports needs to be left blank. An example of what the properties window setting should look like is:
           
      • If you get “Cannot connect to WMI provider” errors while using SQL Server Configuration Manager then follow these steps -