Get IP Of AAS To Allow Firewall Set Up In Azure Blob Storage

Get IP Of AAS: This FAQ for Azure Storage https://docs.microsoft.com/en-us/azure/analysis-services/analysis-services-network-faq

For backing up the database, the following workaround only work if the AAS and storage accounts are in different region. For data refresh reading data from Azure Blob storage, being in the same data center is not required.

Get IP Of AAS

I found a way to get the IP address of the current AAS server node and make it available to be added to Azure Blob storage firewall rule with no need to use a gateway and with a very low cost.

I also found that if the firewall rule blocks the refresh command, the refresh will return error saying the credential is invalid, which can be misleading

1. Create the smallest Azure SQL DB that is a basic tier, cost will be $15/month
2. Create a function in the database

CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
	  DECLARE @IP_Address varchar(255);
	  SELECT @IP_Address = client_net_address
	  FROM sys.dm_exec_connections
	  WHERE Session_id = @@SPID;
	  Return @IP_Address;
END

3. Create a new view to get Get IP Of AAS

CREATE VIEW [dbo].[GetMyIP]ASSELECT dbo.GetCurrentIP() AS MyIPGO

4. Create a new tabular model to import data from this view. The IP below is my home IP, since I am importing data in SSDT at home and this will help me to Get IP Of AAS

5. Deploy this model to your AAS server, process it. Run a DAX query to get the data

evaluate GetMyIP

6. Add this IP to the Azure Blob storage Firewall to Get IP Of AAS

If you want to run the DAX query programmatically using PowerShell, below is the sample script to parse the result

Cleaned up PowerShell script with new PowerShell module with steps processing the IP model, query the IP, adding the IP to the storage firewall, backup model, and remove IP from storage firewall.

# Replace the information here with your data
$TenantId = "***"  
$AASAppId="***"   
$AASAppKey= "***"   

$AASServerName = "asazure://***.asazure.windows.net/***"  
$AASDatabaseName = "AzureAS_GetIp"  # or use the model name that you created 
$BackupFile = "***.abf"

$StorageRG = "***"
$StorageAccount = "***"

# Create Credential
$PWord = ConvertTo-SecureString -String $AASAppKey -AsPlainText -Force 
$Credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $AASAppId , $PWord 

# Process model to get current IP
Invoke-ProcessASDatabase -Server $AASServerName -DatabaseName $AASDatabaseName -RefreshType "DataOnly" -ServicePrincipal -ApplicationId $AASAppId -TenantId $tenantId -Credential $Credential 

# Query the IP from the model
$query="evaluate GetMyIP" 
$result= Invoke-ASCmd -Server $AASServerName -Database $AASDatabaseName -Query $query -ServicePrincipal -ApplicationId $appID -TenantId $tenantId  -Credential $Credential  
$MyIp=$result.Substring($result.IndexOf("_MyIP_")+1)#first one is the schema 
$MyIp=$MyIp.Substring($MyIp.IndexOf("_MyIP_")+1)#second one is the begin of the result element 
$MyIp=$MyIp.Substring($MyIp.IndexOf(">")+1,$MyIp.IndexOf("<")-$MyIp.IndexOf(">")-1) 
Write-Output $MyIp 

# Add AAS IP to storage firewall
Add-AzStorageAccountNetworkRule -ResourceGroupName $StorageRG -AccountName $StorageAccount -IPAddressOrRange $MyIp  
# have to wait a little while for the firewall update on the storage to take effect
Start-Sleep -s 120

# Backup AAS model
Backup-ASDatabase -backupfile $BackupFile -name $AASDatabaseName -server $AASServerName -Credential $Credential -ServicePrincipal -ApplyCompression -AllowOverwrite -verbose 

# Remove the IP again from the storage firewall after backup is finished
Remove-AzStorageAccountNetworkRule -ResourceGroupName $StorageRG -AccountName $StorageAccount -IPAddressOrRange $MyIp 

7. Then anyone can use the scripts from  https://docs.microsoft.com/en-us/azure/storage/common/storage-network-security#grant-access-from-an-internet-ip-range  to update the firewall rule.

Add-AzStorageAccountNetworkRule -ResourceGroupName "myresourcegroup" -AccountName "mystorageaccount" -IPAddressOrRange $MyIp

Code sample https://docs.microsoft.com/en-us/powershell/module/az.storage/add-azstorageaccountnetworkrule?view=azps-3.6.1 

Anyone can run Azure automation job to update their firewall rule every “x” minutes, or they can run this just before doing a refresh.

I hope this can help some of our users/viewers so they do not need to whitelist the whole data center IP

This method above only works with pulling data from Azure Blob storage. But it will not work with taking a backup to Azure blob storage (Get IP Of AAS)

TSG’s

1. Postman: AAS Model Refresh Using REST API
2. RDCMan Scaling Is Blurry: Fix In 3 Steps (kingfishertechtips.in)
3. Port Number For Analysis Services – Find Ports For SSAS? (kingfishertechtips.in)
4. SMTP Server In SSRS/PBIRS |Subscribe To Email Problem/Issue (kingfishertechtips.in)
5. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
6. Creating Tabular Project & Deploy Model To Analysis Service (kingfishertechtips.in)
7. Deploy Tabular Project To Azure Analysis Service – SSDT Tool (kingfishertechtips.in)
8. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
9. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
10. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
11. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
12. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
13. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
14. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
15. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
16. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)
17. Login With AAD/Domain/Microsoft Account To Azure VM’s/RDP (kingfishertechtips.in)