I'm trying to write data to azure blob storage from a SQL 2022 VM, using polybase.
I'm using managed identity for authentication with blob storage, as the VM is in a different VNET to the storage, this is the only way to allow the traffic through the azure storage firewall. Adding the public IP through the firewall does not allow traffic through (I assume this is because VM and Blob are in the same datacentre/network).
When executing CETAS, I get the below error:
Azure Active Directory authentication to Azure Storage is not allowed in BULK INSERT/OPENROWSET.
Switching to use a SAS token and I cannot connect, as this is blocked by the firewall. Is there a method to enable data write with managed identity or a method to allow SAS token traffic through the azure blob firewall?
I was using a abfss URI, as this is what our synapse pool was using.
abfss://[email protected]
This doesn't appear to work in SQL Server, which requires an abs URI:
abs://account.blob.core.windows.net/container