Error importing Tier 2 database to Tier 1 VM - The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server.

[D365-User]

I am running into a new issue. My Tier 1 VM has SQL 2019. When I try importing a .bacpac that is exported from Tier 2 VM, I get this message:
[Invoke-Process] Error output was: \r\n *** Error importing database:Could not import package.
Warning SQL0: Overriding model.xml using file 'C:\Temp\UpdatedBacpacModel.xml'. Use of this setting may result in deployment failure and/or unintended data loss. This setting is intended only for use when troubleshooting issues with publish, import or script generation.
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];

Has anyone else seen this issue and found a solution?

[FH-Inway]

Thanks for reporting. I do not think we had this error before.

Could you let us know which version of d365fo.tools and SqlPackage is being used?
Is this the first time a backup from that T2 is being restored? Is it the first time on this T1?

[D365-User]

Hi Florian - I actually tried the import on 2 cloud hosted DEV VM's and when they both failed I tried it on a onebox local VM. On the onebox local VM it was a fresh install of d365fo.tools using the command "Install-Module -name d365fo.tools"and fresh install of sqlpackage (sqlpackage-win-x64-en-162.2.111.2). It is not the first time a backup from this T2 was restored. The last time was about a month ago. It is not the first time on the 2 T1 cloud hosted VM's but was the first time on the local onebox.

[FH-Inway]

Thanks. Out of ideas for now on the root cause, maybe someone else has one or the weekend will give me new ones :)
I am curious about the ms_db_configreader mentioned in the error message. Don't think I saw that one before.
Really hope Microsoft will soon start giving us T1 environments with SQL Server 2022.

As a workaround you could investigate restoring the .bacpac on a SQL Server 2022 instance. Does not have to be one of the VMs you have used so far, any instance should do. From there, you can create the .bak and restore that on the VMs.

Another idea (but a bit tricky to set up, have not tried it myself yet) is to create your own .bacpac from a T2 environment with JIT (just in time) access. This might give you the option to create a SQL Server 2019 compatible .bacpac.

Also noticed you seem to be using a custom model.xml. Could you tell us more about that (why, how is it created, ...)?

[D365-User]

There was a similar problem introduced late in 2023 when Microsoft switched to sql 2022 kernel in the T2's. In the yammer groups a powershell script called Remove-AutoDropFromBacpacModel.ps1 was shared.
See #747
Original script by @batetech in https://www.yammer.com/dynamicsaxfeedbackprograms/#/Threads/show?threadId=2382104258371584
this script creates BacpacModel.xml and UpdatedBacpacModel.xml
I have also tried the regular sqlpackage.exe /Action:Import command (with multiple parameters) on all 3 VM's and get the same error. I have tried everything. I guess this weekend I will try upgrading to SQL2022.

[Splaxi]

If the regular SqlPackage.exe fails, even if you downloaded the latest version directly from the MS Documentation - d365fo.tools will not solve the issue, without us coming up with a work around.

That said - reading the documentation for SQL Server 2016 (and higher): https://learn.microsoft.com/en-us/sql/t-sql/language-elements/kill-transact-sql?view=sql-server-2016#permissions

This could indicate that the local permissions on the SQL Server doesn't match what the bacpac is trying to instruct / execute on the import of the bacpac file.

Are your T1 deployed from LCS or VHD downloaded / running T1?

So the connection from the SqlPackage.exe is either taking the current local user (AdminABC) or use the AxDbAdmin (Sql User), found from the web.config file. Either way - this could be something to look into.

[FH-Inway]

Also discussed here: https://www.yammer.com/dynamicsaxfeedbackprograms/threads/2782521902366720

Since the weekend, I am also getting the issue on two environments.

[FH-Inway]

It seems removing the ms_db_configreader and ms_db_configwriter roles from the T2+ database resolves the issue.

Alternatively, removing elements like the following from the .bacpac model file should also resolve the issue. A script similar to Remove-AutoDropFromBacpacModel.ps1 can be used to do that.

<Element Type="SqlPermissionStatement" Name="[Grant.KillDatabaseConnection.Database].[ms_db_configreader].[dbo]">
	<Property Name="Permission" Value="1114" />
	<Relationship Name="Grantee">
		<Entry>
			<References Name="[ms_db_configreader]" />
		</Entry>
	</Relationship>
	<Relationship Name="SecuredObject">
		<Entry>
			<References Disambiguator="1" />
		</Entry>
	</Relationship>
