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:
- '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?
- How to Rename a Azure SQL Server
- Provision new SQL Azure database into existing, non-terraform managed SQL Server instance
- "We're sorry, your sql database is unavailable" What does this mean exactly in Azure SQL?
- Bacpac vs dacpac for migrating database to Azure
- Azure Logic Apps: Set condition to False when SQL query returns no rows of data
- Sink not overwriting table with specified data types
- Azure SQL database backup?
- Azure .NET SDK: Scale Azure SQL databases through SDK
- EF6.5 SetExecutionStrategy vs SetTransactionHandler
- Creating External table in Azure SQL Database using ADLS2 Datasource
- Search a column in database
- How to reduce the no. of columns on join when accessing a navigation property?
- sqlcmd fails with Incorrect syntax near ';' after upgrading to .NET 8
- ODBC Driver 18 Not Found in Azure Function Using Python 3.11
- SQL Post Deployment SQL CMD Variables and YAML Task
- 'dbo' user should not be used for normal service operation
- Logic App to call Azure SQL stored procedure passing datetime param