Import-D365Bacpac: The login already has an account under a different user name

[FH-Inway]

          Hello. I also noticed this issue with the latest d365fo.tools and SQLpackage version.

Doesn't matter if I use the -ImportOnly parameter or not. Also Invoke-D365InstallSqlPackage was run before this command.

The interesting thing is that if I run via CMD SqlPackage.exe /a:import /sf:"J:\MSSQL_BACKUP\AxDBFOClean31012023.bacpac" /tsn:localhost /tdn:ImportedDatabase /p:CommandTimeout=1200 /TargetTrustServerCertificate:True

then the DB gets imported without any issues.

Could it be that due to a recent change where /TargetTrustServerCertificate:True needs to be added to the command, then this also needs to be incorporated into d365fo.tools Import-D365Bacpac ?

Originally posted by @kalejjanis in https://github.com/d365collaborative/d365fo.tools/issues/708#issuecomment-1430024582

[FH-Inway]

@kalejjanis Thanks for running the commands and providing the detailed output. As you noticed the issue is caused because Import-D365Bacpac calls SQLPackage.exe with the /TargetUser and /TargetPasswort parameters. Since this is a different issue than the one discussed in #708 I created this new issue so we can continue the discussion regarding your issue here.

To make it easier for others looking at this, here is again the screenshot you provided in the other issue where you ran Import-D365Bacpac with -OutputCommandOnly as well as -Verbose and -ShowOriginalProgress.

[FH-Inway]

@kalejjanis In #708 you also asked

Is there away to "remove" or disable those switches from Import-D365Bacpac? Or are there better ways?

There is currently no way to remove or disable the /TargetUser and /TargetPassword parameters that Import-D365Bacpac uses when calling SqlPackage.exe.

You can however provide your own values for those parameters by using the -SqlUser and -SqlPwd parameters of the Import-D365Bacpac cmdlet.

It is uncommon that those parameters are needed since the cmdlet is usually smart enough to figure out those values. From the output, it seems like there are multiple databases in play here? Could you share more about your setup?

[kalejjanis]

Hello I’ve tried all kinds of scenarios, but with no success. Below you can see the commands that were used and the respective results. Tried using different -SqlUser/Pwd combos from the target environment ,but also no success. Maybe I missed that, but it’s a bit unclear what SQL user and password to use there.. axdbadmin or others, but from what I’ve tried the results didn’t change for the better. Brief description of what I’m actually trying to achieve. • The long term goal is to create a solution that could grab a database export from LCS under Asset library > Database backup and restore that on a Tier1 dev machine. As I understood grabbing a fresh DB from Tier2 environment directly and restore to Tier1 is not possible. • The current goal is to restore this database which is already downloaded on the target VM and originates from another Tier1 environment. So, two Tier 1 environments. Additionally, I try to use d365fo.tools to restore it and switch the DBs. So basically, I want to understand how d365fo.tools work and if they can get the job done as it would be easier to use them and automate the whole process via DevOps pipelines for example. Using -ImportOnly

Standard command:

[Splaxi]

What you want to achieve is quiet possible, as I have personally been doing that for several years - fully automated.

What strikes me is that you are the first to have issues, after we did the last update to the import cmdlet.

From what, specific date, is your bacpac file generated inside LCS? Did you run the Invoke-D365InstallSqlPackage prior? This should obtain the latest release, that we know works and have been working for some time.

I know that MS did a new build of the SqlPackage, so it is now based on .Net 6.0 - which is a major change from the .Net Core we have been using for several years.

Could you share:

• VM D365FO application version (10.0.XX)
• Freshly deployd / updated for several years ?
• LCS Deployed vs Onebox (VHD)

[FH-Inway]

Thanks for the help @Splaxi . @kalejjanis mentioned in the other issues that they ran Invoke-D365InstallSqlPackage before Import-D365Bacpac > https://github.com/d365collaborative/d365fo.tools/issues/708#issuecomment-1430024582

[FH-Inway]

@kalejjanis

The current goal is to restore this database which is already downloaded on the target VM and originates from another Tier1 environment. So, two Tier 1 environments.

Just to clarify, you are exporting a .bacpac from one T1 environment (let's call it origin), copy it over to another T1 environment (target) and there you run the commands?

In addition to @Splaxi 's questions, could you share more about how the .bacpac was exported?

• Are you using SqlPackage.exe to export the .bacpac from the origin environment?
  • If so, what is the version of SqlPackage.exe on both origin and target?
  • What command is used for the export?
  • If not, how is the .bacpac exported?
• Was Prepare the database done before the export?

[Splaxi]

If you are doing Tier1 -> Tier1

• skip bacpac and go directly to classic sql backup/restore

[kalejjanis]

Hello Sqlpackage.exe is up to date on both environments. Made sure to run the update command. And yes, I’m using Sqlpackage.exe to export the DB from the Source Tier 1 environment as you can see below. Prepare the database was not run before the export. Both environments are on version: 10.0.31 (10.0.1406.77) Source environment deployed via LCS: 9/27/2022 Target environment for testing deployed via LCS: 2/17/2023

Here are the exact steps: Installed d365fo.tools on Source Tier 1 environment Installed SQLpackage via: Invoke-D365InstallSqlPackage -Path "C:\SqlPackage" Exported Source DB with: C:\sqlpackage\SqlPackage.exe /a:export /ssn:localhost /sdn:AxDB /tf:J:\MSSQL_BACKUP\AxDBclean20022023.bacpac /p:CommandTimeout=1200 /p:VerifyFullTextDocumentTypesSupported=false /SourceEncryptConnection:False

Upload the .bacpac to LCS Download the .bacpac from LCS on target Tier 1 environment Installed d365fo.tools on Target Tier 1 environment Installed SQLpackage via: Invoke-D365InstallSqlPackage -Path "C:\SqlPackage"

Import Source DB on Target Tier1 VM: Import-D365Bacpac -ImportModeTier1 -BacpacFile "I:\MSSQL_BACKUP\AxDBclean20022023.bacpac" -NewDatabaseName "ImportedDatabase" -Verbose -ShowOriginalProgress

Strange.. everything is fresh and I even did a fresh export of the DB Today where sqlpackage.exe is up to date.

[FH-Inway]

@kalejjanis Could you try Prepare the database ? The SQL script drops the axdbadmin user that is causing issues during the import.

@Splaxi

If you are doing Tier1 -> Tier1

• skip bacpac and go directly to classic sql backup/restore

I think the idea is that the bacpac export from Tier1 is supposed to simulate the later bacpac export from Tier2. @kalejjanis mentioned that this is the actual goal they want to achieve:

The long term goal is to create a solution that could grab a database export from LCS under Asset library > Database backup and restore that on a Tier1 dev machine.

[kalejjanis]

1st of all. Thank you both for the great support. I finally managed to import the database using Import-D365Bacpac. Guess I should've followed the best practices all along... After exporting the DB and restoring it on the same VM and preparing the DB and then export it that one, I managed to import it on the target Tier1 environment. without issues:

I suppose that I should follow the instructions in Yammer and everything should work. But I'll start with getting Get-D365LcsDatabaseBackups to work as that was the other major thing that gave me a headache.

Thanks again for the help.

[FH-Inway]

Great to hear you got it working and thanks for the feedback. Let us know if you need further help in setting this up.