# 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://learn.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage?view=azuresql) 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.<br>

  ```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:** Don't 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;*.*<br>

  ```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:** Don't 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`).
* 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.

{% 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 %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.workflowgen.com/azure/azure-sql-database-configuration.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.