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 Create an Azure SQL database in the Azure portal 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. 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. 2.
    You have to create a SQL Server user account with db_datareader and db_datawriter permissions. See How to create a non-administrator user account, or run the following script in the SQL Database Query Editor or SQL Management Studio (the master database must be selected):
    1
    -- Replace <database name>, <database user>, and <password> with the ones you choose (e.g. WFGEN, wfgen_user, <YourPWD>!)
    2
    -- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database
    3
    4
    CREATE LOGIN <database user>
    5
    WITH PASSWORD = '<password>'
    6
    GO
    7
    8
    -- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database
    9
    10
    CREATE USER <database user>
    11
    FROM LOGIN <database user>
    12
    WITH DEFAULT_SCHEMA = <database name>
    13
    GO
    14
    15
    -- Add user to the database owner role
    16
    EXEC sp_addrolemember N'db_datawriter', N'<database user>'
    17
    EXEC sp_addrolemember N'db_datareader', N'<database user>'
    18
    GO
    Copied!
  3. 3.
    Get the database creation script by downloading the latest WorkflowGen manual installation pack and extracting it to DRIVE:\temp.
  4. 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. 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.)
1
# Configuration variables
2
$resourceGroup= "workflowgen"
3
$location="East US"
4
$sqlServer="wfgen-sql-server"
5
$sqlAdminUsername="wfgen_sa"
6
$sqlAdminPassword="<your(Strong!)password1>"
7
$sqlDatabase="WFGEN"
8
$sqlServiceObjective="Basic"
9
$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;"
10
$sqlScriptPath = "C:\Azure\setup\sql"
11
$sqlScriptCreation = Join-Path $sqlScriptPath "create.sql"
12
13
# Create the Azure SQL Server
14
az sql server create `
15
--resource-group $resourceGroup `
16
--location $location `
17
--name $sqlServer `
18
--admin-user $sqlAdminUsername `
19
--admin-password $sqlAdminPassword
20
21
# Enable Azure internal services access
22
az sql server firewall-rule create `
23
--resource-group $resourceGroup `
24
--server $sqlServer `
25
--name AllowAzureServices `
26
--start-ip-address 0.0.0.0 `
27
--end-ip-address 0.0.0.0
28
# Azure SQL database creation
29
az sql db create `
30
--name $sqlDatabase `
31
--resource-group $resourceGroup `
32
--server $sqlServer `
33
--service-objective $sqlServiceObjective
34
35
# Allow my public IP to access the SQL Server
36
$myPublicIP = (Invoke-WebRequest https://itomation.ca/mypublicip).content
37
az sql server firewall-rule create `
38
--resource-group $resourceGroup `
39
--server $sqlServer `
40
--name AllowMyTempPublicIP `
41
--start-ip-address $myPublicIP `
42
--end-ip-address $myPublicIP
43
44
# Initialize the database
45
Invoke-Sqlcmd `
46
-ConnectionString $connectionStringWithSqlAdmin `
47
-InputFile $sqlScriptCreation
48
49
# Remove my public IP
50
az sql server firewall-rule delete `
51
--resource-group $resourceGroup `
52
--server $sqlServer `
53
--name AllowMyTempPublicIP
Copied!
  • 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.
    1
    # Database user credentials
    2
    $sqlUserUsername = "wfgen_user"
    3
    $sqlUserPassword = "<your(Strong!)Password>"
    4
    5
    # Allow my public IP to access the SQL Server
    6
    $myPublicIP = (Invoke-WebRequest https://itomation.ca/mypublicip).content
    7
    az sql server firewall-rule create `
    8
    --resource-group $resourceGroup `
    9
    --server $sqlServer `
    10
    --name AllowMyTempPublicIP `
    11
    --start-ip-address $myPublicIP `
    12
    --end-ip-address $myPublicIP
    13
    14
    # Create the database user in the contained database
    15
    $queryVariables = "USERNAME=$sqlUserUsername","PASSWORD='$sqlUserPassword'"
    16
    Invoke-Sqlcmd `
    17
    -ConnectionString $connectionStringWithSqlAdmin `
    18
    -Query '
    19
    CREATE USER $(USERNAME) WITH PASSWORD = $(PASSWORD);
    20
    ALTER ROLE db_datareader ADD MEMBER $(USERNAME);
    21
    ALTER ROLE db_datawriter ADD MEMBER $(USERNAME);
    22
    ' `
    23
    -Variable $queryVariables
    24
    25
    # Remove my public IP
    26
    az sql server firewall-rule delete `
    27
    --resource-group $resourceGroup `
    28
    --server $sqlServer `
    29
    --name AllowMyTempPublicIP
    Copied!
    ✏️ 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.
    1
    # Master database connection string
    2
    $connectionStringMaster = "Server=tcp:$sqlServer.database.windows.net,1433;Persist Security Info=False;User ID=$sqlAdminUsername;Password=$sqlAdminPassword;MultipleActiveResultSets=False;Encrypt=True;"
    3
    4
    # Database user credentials
    5
    $sqlUserUsername = "wfgen_user"
    6
    $sqlUserPassword = "<your(Strong!)Password>"
    7
    8
    # Allow my public IP to access the SQL Server
    9
    $myPublicIP = (Invoke-WebRequest https://itomation.ca/mypublicip).content
    10
    az sql server firewall-rule create `
    11
    --resource-group $resourceGroup `
    12
    --server $sqlServer `
    13
    --name AllowMyTempPublicIP `
    14
    --start-ip-address $myPublicIP `
    15
    --end-ip-address $myPublicIP
    16
    17
    # Create the database login in the master db
    18
    $queryVariables = "USERNAME=$sqlUserUsername","PASSWORD='$sqlUserPassword'","DATABASE=$sqlDatabase"
    19
    Invoke-Sqlcmd `
    20
    -ConnectionString $connectionStringMaster `
    21
    -Query '
    22
    CREATE LOGIN $(USERNAME) WITH PASSWORD = $(PASSWORD);
    23
    ' `
    24
    -Variable $queryVariables
    25
    26
    # Create the user in the WorkflowGen database
    27
    Invoke-Sqlcmd `
    28
    -ConnectionString $connectionStringWithSqlAdmin `
    29
    -Query '
    30
    CREATE USER $(USERNAME) FROM LOGIN $(USERNAME) WITH DEFAULT_SCHEMA = $(DATABASE);
    31
    ALTER ROLE db_datareader ADD MEMBER $(USERNAME);
    32
    ALTER ROLE db_datawriter ADD MEMBER $(USERNAME);
    33
    ' `
    34
    -Variable $queryVariables
    35
    36
    # Remove my public IP
    37
    az sql server firewall-rule delete `
    38
    --resource-group $resourceGroup `
    39
    --server $sqlServer `
    40
    --name AllowMyTempPublicIP
    Copied!
    ✏️ 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>:
1
<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" />
Copied!
  • 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.
Last modified 4mo ago