Unable to upgrade/import a dacpac file

Question

ORIGINAL QUESTION:

Trying to upgrade a blank database created in a test VM using a .dacpac file, but get the following error message:

Error SQL72014: .Net SqlClient Data Provider: Msg 15401, Level 16, State 1, Line 1 Windows NT user or group 'SOURCE_DOMAIN\SOURCE SQL Readers' not found. Check the name again.
Error SQL72045: Script execution error.  The executed script:
CREATE LOGIN [SOURCE_DOMAIN\SOURCE SQL Readers]
    FROM WINDOWS WITH DEFAULT_LANGUAGE = [us_english];


 (Microsoft.SqlServer.Dac)

------------------------------
Program Location:

   at Microsoft.SqlServer.Dac.DeployOperation.ThrowIfErrorManagerHasErrors()
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<>c__DisplayClass16.<CreatePlanExecutionOperation>b__13()
   at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
   at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass14.<CreatePlanExecutionOperation>b__12(Object operation, CancellationToken token)
   at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
   at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
   at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext, Action`3 reportPlanOperation, Boolean executePlan)
   at Microsoft.SqlServer.Dac.DacServices.Deploy(DacPackage package, String targetDatabaseName, Boolean upgradeExisting, DacDeployOptions options, Nullable`1 cancellationToken)
   at Microsoft.SqlServer.Management.Dac.DacWizard.UpgradeModel.RunAction()
   at Microsoft.SqlServer.Management.Dac.DacWizard.ExecuteDacPage.backgroundWorker1_DoWork(Object sender, DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

Assuming that user existed in the source, but not in the destination. Will creating that user on the VM fix this issue or will I need to use a different approach to get the schema data from the source re-created in a VM destination for testing purposes?

UPDATE TO QUESTION 1:

The .dacpac file is generated on a server which is on a totally different domain and it will not be possible for the test VM to ever be on the same domain. With that in mind, how do I get the .dacpac file to work on the test VM?

There is no need to create the domain user on the test VM since SQL Server will trust Windows for authentication. The error suggests the test VM is not joined to `SOURCE_DOMAIN` so SQL Server cannot find the Windows account (group). — Dan Guzman, Apr 20 '20 at 11:31
That is correct, there is no connection whatsoever between the test VM and the domain from which the `.dacpac` file was generated on. There is no chance of connecting the test VM with the sources domain as is an external company. With that in mind, how I do use this `.dacpac` file on my test VM? — oshirowanen, Apr 21 '20 at 07:53

Lukasz Szozda · Answer 1 · 2020-04-25T10:17:52.780

If you still have access to VM, you could generate .dacpac again this time ignoring the logins. Depending which tool you use you should have access to option like "Include User Login Mapping".

The most roboust one has the VS: "How to create DACPAC file?" by Kamil Nowinski:

^{Image source: https://sqlplayer.net/wp-content/uploads/2018/10/visual-studio-extract-dacpac-options.png}

You could recreate the proper logins and users afterwards with own SQL script.

Related: Using Publish Profiles to Deploy a DACPAC Database Without User Accounts

The solution to this problem lies in defining an appropriate publish profile for your DACPAC, which then instructs your chosen deployment tool – SQLPackage.exe, Visual Studio, or Azure DevOps – on how to carry out the deployment

The profile is defined as an XML file.
ExcludeUsers
ExcludeLogins
ExcludeDatabaseRoles
By setting these options to True within our publish profile, creation or modification of these objects will be skipped entirely during any database deployment.

One more option is to use dbtools.io - Export-DbaDacPackage

Key point here is:

$exportProperties = "/p:IgnorePermissions=True /p:IgnoreUserLoginMappings=True" # Ignore

and publish.xml:

...
<ExcludeLogins>True</ExcludeLogins>
<IgnorePermissions>True</IgnorePermissions>
<IgnoreLoginSids>True</IgnoreLoginSids>
<IgnoreRoleMembership>True</IgnoreRoleMembership>

Summary:

create a dacpac without login
create a publish.xml file that will ignore permissions

score 0 · Answer 2 · answered Apr 20 '20 at 08:19

0

Creating the user inside the VM is one way to solve this issue, but you will need to change 'SOURCE_DOMAIN' to the VM hostname, as the user will be part of the local user database.

Probably the best solution is to fix VM communication to the Domain Controller, so authentication will work and user accounts end up being actually visible within the VM.

answered Apr 20 '20 at 08:19

Alan Rezende

98
6

As the test VM is not on the same domain as the server from which the `.dacpac` file was generated on, it will not be possible to connect the test VM to that domain controller. As it is not possible to connect the test VM to the domain from which the `.dacpac` file was generated on, how do I go about using this `.dacpac` file on the test VM? – oshirowanen Apr 21 '20 at 07:57

score 0 · Answer 3 · answered Apr 26 '20 at 14:03

0

Take a look at this, This error usually occurs because of COMPATIBILITY_LEVEL I would recommend trying this quarry out:

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL =  130;

Hope it helps!

answered Apr 26 '20 at 14:03

zyngot

275
1
4
19

score 0 · Answer 4 · answered Apr 21 '21 at 09:47

If a dacpac contains users or groups that aren’t on the domain where the dacpac is being deployed, then one way to deploy it is using the SqlPackage command line tool, as this allows you to explicitly list the object types you want to exclude.

To exclude users and groups, the PowerShell command would be something like this:

.\SqlPackage.exe `
  /a:Publish `
  /tsn:"(localdb)\mssqllocaldb" `
  /tdn:YourDatabaseName `
  /p:ExcludeObjectTypes="Users;RoleMembership;Logins;ServerRoles;ServerRoleMembership;Permissions" `
  /sf:YourFile.dacpac

This command uses the following switches:

/a (Action): the action to run, in this case Publish
/tsn (TargetServerName): the name of the server to deploy to
/tdn (TargetDatabaseName): the name of the database to deploy to
/p (Properties): name value pair of action-specific properties, in this case:
- ExcludeObjectTypes: a semicolon-delimited list of object types that should be ignored
/sf (SourceFile): the dacpac file to deploy

More details of the syntax for Publish (including a list of the object types that can be exlcuded) are available in the docs for the publish action.

Unable to upgrade/import a dacpac file

4 Answers4