Import bacpac file on self-service environment

[VincentVerweij]

Hi there,

A customer is having a self service environment where I want to import a bacpac file to. Now when looking around in this repo I found the following function that might work for me:

• import-d365bacpac.ps1

So I read the documentation which makes me believe that I should work with the ImportTier2 flag. But this flag requires me to pass on a user and password, which makes me think this is not suitable for a self service environment?

In this issue something was mentioned about the SPARTAN database that is used, which my environment uses too (I can see this when I request access via LCS). So the big question remains, is there a way to import a bacpac into such a self-service environment?

[Splaxi]

Hi @VincentVerweij

So you hit a soft spot and you did your searching prior asking the question 😉

The real issue for you is that if you want to be able to import a bacpac file into a Tier2 environment, you need to provide the SQL Server connection details. For Self-Service environments, you need to enable JIT access for the database, which in respons provides you with the username + password for the database behind the Self-Service.

Let's say that you already have the details at hand, taken from the LCS page, after you requested JIT access and did enable Azure SQL DB access with an IP address also, then it should work. We never did test it for any of our customers, because they are all live and don't need to import a bacpac file into a Tier2. I'm guessing that you have a GOLDEN config bacpac, which you want to import.

The Import-D365Bacpac is just creating a SQL Server connection, and that is why you need provide some details. Because you want to import into a Tier2, we know that previously you needed to fill in a lot of details for all the connected service accounts, which you should be prompted about, if you don't provide them.

What I don't know, but what we should learn quickly if you feel like testing it, is whether or not we can import a bacpac into a database that already exists. Prior SPARTAN, we had access to an Azure SQL DB instance, which made it possible for us to create a new database while importing and then we could switch it out after the import completed. SPARTAN doesn't offer that, and could prove as the single point where the whole thing falls apart.

I know that Microsoft has been loooking into dacpac files, which is almost the same as a bacpac file, but offer some better things. One thing is that is for sure can import directly into a database.

Let me know if you would like us to team up and see if we can find a way for you to import the bacpac file into your Self-Service / SPARTAN database. I'm more than happy to help and learn some new stuff along the way.

[VincentVerweij]

Hi @Splaxi

Thanks for the fast reply.

So, you are right about the JIT access and the enable access IP that needs to be requested/added. I too was thinking about this approach. But this takes away the approach of doing it fully automated. I didn't find any functions in the repo which allow us to add the JIT access and/or add the IP for such an environment. This was also one of the reasons why I created this issue.

You are almost right with the GOLDEN config 😃 What we try to do is prepare a migration dataset on a OneBox (which includes the GOLDEN config), and once this is completed we create a bacpac. Since it is a OneBox we have access to everything and can create whatever we want. We are orchestrating this via an Azure DevOps release pipeline. When this dataset is ready we want to import it to some Tier2+ environment, which can be done by the way how you described it.

It would be nice if we can create steps in our Azure DevOps release pipeline that first adds the IP for that specific agent, next request credentials and finally use those to call the importbacpac function. When this is done once a week, one could argue that we can click manually in LCS to request those things, but when doing it more than once a week you really want to automate it fully. So that the migration team can have many iterations and deliver at full speed 🚀 .

Apart from the preferable fully automated approach, I will ask our team/try it myself and let you know how it works out asap.

[Splaxi]

I personally plan on using the Power Automate UI Flow to handle the clicking around inside LCS.

The only thing that I need to fully test, is if the UI Flow is capable of selecting and copying text from the screen and pass it on.

My overall plan would be something like this: Azure Devops Pipeline:

1. Invoke Power Automate UI Flow

Power Automate UI Flow:

1. Get user name, password and IP address from Azure KeyVault
2. Logon into LCS, based on the username and password from step 1, select the correct project and locate the correct environment.
3. Enable Access, based on the IP address from step 1.
4. Enable JIT, refresh site, select and copy paste all details (Server, username and password)
5. Call second Azure DevOps pipeline and pass the details

Next Azure DevOps Pipeline:

1. Receive details from http call
2. Do what I need to do....

I don't know if it will work, but that would be my plan if I could get Power Automate UI Flow to copy and paste the details from a page. You could start a trial and see if you could get it to work. Then we know if we can go down that path or not.

[Splaxi]

To be open and fair.

The only reason we don't have that in the tools, is that we don't have a LCS endpoint that allows us to do it in an automated fashion.

Poke Lane Swenka and get him to provide those API endpoints and I'll work day and night until we have it supported in the tools. 😃

[Splaxi]

Keep me posted on how you progress with this, and let me know if you hit any road blockers.

[Splaxi]

@VincentVerweij Any news on this?

I'll be closing the issue in 7 days, if we don't hear back from you.

[VincentVerweij]

@Splaxi nope, no news yet.

We are currently in close collaboration with Microsoft to get our migration up and running for our ISV solution. So I cannot access any of those Self-Service environments to test it out :-( Will re-open this issue once I get to it and have some results.

But currently I think it is best to close this, as the issue is not related to d365fo.tools but to lack of functionality in the LCS APIs.

[smholvoet]

Somewhat related to this issue: I guess 1 year down the line there still isn't an easy way to request JIT access through the LCS API @Splaxi? 😞

Use case: request JIT access to a sandbox environment, store the Azure SQL details (instance, db name, login, password) and pass the details to dbatools. As was mentioned earlier in this issue, you'd still need a way to add your IP to the firewall rules of the DB server, which also isn't possible afaik (Get-D365ExternalIP is only a part of the solution).