Section 11 - Deploy Azure SQL

Requirements

The Azure Administrator will be required for this step. The user must have:

• Owner role¹ for the subscription
• Access to the Azure Portal

Manual Steps:

Video Walkthrough

Step 1: Create SQL Database

1. Browse and log into Azure Portal

Use the correct URL for your environment:

Worldwide (Commercial) & GCC

https://portal.azure.com

GCC-High and DoD

https://portal.azure.us

1. Type in azure sql in the top search textbox
2. Select Azure SQL Database from the search results

1. Click on Create

1. Set the following properties, and click on Create new under the Server property

1. Set the following properties and click on OK

1. Set the following properties, and click on Next

For Tenant Sizes: < 50k sites

For Tenant Sizes: < 100k sites

For Tenant Sizes: > 100k sites

For tenants > 150k sites during intake and inventory runbook jobs, it’s recommended to update the compute and storage to Premium P1. Return to the Standard S1 after the jobs have completed.

1. Set the following properties, and click on Next

1. Click on Configure Identities for the Server identity property
2. Add the uami-spark-spoactions identity
3. Select the uami-spark-spoactions identity as the Primary Identity
4. Click on Apply

If this section is not available during creation, you can set this property later.

1. Set Enable secure enclaves to On
2. Click on Next until Review + create
3. Click on Create

Step 2: Configure SQL Access Control

1. Access the Azure SQL logical servers
2. Click on SQL logical servers
3. Select the sub-sql-spark-svr server created in the previous step
4. Select the Access control (IAM) and click on Add role assignment

1. Search for sql db
2. Select SQL DB Contributor and click on Next

1. Select Managed identity for the Assign access to
2. Select the uami-spark-spoactions identity, and click on Select
3. Click on Review + assign, and then Review + assign to add the role assignment

Step 3: Configure SQL UAMI Identity

1. Select SQL databases from the left navigation
2. Select the sub-sql-spark-svr database
3. Under Security, click on Identity
4. Select Add, select the uami-spark-spoactions identity and then click on Add
5. Click Save

Step 4: Get SQL Connection String

1. Click on SQL databases from the left navigation
2. Select the sqldb-spark database
3. Under Overview, click on See connection strings
4. Annotate the Microsoft Entra passwordless authentication database connection string

Update Connection String

Replace TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default"; with MultipleActiveResultSets=True;

1
Server=tcp:sub-sql-spark-svr-qa1.database.windows.net,1433;Initial Catalog=sub-sql-spark-svr-qa1;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Authentication="Active Directory Default";

1
Server=tcp:sub-sql-spark-svr.database.windows.net,1433;Initial Catalog=sqldb-spark;Encrypt=True;MultipleActiveResultSets=True;

Annotate the following variables in the template spreadsheet:

• v_sqlConnectionString: The sql connection string

Step 5: Connect to SQL Database

1. Click on SQL databases from the left navigation
2. Select the sqldb-spark database
3. Under Query editor, click on Allow IP [IP Address]... to be able to connect to the database
4. Click on Continue as [user account] to access the query editor

The user must be added as a Member part of the SPARK System Admins group.

Step 6: Run SQL Queries

1. From the query editor, run the following scripts in the order shown in the table below
2. Confirm each query runs successfully

Continue to creating the Function Apps

References