emdash™ is able to manage Azure environments, in particular virtual machines and SQL Azure databases.
emdash™ uses the Az and SqlServer PowerShell modules to manage Azure resources.
In order to manage resources in Azurre (virtual machines, databases) emdash™ needs to be able to connect to your subscription with an account having the required permissions.
Authenticating With Azure
In order to manage resources emdash connects to Azure via the Connect-AzAccount command. emdash™ supports the following authentication options
- Authenticate with Azure using a user name and password
- Authenticate with Azure using a Service Principal and a client secret
- Authenticate with Azure using a Service Principal and a certificate
Of the three options, option 3 is preferred as it is the most secure and does not require the management of passwords or secrets.
The authentication option used by emdash is controlled by the AzureConnectionMethod environment wide setting which supports the values Certificate, UserNamePassword and ClientSecret
Setting up an emdash™ Azure Service Principal (SPN)
If the environment wide setting AzureConnectionMethod is set to Certificate or ClientSecret then in order to authenticate with Azure emdash™ requires an Azure service principal (SPN) account to be set up. emdash™ authenticates with Azure using this SPN via the certificate or the client secret.
Creating the SPN
The following steps are performed in the Azure portal to create the service principal (SPN). This assumes we are creating an SPN called emdash-app
- Navigate to Azure Active Directory -> App registrations
- Select New registration and enter the following details then click Register.
- Navigate to the app registration you just created and note down the Application (client) id:
The service principal creation process is now complete.
Client secret set up
If AzureConnectionMethod is set to ClientSecret then follow these steps to create the secret for the SPN.
Login to the Azure portal and navigate to the app registration set up previously (emdash-app)
Click New client secret and enter the secret details with a recommended expiry date based on your organizations policies.
You must copy the secret Value immediately after creation as once you have navigated away from the page it can no longer be retrieved.
Certificate set up
If AzureConnectionMethod is set to Certificate then follow these steps to create the Certificate for the SPN.
The following PowerShell can be run on the emdash server to create a self-signed certificate. Alternatively a certificate from an organizational certificate authority or third party provider can be used. Note – the pfx password below should be replaced with a different secure value and the pfx file and password should not be shared outside the system administration team.
$certname = "emdashServicePrincipalCert"
$pfxPassword = "5j35hkefhisety7ewy7trretjhsjf"
$exportPfxPath = "C:\Users\EMDASH\emdashServicePrincipalCert.pfx"
$cert = New-SelfSignedCertificate -Subject "CN=emdashServicePrincipalCert" -CertStoreLocation "Cert:\CurrentUser\My" -KeyExportPolicy Exportable -KeyLength 2048 -KeyAlgorithm RSA -HashAlgorithm SHA256 -NotAfter "31/12/2035" `
-FriendlyName $certname -KeyUsageProperty All
$mypwd = ConvertTo-SecureString -String $pfxPassword -Force -AsPlainText
Export-PfxCertificate -Cert $cert -FilePath $exportPfxPath -Password $mypwd
Note that the certificate must be imported into the Personal store of the emdash service account on each emdash server, including workflow servers. This can be done by importing the exported pfx file on each server.
Upload the certificate to Azure
Export the certificate from the local user Personal store to a .CER file format. Login to the Azure portal and under the emdash-app app registration upload the certificate:
Also make a note of the certificate thumbprint as this is required in the next step.
SPN required permissions in Azure subscription
SQL Server Management
In order to carry out the core emdash™ SQL functions the SPN will require these roles in the subscription. These can be added under the Subscription à Access control (IAM) à Role Assignment
Virtual Machine Management
In order to carry out the core emdash™ Azure virtual machine functions the SPN will require these roles in the subscription
Setting up emdash to manage SQL Azure databases
The following steps need to be completed in order to manage SQL Azure databases in emdash.
Set up an Azure storage account
In order to backup database to bacpac format an Azure Storage Account is required. We recommend creating a private link to the storage account in the same virtual network and subnet as the emdash server.
Create a container
In the Azure portal under the storage account created create a new container:
Set the access level as
Create access tokens
For the container just created a new shared access token must be created with the following properties:
Set the expiry date to a date in the future. Once this date has passed a new token will need to be generated and emdash reference data updated with it.
You should select all 7 permissions under the Permissions drop down list.
Once configured click the Generate SAS token and URL button.
Copy the generated Blob SAS token value as this will be required for the environment wide setting AzureStorageAccountContainer_<storage acct name>_<container>_SharedAccessToken
Set up environment wide settings
| Setting | Used by | Description |
| Override<AppName>BackupPath | Restore from a bacpac, Backup to bacpac | https://<storage acct name>.blob.core.windows.net/<container> |
| SQLAzureBackupStorageKeyType | Restore from a bacpac, Backup to bacpac | Always set to StorageAccessKey |
| AzureStorageAccount_<storage acct name>_AccessKey | Restore from a bacpac, Backup to bacpac | Storage access key for the storage account. Set this to the key1 value under Access keys for the storage account. |
|
AzureStorageAccountContainer_ <storage acct name> _<container>_SharedAccessToken |
Restore from a bacpac, Backup to bacpac | Set this to the access token created for the container. |
| AzureSubscriptionId | All functions | The subscription holding the Azure resources being managed by emdash. |
| SQLAzureAdminAccountPassword | All functions | The server admin account password to be used to connect to the SQL Azure instance |
| SQLAzureAdminAccountUsername | All functions | The server admin account username to be used to connect to the SQL Azure instance |
| AzureResourceGroupName | All functions | The resource group holding the Azure resources being managed by emdash |
| AzureTenantId | All functions | The id of the Azure tenant |
|
EmdashAzureServicePrincipal ApplicationId |
All functions | If AzureConnectionMethod is set to Certificate or ClientSecret then this value must be set |
|
EmdashAzureServicePrincipal CertificateThumbprint |
All functions | If AzureConnectionMethod is set to Certificate then this value must be set |
| SQLAzureServiceObjectiveName e.g. Basic S0 P1 etc |
Restore from a database copy, Restore from a point in time backup, Restore from a bacpac, Create database
|
Service objective of the target database during a restore. |
| SQLAzureEdition e.g. Premium Basic Standard DataWarehouse Free |
Restore from a database copy, Restore from a point in time backup, Restore from a bacpac, Create database
|
Edition of the target database during a restore. |
| SQLAzureDatabaseMaxSizeBytes |
Restore from a database copy, Restore from a point in time backup, Restore from a bacpac, Create database
|
Maximum size of the target database during a restore. |
|
SQLAzureBusinessCritical IntermediateServiceObjectiveName |
Point in time restore, Copy database restore | For a point in time restore emdash creates an intermediate database as part of the process – this setting controls the service objective value for the intermediate database. |
| AzureSQLDatabaseLatestPITRMinutes | Point in time restore | Only point in time restore points older than this number of minutes will be available to the user. |
| EmdashAzureClientSecret | All functions | If AzureConnectionMethod is set to ClientSecret then this value must be set |
| AzureConnectionMethod | All functions |
Allowable values: Certificate UserNamePassword ClientSecret |
Set up System Wide Settings
| Setting | Used By | Description |
| AzureAdminAccountUsername | All functions | If AzureConnectionMethod is set to UserNamePassword then this value must be set |
| AzureAdminAccountPassword | All functions | If AzureConnectionMethod is set to UserNamePassword then this value must be set |
emdash SQL Azure Management Functions
Once the above steps have been completed emdash is set up to perform the following functions for SQL Azure databases.
Emdash devops supports a variety of options to manage SQL Azure databases:
- Restore from a database copy (Environment refresh, Create environment)
- Restore from a point in time backup (Environment refresh, Create environment)
- Restore from a bacpac (Environment refresh, Create environment)
- Backup to bacpac (Backup all databases)
- Create database (Create environment)
- Delete database (Environment decommission)
These options will be described in the following sections.
Restore from a database copy
Emdash supports database restore from a copy of a source database. This is the equivalent of the Copy Database option using the Azure Portal - https://learn.microsoft.com/en-us/azure/azure-sql/database/database-copy?view=azuresql&tabs=azure-powershell#copy-using-the-azure-portal
Restoring from a Point in time backup
A new option is available when restoring a SQL Azure database from a source SQL Azure database
This is the point in time restore option. This is only available when the source and target databases are in the same SQL Azure instance .
Restore from a bacpac
Emdash has the capability to restore on-premise SQL Server and SQL Azure databases using the bacpac format, in addition to the old .bak file format which can still be used for on-premise SQL databases.
Backup to bacpac
Emdash has the capability to backup on-premise SQL Server and SQL Azure databases using the bacpac format, in addition to the old .bak file format which can still be used for on-premise SQL databases.
Create database
Creates a new SQL database.
Delete database
Deletes a SQL database
Setting up emdash to manage Azure Virtual Machines
Set up environment wide settings
| Setting | Used by | Description |
| AzureSubscriptionId | All functions | The subscription holding the Azure resources being managed by emdash. |
| AzureResourceGroupName | All functions | The resource group holding the Azure resources being managed by emdash |
| AzureTenantId | All functions | The id of the Azure tenant |
|
EmdashAzureServicePrincipal ApplicationId |
All functions | If AzureConnectionMethod is set to Certificate or ClientSecret then this value must be set |
|
EmdashAzureServicePrincipal CertificateThumbprint |
All functions | If AzureConnectionMethod is set to Certificate then this value must be set |
| EmdashAzureClientSecret | All functions | If AzureConnectionMethod is set to ClientSecret then this value must be set |
| AzureConnectionMethod | All functions |
Allowable values: Certificate UserNamePassword ClientSecret |
Set up System Wide Settings
| Setting | Used By | Description |
| AzureAdminAccountUsername | All functions | If AzureConnectionMethod is set to UserNamePassword then this value must be set |
| AzureAdminAccountPassword | All functions | If AzureConnectionMethod is set to UserNamePassword then this value must be set |
Emdash Azure Virtual Machine Management Functions
Emdash supports a number of options to manage virtual machines in Azure as part of the standard out of the box workflows.
- Create a virtual machine
- Delete a virtual machine
- Stop a virtual machine
- Start a virtual machine
SQL To set up env wide settings
-- Script will set up env wide setting options and env wide settings required by emdash to manage Azure connecting with an SPN with certificate or client secret
-- SQL Azure backups to storage account
-- some of the settings may already exist
-- any settings with default value 'set secure value' should be set with the correct value via the emdash UI
declare @environmentId INT
SET @environmentId = -1
DECLARE @appName NVARCHAR(100)
SET @appName = 'Fusion'
DECLARE @azureConnectionMethod NVARCHAR(100)
SET @azureConnectionMethod = 'Certificate'
declare @storageAccountName NVARCHAR(200)
SET @storageAccountName = 'acmewestus2storageacct'
declare @bacpacContainer NVARCHAR(200)
SET @bacpacContainer = 'bacpacs'
declare @overrideBackupPathEwsName NVARCHAR(200)
SET @overrideBackupPathEwsName = 'Override' + @appName + 'BackupPath'
declare @overrideBackupPath NVARCHAR(400)
SET @overrideBackupPath = 'https://' + @storageAccountName + '.blob.core.windows.net/' + @bacpacContainer
declare @resourceGroup NVARCHAR(100)
SET @resourceGroup = 'acme-rg-westus2'
declare @EmdashAzureServicePrincipalApplicationId NVARCHAR(200)
SET @EmdashAzureServicePrincipalApplicationId = 'a1c02eaa-f889-4d55-8a14-7ac9fd31ca80'
declare @EmdashAzureServicePrincipalCertificateThumbprint NVARCHAR(200)
SET @EmdashAzureServicePrincipalCertificateThumbprint = '84905e32ee8b0aa2bd3b95a35e9e5a9f66ae0170'
declare @tenantId NVARCHAR(200)
SET @tenantId = '5d1d0080-6723-4141-a6e2-2bb02d63b283'
declare @AzureSubscriptionId NVARCHAR(200)
SET @AzureSubscriptionId = '36f6e861-4030-4a15-bab3-3f44faca12b4'
declare @SQLAzureAdminAccountUsername NVARCHAR(200)
SET @SQLAzureAdminAccountUsername = 'sqladmin'
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('AzureConnectionMethod','TEMPLATE_AzureConnectionMethod',@azureConnectionMethod,'Connection method to use with Azure - Allowable values are Certificate UserNamePassword ClientSecret',NULL,NULL,'String')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('EmdashAzureClientSecret','TEMPLATE_EmdashAzureClientSecret','set secure value','Client secret for the emdash Azure SPN - used to connect when AzureConnectionMethod is set to ClientSecret',NULL,NULL,'Secure')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('AzureResourceGroupName','TEMPLATE_AzureResourceGroupName',@resourceGroup,'Default Azure resource group used when creating Azure virtual machines from resource manager (ARM) templates.',NULL,NULL,'String')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES (@overrideBackupPathEwsName,'TEMPLATE_' + @overrideBackupPathEwsName + '','NA','Backup location for ' + @appName + ' backups',NULL,NULL,'String')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('EmdashAzureServicePrincipalApplicationId','TEMPLATE_EmdashAzureServicePrincipalApplicationId',@EmdashAzureServicePrincipalApplicationId,'emdash Azure AD service principal application id.',NULL,NULL,'String')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('EmdashAzureServicePrincipalCertificateThumbprint','TEMPLATE_EmdashAzureServicePrincipalCertificateThumbprint',@EmdashAzureServicePrincipalCertificateThumbprint,'emdash Azure AD service principal certificate thumbprint.',NULL,NULL,'String')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('AzureTenantId','TEMPLATE_AzureTenantId',@tenantId,'Azure AD tenant id.',NULL,NULL,'String')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('AzureSubscriptionId','TEMPLATE_AzureSubscriptionId',@AzureSubscriptionId,'Azure subscription id.',NULL,NULL,'String')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('SQLAzureAdminAccountPassword','TEMPLATE_SQLAzureAdminAccountPassword','set secure value','emdash database user password when managing a SQL Azure database in an environment.',NULL,NULL,'Secure')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('SQLAzureAdminAccountUsername','TEMPLATE_SQLAzureAdminAccountUsername','tbd','emdash database user when managing a SQL Azure database in an environment.',NULL,NULL,'String')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('SQLAzureBackupStorageKeyType','TEMPLATE_SQLAzureBackupStorageKeyType','StorageAccessKey','StorageKeyType when backing up a SQL Azure database to a storage account.',NULL,NULL,'String')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('AzureStorageAccountContainer_' + @storageAccountName + '_' + @bacpacContainer +'_SharedAccessToken','TEMPLATE_AzureStorageAccountContainer_' + @storageAccountName +'_' + @bacpacContainer +'_SharedAccessToken','set secure value','Azure storage account shared access token - this is for storage account ' + @storageAccountName + ' and container bacpacs. This is used in backup all databases.',NULL,NULL,'Secure')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('AzureStorageAccount_' + @storageAccountName +'_AccessKey','TEMPLATE_AzureStorageAccount_' + @storageAccountName +'_AccessKey','set secure value','This is the access key for the storage account miecowestus2storageacct in Azure - it is used when backing up and restoring bacpac backups to the storage account.',NULL,NULL,'Secure')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('SQLAzureServiceObjectiveName','TEMPLATE_SQLAzureServiceObjectiveName','S2','Service Objective to use when restoring a SQL Azure database from a bacpac or database copy. Standard S2: 50 DTUs, 250 GB storage is recommended to keep costs low for non production environments.',NULL,NULL,'String')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('SQLAzureEdition','TEMPLATE_SQLAzureEdition','Standard','SQL Azure Edition to use when restoring a SQL Azure database from bacpac or database copy.',NULL,NULL,'String')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('SQLAzureDatabaseMaxSizeBytes','TEMPLATE_SQLAzureDatabaseMaxSizeBytes','214748364800','Maximum size in bytes of a SQL Azure database following a bacpac restore. 214748364800 bytes is 200 GB.',NULL,NULL,'Number')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES ('SQLAzureDatabaseMaxSizeBytes','TEMPLATE_SQLAzureDatabaseMaxSizeBytes','214748364800','Maximum size in bytes of a SQL Azure database following a bacpac restore. 214748364800 bytes is 200 GB.',NULL,NULL,'Number')
INSERT INTO [dbo].[EnvWideSettingsOptions]([Setting],[TemplateValue],[DefaultValue],[Description],[ApplicationId],[EncryptedDefaultValue],[SettingType])
VALUES (' AzureSQLDatabaseLatestPITRMinutes','TEMPLATE_ AzureSQLDatabaseLatestPITRMinutes','15','Most recent (in minutes) point in time restore available to the user.',NULL,NULL,'Number')
declare @settingId INT
select @settingId = settingid from dbo.EnvWideSettingsOptions where setting = @overrideBackupPathEwsName
INSERT INTO [dbo].[EnvWideSettings] ([EnvironmentId],[Setting],[Value],[EnvWideSettingsOptionsSettingId],[EncryptedValue])
VALUES (@environmentId,@overrideBackupPathEwsName,@overrideBackupPath,@settingId,NULL)
select @settingId = settingid from dbo.EnvWideSettingsOptions where setting = 'SQLAzureAdminAccountUsername'
INSERT INTO [dbo].[EnvWideSettings] ([EnvironmentId],[Setting],[Value],[EnvWideSettingsOptionsSettingId],[EncryptedValue])
VALUES (@environmentId,'SQLAzureAdminAccountUsername',@SQLAzureAdminAccountUsername,@settingId,NULL)
select @settingId = settingid from dbo.EnvWideSettingsOptions where setting = 'SQLAzureAdminAccountPassword'
INSERT INTO [dbo].[EnvWideSettings] ([EnvironmentId],[Setting],[Value],[EnvWideSettingsOptionsSettingId],[EncryptedValue])
VALUES (@environmentId,'SQLAzureAdminAccountPassword','set secure value',@settingId,NULL)
0 Comments