Finance | Project Operations, Human Resources, ...

Facing error on converting bacpac to bak in D365 f&o (10.0.39)

(2) Share

Report

Posted on by Rubalingam S

Hi,

I need to import sandbox environment db backup file (bacpac.) to cloud hosted environment (bak.). While converting it through sql.net tool using command prompt facing below error.

*** Error importing database:Could not import package.

Error SQL72014: Core Microsoft SqlClient Data Provider: Msg 4630, Level 16, State 1, Line 1 The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server. Alternatively, use the server level 'ALTER ANY CONNECTION' permission.

Error SQL72045: Script execution error. The executed script:

GRANT KILL DATABASE CONNECTION TO [ms_db_configreader];

sandbox environment version - 10.0.39
Cloud hosted environment version - 10.0.39

Can someone guide me to resolve it.

Thanks and Regards,

Rubalingam.S

I have the same question (0)

All responses (21)

Answers (0)

Suggested answer

fsilva-jr 20 on at

Like (0)

Report

Two possibilities:

GRANT ALTER ANY CONNECTION TO YOURUSERACCOUNT;

or:

sp_configure 'contained database authentication', 1;
GO
RECONFIGURE;
GO

As this is your DEV machine, i don´t see issues in changing the mentioned configurations. Don´t do it in a production database!!!

Was this reply helpful? Yes No
Anthony Blake 2,956 Super User 2025 Season 2 on at

Like (0)

Report

Hi Rubalingam,

Have you managed to convert using the .net (assume it's sqlpackage.exe?) tools version you have installed before?

Was this reply helpful? Yes No
Suggested answer

Mohamed Amine Mahmoudi 26,390 Super User 2025 Season 2 on at

Like (1)

Report

Hi @Rubalingam,

Try to use sqlpackage.exe

make sure that execute cmd as administrator

https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage?view=sql-server-ver16

Best regards,

Mohamed Amine MAHMOUDI

Was this reply helpful? Yes No
Luis Carlos Marin R on at

Like (0)

Report

Hey, I'm having the same issue trying to restore a UAT environment backup to a version 39 sandbox. I've tried all the suggested fixes but no luck. Got any other ideas?

Was this reply helpful? Yes No
Suggested answer

Navneeth Nagrajan 2,407 Super User 2025 Season 2 on at

Like (0)

Report

Hi Rubalingam S,

Refer to this article:

The contained database authentication value should be set to 1 from 0 before performing the conversion.
https://stackoverflow.com/questions/75404400/kill-database-connection-is-not-supported-when-trying-to-import-bacpac-file-to-l

Additionally, have you downloaded the latest version of sqlpackage.exe through the link mentioned below?
https://aka.ms/dacfx-msi

Looks like the sqlpackage is out of date. You can download the latest version through the link mentioned above.

Was this reply helpful? Yes No
KP-08050337-0 on at

Like (0)

Report

I have run into this exact problem as well both yesterday and today with the newest version of SQLPackage and an older version of SQLPackage. I have tried the various things suggested with no positive results. The process runs for about an hour before the error message.

If you look at the error message closely, it would imply that this command is attempting to be executed or possibly just being parsed as part of the Import process and that this command isn't available with SQL Server but it is apparently available in Azure SQL.

I believe it has something to do with the bacpac file that is now being created with 10.0.39 but I have NO idea what the fix is for getting the database imported into a SQL Server environment.

After the process errors out, the database structure has been created but there is no data that has been imported into it.

Was this reply helpful? Yes No
Wilson Kong 6 on at

Like (0)

Report

Delete my old msg

Was this reply helpful? Yes No
Suggested answer

Wilson Kong 6 on at

Like (2)

Report

Hello,

Seem we are quite update today. haha.
I had the same issue as you these 2 days. I think the issue caused by MS updated their Azure SQL export script in LCS but made something wrong.

Anyway, there are some hints helping us to resolve the issue.
1. Rename you .bacpac file to .zip

2. open the zip file and find the model.xml file inside, copy it out to somewhere you like

3. Open the model.xml file, which is pretty big.

4. Find string "Grant.KillDatabaseConnection" and replace it as "Grant.Alter.Database" in the model.xml
If you comparing with an old .bacpac, you will find this is the corrent string in model.xml of old bacpac file.
KillDatabaseConnection is SQL 2008 script and not sure why it generated to our current SQL 2019 version backup export.

5. Put the new model.xml file back to the .zip file

6. By same way to update the Origin.xml with new model.xml checksum, update the following line in Origin.xml:
<Checksum Uri="/model.xml">XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX</Checksum>

https://techcommunity.microsoft.com/t5/azure-database-support-blog/editing-a-bacpac-file/ba-p/368931
To generate the checksum by the above URL provided powershell script:.
- Create a checksum.ps1 file,
- copy the following into it.
$modelXmlPath = Read-Host "model.xml file path"
$hasher = [System.Security.Cryptography.HashAlgorithm]::Create("System.Security.Cryptography.SHA256CryptoServiceProvider")
$fileStream = new-object System.IO.FileStream ` -ArgumentList @($modelXmlPath, [System.IO.FileMode]::Open)
$hash = $hasher.ComputeHash($fileStream)
$hashString = ""
Foreach ($b in $hash) { $hashString += $b.ToString("X2") }
$fileStream.Close()
$hashString

- run the checksum.ps1 and enter your model file path.

7. Rename it back to .bacpac.

8. Run the data package loading again, for an example I used:
"C:\Program Files\Microsoft SQL Server\160\DAC\bin\SqlPackage.exe" /a:import /sf:C:\Temp\xxx-bldbackup.bacpac /tsn:localhost /tdn:AxDB_BLD /p:CommandTimeout=1200 /TargetTrustServerCertificate:True

Hope this help you and me.

Was this reply helpful? Yes No
Dan Rooney 2 on at

Like (0)

Report

I was successful getting past this error with Wilson Kong's solution but without the need to rebuild the bacpac with the new model.xml.

I unzipped the bacpac (renaming a copy with a .zip extension) and modified the model.xml file. Then ran the import using the /ModelFilePath switch to override the one that is in the current bacpac file. In my case I was using the D365 Import-D365Bacpac function with the "-ModelFile" switch, but I would expect it to work the same with the SqlPackage.exe "/mfp:" switch.

No need to put the modified model.xml file back in the bacpac if you override it. (my experience anyway)

Was this reply helpful? Yes No
Wilson Kong 6 on at

Like (1)

Report

Hi DR-08051736-0,

Good to hear this helping you.

Yes, I did the same with -mfp with SqlPackage.exe, which should be identical to Import-D365Bacpac with -modelfile.

However, not sure why this is not work on me, so i edited the solution with more complicated step.

BTW, I reported the issue to MS and found the new backup export file fixed this issue in LCS already today.

Was this reply helpful? Yes No