SSIS & Azure SQL Managed Instance with MFA
We've been testing out Azure SQL Managed Instance and created SSIS packages to run against it. We set connections using the ADO.Net connection manager and all worked well. We recently had to apply MFA to all logins and this is causing issues within SSIS as there is no MFA option for logins like there is in SSMS. We can connect if we use SQL Server Authentication but we'd rather not do that. When using Active Directory Integrated Authentication, all of our test connections fail and the package will not run locally. In SSMS, there is an integrated authentication with MFA option and that works from within SSMS. How can we use SSIS and connect to the database using Windows Authentication when MFA is required?
Azure SQL Managed Instance Business Critical Tier, Transactional Replication, and failing over
Hi all, My company is planning a migration from SQL Server onprem to Managed Instance on Azure, using the business critical tier with zone-redundancy for HA. We currently have a peer-to-peer replication setup where our data services team updates data on a second read/write node that is separate from our main application read/write node, and the replication keeps everything in sync. My question is: on Azure if we have a second read/write MI (for our data team) syncing with the main business-critical tiered MI and there is a failover there to one of the secondary read-only replicas, what is the impact on the transactional replication to the data team MI? Could it break? Or would it now just sync with the new primary read/write node? Thanks.ODBC Linked Server in Managed Instance
Hi, Peoples I have a Managed Instance that I access via a VM created on Azure. On this same machine I installed the database ODBC driver and registered the connection in ODBC Data Sources. In SQL in Linked Servers I created the connection, but I can't find the data source.How To Read/Write Files from/to Blob Storage with Storage Firewall enabled for Managed Instance
Background Previously, we have discussed about how to read files from blob storage with storage firewall enabled for Azure SQL Database. Please refer to the below online blog for more information. https://techcommunity.microsoft.com/t5/azure-sql-database/how-to-read-files-from-blob-storage-with-storage-firewall/m-p/1404901#M12 When configured firewall rules of storage accounts for Managed Instance, audit logs could no longer be recorded into atorage account, and user will received such email notification as below. "Audit logs for database 'xxxxxxx' on server 'xxxxxxx' are not being recorded in storage account 'xxxxxxx'" There is the option of 'Allow trusted Microsoft service to access this storage account' under storage account firewall configuration page, unfortunately, according to our online document, Managed Instance is not yet considered as trusted service. Refers to https://docs.microsoft.com/en-nz/azure/storage/common/storage-network-security#exceptions Moreover, comparing with SQL Azure, Managed Instance does not support Managed Identity with the error below in the errorlog. Guidance To enable the read/write access to Azure Storage with Firewall turned on, users need to add Managed Instance’s subnet to Storage Account Vnet firewall rules with leveraging the MI subnet delegation and Storage service endpoint. Firstly, users would need to determine which subnet the managed instance has been deployed on. After selecting the specific subnet, change the configuration of Subnet delegation to Managed Instances. Following, users can delegate this subnet to Managed Instance, please wait for approximately one hour, and arrange Storage as a service added to service endpoints. After both of the above steps have been performed successfully, add the configured Vnet/ Subnet to Virtual networks rules of storage account. Using this workaround, users are able to write audit logs to storage account with firewall rules configured. Please note, the above steps could only be performed when the storage account and the managed instance are in the same or paired region. In this case, the Managed Instance was deployed in East Asia, and the storage account is deployed in East US. While adding the configured Vnet/ Subnet to Virtual network rules of the storage account, user would not see any available virtual networks from the list and also portal will show a notification that only virtual networks in 'East US' and 'West US' will be listed, which is paired region. Terminology Behind the Guidance The workaround delegates the specific subnet to Managed Instance, and enables storage service endpoints on this subnet, thus, the subnet will be able to access the storage account through service endpoint. According to our online document, "Subnet delegation enables you to designate a specific subnet for an Azure PaaS service of your choice that needs to be injected into your virtual network. Subnet delegation provides full control to the customer on managing the integration of Azure services into their virtual networks." Refers to https://docs.microsoft.com/en-us/azure/virtual-network/subnet-delegation-overview Using the same terminology, can we access storage account to read files? Before adding the configured Vnet/subnet to storage account, users will have denied access to storage account to read files from a storage account which has configured firewall rules. 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.). After performing the above workaround, users should be able to read files and access the storage account as the subnet of the Managed Instance has been whitelisted. Author: Marlon Jin <marlonj@microsoft.com>; Yvonne Zhou <wenzho@microsoft.com> Please feel free to contact us if any questions.
