Power BI – Restore Datasets to new on-premise Gateway when old Gateway has failed or Recovery Key is lost

Power BI on-premises data gateway is a service running on a Windows server working as a connecting platform between the Power BI cloud service and the on-premise data sources.

Setting up a data gateway on-premise is fairly a straightforward process. There can be instances where your on-premise gateway fails because of a hardware failure, issues due to updates or you may want to move the gateway instance to a new server then you realize you need the recovery key which is no where to be found.

Without a functioning gateway, the reports and dashboard in the Power BI cloud service with datasets that are connected to on-premise data sources will fail resulting in data to become stale. I’ll elaborate more on this issue in this post on how to restore datasets from an old or failed on-premise gateway to a new gateway.

I faced a similar scenario recently and it was a great learning experience. There are few methods using which you can resolve this issue. I’ll try to cover them all in as much detail as possible.

Manual Method

Well.. If you don’t have too many data sources on-premise or if you are just planning for a quick fix maybe because someone important in your organization needs this fixed and they notified you like an hour before their big meeting.

Here are the high-level steps,

Once you install and configure the data gateway, you can see and manage both the old and new instances from the Power BI portal.

To add a user as admin to the gateway in the portal, follow below steps.

This image has an empty alt attribute; its file name is image-15-1024x370.png

Search user using username or email address, select Admin and click Share.

This image has an empty alt attribute; its file name is image-16-1024x504.png

Now to add a new data source, from the page header in the Power BI service, select Settings gear icon > Manage gateways.

I have highlighted my failed gateway and the new gateway server in my case.

Your next step is to determine the data source of the affected dataset. To get this information, you’ll need access to the workspace. As you can see, I have a report named ‘AdventureWorksProducts‘ and the underlying dataset with the same name.

Under the Gateway connection section, you’ll find the necessary information to setup the data source in the new gateway.

Back in the Manage Gateways page and in the Data sources tab, Click on New

Choose the new gateway to create the connection on, then select the Data source type. In my scenario, I picked SQL Server.

Once you provide all the information, Select Create. You see Created New data source if the process succeeds and a new data source entry like in screenshot below.

If you’ve made it this far, you are almost at the end of this method. Now back to the dataset’s settings like we did earlier and on to the Gateway connection section. As a reminder, you’ll need access(Admin, Member or Contributor) to the workspace and to the dataset, also keep in mind that you also need admin permissions on the gateway.

You should see the new data source we created listed. Select it from the drop-down and click Apply

That should take care of the connection and to confirm, you can refresh your dataset to make sure the connection works ok.

Like I said earlier, this method should be good in a small environment or if you are in a hurry to get it fixed and worry about the bulk of things later. I’ll cover the semi-automated way in the coming sections. I use the word automated loosely here but it’s more like less clicks and not moving around in the BI portal as much.

Using a Service Account

In this method, I’m using a service account or in other words a regular user account without any roles assigned to it. This can be an AD synced account or a Azure AD cloud only account. This account will need a Power BI Pro license assigned to it.

Here are the high-level steps,

I’ve already covered the adding data source part to the gateway in the earlier section and the process is same for this method too. You can do it with PowerShell or REST APIs but I don’t believe there is a method to copy the data sources from one gateway to another.

Permissions

In this method, I’m using a service account which was granted Admin permissions for the gateways and set as Owner on the data source. You should be able to get away with just having the account set as user on the data source. This service account is also set as Admin on the workspace but Member or Contributor should do the trick.

You can grant the gateway admin permission in the portal which I’ve covered in the earlier method or use the below script to add the user as admin.

Connect-AzureAD
Connect-DataGatewayServiceAccount
Get-DataGatewayAccessToken

Get-DataGatewayCluster
$gw = Read-Host "Enter Gateway ID"
$user = Read-Host "Enter username to be added as gateway admin"
$userToAdd = (Get-AzureADUser -Filter "userPrincipalName eq '$user'").ObjectId
Get-DataGatewayRegion
$Region = Read-Host "Enter region value where IsDefaultPowerBIRegion is set to true"
Add-DataGatewayClusterUser -GatewayClusterId $gw -PrincipalObjectId $userToAdd -AllowedDataSourceTypes $null -Role Admin -RegionKey $Region

With all these permissions, the service account still needs to take ownership of the dataset to finish rebinding the data source to the new gateway. You won’t have to manually take ownership of the dataset, the script below will do it for you on the dataset you specify.

Rebind dataset

Before proceeding further make sure you have the Microsoft Power BI Cmdlets for PS installed and logged in to the Power BI service using PowerShell,

Connect-PowerBIServiceAccount
Get-PowerBIAccessToken

