Let SSAS connect to Synapse without enabling 'Allow Azure services'

Question

Hi all,

I want to enable my Azure Analysis Services Cube to process with data from Synapse. But I want to disable the 'Allow all Azure Services' option on the firewall.

So now I'm thinking about adding an action to my Data Factory Pipeline (self hosted runtime because of the disabled option) which will enable a connection to the cube that must be processed (lets say a GET on the active refreshes). This will be blocked because of the IP is not whitelisted. Then I want to catch the IP-address where AAS is connecting with and then I want to whitelist this specific IP, process the cube and deactive the whitelisting of this IP.

Is this something that could work you think? Or am I missing something?

My other questions for this case are:

• How to check the IP which is denied? Can I just ping the server and believe this IP will be active for the next few seconds? Or at least believe it won't change very much and build in a retry for when the IP has just changed in that moment
• Can I assume that the IP which answers the GET refresh is the same as where the process will be sent to? I do believe that because of what I've seen, but I was assuming at first that the process is taking place at a lower level server, while the refresh is send to a load balancer or something.
• Any other (better?) suggestions for this case? I just want to process AAS cube based on a SQL Synapse DW, without whitelisting all Azure Services, that's it...

Answer 1

Hello @MiKeZZa

I believe that you are planning to use the web activity in ADF to make a API call to AAS . Once you do that and the as client IP ( in this is case the ADF ) is not whitelist , AAS will reject the call . I am assuming that the you are trying to do something when you try to connect to Azure SQL . But I don;t think it will help you much , the IP which comes from the rejected call is the client and not the server IP ,

One other think you you try is to run a fiddler trace and audit the trace once you have connected to the AAS locally . I beleve it will give you the remote IP .

Do let me know how it goes .

----------

Thanks
Himanshu

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

Answer 2

I think this won't work. I can only integrate it all in a VNet but that's not a nice practice because that AAS requests a gateway.

Answer 3

Hi @MiKeZZa ,

What concerns do you have using AAS with a gateway?

If you use an On-Premises Gateway, you can lock the refresh of the cube to that gateway only.
In advanced settings, there is an option ASPaaS \ AlwaysUseGateway which will ensure the gateway is always used when connecting the sources.

Then you just need to connect the subnet the Gateway VM is associated too, to the logical SQL server hosting the Synapse SQL pool.

If you wish to go with the option of using a Self Hosted Integration Runtime to find the IP address of Analysis Services, I was unable to make a web call with MSI authentication using Self Hosted integration runtime, the error was
"Could not load file or assembly 'Microsoft.DataTransfer.TransferServiceLib, Version=1.1.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified"

This appears to be a problem with the Integration runtime, because if I run the same call with an Authorization header, it works as expected. I suggest raising a case with Microsoft if you decide to go down this path so they can fix it.

Using this method, you would firstly initiate a POST with /refreshes on your model with the refresh details in the body.
You would then GET /refreshes , which will return a list of the refreshes (unfortunately the initial call doesn't return the refresh id) and find the last refresh id.
You would then GET /refreshes and the ID you picked from the previous step.
And then parse the output for the IP address

After whitelisting the IP address, you can run a POST on /refreshes again with the original body, or use other methods to process the cube.

-Rob

Answer 4

But then we must use VM's and that's a way we don't want to go...

Answer 5

It can still work using the the REST API without a self hosted integration runtime or an on-premises gateway.

You just need to whitelist the Data Factory IP address range on the SSAS firewall (you didn't mention if you were using this), and then whitelist the SSAS public IP on the SQL Server firewall, which can be identified using the API as described above.

-Rob