</Element>

[FH-Inway]

Fix-BacpacModel.ps1 can be used to resolve the issue by removing the elements with Grant.KillDatabaseConnection.Database.

[valerymoskalenko]

Fix-BacpacModel.ps1 can be used to resolve the issue by removing the elements ...

Is it possible to include your script as a part of d365fo.tools library?
I mean, add the following function to the d365fo.tools. Maybe with a different name.

function Local-FixBacPacModelFile
{
    param(
        [string]$sourceFile, 
        [string]$destinationFile,
        [int]$flushCnt = 500000
    )
   ...

[Splaxi]

It seems removing the ms_db_configreader and ms_db_configwriter roles from the T2+ database resolves the issue.

Alternatively, removing elements like the following from the .bacpac model file should also resolve the issue. A script similar to Remove-AutoDropFromBacpacModel.ps1 can be used to do that.

<Element Type="SqlPermissionStatement" Name="[Grant.KillDatabaseConnection.Database].[ms_db_configreader].[dbo]">
	<Property Name="Permission" Value="1114" />
	<Relationship Name="Grantee">
		<Entry>
			<References Name="[ms_db_configreader]" />
		</Entry>
	</Relationship>
	<Relationship Name="SecuredObject">
		<Entry>
			<References Disambiguator="1" />
		</Entry>
	</Relationship>
</Element>

We can confirm, that removing Grant.KillDatabaseConnection.Database and everything else from the model file, that points towards ms_db_configreader & ms_db_configwriter - did the trick. Just removing the Grant.KillDatabaseConnection.Database wasn't enough AND the errors posted, did actually reference the GRANT / KILL command. So something is hiding inside the remaining elements in the model file.

[FH-Inway]

Curious, for me and several others, just removing the elements with Grant.KillDatabaseConnection.Database worked. I wonder if Microsoft continues making changes there that add additional elements that reference ms_db_configreader and ms_db_configwriter.

What did the additional elements that you had to remove look like?

[Splaxi]

<Element Type="SqlPermissionStatement" Name="[Grant.Insert.Object].[ms_db_configreader].[dbo].[dbo].[AutotuneLogBase]">
			<Property Name="Permission" Value="1" />
			<Relationship Name="Grantee">
				<Entry>
					<References Name="[ms_db_configreader]" />
				</Entry>
			</Relationship>
			<Relationship Name="Grantor">
				<Entry>
					<References ExternalSource="BuiltIns" Name="[dbo]" />
				</Entry>
			</Relationship>
			<Relationship Name="SecuredObject">
				<Entry>
					<References Name="[dbo].[AutotuneLogBase]" />
				</Entry>
			</Relationship>
		</Element>
		<Element Type="SqlRoleMembership">
			<Relationship Name="Member">
				<Entry>
					<References Name="[ms_db_configreader]" />
				</Entry>
			</Relationship>
			<Relationship Name="Role">
				<Entry>
					<References ExternalSource="BuiltIns" Name="[db_ddladmin]" />
				</Entry>
			</Relationship>
		</Element>

[FH-Inway]

@valerymoskalenko Splaxi started work on a much more generic version of the script as cmdlet for d365fo.tools: #824 🎉

[Splaxi]

0.7.11 solves this!

[FH-Inway]

Good work @Splaxi

To elaborate, Repair-D365BacpacModelFile is now availabe in version 0.7.11.

Use it like this to replicate the behavior of the gist script:

Repair-D365BacpacModelFile -Path $modelFilePath -OutputPath $fixedModelFilePath -Verbose -PathRepairQualifier "" -PathRepairSimple .\SimpleKillConnection.json

Where SimpleKillConnection.json is a JSON file with the following content in the folder where the cmdlet is called from:

[
  {
      "Search": "*<Element Type=\"SqlPermissionStatement\"*Grant.KillDatabaseConnection.Database*ms_db_configreader*",
      "End": "*</Element>*"
  },
  {
      "Search": "*<Element Type=\"SqlPermissionStatement\"*Grant.KillDatabaseConnection.Database*ms_db_configwriter*",
      "End": "*</Element>*"
  }
]