Question 169754 in Azure Question posted in
The official documentation to get you started can be found here.

Can Azure AD Users cause an error when deploying a Bacpac on an Azure SQL Database?

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.exe /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.exe /a:publish /tcs:"Data Source=Connection String" /sf:"./target.dacpac" /p:DatabaseEdition=Basic /p:DropObjectsNotInSource=True /p:ExcludeObjectTypes="Users;Logins;RoleMembership;Permissions;DatabaseRoles"

  2. 0

    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 [[email protected]] FROM EXTERNAL PROVIDER;

    And then try to deploy the database in azure SQL.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search