I don’t do Power BI administration on a daily basis and there was a learning curve for me to understand the inner workings. Here is the thought process that went into building this script.

  1. Get all the gateways the service account has access to
    • Using the output, determine and copy the new gateway ID and store it in a variable
  2. Using the variable from earlier step, return a list of data sources from the new gateway
    • Using the output, determine and copy the data source ID where the affected dataset should be mapped to and store it in a variable
  3. Returns a list of workspaces the user has access to
    • Using the output, determine and copy the workspace ID which has the affected dataset
  4. Using the variable from earlier step, return list of datasets from the specified workspace
    • Using the output, determine and copy the affected dataset’s ID
  5. Using the variable from step 3 and step 4, transfer ownership over the specified dataset to the service account
  6. Using variable from steps 1, 2, 3 and 4, bind the specified dataset from the specified workspace to the new gateway
$gateways = Invoke-PowerBIRestMethod -Url "gateways" -Method Get | ConvertFrom-Json
$gateways.value
Write-Host "Please copy the new Gateway ID from above output" -ForegroundColor Red
$newGWID = Read-Host "Please paste the new Gateway ID"

$GWdatasources = Invoke-PowerBIRestMethod -Url "gateways/$($newGWID)/datasources" -Method Get | ConvertFrom-Json
$GWdatasources.value
Write-Host "Please note down the Data Source ID used by the dataset that needs to be migrated from above output" -ForegroundColor Red
$datasourceObjectIds = Read-Host "Please paste the Data source ID"

$ws = Invoke-PowerBIRestMethod -Url 'groups' -Method Get | ConvertFrom-Json
$ws.value
Write-Host "Please note down the Workspace ID which has the dataset that needs to be migrated from above output" -ForegroundColor Red
$wsID = Read-Host "Please paste the Workspace ID"

$dataset = Invoke-PowerBIRestMethod -Url "groups/$($wsID)/datasets" -Method Get | ConvertFrom-Json
$dataset.value
Write-Host "Please note down the dataset ID that needs to be migrated from above output" -ForegroundColor Red
$dsID = Read-Host "Please paste the dataset ID"

#This below line is not needed if the service account already has ownership of the dataset and is safe to comment out
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$($wsID)/datasets/$($dsID)/Default.TakeOver" -Method POST

try { $body = "{
  'gatewayObjectId': '$newGWID',
  'datasourceObjectIds': [
    '$datasourceObjectIds'
  ]
}"

Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$($wsID)/datasets/$($dsID)/Default.BindToGateway" -Body $body -Method POST
Write-Host "Dataset updated" }

catch {
  Write-Host "An error occurred"
}

You can adjust this script according to your needs as in some instances, your gateway ID, new data source ID and workspace ID will be the same, only the affected dataset ID will vary.

Using a Service Principal

In this method, I’m using a service principal to accomplish the same as above. One added advantage of using this method is, the Power BI Dataset can be setup to refresh without an actual user account. This would be great from an automation point of view and to avoid being tied to a specific user.

Here are the high-level steps,

Create SPN

The az ad app is part of Azure CLI and not a PS cmdlet. You’ll need to have Azure CLI installed and do az login as well before running this.

Connect-AzureAD 
Connect-AzAccount
az login

You can create an Azure AD application which will be the service principal from the portal and grant the and grant the ‘Dataset.ReadWrite.All’ API permission or use the below lines to create it. I’ve detailed how to determine the API ID and Permission ID in this blog post here.

A new Azure AD group is also needed and the Azure AD application has be made a member of this group. The below lines will accomplish that and if you have an existing group you have in mind, you can use that too. I’ll go over the reason for creating this group later in this section.

$appname = Read-Host "Enter a name Azure AD Application's Display Name"
$ObjID = New-AzureADApplication -DisplayName $appname | Select ObjectId
Add-AzADAppPermission -ObjectId $ObjID.ObjectId -ApiId 00000009-0000-0000-c000-000000000000 -PermissionId 322b68b2-0804-416e-86a5-d772c567b6e6 -Type Scope
Start-Sleep -Seconds 60
az ad app permission admin-consent --id $ObjID.ObjectId
Get-AzureADApplication -Filter "DisplayName eq '$appname'" | fl

$grpName = Read-Host "Enter a name for new Azure AD group"
$grpID = (New-AzureADGroup -DisplayName $grpName -MailEnabled $false -SecurityEnabled $true -MailNickName "NotSet").ObjectId
Get-AzureADGroup -ObjectId $grpID
Add-AzureADGroupMember -ObjectId $grpID -RefObjectId $spnToAdd
Get-AzureADGroupMember -ObjectId $grpID

