DEV Community: Eduardo Silva Rossi

Fix "The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server" while importing a D365FO .bacpac

Eduardo Silva Rossi — Mon, 27 May 2024 02:58:30 +0000

Recently, while making a copy of production database to my development environment, I came across an unusual error when importing the .bacpac file using SqlPackage:

*** 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];

After some digging, I was abble to resolve the problem following the solution below.

Solution

This solution will help you to edit model.xml file contained inside the package and remove/replace the KILL ANY CONNECTION statement.

Make a backup of your original .bacpac package.
Go to your package in File Explorer and change his file extension from .bacpac to .zip. The renamed file should look like this:

Now you can open the renamed package and extract the model.xml file into another folder:

Open the model.xml file with Notepad or another text editor and search for "Grant.KillDatabaseConnection.Database".

The search should return one or more SqlPermissionStatement elements. You can remove¹ these elements from the model.xml file.
After this, save and close the modified model.xml file.
Then copy and replace the modified model.xml into the zipped package and change the package file extension from .zip to .bacpac again. Your files should look like this:

Here comes the fun part, usually you would have to extract the package completely, modify the model.xml file, generate a new checksum, update the Origin.xml file with the new checksum value and then compress the package again.

Instead, you can skip all this work by using the SqlPackage import parameter /ModelFilePath (/mfp):

.\sqlpackage.exe /a:import /sf:"C:\Users\localadmin\Desktop\Goldenbackup.bacpac" /mfp:"C:\Users\localadmin\Desktop\model.xml" /tsn:localhost /tdn:AxDB_fromGolden  /ttsc:true /p:CommandTimeout=0

Parameters description:

/sf: put your .bacpac package file path here.
/mfp: overrides the model.xml in the source file with the modified model.xml.

Now the .bacpac package will be imported successfully. 😊

Final considerations

¹ Instead of removing the SqlPermissionStatement elements, you can also replace the permission "Grant.KillDatabaseConnection.Database" with "Grant.AlterAnyConnection.Database".
² This solution also solves the error "Operation Failed: File contains corrupted data" while importing a .bacpac package in SSMS.

How to resolve budget ledger dimension in X++/D365 F&O

Eduardo Silva Rossi — Tue, 05 Mar 2024 01:43:56 +0000

Sometimes we get a requirement to create Budget register entries by X++ code and moments later we realize that the usual methods of creating ledger dimensions used for ledger journals doesn't work in this scenario.

This happens because budget and ledger accounts have different sets of ledger dimensions based on defined Budgeting module parameters (Budgeting/Setup/Basic budgeting/Budgeting dimensions). In this case we should resolve our ledger dimension using the code bellow:

public static void main(Args _args)
{
    BudgetAccountContract budgetAccountContract = new BudgetAccountContract();

    budgetAccountContract.parmValues(new List(Types::Class));
    budgetAccountContract.parmAccountStructure('Manufacturing P&L');

    DimensionAttributeValueContract dimensionAttributeValueContract;

    dimensionAttributeValueContract = DimensionAttributeValueContract::construct('MainAccount', '607200');
    budgetAccountContract.parmValues().addEnd(dimensionAttributeValueContract);

    dimensionAttributeValueContract = DimensionAttributeValueContract::construct('BusinessUnit', '002');
    budgetAccountContract.parmValues().addEnd(dimensionAttributeValueContract);

    dimensionAttributeValueContract = DimensionAttributeValueContract::construct('Department', '022');
    budgetAccountContract.parmValues().addEnd(dimensionAttributeValueContract);

    BudgetDimensionCombinationServiceProvider budgetDimensionServiceProvider = BudgetDimensionCombinationServiceProvider::newForBudgetAccountContract(budgetAccountContract);
    DimensionStorageResult dimensionStorageResult = budgetDimensionServiceProvider.resolve();

    if (dimensionStorageResult.parmInvalidValue())
    {
        throw Error('Error while resolving ledger dimension.');
    }

    info(strFmt('Budget LedgerDimension RecId: %1', dimensionStorageResult.parmSavedRecId()));
}

With a ledger dimension display value string the solution is even shorter:

BudgetAccountDimensionResolver budgetAccountDimResolver = BudgetAccountDimensionResolver::newResolver('607200-002-022--', 'Manufacturing P&L');
DimensionDynamicAccount ledgerDimension = budgetAccountDimResolver.resolve();

How to fix Dynamics AX 2012 R3 when the time is 1 hour ahead when not using daylight savings time

Eduardo Silva Rossi — Fri, 16 Feb 2024 16:31:06 +0000

In 2020 I wrote this article in another platform telling how to fix a time zone problem in Dynamics AX 2012 R3 when a country changes its daylight savings time legislation. Recently, I noticed that some people experiencing the same problem in other countries so I decided to share this fix again here in DEV community.

The problem

On the second half of 2019 the brazilian government changed his timezone rules for the first time in several years. From that year on, daylight savings time would no longer be implemented in the national territory.

Given this situation, in a customer with Dynamics AX 2012 R3 we had some problems like transaction time one hour ahead in forms and reports and the inability to cancel an invoice within 1 hour of its posting.

Some context

In Dynamics AX 2012, time zones are defined in the system table TimeZoneRulesData (AOT/System Documentation/Tables/ TimeZoneRulesData). This table contains data like the difference in minutes between a local time zone and the time zone (GMT) UTC+0 (BIAS column), and also the difference in minutes that should be applied if the timezone has daylight savings time (DBIAS column), when this difference should be applied and others.

Note that the DBIAS column keeps the difference in minutes that should be applied in daylight savings time, so in time zones where the time is regressed by 1 hour this column will have the value of -60 as we can see in the result of the SQL statement bellow:

SELECT TOP 1 [RULEID]
      ,[TZENUM]
      ,[YEAR]
      ,[BIAS]
      ,[DBIAS]
  FROM [DAX12_DATABASENAME].[dbo].[TIMEZONESRULESDATA]
 WHERE TZENUM = 28 --Timezone::GMTMINUS0300BRASILIA enum value
   AND YEAR = 2020

How to fix

To prevent this behavior we need to run a simple SQL code to update the DBIAS column to zero in our related timezone:

UPDATE [DAX12_DATABASENAME].[dbo].[TIMEZONESRULESDATA]
   SET DBIAS = 0
 WHERE TZENUM = 28 --Timezone::GMTMINUS0300BRASILIA enum value
   AND YEAR >= 2020

After we run the update above we need to restart the DAX12 AOS service for the fix take effect.

Links

Here you can find the complete Timezone enumeration and its values.
Dynamics AX 2012 Time Zone docs