Requirements

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

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

Reference Link

Step 1: Sign into Azure

Use the correct URL for your environment:

Environment Login URL
Worldwide (Commercial) https://portal.azure.com
GCC https://portal.azure.com
GCC High https://portal.azure.us
DoD https://portal.azure.us

Step 2: Create SQL Database

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

3. Click on Create

Create SQL

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

Name Value
Subscription The subscription created for this toolkit
Resource Group sub-spark-rg
Database Name sqldb-spark
Create SQL Basics

5. Set the following properties and click on OK

Name Value
Server Name sub-sql-spark-svr
Location The same region you have been using for the Azure services
Authentication Method Use Microsoft Entra-only Authentication
Set Microsoft Entra admin SPARK System Admins
Create SQL Server

6. Set the following properties, and click on Next

For Tenant Sizes: < 100k sites

Name Value
Compute + storage Standard S0 2GB
Backup storage redundancy Locally-redundant backup storage
Create SQL S0

For Tenant Sizes: > 100k sites

Name Value
Compute + storage Standard S1 5GB
Backup storage redundancy Locally-redundant backup storage
Create SQL S1

[!NOTE] 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.

7. Set the following properties, and click on Next

Name Value
Connectivity method Public endpoint
Allow Azure services and resources to access this server Yes
Create SQL Networking

8. Click on Configure Identities for the Server identity property

9. Add the uami-spark-spoactions identity

10. Select the uami-spark-spoactions identity as the Primary Identity

11. Click on Apply

Create SQL Identity

[!NOTE] If this section is not available during creation, you can set this property later.

12. Set Enable secure enclaves to On

13. Click on Next until Review + create

14. Click on Create

Create SQL Security

Step 3: 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
SQL Server IAM

5. Search for sql db

6. Select SQL DB Contributor and click on Next

SQL Add Role Assignment

7. Select Managed identity for the Assign access to

8. Select the uami-spark-spoactions identity, and click on Select

9. Click on Review + assign, and then Review + assign to add the role assignment

SQL Server Managed Identity

Step 4: 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
Fix SQL UAMI Identity

Step 5: 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;
Get Connection Strings

[!NOTE] Annotate the following variables in the template spreadsheet:

  • v_sqlConnectionString: The sql connection string

Step 6: 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

[!NOTE] The user must be part of the SPARK System Admins group

Connect SQL DB

Step 7: 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
Name Files
Create Users 01_create_spark_sql_users
Create Tables 02_create_spark_sql_tables
Create Views 03_create_spark_sql_views
Create Stored Procedures 04_create_spark_sql_procs
Run SQL Queries