sql - 无法升级/导入 dacpac 文件

Question

原始问题:

尝试使用 .dacpac 文件升级在测试 VM 中创建的空白数据库，但收到以下错误消息:

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)

假设用户存在于源中，但不存在于目标中。在 VM 上创建该用户是否会解决此问题，或者我是否需要使用不同的方法从在 VM 目标中重新创建的源中获取架构数据以进行测试？

---

问题 1 的更新:

.dacpac 文件是在位于完全不同域的服务器上生成的，测试虚拟机不可能位于同一域。考虑到这一点，如何让 .dacpac 文件在测试 VM 上运行？

Answer 1

如果您仍然可以访问 VM，这次您可以再次生成 .dacpac 并忽略登录。根据您使用的工具，您应该可以访问“包括用户登录映射”等选项。

最强大的有 VS:"How to create DACPAC file?" by Kamil Nowinski :

^{图片来源:https://sqlplayer.net/wp-content/uploads/2018/10/visual-studio-extract-dacpac-options.png}

之后您可以使用自己的 SQL 脚本重新创建正确的登录名和用户。

---

相关: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.

---

另一种选择是使用 dbtools.io - Export-DbaDacPackage

这里的重点是:

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

和 publish.xml:

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

---

总结:

• 创建一个无需登录的 dacpac
• 创建一个将忽略权限的 publish.xml 文件