Forum Widgets
Latest Discussions
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://docs.microsoft.com/en-us/azure/sql-database/sql-database-vnet-service-endpoint-rule-overview#azure-synapse-analytics-polybase 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://docs.microsoft.com/en-us/azure/sql-database/create-auditing-storage-account-vnet-firewall 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. "You have selected a storage account that is behind a firewall or in a virtual network. Using this storage enables 'Allow trusted Microsoft services to access this storage account' on the storage account and creates a server managed identity with 'storage blob data contributor' RBAC." 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 )
E-mail notifications of canceled SQL Server Agent job should include the name of the Azure MI
Dear Community, In our Azure Managed Instances we use the SQL Server Agent for maintenance tasks and for checks. If these jobs are canceled for any reason, they inform you about this cancellation by email. Notifications have been configured in the job properties for this purpose. However, the mail that is sent in such a case does not show in the subject which Azure MI it was sent from: Example: If a job in the Azure MI "my-azure-mi-1.e4f0d600e52e.database.windows.net" aborts, we receive an email with the subject "[The job failed.] SQL Server Job System: 'klesstest' completed on \\DB8C1\B7BCF28BD9A3." In the future, we will operate a larger number (>30) of Azure Mis, we are dependent on knowing which Azure MI is affected when jobs are canceled. We would like to see the email notifications about a canceled SQL Server Agent job include the name of the Azure Managed Instance in the subject of the email and not the hostname where the SQL Server Agent service is running. The problem can be alleviated somewhat by including an explicit “mail-in-case-of-job-step-failed” in the job and adapting the “on-failure-action” in the individual steps so that it forwards to the “mail-in-case-of-job-step-failed” step. In step "“mail-in-case-of-job-step-failed" you can control the subject of the mail when using [msdb].[dbo].[sp_send_dbmail] and include @@servername in the subject of your mail. Nevertheless, you cannot completely do without the standard notification ob the SQL Server Agent Jobs and the problem is exacerbated with a growing number of custom Azure MIs Please vote for this request. Kind regards, MichaelUsing Azure SQL Database ledger
Last week at Build, we announced Azure SQL Database ledger, bringing the benefits of blockchain into your Azure SQL Database. While we have great documentation on how to use the ledger feature, there are likely many questions folks have around how it works, when you'd use it, and tips on getting started. In addition, many folks are probably wondering whether they should use the ledger feature instead of blockchain, or whether using Azure Confidential Ledger is another alternative. Let us know what you think - we're here to answer your questions!Welcome to the new Azure SQL Database community!
The documentation for Azure SQL Database is the place to get the latest information: https://docs.microsoft.com/en-us/azure/sql-database/ Of course, if you have questions, comments or requests after reading the documentation please feel free to post them here!
