[Solved] Dynamics 365FO DB restore error Line 1 The permission 'KILL DATABASE CONNECTION' is not supported in this version of SQL Server.

Important Note: Microsoft frequently updates SqlPackage. Therefore, it’s crucial to download the latest version of SqlPackage each time you intend to use any of its commands. Download the latest SQLPackage.

Error Details: The error message for BacPac DB restore is as follows:

Dynamics 365FO DB restore error 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.

Dynamics 365FO DB restore error 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.

Recommended solution: 

Step 1: Navigate to the folder where your BACPAC file is saved and change the file extension from .bacpac to .zip.

Step 2: Open the zip file and copy the model.xml file to a different location. Open the copied file in a text editor such as Notepad, VS Code, or Visual Studio. (Avoid editing the file directly in the zip folder or the original file).

Model file may not load in notepad, I would prefer to open this into VS code, Visual Studio itslef or Notepad ++, 

Step 3: In the copied model.xml file, find and delete the entire Element tag that contains “Grant.KillDatabaseConnection”. Save the modified file as ModelCopy1.xml.

Step 4: Copy the modified file (ModelCopy1.xml) and paste it into the SqlPackage folder. (Download the latest version of SQLPackage)

Step 5: Change the file extension of the zip file back to .bacpac (reverse of Step 1).

Step 6: Go to the downloaded SQLPackage folder and execute the following command:

SqlPackage.exe /a:import /sf:J:\MSSQL_BACKUP\PreProdDB.bacpac /tsn:localhost /tdn:AxDB_PreProd2005 /p:CommandTimeout=1200 /TargetUser:"axdbadmin" /TargetPassword:"<DbPassword>" /TargetTrustServerCertificate:True /mfp:"ModelCopy.xml

DB import should be successful this time. 

-Harry Follow us on Facebook to keep in rhythm with us. https:fb.com/theaxapta

Covert timestamp (1716336000000) to ISO format (2020-08-20T23:00:00Z)

To convert a timestamp in milliseconds to a date format like 2020-08-20T23:00:00Z in X++, you can use the DateTimeUtil class to convert the timestamp to a DateTime value, and then format it as needed. Here’s an example of how you might do it:

static void ConvertTimestampToDate(Args _args)

{

    // Your timestamp in milliseconds

    int64 timestamp = 1716336000000;

    // Convert the timestamp to DateTime

    DateTime dateTime = DateTimeUtil::addMilliseconds(DateTimeUtil::utcNow(), timestamp - DateTimeUtil::getSystemDateTime());

    // Convert DateTime to desired format

    str formattedDate = DateTimeUtil::toStr(dateTime, DateTimeUtil::TimeZone::UTC);

    // Print the formatted date

    print formattedDate;

    pause;

}

This code snippet assumes that the timestamp 1716336000000 is the number of milliseconds since the Unix epoch (January 1, 1970). The DateTimeUtil::addMilliseconds method is used to add the timestamp to the Unix epoch to get the correct DateTime. Then, DateTimeUtil::toStr is used to convert the DateTime to a string in the ISO 8601 format, which matches the format you provided (2020-08-20T23:00:00Z). Please adjust the logic if your timestamp is based on a different epoch or requires different handling.

-Harry Follow us on Facebook to keep in rhythm with us. https:fb.com/theaxapta