The following article provides steps for setting up the Microsoft SQL Server Data Gateway for Popdock. This will ensure that you can safely provide access to on-premise SQL Server data through a firewall.
- Internet Information Services(IIS) installed on a Microsoft Windows computer
- Install the .NET Core Hosting Bundle (3.1) (download)
Installation and Setup Steps
1) Download the SQL Server Gateway (download)
2) Install the Gateway following the steps below:
A) Select the Next button to begin.
B) Pick the desired folder where the files will be installed. It will default into the Program Files (x86) directory.
C) Provide the credentials to connect to the Microsoft SQL Server.
- Server: The local address to the SQL Server and instance if necessary
- Database: The name of the database you need to access
- Username: The desired SQL Server user. Doesn’t need to be sa but must have access to read SYS tables to access the metadata
- Password: The password for the SQL Server user above
D) Choose the Authentication type that Popdock will use to connect with the Gateway. Our recommendation is Token but we will cover both methods.
- Token Authentication
Create a Token that will be used by Popdock to connect. Please store this value for later use.
- Basic Authentication
Create a Username and Password that will be used by Popdock to connect.
E) Select the Install button to finish the process.
3) Add the Gateway to an existing website in IIS Manager.
A) In the IIS Manager, Right-click on the Application Pools and choose, Add Application Pool.
B) Name the Application Pool something related to SqlGateway, in the example below we called it SqlServerGateway.
C) Now, expand the Sites in the IIS Manager, select the site you want to add the SQL Server Gateway (I have chosen the Default Website in the example below), and right-click and choose Add Application.
D) In the Alias field, name the application something simple (I’ve chosen SqlServerGateway in the example below but something simpler like SqlGateway would be fine), choose the Application Pool from the previous step above (Step 3-B) and finally browse to the location where we installed the Gateway in Step 2-B.
4) Test that the Gateway is working by browsing to the website.
5) Inside Popdock, Add a new SQL Server Connector and change the Connection type to one of the Gateway options (Depending on if you chose Token or Basic Authentication in Step 2-D above).
A) Gateway with Token Authentication: In the Gateway URL field, choose the URL that was tested in Step 4 above, then use the Token that was created in Step 2-D above.
B) Gateway with Basic Authentication: In the Gateway URL field, choose the URL that was tested in Step 4 above, then use the Username and Password that was created in Step 2-D above.
C) Select the Validate button to ensure that Popdock can connect to the SQL Server Gateway.
D) Select the Connect button to start the process of creating the SQL Server lists in Popdock.
E) Select the tables you would like to add as lists in Popdock and start accessing data!
Please contact firstname.lastname@example.org if you need further assistance.