The Get-AzureADApplication cmdlet will list the API permissions we applied. This can be verified in the ‘App registrations‘ blade from the Azure AD portal too.

Create a new Secret in this Azure AD application. You can also achieve this by using PowerShell. This secret value is needed for authentication while running the script later this section.

Remember to copy the secret value as it’ll be masked forever.

And we can also make sure of the group we created and it’s membership. I named the group, ‘PBI-API‘ in Azure AD.

For an Azure AD app to be able to access the Power BI content and APIs, the following settings need to be enabled in Azure AD portal. This is where the Azure AD group comes into play.

Go to Tenant settings in the Power BI Admin portal, and scroll down to Developer settings

  • Enable the Allow service principals to use Power BI APIs
  • Enable the Allow service principals to create and use profiles

Create SPN profile

I noticed that the SPN way of doing things worked in one instance without having a service principal profile created by the service principal. Profiles can be created using Profiles REST API. I’ve included the below lines which will create a profile for the SPN.

$prof = Read-Host "Enter a name for SPN's profile"

$body = "{
    'displayName' : '$prof'
}"

Invoke-PowerBIRestMethod -Url 'https://api.powerbi.com/v1.0/myorg/profiles' -Body $body -Method POST

A service principal can also call GET Profiles REST API to get a list of its profiles.

Invoke-PowerBIRestMethod -Url 'profiles' -Method Get

Permissions

Next, the service principal needs permissions on the dataset. We can achieve this by granting permissions to the service principal on the workspace.

Note: Adding the Azure AD group that has SPN as members doesn’t work

This next step is kind of where things get tricky.

What are we trying to achieve here?

  • Grant the service principal, admin permissions on the new gateway
  • Grant the service principal, user permissions on the gateway data source

Reason why it is tricky is, I first tried adding the Azure AD group the above permissions and it allowed me to add it but the script which comes later in this section didn’t work as expected. Based on further research, I realized that the SPN needs to be granted the above access directly instead of using the Azure AD group. Also, at the time of writing this post, adding SPN the above permissions using the portal is not supported. Hence, we’ll have to use PowerShell cmdlets,

Before proceeding further, please connect to the AzAccount and PowerBIService using the below cmdlets,

Connect-AzAccount
Connect-PowerBIServiceAccount
Get-PowerBIAccessToken

The below script will add the permissions I mentioned above and display the same at the end of executing the cmdlets. One good thing about the part where you add permissions to the gateway, data sources and workspaces is, it is a one-time deal.

Get-DataGatewayCluster
$gw = Read-Host "Enter Gateway ID"
$spn = Read-Host "Enter App name to be added as gateway admin"
$spnToAdd = (Get-AzADServicePrincipal -DisplayName $spn).Id
Get-DataGatewayRegion
$Region = Read-Host "Enter region value where IsDefaultPowerBIRegion is set to true"
Add-DataGatewayClusterUser -GatewayClusterId $gw -PrincipalObjectId $spnToAdd -AllowedDataSourceTypes $null -Role Admin -RegionKey $Region
Get-DataGatewayCluster -GatewayClusterId $gw | Select -ExpandProperty Permissions | ft
Get-DataGatewayClusterDatasource -GatewayClusterId $gw
$gwDSID = Read-Host "Enter Gateway Cluster DatasourceId"
Add-DataGatewayClusterDatasourceUser -GatewayClusterId $gw -GatewayClusterDatasourceId $gwDSID -DatasourceUserAccessRight Read -Identifier $spnToAdd
Get-DataGatewayClusterDatasourceUser -GatewayClusterId $gw -GatewayClusterDatasourceId $gwDSID

With all the permissions for the SPN now in place, we are ready to take ownership of the affected datasets in the workspaces and bind it with the new data source on the new gateway

Rebind dataset

In this SPN method, Instead of logging in with a username and password, you’ll have to login with the Application ID and secret

$Tenant = Read-Host "Enter Azure AD Tenant ID"
Connect-PowerBIServiceAccount -Tenant $Tenant -ServicePrincipal -Credential (Get-Credential) #user = Application (client) ID | Password is the secret value we created earlier in this section
Get-PowerBIAccessToken

The script is pretty much the same as in earlier section but only runs in the SPN context.

$gateways = Invoke-PowerBIRestMethod -Url "gateways" -Method Get | ConvertFrom-Json
$gateways.value
Write-Host "Please copy the new Gateway ID from above output" -ForegroundColor Red
$newGWID = Read-Host "Please paste the new Gateway ID"

