Exporting on-prem SQL Server to Azure SQL using BAPAC fails because of users with Windows Auth
I have an on-prem SQL Server 2014 database - the schema itself is fine and fully Azure SQL compatible.
However, it has a USER
object that is bound to a Server LOGIN
which is configured to use Windows Authentication (so passwords aren't stored in connection-strings).
I want to Export this database to Azure SQL, however BACPAC generation fails because of the USER
object:
One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71564: The element User:[applicationuser]
has propertyAuthenticationType
set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element Login:[SERVERNAME\LocalUserName]
has propertyIsMappedToWindowsLogin
set to a value that is not supported in Microsoft Azure SQL Database v12.
SQL Server Management Studio 2014 does not let me exclude or ignore USER
and LOGIN
objects when creating BACPAC files.
SQL Server Data Tools does let me exclude those objects, but SSDT cannot (to my knowledge) perform BACPAC generation, which is my objective.
I first tried to delete the users which didn't work. Then I found this MS tool which worked perfectly. I was able to migrate both the schema and the data with it without any issues.
MS Data Migration Assistant (DMA)