Unable to update the table values in STAGE database using SQL - D365FO

(5) Share

Report

Posted on by Akbar Pasha

1,505

Hi all,

We are trying to update few values in BankParameters table in STAGE database using SQL script(started SQL as administrator) , but getting the error below.

Please advice how we can resolve this.

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (10)

Answers (3)

Saif Ali Sabri 2,351 Super User 2025 Season 2 on at

Like (0)

Report
In Microsoft Dynamics 365 Finance & Operations (D365FO), direct SQL updates to the STAGE (or any other) database are restricted due to security and data integrity policies. Microsoft enforces this restriction to prevent data corruption and maintain application consistency.

Solution Options:

1. Use D365FO Data Entities (Recommended)

Navigate to Data management (System Administration > Workspaces > Data management).

Use the BankParameters data entity to export the current data.

Modify the values in Excel and re-import them using the same data entity.

2. Use X++ Code (if you have development access)

If direct SQL updates are necessary, use X++ to update records programmatically.
Example:

xpp

BankParameters bankParam = BankParameters::find(); bankParam.SomeField = "NewValue"; bankParam.update();

Deploy the updated code via a data package or through a developer environment.

3. Use SQL via the LCS Sandbox (Limited)

If you need direct database updates, use Microsoft’s LCS Database Movement feature to temporarily allow query execution via Azure SQL Query Editor.

Navigate to LCS (Lifecycle Services).

Open the Environment details page.

Under the Database section, access the query editor and execute necessary scripts.

4. Raise a Microsoft Support Request

If no other options work, request Microsoft to perform the update through a support ticket.

Error Explanation

D365FO enforces security by blocking direct SQL modifications. Attempting UPDATE statements on the STAGE database leads to an error due to the SQL Azure Managed Instance security model.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,807 Most Valuable Professional on at

Like (1)

Report

It's not true; you can enable write access to non-production databases.

Why do you need to use SQL? Are you sure your goal can't be achieved differently, e.g. changing the value through GUI or a data entity?

What exactly do you mean by "STAGE database"? If you mean a DB of T2+ F&O environment provisoned through LCS, have you enabled write access in LCS?

Was this reply helpful? Yes No
Verified answer

Adis 6,048 Super User 2025 Season 2 on at

Like (1)

Report

Hey,

Have you tried the custom scripts? I think they are suitable for your case.

Run custom X++ scripts with zero downtime - Finance & Operations | Dynamics 365 | Microsoft Learn

Kind regards, Adis

If this helped, please mark it as "Verified" for others facing the same issue

Keep in mind that it is possible to mark more than one answer as verified

Was this reply helpful? Yes No
Akbar Pasha 1,505 on at

Like (1)

Report

Hi Saif

We have multiple legal entities.

We need to remove few details from custom fields from BankParameters table whenever we restore the Database from PROD. As we have multiple legal entities it will take more time to remove from the front end, so thought we can write the update script in STAGE DB and remove the custom fields data at once.

Using Data entities - It is just exporting the data from one legal entity, but we need to remove the data from multiple companies.

Option 3 from your inputs - I think it will provide only the database access with credentials for 8 hours. This is the option I am trying. We can execute the select statements but not the update.

How we can enable the Write access?

Regards,

Akbar

Was this reply helpful? Yes No
Akbar Pasha 1,505 on at

Like (0)

Report

Hi Martin,

We have restored the PROD data into STAGE, and we need to remove few custom details which got copied from PROD. And we have multiple legal entities which will take more time to remove it from front end. So thought to remove using the SQL update statement at once from BankParameters.

Using Data entities - It is just exporting the data from 1 legal entity only.

Please advice how we can enable write access for non-prod database.

Was this reply helpful? Yes No
Akbar Pasha 1,505 on at

Like (1)

Report

Hi Adis,

I have not tried custom scripts, will check this option. Thanks.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,807 Most Valuable Professional on at

Like (0)

Report

Using SQL code in this scenario is the usual approach.

Your statement "it will provide only the database access with credentials for 8 hours" suggests that you know how to enable access, but you still haven't told us whether you enable write access or just read-only access. Please give us more information about what you did.

Was this reply helpful? Yes No
Akbar Pasha 1,505 on at

Like (1)

Report

Thank you very much Martin for your hint.

I enabled the read only access earlier. I have just checked the options and found this one.

I will try with this and will update here.

Was this reply helpful? Yes No
Akbar Pasha 1,505 on at

Like (1)

Report

Thank you Martin, able to execute the update scripts now.

Really appreciate your help.

Regards,

Akbar

Was this reply helpful? Yes No
Suggested answer

Bharani Preetham Pe... 3,634 Moderator on at

Like (0)

Report
You can also explore these ways.

You can write a code in X++ and provide this class related menuitem only to admin and he will be able to run this once DB refresh is completed.

Other way is to use a Data Entity as everyone are suggesting.

You can also achieve this using a Odata which can be triggered from postman.

Was this reply helpful? Yes No