$GWdatasources = Invoke-PowerBIRestMethod -Url "gateways/$($newGWID)/datasources" -Method Get | ConvertFrom-Json
$GWdatasources.value
Write-Host "Please note down the Data Source ID used by the dataset that needs to be migrated from above output" -ForegroundColor Red
$datasourceObjectIds = Read-Host "Please paste the Data source ID"

$ws = Invoke-PowerBIRestMethod -Url 'groups' -Method Get | ConvertFrom-Json
$ws.value
Write-Host "Please note down the Workspace ID which has the dataset that needs to be migrated from above output" -ForegroundColor Red
$wsID = Read-Host "Please paste the Workspace ID"

$dataset = Invoke-PowerBIRestMethod -Url "groups/$($wsID)/datasets" -Method Get | ConvertFrom-Json
$dataset.value
Write-Host "Please note down the dataset ID that needs to be migrated from above output" -ForegroundColor Red
$dsID = Read-Host "Please paste the dataset ID"

Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$($wsID)/datasets/$($dsID)/Default.TakeOver" -Method POST

try { $body = "{
  'gatewayObjectId': '$newGWID',
  'datasourceObjectIds': [
    '$datasourceObjectIds'
  ]
}"

Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$($wsID)/datasets/$($dsID)/Default.BindToGateway" -Body $body -Method POST
Write-Host "Dataset updated" }

catch {
  Write-Host "An error occurred"
}

Similar to the earlier section, you can adjust this script according to your needs as in some instances, your gateway ID, new data source ID and workspace ID will be the same, only the affected dataset ID will vary.

Needless to say, you can test if this was successful by doing a ‘Refresh now‘ on the dataset.

Issues you may encounter and How to fix it

Issue: You may encounter below status codes while running the Invoke-PowerBIRestMethod

Response status code : 404 (Not Found)
Response status code : 400 (Bad Request)

Fix or workaround: Well.. If you’ve already browsed though community.powerbi.com, then might have already realized that you are not alone dealing with these error codes. Usually this means you are requesting the Power BI REST API endpoints for data that doesn’t exist or you or the SPN that’s requesting the resource doesn’t have the necessary permissions to it. These best way to troubleshoot is to run these requests one at a time to determine where you it is failing or understand which resource you don’t have permissions to.

Issue: Applied permissions don’t reflect in the portal

Fix or workaround: I noticed that some of the changes takes time. Give it a few minutes before you go changing more things and you lose track of all the things you’ve changed in the process. If the permissions still didn’t show up for a while, use PowerShell cmdlets to verify if the permissions you’ve set was applied or not.

I’ll keep experimenting other scenarios and I’ll update the issues I come across later on.

This was one of those really lengthy posts but hey..as long as there is a solution at the end..Hopefully..am I right?..😁🤷‍♂️

Thank you for stopping by.✌

Azure AD – Assign Groups and Users to an application

Azure AD allows granting access to resources by providing access rights to a single user or to an entire Azure AD group. Using groups let the application or the resource owner to assign a set of permissions to all the members of a group. Management rights can be granted to other roles, like example., Helpdesk administrators to add or remove members from the group.

When a group is assigned to an application, only users in the group will have access. Also, if the application exposes role, roles can also be assigned to groups or users.

When I was working on integrating Salesforce with Azure AD for SSO, I needed to assign groups to the roles that Salesforce exposed and I figured I’d document the process I went though here.

Bulk create Azure AD groups

This section describes how to create multiple groups in Azure AD. This is not needed if your organization already has groups created.

Use below script to create multiple Azure AD groups that are listed in a csv file,

Connect-AzureAD
$groups = import-csv "C:\tmp\AzureAD Groups\groups.csv"

Foreach($group in $groups) {

New-AzureADGroup -DisplayName $group.name -Description $group.description -MailEnabled $false -SecurityEnabled $true -MailNickName "NotSet"

}

csv file input,

csv file

PowerShell output,

output

Assign Groups and Users to an app using PowerShell

Assigning groups or users can be done from the Azure AD admin portal by clicking on the Users and groups tab in the application which you are granting access to.

My plan here is to create Azure AD groups that corresponds to the name of the role that Salesforce exposes and then add users to those groups which provides them with appropriate access to the application.

Determine the roles available for the application

To determine the roles that the application exposes, use the cmdlet below.

$appname = Read-Host "Enter your App's Display Name"
$spo = Get-AzureADServicePrincipal -Filter "Displayname eq '$appname'"
$spo.AppRoles | ft DisplayName,IsEnabled,Id
AppRoles Output

Assign Groups to Roles in Application

Use below script to assign the application’s roles to groups. If you notice the csv file, I’m using the groups created in the previous step to the roles. This way, it is easier to manage. The New-AzureADGroupAppRoleAssignment cmdlet can be used to achieve this.

