Making Azure DMS More Secure: Azure Portal Permission Enhancements
Migrating databases to Azure SQL Managed Instance or Azure SQL Virtual Machine is a critical step in modernizing enterprise infrastructure. With security and compliance top of mind, Azure Database Migration Service (DMS) has introduced key changes to its Azure portal experience—especially around permission for blob container access. Why the Change? Previously, in case of Azure Portal, DMS relied on account key-based access to Azure Blob Storage for listing and accessing backup files on the migration configuration page. While functional, this approach is not best in terms of security, especially for industries which prohibit the use of shared keys. Now, DMS's Azure portal uses security context of the current signed in user on the Azure portal to list and access backup files in the blob container, making it better security approach. Impact of the Change When migrating to Azure SQL Managed Instance or Azure SQL Virtual Machine via Azure portal make sure the current signed in user has Storage Blob Data Reader role on the Blob container that contains the backup files. This permission is needed to list folders and files in the blob container during migration setup via Azure portal only. If the current signed in user lacks the Storage Blob Data Reader role on the Blob container, users will encounter the following error: Error: "Blob container selection error: Error listing the contents of the container: This request is not authorized to perform this operation using this permission." Solution: Make sure the current signed in user has "Storage Blob Data Reader" role on the Blob container that contains the backup files. For more information, refer : Tutorial: Migrate SQL Server to Azure SQL Managed Instance - Azure Database Migration Service | Microsoft Learn Tutorial: Migrate SQL Server to SQL Server on Azure Virtual Machine Using Azure Data Studio - Azure Database Migration Service | Microsoft LearnDMS - Support for Managed Identity for Azure SQL Managed Instance migration
Azure Database Migration Service (DMS) has introduced a new feature that supports the use of Managed Identity for migrating to Azure SQL Managed Instance. This enhancement simplifies the migration process and ensures secure and seamless integration with Azure Database Migration services. In this blog post, we will dive into the prerequisites, permissions or role(s) required, and how to use this associated Managed identity for migrating to Azure SQL Managed Instance. Currently, this feature is supported through Azure Portal, PowerShell, and Az cmdlets. Prerequisites Before you begin the migration to Azure SQL Managed Instance using Managed identity, ensure that following prerequisites are in place: 1. The Target Azure SQL Managed Instance's associated Managed Identity: Azure Database Migration Service only supports Managed Identity that is associated with the target Azure SQL Managed Instance. How to identify the associated Managed Identity? Once you start the migration to Azure SQL Managed Instance using Azure Database Migration Service and on second page, select the target Azure SQL Managed instance, its associated Managed Identity will be displayed if "Use Managed Identity" is selected (default), as highlighted below. Alternatively, you can follow these steps: a) Go to the target Azure SQL Managed Instance's home page. b) On the left menu, under Security > Identity: If User-assigned Managed Identity is present, the associated Managed Identity will be same as selected under the Primary Identity. If there is no User-assigned Managed Identity and only System-assigned Managed Identity is enabled, the associated Managed Identity will be System-assigned Managed Identity and have the same name as the Azure SQL Managed Instance's name. For example, for ABCSQLMI - Azure SQL Managed Instance the System-assigned Managed Identity will be "ABCSQLMI". 2) Permissions: Assign the "Storage Blob Data Reader" role on the storage account to the target instance's associated Managed Identity. Steps to Assign Permissions In the Azure portal, go to the storage account that will be used in migration for keeping the backup files. On the left menu under Access Control (IAM), click on "+Add" > Add role assignment Select or search for builtin role "Storage Blob Data Reader", click Next. Assing this role access to Managed Identity by selecting the associated Managed Identity identified in the previous step as the member. Note: When migrating to Azure SQL Managed Instance or Azure SQL Virtual Machine via Azure portal make sure the signed in user has Storage Blob Data Reader access on the Blob container that contains the backup files. This permission is needed to list folders and files in the blob container during migration setup via Azure portal only. How to use associated Managed identity for migration? Upon initiating the migration to Azure SQL Managed Instance using Azure Database Migration Service, navigate to the second page and select the target Azure SQL Managed Instance. If the "Use Managed Identity" option is selected (default), the associated Managed Identity will be displayed and used for the migration (as shown in the first image above). Once Managed identity is used for the migration, DMS will utilize this Managed identity for reading the backup files on the Azure blob storage and thus removing the need for SAS keys. Limitations: Azure Database Migration Service supports Managed Identity that is associated with the target Azure SQL Managed Instance only. It can be either User assigned, or System assigned Managed identity. Currently, this feature is supported through Azure Portal only. Ensure that the storage account has the "Allow storage account key access" enabled. Benefits of using Managed Identity: Using Managed Identity for Azure SQL Managed Instance migrations offers several security benefits: Enhanced Security: Managed identities eliminate the need to use SAS key, reducing the risk of SAS key token exposure. Simplified Management: As associated Managed Identity of the target Azure SQL MI is used, it allows for seamless integration with Azure Database Migration services, making it easier to manage access permissions and roles. Improved Efficiency: The streamlined authentication process speeds up migrations and reduces the complexity of managing SAS keys. Improved Compliance: By using Managed Identity, user can ensure that they adhere to security best practices and compliance requirements, as it is managed securely by Azure. All the above benefits make Managed Identity better than SAS key token. Learn more. Conclusion The new feature supporting Managed Identity in Azure Database Migration Service for Azure SQL Managed Instance migrations offers a secure and efficient way to manage permissions during the migration process. By following the steps outlined above and leveraging the security benefits of Managed Identity, you can ensure a smooth and secure migration to Azure SQL Managed Instance.Migration Dashboard for SQL Server enabled by Azure Arc
Overview We are excited to announce the new Migration Dashboard feature in the Azure portal. SQL Server enabled by Azure Arc, automatically produces an assessment for migration to Azure. This assessment plays a vital role in the success of your cloud migration and modernization journey. This dashboard provides visibility into the assessment coverage and migration readiness across all your SQL Server instances enabled by Azure Arc. How to view the Migration Dashboard Using this feature is simple. Navigate to the Azure portal and search for Azure Arc. Click through into the Azure Arc center. Under Data Services, click on SQL Server Instances. You will be presented with the list of SQL Server instances enabled by Azure Arc. Switch to the Migration Dashboard tab in the right pane to get the at-scale summary of your instances, the migration assessment coverage and migration readiness of your SQL Server enabled by Azure Arc. Understand the Migration Dashboard The dashboard: Provides an overview of SQL Server instances and databases discovered. Provides an overview of the SQL Server instances which have assessments generated. Provides migration readiness summary for each of the Azure SQL offerings. Provides rich filtering capabilities enabling you to tailor the view to your needs. The first section of the migration dashboard presents an overview of all SQL Server instances and databases accessible to you. You can also see the distribution of the instances by version and edition. The next section provides you with an overview of the migration assessment and migration readiness of the instances of SQL Server enabled by Azure Arc. You can see how many instances have assessments available. The migration readiness for each Azure SQL offering is separately shown. Call to Action We encourage you to use the Migration Dashboard to: Track Migration Assessment coverage across your SQL Server instances enabled by Azure Arc. Understand your migration readiness to the Azure SQL offerings. Here are a few steps to get started: Explore the Feature: Visit the Azure portal and take some time to familiarize yourself with the dashboard and the insights it provides. Join the Community: Share your experiences and insights with the Azure Arc community. Join our forums and participate in discussions to learn how others are leveraging this feature to optimize their server environments. Stay Informed: Keep up with the latest updates and best practices for SQL Server enabled by Azure Arc by following our blog and subscribing to our newsletter. We regularly share tips, tutorials, and success stories from our users. We are confident that the Migration Dashboard will be a valuable addition to your SQL Server migration journey to Azure. We look forward to your feedback and success stories as you begin to explore this new feature.Client Connection Summary for SQL Server enabled by Azure Arc
Overview We are excited to announce our new SQL Server client connection summary feature for SQL Server enabled by Azure Arc. This addition provides visibility to the clients connecting to your SQL Server instances, helping to simplify monitoring, security and management. Customers wanting to determine the clients that are connecting to their SQL Server instances need to poll the sys.dm_exec_sessions dynamic management view to identify these connections and track activity. With this feature in SQL Server enabled by Azure Arc, the agent is able to capture this information, providing visibility into which clients are connecting to your SQL Server instances. The system automatically polls the DMV at hourly intervals and aggregates these connections for you, enabling more informed decision-making and streamlined operations. How does it work? With this new feature: Hourly Data Collection: The monitoring agent collects client connection data at hourly intervals using the sys.dm_exec_sessions dynamic view. 30-Day Data Storage: The data is stored in telemetry for 30 days. This allows for extended analysis. Data Summarization: The unique client connections are summarized over a time period of your choice within the past 30 days. How to Use the Client Connection Summary Using this feature is simple. Follow these steps: Step 1: Enable the Client Connection Feature Navigate to the Azure portal and select your SQL Server enabled by Azure Arc instance. Under the "Monitoring" tab, navigate to the SQL Server Connections view. Ensure that the feature is enabled. If it is not, click the button to enable data collection. This initiates the task for collecting data at hourly intervals. Step 2: Access the Client Connection Summary Once enabled, you will soon start seeing an overview of client connections. Step 3: Analyze the Data Filter for specific program name values of interest. You can also change the Time Range as required and examine the data to understand connection patterns. Call to Action We encourage you to use the Client Connection Summary feature of SQL Server enabled by Azure Arc. Here are a few steps to get started: Explore the Feature: Visit the Azure portal and enable client connection tracking on your SQL Server instances. Take some time to familiarize yourself with the view and the insights it provides. Join the Community: Share your experiences and insights with the Azure Arc community. Join our forums and participate in discussions to learn how others are leveraging this feature to optimize their server environments. Stay Informed: Keep up with the latest updates and best practices for SQL Server enabled by Azure Arc by following our blog and subscribing to our newsletter. We regularly share tips, tutorials, and success stories from our users. We are confident that the Client Connection summary will be a valuable addition to your SQL Server management toolkit. We look forward to your feedback and success stories as you begin to explore this new feature.SQL Migration assessments now include Next-gen General Purpose Azure SQL MI
Announcing the inclusion of the Next-gen General Purpose service tier in migration assessments for SQL Server migration to Azure SQL Managed Instance. All database migration tooling includes this capability now. This service tier is more customizable, allowing the configuration to be tailored more closely to the resource requirements.
A quick way to generate Azure SQL migration assessments
Overview Azure Migrate doesn’t allow users to import the SQL Server Inventory and discovery information from sources other than Azure Migrate appliance as of today. As a result, users who cannot deploy Azure Migrate appliance to discover their SQL Server estate for technical, business or compliance reasons or have already discovered their SQL Server estate using tools like rvtools and want a quick and approximate way to check readiness and total cost savings by moving to Azure, cannot leverage the rich capabilities SQL assessment and business case capabilities that Azure Migrate offers. To help customers quickly get migration readiness and cost savings by migrating to Azure, we are introducing “Import SQL Server Discovery data as CSV” feature in Azure Migrate that allows users to import the SQL Server inventory as a CSV file and use the discovery data to generate assessments and business cases. This feature is launched in private preview. In this blog post, we will do a deep dive on how to use this feature to generate quick assessments for your off-Azure SQL Server estate. Here are the high-level steps for importing your off-Azure SQL Server inventory as a CSV: 1) Step-1: Identify the SQL Server Instances and extract the discovery data into a CSV file. 2) Step-2: Validate and upload the inventory data to Azure Migrate 3) Step-3: Generate and view Assessments Pre-requisites: Import SQL Discovery data as a CSV feature is built on top of the new Azure Migrate experience which is also in Private Preview phase. To use Import SQL Discovery data as CSV feature, you must onboard to the private preview of new Azure Migrate experience and here is the form to sign up for Private preview: Azure Migrate Private Preview Sign-Up Survey. We will share the instructions to onboard via the contact details you provided. Make sure you have the credentials to connect to the SQL Server Instances of interest Make sure you have the requisite roles and privileges on each SQL Server Instance to access and extract the required metadata from Dynamic Management Views. To generate a login with required privileges, please run the scripts listed here: Configure custom login for SQL Server Discovery An Azure Subscription. Please note you don't need to deploy an Azure Migrate appliance to use the SQL import CSV feature. Step-1: Identify and extract the discovery data of SQL Server Instances you intend to migrate to Azure SQL into a CSV file Identify the list of all off-Azure SQL Server Instances you intend to migrate to Azure SQL from either on-premises infrastructure or other cloud platforms like Amazon Web Services (AWS), Google Cloud Platform (GCP). To extract the discovery data, please run the SQL Script below against the SQL Server Instance which you would like to import into Azure Migrate via CSV and store the output into a CSV file. /* PLEASE ENTER THE FULLY QUALIFIED DOMAIN NAME OF THE HOST IN THE LINE BELOW BEFORE RUNNING THE QUERY */ Declare @FullyQualifiedDomainName varchar(200) = ''; With ServerCores AS( SELECT cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio], cpu_count/hyperthread_ratio AS [Physical CPU Count], sqlserver_start_time FROM sys.dm_os_sys_info ), Config AS( SELECT value_in_use AS [Max Server Memory(MB) In Use] FROM sys.configurations WHERE name = 'max server memory (MB)'), UserCount AS( SELECT count(database_id) AS [Number User Databases] FROM sys.databases WHERE database_id > 4 ), DbSize AS( SELECT SUM(CAST(Size AS bigint))*8/1024 AS [Max server memory (in MB)] FROM sys.master_files WHERE database_id > 4 ), TempDbSize AS ( SELECT SUM(CAST(Size AS bigint))*8/1024 AS [TempDb Size (In MB)] FROM sys.master_files WHERE db_name(database_id) = 'tempdb' ), Logins AS (Select count(name) AS [NumOfLogins] from sys.syslogins), Properties AS ( select db.name as database_name, db.database_id as database_id, cast(db.compatibility_level as int) as database_compatibility_level, db.collation_name as database_collation, db.is_broker_enabled as is_service_broker_enabled, '0' as [Number of Nics], SERVERPROPERTY ('servername') as instance_name, SERVERPROPERTY ('productversion') as instance_version, SERVERPROPERTY ('edition') as instance_edition, SERVERPROPERTY ('productlevel') as instance_level, SERVERPROPERTY ('IsHadrEnabled') as is_hadr_enabled, SERVERPROPERTY ('collation') as server_collation, CASE WHEN SERVERPROPERTY ('IsClustered') = 1 THEN 'TRUE' ELSE 'FALSE' END as is_failover_clustered, COALESCE(SERVERPROPERTY ('IsIntegratedSecurityOnly'), 0) as is_integrated_security_only, COALESCE(SERVERPROPERTY ('IsPolyBaseInstalled'), 0) as is_polybase_installed, CASE WHEN 'A' = 'a' THEN 0 ELSE 1 END as is_server_case_sensitive, /* '' + @host_platform + '' as host_platform,*/ db.state as database_state, /* db.is_auto_close_on as is_auto_close_on,'+ CASE when @check_cdc = 1 THEN 'db.is_cdc_enabled' else '0' END + ' as is_cdc_enabled,*/ CASE when db.user_access = 0 THEN 1 ELSE 0 END as is_multi_user, CASE when db.recovery_model = 0 THEN 0 ELSE 1 END as is_full_recovery_model, db.is_read_only as is_read_only, case when source_database_id IS NULL THEN 0 ELSE 1 end as is_snapshot, case when is_published = 1 OR is_subscribed = 1 OR is_merge_published = 1 or is_distributor = 1 THEN 1 ELSE 0 end as is_replication_enabled, db.is_encrypted from sys.databases db where db.name not in ('master', 'tempdb', 'model', 'msdb') and is_distributor <> 1), EachDbSize AS( SELECT database_name = DB_NAME(database_id), CAST(SUM(CAST(size AS bigint)) * 8. / 1024 AS DECIMAL(12,2)) AS [Database size (in MB)], convert(varchar(12),CAST((sum(case when physical_name like '%ldf'then 0 else size end)*8.0)/1024.0 AS numeric(8,2))) AS [Data file size (in MB)], convert(varchar(12),CAST((sum(case when physical_name like '%ldf'then size else 0 end)*8.0)/1024.0 AS numeric(8,2))) AS [Log file size (in MB)] FROM sys.master_files WHERE database_id > 4 GROUP BY database_id) select @FullyQualifiedDomainName as [Fully qualified domain name], instance_name as [Instance Name], p.database_name as [Database Name], database_state as [Database Status], instance_edition [SQL Edition], instance_version as [SQL Version], [Logical CPU Count], [Hyperthread Ratio], [Physical CPU Count], [TempDb size (in MB)], is_failover_clustered as [Is FCI Enabled], '' as [Failover cluster name], '' as [Failover cluster instance role], '' as [Failover cluster shared disk count], '' as [Is failover cluster multi subnet], [Max server memory (in MB)], [Number of Nics], instance_level as [Service pack], '' as [ESU status], 'FALSE' as [IS AG enabled], instance_level as [Build version], 'Online' as [Service status], 'FALSE' as [Is database highly available], 'FALSE' as [Is part of distributed availability group], [Database size (in MB)], [Data file size (in MB)], [Log file size (in MB)], database_compatibility_level as [Compatibility level], '' as [Availability group ID], '' as [Availability group name], '' as [Availability group type], '' as [Availability replica ID], '' as [Availability replica name], '' as [Commit mode], '' as [Replica type], '' as [Replica state], 'FALSE' as [Is AG multi subnet], '' as [AG replica sync status], '' as [AG replica seed mode], '' as [AG replica read mode] from ServerCores, Config, UserCount, DbSize, TempDbSize,Logins, Properties p LEFT JOIN EachDbSize s on p.database_name = s.database_name order by p.database_name Collate the output of all SQL Server Instances of interest into a single file. Step-2: Validate and upload the CSV file To validate and upload the generated CSV file with SQL Server discovery data: 1) Go to the Azure Migrate experience link you would have received from Microsoft after you signed up for the private preview of this feature. Click on “Create Project” to create a new Project (if not done already): 2) Fill in the required fields in create project wizard 3) Click on the created project and select “Using custom Import” option under “Start Discovery” button. 4) In the Discover page, select “File Type” as “SQL Server inventory (CSV)” 5) (Optional) Click on “Download” button under Step 1: Download template to view and understand the template expected by Azure Migrate. Optionally, you can skip this step as the CSV file generated by SQL script shared above confirms to the template. 6) Browse and select the inventory file in Step-2 and click on Validate to validate the uploaded SQL Inventory data. 7) If the uploaded doesn’t contain any warnings or error messages, it will show validation completed successfully with 0 errors and warnings. If there are any errors or warnings Azure Migrate will show a count of warning and error messages. To view and resolve the warnings or errors (if any) with the imported file, click on Download ErroDetails.csv, which displays errors/warning messages corresponding to each database along with remedial action. Please note warnings are non-blocking messages, i.e., user can still go ahead and complete the Import operation. Whereas errors are blocking in nature i.e. users will not be allowed to import unless the error messages are resolved. 8) Once the validation phase is completed, click on Import button to complete the import operation. Step-3: Create Assessments using imported SQL Discovery Data SQL Server Instances and the databases uploaded via CSV import route are treated at par with the SQL Server instances that are discovered by Azure Migrate appliance. You can view the SQL Server Instance properties like Version details, Number of databases, Support status and all other properties uploaded via CSV file. The quality of property details and the assessments generated is directly dependent on the quality of data uploaded via CSV. To create SQL assessments for the uploaded SQL Server Instances, go to the Azure Migrate project's resource page and click on Create Assessments button: Give a name for the assessment and click on Add workloads to add the imported and appliance discovered workloads: In the following page, select the SQL Server instances that have value “Import” for Discovery Source column. You can also create assessments on a combination of workloads that are imported and appliance discovered. Click Add button to add the workloads and click on Review + Create Assessments to create assessment. For imported SQL Server instances, the quality of assessments is only as good as the data uploaded via CSV, please ensure that the data uploaded via CSV accurately depicts the metadata of the SQL Server instance. Like regular SQL assessments, you will have all the assessment configuration options like choosing Sizing criteria (performance-based vs as on-premises), migration strategy (optimizing cost vs migration to PaaS), comfort factor etc. By default, Azure Migrate will try to create a performance-based incase the performance-metrics are not available, it defaults to As-on-premises assessment. Assessment created on the imported SQL Servers will help you know which SQL Server Instances are ready for migration to Azure SQL, the recommended Azure SQL Target type. Linking and Rediscovery Linking If the host where the imported SQL Instance currently hosted is discovered via import of VM Servers or via appliance, the SQL Server Instance will be automatically associated with that host. SQL Server Instance will be rolled up under that VM Server in the discovery view. The SQL Server instance will be uniquely identified based on the FQDN and the SQL Server Instance name. The combination of attributes is used for conflict resolution as well. Rediscovery and precedence rules 1) If the SQL Server instance is originally imported via CSV and is reuploaded again via CSV, upload operation will rewrite all the properties. 2) If the SQL server instance is first imported via CSV and then discovered via Azure Migrate appliance and the user has provided credentials to do deep discovery via Azure Migrate appliance, then the appliance discovered data will override the data imported via CSV. 3) If the SQL server instance is first imported via CSV and then discovered via Azure Migrate appliance and the user has not provided SQL Server credentials to do deep discovery, then the appliance discovered data will only update the additional data that appliance discovered. 4) If the SQL Server Instance is first discovered via the Azure Migrate appliance and is deep discovered (correct SQL Server credentials are provided), and if the SQL Server is imported via CSV, data imported via CSV will be discarded. 5) If the SQL Server Instance is first discovered via the Azure Migrate appliance and is only inventoried (correct SQL Server credentials are not provided), and if the SQL Server is later imported via CSV, upload operation will completely overwrite the appliance discovered details. How to onboard to Private Preview To onboard Preview of SQL Inventory import as CSV, please fill the Signup form for Private preview What’s Next 1) In the next release will ship out capabilities to create Business case for SQL Servers imported via CSV. 2) We will also build support for Availability Groups and Failover cluster Instances for the SQL Server instances imported via CSV.Release: Azure SQL Migration extension for Azure Data Studio v1.5.6
We're delighted to announce the release of the latest version of the Azure SQL Migration extension for Azure Data Studio, v1.5.6. This release provides you with Azure Database Migration Service’s new features like: 1) Support for Next-gen General Purpose service tier for Azure SQL Managed Instance. 2) Target Provisioning based upon SKU recommendation (using ARM templates) - Public Preview. 3) Enhanced login migration experience - Public Preview. What is new in Azure SQL Migration extension v1.5.6? 1) Support for Next-gen General Purpose service tier for Azure SQL Managed Instance: The Next-gen General Purpose service tier is an architectural upgrade to the existing General Purpose service tier that can be used for new and existing instances. Now, the Azure Data Studio extension for Azure Database migration service – Azure SQL Migration support Next-gen General Purpose as SKU recommendation for Azure SQL Managed Instance. For details, refer here. This service tier provides better performance, throughput, greater storage capacity and support more than 100 databases on a single instance. 2) Target Provisioning based upon SKU recommendation (using ARM templates) - Public Preview: With Azure SQL Migration v1.5.6, now users can generate ARM templates directly based upon the SKU recommendation generated using performance data collected from the source. User can use these ARM templates for all the Azure SQL offerings – Azure SQL VM, Azure SQL MI and Azure SQL DB and easily create the Azure SQL Target for the migrations. To create and deploy the Azure SQL Target, users have two options: a) Copy or save the ARM template in JSON and use Azure CLI, PowerShell and other deployment operations. b) Using Deploy-to-Azure button, then provide the Azure blob storage account details to store the template and deploying it though the Azure Portal. This feature is in Public Preview and will help you to streamline the Azure SQL target creation using ARM templates, can automate deployments and use the practice of infrastructure as code, deploy them quickly and CI/CD integration. 3) Enhanced login migration experience - Public Preview: After completing the data migration, the next critical step is to setup the authentication and authorization for the databases and thus login migration becomes the critical step in the migration journey. Azure SQL Migration extension supports Login migration (Public Preview) and now we have enhanced its experience by adding Pre-requisites validation checks to ensure all the requirements are in place for successful login migrations. Currently, only Azure SQL Managed Instance and SQL Server on Azure Virtual Machines targets are supported. Resources For more information about the extension and Azure Database Migration Service, see the following resources. Azure Database Migration Service documentation Migrate databases using the Azure SQL Migration extension One-click SQL Migration PoC environment Architecture of Azure Database Migration Service | Microsoft Community Hub
