Power App writeback to Sql server doesn't work from the service

[WilliamA]

Hi All,

I have created a power app that takes information from a power BI report and allows users to write data back to a sql database. 

when I preview the app directly in power apps, I can save the form and I can see the data get updated in the database.

however, when I use the power app within power BI, I get the below error:

writeback.dbospwriteback failed: { "error": { "code": 403, "message": "Operation is not allowed", "source": "default-540c4cb1-7eb5-444d-a6cc-2f4b5bc92b4e.08.common.australia.azure-apihub.net", "path": "choose[3]\\when[1]\\choose\\when[1]", "policyId": "", "clientRequestId": "bb76c028-c3c7-46fd-8869-5ee1d8998250", "dreeSet": "true" } }

power apps calls a stored procedure that does an insert. 

could there be some permission issue in power BI that is blocking it? I dont understand why it works directly in power apps but not when the power app sits inside of power BI.

[v-csrikanth]

Hi @WilliamA 
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @WilliamA 
Thanks for confirming that you are working on the above posted solution.

If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @WilliamA 

We are following up once again regarding your query. Did you gat any chance to raise it in the ideas forum with Microsoft.
As per Microsoft community guidelines, we will now proceed to close this thread to keep discussions focused and manageable. If you still need assistance, you're welcome to start a new thread in the community at any time.
We appreciate your understanding and participation.

Thank you for your understanding and participation.

Best Regards,
Community Support Team _ C Srikanth.

[WilliamA]

Hi @v-csrikanth , I am still in the process of trying the suggested solution. 

[v-csrikanth]

Hi @WilliamA 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @WilliamA 
Sorry  for the late response.

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth · Accepted solution]

Hi @WilliamA 
Welcome to the Microsoft Fabric Community.
Below are the steps to navigate to cross verify the sql configuration.

1. How to verify SQL Connection in Power BI Service:

-> Access the Data Gateway Configuration:

1. Log in to Power BI Service: Navigate to Power BI.
2. Go to Settings:
   • Click on your profile icon in the top-right corner.
   • Select Settings.
3. Choose Gateway Settings:
   • In the left panel, select Manage gateways under the Admin portal section.

-> Review Gateway Configurations

1. Locate the gateway associated with your SQL Server data source.
   • Check the Name of the gateway from your Power BI dataset settings or workspace settings.
2. Expand the gateway, and you will see the Data Sources configured under it.

->Check SQL Data Source Settings

1. Click on the SQL Server data source to view its configuration.
2. Verify the following settings:
   • Data Source Name: Ensure the name matches the expected SQL Server data source.
   • Server and Database: Confirm the correct SQL Server instance and database are specified.
   • Authentication Method:
     • Windows Authentication: User domain credentials.
     • SQL Authentication: Uses a username and password stored in the gateway configuration.
   • Credentials: If SQL Authentication is used, ensure the correct username and password are configured.

Step 1.4: Test Connection

1. Click Test Connection to verify if the gateway can successfully connect to the SQL Server.
2. If the connection fails, recheck the server details, database name, and credentials.

2. Verify SQL Connection in Power BI Embedded:

-> Access Power BI Embedded Configuration

1. Log in to the Azure Portal: Navigate to Azure Portal.
2. Locate the Azure Power BI Embedded Capacity:
   • Search for "Power BI Embedded" in the search bar.
   • Select your capacity resource.

-> Check Embedded Application Settings

1. Go to the App Owns Data configuration or the code that embeds Power BI.
2. Review the connection string or credentials passed from your app to the embedded report:
3. Look for any hardcoded SQL credentials in your application code.
4.       4.Check if the app uses a Service Principal or a user account.

If the above information helps you, please give us a Kudos and marked the reply as a solution.

Thanks,
Cheri Srikanth

[Poojara_D12]

Hi @WilliamA 

I think the error is likely due to a permissions issue. When embedded in Power BI, the app may use different credentials lacking SQL database writeback rights.

1. Ensure the Power BI user or service principal has EXECUTE and INSERT permissions on the SQL database.
2. Verify the SQL connection in Power Apps.
3. Check gateway permissions if using an on-premises gateway.
4. Review any Data Loss Prevention (DLP) policies.

These should resolve the issue.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[WilliamA]

Thanks @Poojarai, I am using power BI embedded "app owns data". is there a way to tell which credientials are being used? the power app sql connection is connected via an on-prem gateway and the sql account used in the power BI report has access to the database. 

regarding the gateway - where are these permissions? I couldn't locate anything on the gateway around permissions.