$appname = Read-Host "Enter your App's Display Name"
$spo = Get-AzureADServicePrincipal -Filter "Displayname eq '$appname'"
$groups = import-csv "C:\tmp\Salesforce_Asgn\groups.csv"

Foreach($group in $groups) {
	$id = Get-AzureADGroup -SearchString $group.name
	$app_role_name = $group.role
	$app_role = $spo.AppRoles | Where-Object { $_.DisplayName -eq $app_role_name }
	New-AzureADGroupAppRoleAssignment -ObjectId $id.ObjectId -PrincipalId $id.ObjectId -ResourceId $spo.ObjectId -Id $app_role.Id
	
}
csv input
PowerShell output

This below is how the application looks like in the Azure AD admin portal after running the above script,

Application Users and groups tab

Assign Users to Roles in Application

Use below script to assign the application’s roles to users. This can be achieved using the New-AzureADUserAppRoleAssignment cmdlet. Use the below script,

$appname = Read-Host "Enter your App's Display Name"
$spo = Get-AzureADServicePrincipal -Filter "Displayname eq '$appname'"
$users = import-csv "C:\tmp\Salesforce_Asgn\users.csv"

Foreach($user in $users) {
	$id = Get-AzureADUser -ObjectId $user.name
	$app_role_name = $user.role
	$app_role = $spo.AppRoles | Where-Object { $_.DisplayName -eq $app_role_name }
	New-AzureADUserAppRoleAssignment -ObjectId $id.ObjectId -PrincipalId $id.ObjectId -ResourceId $spo.ObjectId -Id $app_role.Id
	
}
PowerShell Output
Application Users and groups tab

Get all role assignments to an application using PowerShell

Get-AzureADServiceAppRoleAssignment cmdlet can be used to determine all role assignments to an application,

$appname = Read-Host "Enter your App's Display Name"
$spo = Get-AzureADServicePrincipal -Filter "Displayname eq '$appname'"
Get-AzureADServiceAppRoleAssignment -ObjectId $spo.ObjectId -All $true
PowerShell Output

Remove All Groups and Users assigned to an application

To remove all assigned groups and users from an application, Remove-AzureADServiceAppRoleAssignment cmdlet can be used,

$appname = Read-Host "Enter your App's Display Name"
$spo = Get-AzureADServicePrincipal -Filter "Displayname eq '$appname'"
$app_assignments = Get-AzureADServiceAppRoleAssignment -ObjectId $spo.ObjectId -All $true
$app_assignments | ForEach-Object {
	if ($_.PrincipalType -eq "user") {
		Remove-AzureADUserAppRoleAssignment -ObjectId $_.PrincipalId -AppRoleAssignmentId $_.ObjectId
	} elseif ($_.PrincipalType -eq "Group") {
		Remove-AzureADGroupAppRoleAssignment -ObjectId $_.PrincipalId -AppRoleAssignmentId $_.ObjectId
	}
}

It should go without saying that removing all permissions will disable user’s access to the application. Don’t try this as a first step in a production environment, unless you are absolutely sure of it.

Thank you for stopping by.✌

Office 365 – Export Email Addresses and UPN of O365 users with PowerShell

I will go over steps on how to export the list of users with their UPN, Object ID, primary SMTP address and Alias email address.

The Get-AzureADUser cmdlet comes in handy to pull all the user details in this scenario. The Mail attribute contains the Primary SMTP address of the user and the Primary SMTP address and Alias email address are stored in the ProxyAddresses attribute in Azure AD. The ProxyAddresses attribute is a multi-value property. The Primary SMTP address can be easily identified as it is in this format, SMTP:user@emaple.com The upper-case SMTP denotes that it the primary email address.

When an object is synced from on-premise Active Directory to Azure AD, the values in the proxyAddresses attribute in AD are compared with Azure AD rules and then populated in Azure AD. So, the values of the proxyAddresses attribute in AD may not match the ProxyAddresses attribute in AzureAD.

Export all users to csv file

The below script will pull all Azure AD users,

Connect-AzureAD

$Output = @() #create an empty array

$AzureADUsers = Get-AzureADUser -All $true | Select DisplayName,UserprincipalName,ObjectId,Mail,ProxyAddresses #Get all Azure AD users

