sql-serverazureazure-sql-databasessmsbacpac

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


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.


Solution

  • 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.

    enter image description here

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

    CREATE USER [username@domain.com] FROM EXTERNAL PROVIDER;
    

    And then try to deploy the database in azure SQL.