Azure MSSQL Connector

An overview on how to configure Form.io SQL Connector on a managed Azure App Service connected to an Azure SQL Server instance and mount the configuration to volume mounted storage.

Deploy Connector App Service

4KB

azure-formio-mssql-connector.zip

archive

1. Create to the App Services from your dashboard then click + Create to create a new App Service.

2. On the Basics Tab enter these settings.

   • Under Project Details choose a Subscription

   • Under Project Details choose a Resource Group

   • Under Instance Details enter an App Service Name.

   • Under Instance Details select Docker Container as the Publish method.

   • Under Instance Details choose Linux as the Operating System.

   • Under Instance Details select a Region of you choice.

   • Under Pricing Plans choose a Linux Plan which is dependent on the chosen region.

   
3. On the Docker Tab enter these settings.

   • For Options select Docker Compose (Preview).

   • For Image Source choose Docker Hub.

   • Under Docker hub options for Access Type choose Public

   • For your Configuration File select the connector.docker-compose.yml from the downloaded zip at the top of this page.

   

## FILE: connector.docker-compose.yml
version: '3'
services:
  ##############################################
  ## Connector
  ##############################################
  sql-connector:
    image: formio/sql-connector:2.0.10
    container_name: sql-connector
    volumes:
      - ${WEBAPP_STORAGE_HOME}/connector/.sqlconnectorrc:/app/.sqlconnectorrc
    ports:
      - '3002:3002'
    restart: unless-stopped
    logging:
      driver: stdout

4. On the Review + create tab review the App Service settings then press the Create button.

Volume Mount Connector Config to Container

1. From the left panel in Azure, under the Development Tools section select Advanced Tools blade, once on the page press the Go -> link.

2. The Go -> link with redirect to the Azure App Service admin page. From here click on the Bash tab at the top of the page.

3. Once have been directed to the DebugConsole page, run the following commands to use Vim to add the .sqlconnectorrc where it can be reached from the container.

###############################################
## Create a connector directory
mkdir connector

###############################################
## Create a .sqlconnectorrc file using vim
vi .sqlconnectorrc

###############################################
## If you're not familar with vim.
## Right click to paste.
## Press "i" to insert.
## Press "esc" when done.
## Then type ":w" to save the file.
## Then type ":q" to exit the file after save.

###############################################
## Verify changes have been made
cat .sqlconnectorrc

4. Upon completion this should be all you need to configure on the App Service side for the connector to work. From here you will continue by configuring the SQL Connector from the Integration Settings in your project stage.

Configure SQL Connector Project Integration Settings

Now that we've mounted the .sqlconnectorrc file to the container we will want to configure the SQL Connector from the portal so that the API server can make calls to the Connector for Form Submissions.

1. To get to the SQL Connector settings follow this steps, select the Settings option from left drawer -> Select Integrations -> Data Connections -> SQL Connector.

2. Enter the Default Domain from your Azure App Service Overview page for SQL Connector HOST URL

3. Select Microsoft SQL from the Type dropdown.

4. Enter a Basic Auth username and password that will be used for the API server to create submissions against the SQL database.

Test SQL Connector Action with Form

In this section we will be validating that our configuration works with either an existing form or by creating a new form and configurating it with the SQL Connector Action.

In my demo I created a simple Customer Resource that has 2 Text Fields (First Name, and Email).

1. Once you've selected or created a form to use will want to click on the Actions tab for that form. Here's you will be shown a Form Actions card.

2. From the Select an Action dropdown choose SQL Connector (Premium).

3. You will be shown are form for configuring the field mappings that Form.io will use for creating records against an SQL database.

4. After configuring the field mappings you can now test the connection works by navigating to the Use tab and creating a form submission.

5. The last step is to validate in from a GUI connected to your SQL server instance to verify that the record has been propagated to our SQL database.