Forum Discussion
How To Read Files from Blob Storage with Storage Firewall Enabled
Background
Both Azure Storage and Azure SQL Database are popular services in Azure and are used by a lot of customers.
There are two typical scenarios which covering both services:
1) Azure SQL database can store Audit logs to Blob Storage.
2) Customers want to read files from Blob Storage of the database.
This works perfectly long time ago when there is no Azure Storage Firewall rule. Ever since the Azure Storage Service provided the Firewall feature, when customers turn the firewall rule on, they find the above scenarios won’t work anymore even if they turn on the " Allow trusted Microsoft services to access this storage account". Reading files from Blob Storage will fail with “Access Denied error.” Unfortunately Azure SQL Database is not considered as trusted service for now.
The known workaround is to capture the storage account logs and find the IP of Azure SQL database and then whitelist it. However, the whole process is time-consuming and suffering, especially when customers have to repeat these steps when the IP of database changes.
Azure SQL Database Private Link could not help this situation. For someone who knows Azure Synapse Analytics, they may know that it can co-exist with Azure Storage Firewall and the steps are as follow. However, this won’t work for Azure SQL database either.
https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-vnet-service-endpoint-rule-overview%23azure-synapse-analytics-polybase&data=02%7C01%7CWenjie.Zhou%40microsoft.com%7Ca6dc3d9484b54c31fe1c08d7fc8783ed%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637255531296221167&sdata=pS9JDl%2Fx2HWrHwFUZBVJAzhCYAzuA6E77e3D17MHGdA%3D&reserved=0
New Features of Azure SQL database Audit
Recently, our official document has announced there is a solution to bypass the storage account firewall rule and customers are able to write audit logs to storage accounts with Azure Storage firewall rules configured.
Write audit to a storage account behind VNet and firewall
https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fcreate-auditing-storage-account-vnet-firewall&data=02%7C01%7CWenjie.Zhou%40microsoft.com%7C1f12cbe567f7402f3b2808d7fc8857ba%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637255534969472828&sdata=OU8wQj62umkL67o1q%2BfnF9%2F5KM9sgPrPSleKXpUgrR4%3D&reserved=0
What helped Audit bypass the storage account firewall rules, what is the terminology?
From system view< sys.database_scoped_credentials> and also the Azure Storage diagnostic logs, if users enable the audit without turning on the Azure Storage firewall, the authentication method is automatically using Shared Access Signature (SAS).
If the user has configured firewall rules before they enabled audit, while enabling audit, user will receive the following notification from Azure portal. It is warning the user that he/she should create a server Managed Identity for this storage account.
By checking sys.database_scoped_credentials, Azure SQL database service is creating a Managed Identity credential automatically for accessing the storage account.
In order to bypass the firewall rule, SQL Azure change the authentication method to Managed Identity, used to be called as Managed Service Identity (MSI). It was because the authentication method of Managed Identity helped Azure SQL Server bypass the firewall check as a trusted service. Even users turn on 'Allow trusted Microsoft services to access this storage account' option, it cannot bypass the firewall check if users are using Shared Access Signature as authentication method because unfortunately Azure SQL Database is not considered as trusted service for now.
Using the same terminology, can we access storage account to read files and bypass the firewall rules?
In the past, when you used to access storage account and read files from the storage account. Users need to create a database scoped credential with identity of Shared Access Signature. Following the workaround provided above, even with assigning the storage blob data contributor role to the Azure SQL Server, Azure SQL Database hosted on this server is still having trouble to access the storage account with the following error message.
create DATABASE SCOPED CREDENTIAL dsc1
WITH IDENTITY = 'shared access signature'-- Storage Account Name
,SECRET = 'sv=2018-03-28&xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'; -- Access key
BULK INSERT CSVtest
FROM 'product.csv'
WITH ( DATA_SOURCE = 'CSVInsert',
Format='CSV'
);
Msg 4861, Level 16, State 1, Line 40
Cannot bulk load because the file "product.csv" could not be opened. Operating system error code 5(Access is denied.).
Learning the terminology of the Audit, the credentials should be Managed Identity instead of Shared Access Signature. To double confirm, after capturing the diagnostics log for storage account, it is easy to notice that the authentication method for writing audit logs into storage account and reading CSV files stored in storage account is different. After finishing the configuration for the workaround to write audit logs and bypassed firewall rules, the authentication method appears to be OAuth, and the authentication for reading files from storage account appears to be SASIDAuthorization, which failed with error.
Users can easily track which credentials the storage account or the container is utilizing from the below DMV.
select * from sys.database_scoped_credentials
Thus, if the user creates the database scoped credential as Managed Identity will help bypass the firewall rules of the storage account, and users can access the storage account to read files fine. Apparently, users have to setup the AAD identity for SQL Azure server and grant the role on it in the Storage Account.
CREATE DATABASE SCOPED CREDENTIAL msi_cred2 WITH IDENTITY = 'MANAGED IDENTITY';
CREATE EXTERNAL DATA SOURCE eds3
WITH (TYPE = BLOB_STORAGE,
LOCATION = 'https://xxxxxxx.blob.core.windows.net/xxx',
CREDENTIAL = msi_cred2
)