Unable to 'Deploy Database to Microsoft Azure SQL Database' from SSMS 20.2
Hi have attempted to deploy our on-premise SQL Database to an Azure SQLDB using SQL Server Management Studion 20.2
However, I getting the following errors:
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.
Can someone let me know how to get around these issues? Do you think it's because of the version of the SSMS 20.2?
The element User: [xxx\SSO UAT Administrators] has property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71627: The element Login: [xxx\SSO UAT Administrators] has property IsMappedToWindowsLogin set to a value that is not supported in Microsoft Azure SQL Database v12.
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. 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:
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:
- Use User Managed Identity (UMI) to authenticate Azure App Service (Python) to SQL Database
- Azure ArmClient Rename & Copy DB Operations
- Connecting to an Azure SQL database with a VPN (Point-to-Site)
- Poweshell to set Azure SQL Auditing
- SQL Server Error "AADSTS500133: Assertion is not within its valid time range" for Managed Identity
- How to set Azure SQL database instance with Min 0,5 vCores with Terraform?
- Anonymize data in dataflow pipeline gen2
- How to grant a Managed Identity permissions to an Azure SQL Database using IaC?
- Uses SQL connection strings from Azure Keyvault in Azure functions
- Add Azure Active Directory User to Azure SQL Database
- How do I connect locally to Azure SQL database with Microsoft Entra ID registered app in C#?
- Secure access to Azure storage account from only app service, database server and specific Internet address
- Why given code is generating 'The request limit for the database is X' with Azure SQL
- ADF - Column cannot participate in columnstore index
- Unable to link Azure SQL Database to Static Web App
- Azure Data Studio - Why Is Restore Option Missing Entirely?
- Login failed for user '<token-identified principal>' Error 18456 On Deploy Database
- 'Table data cannot be migrated, source table is empty' in Azure SQL migration wizard
- Design in Query Editor disabled for Azure databases
- Merge rows in a specific sequence in SQL (Azure SQL)
- How create external table in Azure SQL to access a table in local SQL Server on same subnet. Getting error on select
- The SELECT permission was denied on the object 'Users', database 'XXX', schema 'dbo'
- Unable to 'Deploy Database to Microsoft Azure SQL Database' from SSMS 20.2
- Azure blob-to-SQL automatic copy
- How to connect a Web App to a SQL database on Azure using Managed Identities?
- Intermittent exception against SQL Azure: Unknown error 258
- Difference between Azure database for MySQL and Azure SQL database
- I'm using SSIS in ADF to move .csv from a blob container to another and then ingest into a Azure SQL database. I get an assembly error
- Is there a way to ignore the "STATISTICS_NORECOMPUTE" clause when doing a schema compare in Visual Studio?
- How to use a bicep template to deploy a SQL Server with auditing to log analytics enabled?