ForEach ($User in $AzureADUsers)
{
	$Output += New-Object PSObject -property $([ordered]@{ #fetch user detail and add to $output
		UserName = $User.DisplayName
		UserprincipalName = $User.UserprincipalName
		UserId = $User.objectId
		SMTPAddress = $User.Mail
		AliasSMTPAddresses = ($User.ProxyAddresses | Where-object {$_ -clike 'smtp:*'} | ForEach-Object {$_ -replace 'smtp:',''}) -join ','
		
	})
}
$Output | Export-csv "C:\tmp\O365Users_$((Get-Date).ToString("MMddyyyy_HHmmss")).csv" -NoTypeInformation -Encoding UTF8 #Export users to csv file

Output file,

csv output

Thank you for stopping by.✌

Automatically Roll Over Kerberos Decryption Key with Azure AD SSO

In Azure AD, Seamless Single Sign-on can be configured when Password Hash Sync or Pass through authentication is configured. Azure AD Seamless SSO automatically signs in users when they are in their corporate owned devices and one the corporate network. This helps save a lot of time without repeatedly typing credentials in to Azure AD and other Azure AD integrated applications.

When we enable Azure AD Seamless SSO, a computer account named AZUREADSSOACC is created in on-premises AD in each AD forest. This computer account represent Azure AD in the on-premises AD. Considering how important this computer object is, it is better to take the following steps to protect it,

  • Move it into a OU,
    • Where they are safe from accidental deletions
    • Where only domain admins have access
  • Ensure that Kerberos delegation on the computer account is disabled
  • No other accounts in AD have delegation permissions
    • This can be easy to miss if you have permissions sprawl

The Kerberos decryption key for this computer account is securely shared with Azure AD. Microsoft recommends to roll over the Kerberos decryption Key at least every30 days. You will notice a warning when the key has not been updated in the past 30 days.

Azure AD warning
Kerberos decryption key

As I mentioned earlier, Microsoft recommends to roll over the keys at least every 30 days. Obviously, I can remember it promptly or even put a meeting invite in Outlook to recur 30 days, keep snoozing it to eventually dismiss the alert because I’m stuck doing something else on that day. I’m sure this feels very familiar to most of you reading this. 😂

This is a great scenario where automation can come in handy and I’ll go through the steps on how I implemented this.

Prerequisites

Below are the requirements for implementing this solution.

  • Global admin account
    • With MFA exception (preferably by origin IP)
  • Administrator account on AD Connect server with logon as a batch job
  • On-Premises AD domain admin account
  • Domain account that has permissions to ‘login as batch service’ on the Azure AD connect server

I covered in later half of another post about securely storing credentials using PowerShell into a file. Run these below lines to store encrypted credentials into a text file. This is far better than storing the password in plain text.

Remember, this method is secure but not 100% safe.

$ADCred = Get-Credential
$ADCred.Password | ConvertFrom-SecureString | Out-File "C:\temp\ADCred_sec.txt"
$AzureAD = Get-Credential
$AzureAD.Password | ConvertFrom-SecureString | Out-File "C:\temp\AzureAD_sec.txt"

Enter the domain user and Azure AD user which has necessary permissions in this below script and save it.

$ADUser = 'domain.com\user' #Must be entered in the SAM account name format contoso.com\jdoe
$ADUserEncrypted = Get-Content "C:\temp\ADCred_sec.txt" | ConvertTo-SecureString
$OnPremADCred = New-Object System.Management.Automation.PsCredential($ADUser,$OnPremADCred)

$AzureADUser = 'admin@tenant.onmicrosoft.com'
$AzureADUserEncrypted = Get-Content "C:\temp\AzureAD_sec.txt" | ConvertTo-SecureString
$AzureADCred = New-Object System.Management.Automation.PsCredential($AzureADUser,$AzureADUserEncrypted)

Import-Module 'C:\Program Files\Microsoft Azure Active Directory Connect\AzureADSSO.psd1'
New-AzureADSSOAuthenticationContext -CloudCredentials $AzureADCred
Update-AzureADSSOForest -OnPremCredentials $OnPremADCred

Schedule to automate

Once the script is stored, create a basic scheduled task to run it every month. Below are my settings,

Scheduled Task – General
Scheduled Task – Trigger
Scheduled Task – Actions

After making sure all the setting, Click OK and you will be prompted for credentials. If you receive the below error,

Task Scheduler Error “A specified logon session does not exist” - Microsoft  Tech Community
  1. Run gpedit.msc
  2. Look in Computer Configuration | Windows Settings | Security Settings | Local Policies | Security Options
  3. Double-click Network access: Do not allow storage of passwords and credentials for network authentication
  4. Set the policy to Disabled
  5. Go back to scheduled task to enter your credentials and click OK

Hope this helped you out in automating the Kerberos decrypting key roll over for the AZUREADSSOACC computer account.

Thank you for stopping by. ✌

Azure – Using KeyVault with PowerShell – Updated

