Error Deploying Bacpac on Azure SQL Database when the database has Azure AD Users

Question

I'm trying to deploy a .bacpac to my Azure Subscription, the database has some Azure AD users. I can generate the .bacpac with SSMS, but when I deploy the replicated database on Azure, I'm getting this error:

Could not import package.

Error SQL72014: .Net SqlClient Data Provider:

Msg 15419, Level 16, State 1, Line 1
Supplied parameter sid should be binary(16).

Error SQL72045: Script execution error. The executed script:
CREATE USER [ADUser]
WITH SID = <ID>, TYPE = E;

(Microsoft.SqlServer.Dac)

Question: how can I deploy the .bacpac if my database has users from Azure AD?

I did some test to avoid this error and I can deploy a .bacpac file with SQL Server users, the problem only appears when the database has Azure users.

Answer 1

Agreed with @AlwaysLearning , as per this MS Document

Creating user with SID is only applies to users with passwords (SQL Server authentication) in a contained database.

also, you can see in below error we cand use SID with the external provider it is only applicable to SQL user with password.

you can use below command to create external user in your database.

CREATE USER [[email protected]] FROM EXTERNAL PROVIDER;

And then try to deploy the database in azure SQL.

Answer 2

Agreed with @pratik-lad and @AlwaysLearning, add the user login and then you can deploy .bacpac file, but in my case I don't want the Azure Users in the new database so I made a script using SQL Package for this.

First extract .dacpac file with the flag /p:ExtractAllTableData=True

sqlpackage /Action:extract /p:IgnorePermissions=true /SourceConnectionString:"Data Source=Connection String" /TargetFile:"target.dacpac" /p:ExtractAllTableData=True

Then you can publish the database with this flag ExcludeObjectTypes="Users;Logins;RoleMembership;Permissions;DatabaseRoles"

sqlpackage /a:publish /tcs:"Data Source=Connection String" /sf:"./target.dacpac" /p:DatabaseEdition=Basic /p:DropObjectsNotInSource=True /p:ExcludeObjectTypes="Users;Logins;RoleMembership;Permissions;DatabaseRoles"