Azure SQL Database Configuration

Overview

This section provides instructions on how to create and configure your Azure SQL database.

Create the Azure SQL database

The Azure SQL database instance has to be created in the Azure Portal. See the Quickstart: Create a single database - Azure SQL Database Microsoft article for more information on how to create the database. Once you've completed the instructions, you'll have:

• The name of the Azure SQL server

• The credentials of the administrator account

• A server-level firewall rule for your IP address server

• The name of the Azure SQL database.

Step 1: Configure the Azure SQL database

Via the Azure Portal

1. Connect to your Azure SQL database instance with the administrator account you created by using the SQL Database Query Editor in Azure Portal or SQL Management Studio.

2. You have to create a SQL Server user account with db_datareader and db_datawriter permissions. See the Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics Microsoft article, or run the following script in the SQL Database Query Editor or SQL Management Studio (the master database must be selected):

    -- Replace <database name>, <database user>, and <password> with the ones you choose (e.g. WFGEN, wfgen_user, <YourPWD>!)
    -- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database
   
    CREATE LOGIN <database user>
        WITH PASSWORD = '<password>' 
    GO
   
    -- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database
   
    CREATE USER <database user>
        FROM LOGIN <database user>
        WITH DEFAULT_SCHEMA = <database name>
    GO
   
    -- Add user to the database owner role
    EXEC sp_addrolemember N'db_datawriter', N'<database user>'
    EXEC sp_addrolemember N'db_datareader', N'<database user>'
    GO

3. Get the database creation script by downloading the latest WorkflowGen manual installation pack and extracting it to DRIVE:\temp.

4. Open the DRIVE:\temp\pack\Databases\MsSQLServer source folder and run the create.sql database creation SQL script on the new database instance.

Via the Azure CLI

The Azure SQL database can also be created via Azure CLI scripts. To do this:

1. Sign in to your Azure account with Azure CLI.
2. Copy the WorkflowGen database create.sql script to the C:\Azure\setup\sql folder. If you want to change the path, you'll have to edit the $sqlScriptPath variable in the following script as well.

The following scripts create the SQL Server and SQL Database. The SQL database admin password variable ($sqlAdminPassword) must be updated; the resource group name ($resourceGroup), pricing tier ($sqlServiceObjective), and SQL script path ($sqlScriptPath) should be updated as well. (For more information on the pricing tier, see https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-service-objectives-azure-sql-database?view=azuresqldb-current.)

# Configuration variables
$resourceGroup= "workflowgen"
$location="East US"
$sqlServer="wfgen-sql-server"
$sqlAdminUsername="wfgen_sa"
$sqlAdminPassword="<your(Strong!)password1>"
$sqlDatabase="WFGEN"
$sqlServiceObjective="Basic"
$connectionStringWithSqlAdmin = "Server=tcp:$sqlServer.database.windows.net,1433;Initial Catalog=$sqlDatabase;Persist Security Info=False;User ID=$sqlAdminUsername;Password=$sqlAdminPassword;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
$sqlScriptPath = "C:\Azure\setup\sql"
$sqlScriptCreation = Join-Path $sqlScriptPath "create.sql"

# Create the Azure SQL Server
az sql server create `
	--resource-group $resourceGroup `
	--location $location `
	--name $sqlServer `
	--admin-user $sqlAdminUsername `
	--admin-password $sqlAdminPassword

# Enable Azure internal services access
az sql server firewall-rule create `
	--resource-group $resourceGroup `
	--server $sqlServer `
	--name AllowAzureServices `
	--start-ip-address 0.0.0.0 `
	--end-ip-address 0.0.0.0
# Azure SQL database creation
az sql db create  `
	--name $sqlDatabase `
	--resource-group $resourceGroup `
	--server $sqlServer `
	--service-objective $sqlServiceObjective

