** Before you begin, Stop the HPC Services on each head node. Ensure that your HPC databases are being backed up properly.
On the SQL PaaS Database Server – Entra ID Admin. Ensure that you are part of the group that has access to run the SQL commands to add the user managed id user.
Go to Identities in the Azure Portal.
Create a user-assigned managed identity to be used for the SQL Authentication (ex: HPCPackSQLAuth)
Note down the Client ID as this will be used for the SQL Authentication connection string below.
For each SQL Database add the user-assigned managed identity for HPCPackSQLAuth run the Create user and Alter Role commands below.
- HPCDiagnostics
- HPCHAStorage
- HPCManagement
- HPCMonitoring
- HPCReporting
- HPCScheduler
- HPCHAWitness
Right click on each of the user databases to open a new query window.
For User-Assigned Managed Identity: (HPCPackSQLAuth is the user managed identity)
CREATE USER HPCPackSQLAuth FROM EXTERNAL PROVIDER
ALTER ROLE db_datareader ADD MEMBER HPCPackSQLAuth
ALTER ROLE db_datawriter ADD MEMBER HPCPackSQLAuth
ALTER ROLE db_ddladmin ADD MEMBER HPCPackSQLAuth
-- Enable HPC setup user to configure database in HpcServer_x64.msi
Grant alter to HPCPackSQLAuth
-- Enable HPC setup user to use user-defined FUNCTION
Grant exec to HPCPackSQLAuth
-- Enable HPC setup user to query database extended property DbVersion
Grant view definition to HPCPackSQLAuthUpdate the connection string inside the HPCHAStorage dbo.DataTable (replace umi-clientid-xxxx with the Client ID and the Sql server name for xxxx.database.windows.net)
Update dbo.DataTable set dvalue='Data Source=xxxx.database.windows.net;Initial Catalog=HPCHAWitness;User ID= umi-clientid-xxxx;Authentication=Active Directory Interactive;' where dpath = 'HKEY_LOCAL_MACHINE\Software\Microsoft\HPC\Security' and dkey = 'HAWitnessDbConnectionString'
Update dbo.DataTable set dvalue='Data Source=xxxx.database.windows.net;Initial Catalog=HPCDiagnostics;User ID= umi-clientid-xxxx;Authentication=Active Directory Interactive;' where dpath = 'HKEY_LOCAL_MACHINE\Software\Microsoft\HPC\Security' and dkey = 'DiagnosticsDbConnectionString'
Update dbo.DataTable set dvalue='Data Source=xxxx.database.windows.net;Initial Catalog=HPCManagement;User ID= umi-clientid-xxxx;Authentication=Active Directory Interactive;' where dpath = 'HKEY_LOCAL_MACHINE\Software\Microsoft\HPC\Security' and dkey = 'ManagementDbConnectionString'
Update dbo.DataTable set dvalue='Data Source=xxxx.database.windows.net;Initial Catalog=HPCMonitoring;User ID= umi-clientid-xxxx;Authentication=Active Directory Interactive;' where dpath = 'HKEY_LOCAL_MACHINE\Software\Microsoft\HPC\Security' and dkey = 'MonitoringDbConnectionString'
Update dbo.DataTable set dvalue='Data Source=xxxx.database.windows.net;Initial Catalog=HPCReporting;User ID= umi-clientid-xxxx;Authentication=Active Directory Interactive;' where dpath = 'HKEY_LOCAL_MACHINE\Software\Microsoft\HPC\Security' and dkey = 'ReportingDbConnectionString'
Update dbo.DataTable set dvalue='Data Source=xxxx.database.windows.net;Initial Catalog=HPCScheduler;User ID=umi-clientid-xxxx;Authentication=Active Directory Interactive;' where dpath = 'HKEY_LOCAL_MACHINE\Software\Microsoft\HPC\Security' and dkey = 'SchedulerDbConnectionString'
Migrate Database Server Step-by-Step Guide | Microsoft Learn
When using User-assigned Managed Identity:
On each of the HPC Head Nodes add the new user-assigned managed identity to each of the head nodes.
HPC Pack stores the connection string in the registry at this location:
Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\HPC\Security\
Configure a user managed identity on the each of the Virtual Machine Head Nodes in the registry for the HPCHAStorage database.
Set-ItemProperty -Path HKLM:\SOFTWARE\Microsoft\HPC\Security -Name HAStorageDbConnectionString -Value "Data Source=xxxx.database.windows.net;Initial Catalog=HPCHAStorage;User ID= umi-clientid-xxxx;Authentication=Active Directory Interactive;"
Test the connection.
Download SSMS 19.x from : https://aka.ms/ssmsfullsetup
Enter the user-assigned managed identity – client id and then under options add the database name to connect to.
Click on options button. Change the database name from default to hpchastorage in the connect to database text box.
Code sample for testing (replace umi-clientid-xxxx with the Client ID)
# Install Azure PowerShell module if not already installed
Install-Module -Name Az -AllowClobber -Scope CurrentUser
# Retrieve access token using managed identity
Connect-AzAccount -Identity -AccountId "umi-clientid-xxxx"
$token = (Get-AzAccessToken -ResourceUrl "https://database.windows.net/").Token
# Create SQL connection
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=xxxx.database.windows.net; Initial Catalog=HPCHAStorage; Encrypt=True;"$SqlConnection.AccessToken = $token
# Open SQL connection
$SqlConnection.Open()
# Example: Execute SQL command
#$SqlCommand = $SqlConnection.CreateCommand()
#$SqlCommand.CommandText = "SELECT * FROM YourTable"
#$SqlDataReader = $SqlCommand.ExecuteReader()
# Close SQL connection
#$SqlConnection.Close()