Powered by GitBook

1 of 1

Loading...

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 . See the 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

Connect to your Azure SQL database instance with the administrator account you created by using the or .
You have to create a SQL Server user account with db_datareader and db_datawriter permissions. See the Microsoft article, or run the following script in the SQL Database Query Editor or SQL Management Studio (the master database must be selected):
Get the database creation script by downloading the and extracting it to DRIVE:\temp

Via the Azure CLI

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

.
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 .)

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.
✏️ 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

Step 2: Configure WorkflowGen

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

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>

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

$sqlUserPassword

wfgen_user

 -- 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

# 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

# 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

<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" />

# 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