topic Re: Dataflow Gen 2 Error - external policy action was denied in SQL database

Dataflow Gen 2 Error - external policy action was denied

wjbreitenbach — Wed, 04 Dec 2024 14:23:59 GMT

I am trying to load a SharePoint Excel file into a SQL database in Fabric. I created the SQL database as I have admin role in the workspace. I then created a Dataflow Gen 2 using Azure SQL database as the destination . I selected the newly created database and the option to create a new table.

When I publish the dataflow, I get the following error: Table Load List_WriteToDataDestination: Mashup Exception Data Source Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: DataSource.Error: Microsoft SQL: The external policy action 'Microsoft.Sql/Sqlservers/Databases/Schemas/Tables/Create' was denied on the requested resource.

Why can I not create the table using the dataflow? Can I only import data into an existing table? Are there additional permissions that need to be set in order to create a table?

Thanks,

Bill

Re: Dataflow Gen 2 Error - external policy action was denied

NandanHegde — Wed, 04 Dec 2024 14:52:14 GMT

Can you please connect to the Fabric SQL database directly and try creating the table manually via SSMS and confirm whether it is a success or not?
That would help us identify whether there is a permission issue or somehwat related to the below link :

https://community.fabric.microsoft.com/t5/Issues/The-external-policy-action-Microsoft-Sql-Sqlservers-Databases/idi-p/3740666

Re: Dataflow Gen 2 Error - external policy action was denied

wjbreitenbach — Wed, 04 Dec 2024 15:14:36 GMT

When trying to connect using SSMS, I get an error that database 'master' on server xxx is not currently available. The session tracing ID is {8B953507-F12D-49D9-8DD2-F53D98FF8733}, error:40613.

Howerver, using Fabric SQL Editor, I can create a table.

Re: Dataflow Gen 2 Error - external policy action was denied

NandanHegde — Wed, 04 Dec 2024 15:23:14 GMT

The error you are getting via SSMS is because you have not provided the database name.

Go to the option settings and rather than database name being default, provide your database name.

And can you confirm the connection in dataflow Gen 2 account itslef is being used to login manually and you are able to create the table?

Re: Dataflow Gen 2 Error - external policy action was denied

wjbreitenbach — Wed, 04 Dec 2024 15:51:23 GMT

Thanks for your help. The connection string was incorrect in the dataflow. I was using xxx..datawarehouse.fabric.microsoft.com from the warehouse instead of yyyy.database.fabric.microsoft.com,1433. Also the database name has a GUID appended to the name. I was missing it as well.

I found the correct connection string by going to Open in SQL Server Management Studio. Once those were entered, the dataflow successfully loaded the data.

Thanks,

Bill

Re: Dataflow Gen 2 Error - external policy action was denied

NandanHegde — Wed, 04 Dec 2024 15:55:42 GMT

Glad it helped!! So seems to be more on connection detail issues

Re: Dataflow Gen 2 Error - external policy action was denied

wjbreitenbach — Wed, 04 Dec 2024 16:02:26 GMT

yes, it was all connection string issues. I also did not expect the database name to have GUID appended to it.

Re: Dataflow Gen 2 Error - external policy action was denied

wjbreitenbach — Wed, 04 Dec 2024 19:26:11 GMT

I created another SQL database. Is there a way for both databases to show up under the same server name? Or do I have to create a separate connection for each one changing the default database in the connection string?

Re: Dataflow Gen 2 Error - external policy action was denied

dlevy — Wed, 04 Dec 2024 19:54:17 GMT

Right now, we randomly assign databases to our pool of logical servers. There is no way to group databases under the same logical server name.

We'd love to hear more about your scenario though. Please create an entry on https://ideas.fabric.microsoft.com/ and tell us about how you'd like it to work and how it would improve your business processes so we can consider it during our next round of planning.