Exporting on-prem SQL Server to Azure SQL using BAPAC fails because of users with Windows Auth

Question

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 property AuthenticationType set to a value that is not supported in Microsoft Azure SQL Database v12.
Error SQL71564: The element Login: [SERVERNAME\LocalUserName] has property IsMappedToWindowsLogin 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.

Solution

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)

Comments

[Peter]

Can you go to Security > Logins and delete the username or user group then try again? Once exported to Azure SQL, create a new login from the Azure portal.

[Dai]

@Peter That would work, but would mean having to stop the production application while the BACPAC generation runs (because the app wouldn't be able to login).

[Peter]

How about changing the database to mixed mode authentication? Is it possible for the application to use a connection string with a SQL login account instead of SSPI / Windows Authentication? Since this is Azure SQL database, I don't think there should be any reference to the on-prem SQL server.

[Jerad Rose]

This is an insanely frustrating issue. I can't believe they don't include an option to just ignore the damn windows authentication users. They're all IIS APPPOOL users!