# Allow my public IP to access the SQL Server
$myPublicIP = (Invoke-WebRequest https://itomation.ca/mypublicip).content
az sql server firewall-rule create `
	--resource-group $resourceGroup `
	--server $sqlServer `
	--name AllowMyTempPublicIP `
	--start-ip-address $myPublicIP `
	--end-ip-address $myPublicIP 

# Initialize the database
Invoke-Sqlcmd `
    -ConnectionString $connectionStringWithSqlAdmin `
    -InputFile $sqlScriptCreation

# Remove my public IP
az sql server firewall-rule delete `
	--resource-group $resourceGroup `
	--server $sqlServer `
	--name AllowMyTempPublicIP 

• Option A: Contained database mode The following script creates the database user (wfgen_user) in a contained database. The SQL database user password variable ($sqlUserPassword) must be updated.

  # Database user credentials
  $sqlUserUsername = "wfgen_user"
  $sqlUserPassword = "<your(Strong!)Password>"
  
  # Allow my public IP to access the SQL Server
  $myPublicIP = (Invoke-WebRequest https://itomation.ca/mypublicip).content
  az sql server firewall-rule create `
  	--resource-group $resourceGroup `
  	--server $sqlServer `
  	--name AllowMyTempPublicIP `
  	--start-ip-address $myPublicIP `
  	--end-ip-address $myPublicIP 
  
  # Create the database user in the contained database
  $queryVariables = "USERNAME=$sqlUserUsername","PASSWORD='$sqlUserPassword'"
  Invoke-Sqlcmd `
  	-ConnectionString $connectionStringWithSqlAdmin `
  	-Query '
  		CREATE USER $(USERNAME) WITH PASSWORD = $(PASSWORD);
  		ALTER ROLE db_datareader ADD MEMBER $(USERNAME);
  		ALTER ROLE db_datawriter ADD MEMBER $(USERNAME);
  	' `
  	-Variable $queryVariables
  
  # Remove my public IP 
  az sql server firewall-rule delete `
  	--resource-group $resourceGroup `
  	--server $sqlServer `
  	--name AllowMyTempPublicIP 

  ✏️ Note: Do not run the Remove my public IP script if you need access to the database from your desktop or if your WorkflowGen server is not hosted by Azure.

• Option B: Standard database mode The following script creates the database user (wfgen_user) in a standard database. The SQL database user password variable ($sqlUserPassword) must be updated.

  # Master database connection string
  $connectionStringMaster = "Server=tcp:$sqlServer.database.windows.net,1433;Persist Security Info=False;User ID=$sqlAdminUsername;Password=$sqlAdminPassword;MultipleActiveResultSets=False;Encrypt=True;"
  
  # Database user credentials
  $sqlUserUsername = "wfgen_user"
  $sqlUserPassword = "<your(Strong!)Password>"
  
  # Allow my public IP to access the SQL Server
  $myPublicIP = (Invoke-WebRequest https://itomation.ca/mypublicip).content
  az sql server firewall-rule create `
  	--resource-group $resourceGroup `
  	--server $sqlServer `
  	--name AllowMyTempPublicIP `
  	--start-ip-address $myPublicIP `
  	--end-ip-address $myPublicIP 
  
  # Create the database login in the master db
  $queryVariables = "USERNAME=$sqlUserUsername","PASSWORD='$sqlUserPassword'","DATABASE=$sqlDatabase"
  Invoke-Sqlcmd `
  	-ConnectionString $connectionStringMaster `
  	-Query '
  		CREATE LOGIN $(USERNAME) WITH PASSWORD = $(PASSWORD);
  	' `
  	-Variable $queryVariables
  
  # Create the user in the WorkflowGen database
  Invoke-Sqlcmd `
  	-ConnectionString $connectionStringWithSqlAdmin `
  	-Query '
  		CREATE USER $(USERNAME) FROM LOGIN $(USERNAME) WITH DEFAULT_SCHEMA = $(DATABASE);
  		ALTER ROLE db_datareader ADD MEMBER $(USERNAME);
  		ALTER ROLE db_datawriter ADD MEMBER $(USERNAME);
  	' `
  	-Variable $queryVariables
  
  # Remove my public IP 
  az sql server firewall-rule delete `
  	--resource-group $resourceGroup `
  	--server $sqlServer `
  	--name AllowMyTempPublicIP

  ✏️ Note: Do not run the Remove my public IP script if you need access to the database from your desktop or if your WorkflowGen server is not hosted by Azure.

Step 2: Configure WorkflowGen

Open the WorkflowGen web.config file and add the following node under <connectionStrings>:

<add name="MainDbSource" connectionString="Data Source=<server name>;Initial Catalog=<database name>;User ID=<database user>;Password=<password>;encrypt=true;trustServerCertificate=false;" providerName="System.Data.SqlClient" />

• Replace <server name> with the server name (e.g. workflowgen.database.windows.net).

• Replace <database name> with the database name (e.g. WFGEN).

• Replace <database user> with the database user (e.g. wfgen_user).

• Replace <password> with the database user's password.

We strongly recommend that you add encrypt=true and trustServerCertificate=false; to the connectionString in order to establish a secure connection to the database.