Azure Key Vault is used to safely manage Secrets, Certificates and Crytographic Keys used by cloud and on-premise applications. After the secrets are stored in Azure Key Vault, we can assign access policies to user accounts or SPNs to retrieve and use them.

In this post, I will cover,

  • How to create Azure Key Vault
    • Create and update secrets in Azure Key Vault
  • Create a new Azure AD application and SPN
    • Create a client Secret
  • Assign a policy in Azure Key Vault to allow access to the SPN we create
  • Store the Azure AD application ID and client secret in the SecretStore vault
  • Retrieve Secret from Azure Key vault using credentials stored in SecretStore vault

I’ll go through the steps in both the portal and via PowerShell.

Before proceeding further, make sure you are connected to Azure AD and Azure via PowerShell. You may or may not use the same global admin account to connect to both Azure AD and Azure, either way you can use the below cmdlets and adjust it accordingly where necessary.

$AzureADcred = Get-Credential
Connect-AzureAD -credential $AzureADcred
$Azcred = Get-Credential
$SubsName = Read-Host "Enter Azure Subscription Name"
Connect-AzAccount -Credential $Azcred -Subscription $SubsName

Azure Key Vault

Register Resource Provider

Using PowerShell

Use below cmdlet to register ‘Microsoft.KeyVault‘ as a resource provider in the subscription,

Register-AzResourceProvider -ProviderNamespace "Microsoft.KeyVault"

To confirm the registration is successful,

Get-AzResourceProvider | Where-Object {$_.ProviderNamespace -contains "Microsoft.KeyVault"} | select ProviderNamespace, RegistrationState

