# 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](https://portal.azure.com/). See the [Quickstart: Create a single database - Azure SQL Database](https://learn.microsoft.com/en-us/azure/azure-sql/database/single-database-create-quickstart?view=azuresql\&tabs=azure-portal) 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](https://azure.microsoft.com/en-ca/blog/t-sql-query-editor-in-browser-azure-portal/) or [SQL Management Studio](https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-ssms).<br>
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](https://docs.workflowgen.com/azure/8.0/broken-reference) Microsoft article, or run the following script in the SQL Database Query Editor or SQL Management Studio (the **master** database must be selected):

   ```sql
    -- 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](http://download.workflowgen.com/product/latest/manual.zip) and extracting it to `DRIVE:\temp`.<br>
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](https://docs.microsoft.com/en-us/cli/azure/install-azure-cli?view=azure-cli-latest).<br>
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>.)

```sql
# 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.

  ```sql
  # 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.<br>
* **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 update&#x64;*.*

  ```sql
  # 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>`:

```markup
<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`).<br>
* Replace `<database name>` with the database name (e.g. `WFGEN`).<br>
* Replace `<database user>` with the database user (e.g. `wfgen_user`).<br>
* Replace `<password>` with the database user's password.

{% hint style="info" %}
We strongly recommend that you add `encrypt=true` and `trustServerCertificate=false;` to the `connectionString` in order to establish a secure connection to the database.
{% endhint %}