Data Gateway : Automation using PowerShell - Part 1

[Jayendran]

Introduction:

Recently, with the new release of Data Gateway Automation, we can now install and configure the gateway using PowerShell itself.

In this blog, we are going to see how to use the new PowerShell scripts to install and configure the data gateway and some best practices.

Note: This feature is still in public preview.

Simple Script:

The below is the simple script used to install and configure the data gateway.

$Psversion = (Get-Host).Version

if($Psversion.Major -ge 7)
{

if (!(Get-Module "DataGateway")) {
Install-Module -Name DataGateway 
}

$securePassword = "" | ConvertTo-SecureString -AsPlainText -Force;
$ApplicationId ="";
$Tenant = "";
$GatewayName = "MyGateway";
$RecoverKey = "Demo@123" | ConvertTo-SecureString -AsPlainText -Force;
$userIDToAddasAdmin = ""


#Gateway Login

Connect-DataGatewayServiceAccount -ApplicationId $ApplicationId -ClientSecret $securePassword  -Tenant $Tenant


#Installing Gateway

Install-DataGateway -AcceptConditions 


#Configuring Gateway

$GatewayDetails = Add-DataGatewayCluster -Name $GatewayName -RecoveryKey  $RecoverKey


#Add User as Admin
Add-DataGatewayClusterUser -GatewayClusterId $GatewayDetails.GatewayObjectId -PrincipalObjectId $userIDToAddasAdmin -AllowedDataSourceTypes $null -Role Admin

}
else{
exit 1
}

Output:

You can also find the above script on GitHub.

You can find the Official PowerShell script here.

Limitations: (As of May 2020)

• Only PowerShell version 7 and above is supported.
• You can't create a new data source .
• You can't restore an existing gateway.
• You can’t upgrade a gateway with this feature, right now the PowerShell script is only applicable for installing DataGateway.
• Adding Gateway Members to newly created cluster is not supported yet.
• The "Add-DataGatewayCluster" script will install the gateway cluster that must be `3000.37.35` (April 2020) or higher gateway version.

Best Practices:

• Make sure you have the PowerShell version 7 because these scripts only work with version 7 and above.
• You have to run the script in the PowerShell in Admin mode.
• If you want to overwrite the existing gateway configuration on the local machine and configure a new one, use -OverwriteExistingGateway in "Add-DataGatewayCluster" script.
• By default, the "Add-DataGatewayCluster" script will install the gateway in Power BI default tenant region, if you want to change the region then you have to provide the correct region which you can get from the "Get-DataGatewayRegion" module.

[Anonymous]

According to the points mentioned upper:

• You can't restore an existing gateway.
• You can’t upgrade a gateway with this feature, right now the PowerShell script is only applicable for installing DataGateway.

If you would like to automatically change Service Account under the Gateway is running on.
You can put this code between Gateway installation code:

#Installing Gateway

Install-DataGateway -AcceptConditions 

# Setup Service Account
$serviceName = "PBIEgwService"    
Stop-Service -Name $serviceName

$Creds = New-Object System.Management.Automation.PSCredential ($serviceAccountName, $servicePassword)
Set-Service -Name $serviceName -Credential $Creds

Start-Service -Name $serviceName

#Configuring Gateway

$GatewayDetails = Add-DataGatewayCluster -Name $GatewayName -RecoveryKey  $RecoverKey

[Dariusz_Karpiej]

Hello!

Please let me know if currently (in October 2021) it is possible to use this script for upgrade gateways?

Thank you in advance for your answer.

[Franck99]

hello,

Please when I did the connect-datagateway I have this error 

Connect-DataGatewayServiceAccount -ApplicationId $appID -ClientSecret $pwd -Tenant $tenantID

Connect-DataGatewayServiceAccount: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. (api.powerbi.com:443).

Can you help me with this error ?

Thank you

[wo94xutao]

If we want to add this task to the Task Scheduler, we need to specify it to run with PowerShell 7

Program/Script: "C:\Program Files\PowerShell\7\pwsh.exe"

Arguments: -file "C:\PowerBI\InstallUpdatePowerBIGateway.ps1"

also you may need to replace:

if (!(Get-Module "DataGateway")) {
Install-Module -Name DataGateway
}

To:

if (!(Get-Module "DataGateway")) {
Install-Module -Name DataGateway -Confirm:$False -Force
}

Otherwise, it may stuck on running state and not proceeding/ending properly