Using Azure Portal

  1. Login to Azure Portal (https://portal.azure.com/)
  2. Navigate to Subscriptions
  3. Click to select the desired Subscription
  4. In the left navigation menu, click on Resource providers
  5. Search for Microsoft.KeyVault
  6. Click on Microsoft.KeyVault
  7. Click Register
  8. Once complete, Status column for Microsoft.KeyVault will show Registered
Register Resource provider – Microsoft.KeyVault

Create Azure Key Vault

Using PowerShell

To proceed further, launch PowerShell as admin and install the Az.KeyVault PowerShell Module

Install-Module -Name Az.KeyVault

The New-AzKeyVault can be used to create a new Key Vault in Azure. To determine the locations where Key Vault is offered, use the below cmdlet,

Get-AzLocation | Where-Object {$_.Providers -contains "Microsoft.KeyVault"} | ft

Use the below cmdlets to create a new Key Vault,

$kvName = Read-Host "Enter a name for Key Vault"
$rg = Read-Host "Enter Resource Group Name"
$loc = Read-Host "Enter Azure location"
New-AzKeyVault -VaultName $kvName -ResourceGroupName $rg -Location $loc

To confirm Key Vault creation,

Get-AzKeyVault

Using Azure Portal

To cerate a new Key Vault from the Azure portal,

  1. Login to Azure Portal (https://portal.azure.com/)
  2. Search for key vault
  3. Click Create or Create key vault
  4. Provide below information,
    • Subscription
    • Resource group
    • Key vault name
    • Region
    • Pricing tier
  5. Leave the other options default and click Review + create
    • The other options in the creation steps,
      • Access policy = I’ll go through it later in this post
      • Networking = All networks to make it publicly accessible
      • Tags = As necessary

Below are my settings,

Create a key vault

Create SPN in Azure AD

In this step, we’re creating a service principal in Azure AD. We will assign permissions for this SP to retrieve secrets from the Azure Key vault in later step.

In Azure AD, the application registration is the template used to create the SP. Also, the SP is what can be authenticated and authorized. Application and SP are associated by Application ID and they differ in it Object ID.

To create a new Azure AD application,

$appname = Read-Host "Enter a name for the Azure AD application"
New-AzureADApplication -DisplayName $appname

To create a service principal,

$appname = Read-Host "Enter name of Azure AD application"
$AppId = (Get-AzureADApplication -Filter "DisplayName eq '$appname'").AppId
New-AzureADServicePrincipal -AccountEnabled $true -AppId $AppId -DisplayName $appname

Create Client Secret

Next, we create a new client secret using the Get-AzureADApplicationPasswordCredential cmdlet,

$appname = Read-Host "Enter Azure AD application name to determine Object ID"
$appObjID = (Get-AzureADApplication -Filter "DisplayName eq '$appname'").Objectid
$KeyId = Read-Host "Enter value for secret identifier"
New-AzureADApplicationPasswordCredential -ObjectId $appObjID -CustomKeyIdentifier $KeyId

Copy the value in the output to a notepad as I have highlighted above. This value will not be available to copy later.

Assign Permissions

Using PowerShell

We can assign necessary permissions to the Azure AD application we created in above step, using the Set-AzKeyVaultAccessPolicy cmdlet,

$appname = Read-Host "Enter Azure AD application name to determine Object ID"
$Appid = (Get-AzureADApplication -Filter "DisplayName eq '$appname'").AppId
$kvName = Read-Host "Enter a name for Key Vault"
Set-AzKeyVaultAccessPolicy -VaultName $kvName -ServicePrincipalName $Appid -PermissionsToSecrets list,get

Using Azure Portal

  1. Login to Azure Portal (https://portal.azure.com/)
  2. Search for Key vault
  3. Click on the Key Vault we created earlier
  4. In the left navigation menu, click on Access policies
  5. Select Permission model as Vault access policy
  6. Click +Add Access Policy
  7. In the Add access policy window
    • For Secret permissions, select Get and List
    • For Select principal, select the SPN we created earlier
  8. Click Add
  9. Click Save to save policy

Manage Secrets in Key Vault

Applications, scripts or users can create, update, delete and retrieve secrets if they have the necessary policy assigned to them

Creating/Updating Secrets

To create a new secret, we can use the Set-AzureKeyVaultSecret cmdlet,

$Secret = ConvertTo-SecureString -String 'Password' -AsPlainText -Force
$kvName = Read-Host "Enter a name for Key Vault"
$SecName = Read-Host "Enter a name for secret"
Set-AzKeyVaultSecret -VaultName $kvName -Name $SecName -SecretValue $Secret

The secret can be updated to a new value using the same Set-AzureKeyVaultSecret cmdlet,

$Secret = ConvertTo-SecureString -String 'Password' -AsPlainText -Force
$kvName = Read-Host "Enter a name for Key Vault"
$SecName = Read-Host "Enter a name for secret"
$Expires = (Get-Date).AddYears(2).ToUniversalTime()
$NBF =(Get-Date).ToUniversalTime()
$Tags = @{ 'Department' = 'IT'; 'Environment' = 'Production'}
Set-AzKeyVaultSecret -VaultName $kvName -Name $SecName -SecretValue $Secret -Expires $Expires -NotBefore $NBF -Tags $Tags

Retrieving Secrets

To retrieve the current version of a secret, we use the Get-AzureKeyVaultSecret cmdlet,

$kvName = Read-Host "Enter a name for Key Vault"
$SecName = Read-Host "Enter a name for secret"
$secruretext = (Get-AzKeyVaultSecret -VaultName $kvName -Name $SecName).SecretValue

This will assign the stored secret to the $secruretext variable as a SecureString. We can now pass this to any other cmdlets that require a SecureString.

As I’ve already covered the Microsoft.PowerShell.SecretManagement and Microsoft.PowerShell.SecretStore PS modules in an earlier post, I’ll follow on the premise and store the client secret we created in the local vault. This way, I don’t have to save the client secret in the code as plaintext. To do this, we can store the Application ID and Client secret in a PSCredential object to the store,

$credential = Get-Credential
Set-Secret -Name azkv-01 -Secret $credential

In the Windows PowerShell Credential request window, for User Name input the Application (client) ID of the Azure AD application and for password input the Client Secret value we copied into a notepad earlier.

I’ve also created another secret as string in the local vault with my tenant ID value.

Putting this all together, we can use these below lines in PowerShell automation scripts,

$vpwd = (Import-CliXml "C:\Scripts\vpd.xml").Password
Unlock-SecretStore -Password $vpwd
$TenantId = Get-Secret -Vault CredsDB -Name TenantId -AsPlainText
$credential = Get-Secret -Vault CredsDB -Name azkv-01
Connect-AzAccount -ServicePrincipal -Credential $credential -Tenant $TenantId

To retrieve the secure string stored in the Azure Key vault, I’m using these lines below. Also for demo purposes, I’m including the -AsPlainText to the Get-AzKeyVaultSecret cmdlet but as I mentioned earlier, we can store this secure string to a variable and pass it on to other cmdlets.

$kvName = Read-Host "Enter a name for Key Vault"
$SecName = Read-Host "Enter a name for secret"
Get-AzKeyVaultSecret -VaultName $kvName -Name $SecName -AsPlainText
#or
$secruretext = (Get-AzKeyVaultSecret -VaultName $kvName -Name $SecName).SecretValue

I know this was a lengthy post and it may have gotten a little confusing right at the end with too many things named vault🤷‍♂️

Hope this helped you out in figuring out in including Azure Key Vault in your PowerShell automations.

Thank you for stopping by ✌