search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.3k stars 21.48k forks source link

Unable to 'Deploy Database to Microsoft Azure SQL Database' from SSMS 20.2 #124880

Open AmareswarapuBhavani opened 3 days ago

AmareswarapuBhavani commented 3 days ago

Document Enhancement Proposal: Unable to 'Deploy Database to Microsoft Azure SQL Database' from SSMS 20.2.

Overview:

Getting below error while deploying on-premises SQL database to Azure SQL database:

Error SQL71627: The element User: [topbs\pina] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element User: [LAP0134-NT\serveradmin] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element User: [CAPWINDS\mY_Team] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element User: [NT AUTHORITY\SYSTEM] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.

Correction Details:

This guide will allow users to:

Handle the above error and will help to get the activity error details.

Steps:

Reason for the above error:

Azure SQL database doesn't support Windows authentication. It supports only SQL Server authentication and Microsoft Enterprise ID authentication. However, the above user is mapped to Windows authentication, which may be the reason for the error.

Steps to avoid this error:

If you want to deploy the on-premises SQL database to Azure SQL Database, delete the users which are mapped to Windows authentication as shown below:

enter image description here

Then try deploying again. After deployment, create contained users in your database to allow users to log in to your SQL Database.

Alternatively, you can use Azure Data Migration Assistant (DMA) for your migration project, which will make the process much easier. DMA assesses your database and allows you to migrate effortlessly. You can download the tool from here. With DMA, you can uncheck any logins (SQL71627) or views not compatible with Azure SQL (SQL71561) and proceed with the migration.

For more information, you can refer to the links below:

https://learn.microsoft.com/en-us/answers/questions/689900/error-while-migrating-on-prem-sql-db-to-azure-sql Can't Export Data-tier Application for Azure

Conclusion:

By following this guide, users can avoid the above error.

Reference:

Unable to 'Deploy Database to Microsoft Azure SQL Database' from SSMS 20.2

This Document creation request aims to improve user experience and security by providing clear documentation.

TPavanBalaji commented 3 days ago

@AmareswarapuBhavani Thanks for your feedback! We will investigate and update as appropriate.