Setting up the Microsoft SQL Server Data Gateway for Popdock

Published: Oct 10, 2023

Setting up the Microsoft SQL Server Data Gateway for Popdock

Published: Oct 10, 2023

Pre-requisites:

– Internet Information Services (IIS) installed on the server that hosts the gateway (How to install IIS)

– Install the .NET Core Hosting Bundle (Note: As of 06/06/2023 the latest version is v6.0.16)

– Have an SSL/TLS certificate (Wildcard is recommended)

– Have an externally facing URL that will be bound to the gateway for the web service (i.e., www.example.comlocalhost will NOT work for this)

– Whitelist the Popdock IP addresses listed on our website here

Hardware Requirements:

– Microsoft Windows Server 2012 or higher

– 8GB of RAM

IIS needs to be installed BEFORE the .NET Core Hosting Bundle. You may need to restart IIS after installing the bundle. To do so, open the command prompt as Administrator and run the command iisreset.

 

 

Installation and Setup Steps

1. Download the SQL Server Gateway.

2. Install the Gateway following the steps below:

a) Select Next in the installer to begin.

6307d9d470b3612e80758d89 sqlgateway1

b) Select the desired folder where the files will be installed. By default, the Program Files (x86) directory will be chosen.

6307d9d470b3618748758d88 sqlgateway2

c) Provide the credentials to connect to the Microsoft SQL Server.

· Server: The local address and the instance to the SQL Server, and the port as necessary (ex. Demo1SQL,49721).

· Database: The name of the database you need to access.

· Username: The desired SQL Server user. This user does not need to be “sa” but they must have access to be able to read the SYS tables in order to access the metadata.

· Password: The password for the SQL Server user above.

6307d9d470b361c0cf758d8a sqlgateway3

d) Choose the Authentication type that Popdock will use to connect with the Gateway. We highly recommend using Token as it is easier and more secure, but we will cover Basic authentication as well.

 

6307d9d470b36180a6758d87 sqlgateway4

· Token

Create a Token that will be used by Popdock to connect. The Token can be created however you like whether through a random generator or a custom one. Store this value somewhere safe for later use.

setting microsoft sql server data gateway popdock
6307d9d470b3616c2c758d8b sqlgateway5

 

· Basic Authentication

Create a Username and Password that will be used by Popdock to connect to the gateway.

6307d9d470b3616f2f758d86 sqlgateway6

 

e) Select the Install button to finish the process.

6307d9d470b361582f758d8c sqlgateway7

If Using Token, continue with the following steps:

Once you have finished up with the installer, go to the SQL Server data Gateway folder in your file browser and open the appsettings.json file.

(C drive -> Program Files (x86) -> Popdock -> SQL Server data Gateway-> appsettings.json)

64833cae4541186cc9078c72 SQlL

Inside the appsettings.json file, delete everything within the password line from the authentication section of the file EXCEPT the double quotes (The Gateway installer will automatically encrypt the blank field of the password section for basic authentication, and this will error out during the validation of the connector).

6479fb212f8e5b52d2f1f990 b

After this is deleted, save the file, and continue onto the IIS section of the Gateway setup process.



If using Basic Authentication, continue with the following steps:

Once you have finished up with the installer, go to the SQL Server data Gateway folder in your file browser and open the appsettings.json file.

(C drive -> Program Files (x86) -> Popdock -> SQL Server data Gateway-> appsettings.json)

64833cae4541186cc9078c72 SQlL

Inside the appsettings.json file, delete everything within the token line from the authentication section of the file EXCEPT the double quotes (The Gateway installer will automatically encrypt the blank token section for token authentication, and this will error out during the validation of the connector).

6479fb73a3a8f935c2ed7542 c

Once this is deleted, save the file, and continue onto the IIS section of the Gateway setup process.

 3. Add the Gateway to an existing website within the IIS Manager.

 

a) In IIS Manager, right-click Application Pools and then select Add Application Pool.

6307d9d470b36129c77585b9 GPDataGatewayinstall8

b) Name the Application Pool something related to the SqlGateway, for example SqlServerGateway.

6307d9d470b36190f4758d8d sqlgateway8

c) Expand the Sites node in IIS Manager and then select the site in which you want to add the SqlGateway. For example, you could add it to the Default Website. Right-click and then select Add Application.

 

6307d9d470b36141d67585c0 GPDataGatewayinstall10

d) In the Alias field, name the application something simple, for example SqlServerGateway. You could use something simpler like SqlGateway, as well. Choose the Application Pool from the previous step and then select the path used to install the Gateway in Step2B. Then select OK.

6307d9d470b361c356758d8f sqlgateway9

4. Ensure the Gateway is properly bound with SSL security and is externally facing.

 a) In IIS Manager, go to the site that you installed the Gateway on. In the upper right-hand corner, select Bindings.

6479fdd15bd5e686bdc311aa d

‍b) In the bindings menu, select Add and within the Edit Site Binding window, change the Type to https, leave the IP address as All Unassigned and the default Port will be set to 443 (if you are using a nonstandard port use that instead). From there, add in the Host name as mentioned in the pre-requisites. Then select the SSL certificate that will be used for this binding. Finally, select OK.

6479fe0420a9d0253879b3dd e

‍‍

‍5. Once the SSL security is setup, return to the site in IIS and select the new web service URL on the right-side. Browse to the site to make sure the Gateway is working.

6479fe37b247ec4d77e9df69 f

‍‍If everything is correct and setup properly, you will get this screen when you browse to the Gateway site.

6307d9d470b361df0a758d90 sqlgateway10

‍6. Return to your Popdock web application and add your new SQL Server Connector.

 

a) Inside Popdock, from the main menu, select Connectors.

image 12

‍b) Select Add Connector from the Settings menu on the left side of the page and then select SQL Server from the list of connectors.

64834014a7d959d37ebada99 Sql server

c) Use the Connection type drop down menu to select the connection type you choose during the setup (Token or Basic Authentication).

 

· Gateway with Token Authentication: In the Gateway URL field, choose the URL that we used in IIS, then use the Token that you created previously.

image 90

· Gateway with Basic Authentication: In the Gateway URL field, choose the URL you used in IIS, then use the Username and Password that you created previously.

image 91

d) Select Validate from the Action menu to ensure that Popdock can connect to the SQL Server Gateway.

e) Select Connect from the Action menu to start the process of creating the SQL Server lists in Popdock.‍

f) Select the tables you would like to add as lists in Popdock and start accessing data.

Questions on setting up your SQL Server Gateway? Contact support@eonesolutions.com.

Content
Installation and Setup Steps

Feeling stuck? Get the support and guidance you need to help you power through any data challenge

Reset Filters