search

pulumi / pulumi-azure-native

Azure Native Provider
Apache License 2.0
126 stars 33 forks source link

azure-native:datafactory:Dataset's created via Pulumi for AzureSqlTable w/ schema fail #1428

Open mdhomer opened 2 years ago

mdhomer commented 2 years ago

Hello!

Issue details

Steps to reproduce

  1. Create source/dest AzureSqlDatabase's, LinkedServices, DataFactory.Dataset's & DataFactory.Pipeline via Pulumi IAC C#:

  2. Run Copy pipeline.

ErrorCode=SqlOperationFailed,'
Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
Message=A database operation failed with the following error: 
'The specified schema name "e9fddac9-d988-4d86-a27f-10d09c1236dd@2e902142-66ac-41e7-905c-d550a763e5b9" either does not exist or you do not have permission to use it.',
Source=,''Type=System.Data.SqlClient.SqlException,
Message=The specified schema name "e9fddac9-d988-4d86-a27f-10d09c1236dd@2e902142-66ac-41e7-905c-d550a763e5b9" either does not exist or you do not have permission to use it.,Source=.Net SqlClient Data Provider,
SqlErrorNumber=2760,
Class=16,
ErrorCode=-2146232060,
State=1,
Errors=[{Class=16,Number=2760,State=1,Message=The specified schema name "e9fddac9-d988-4d86-a27f-10d09c1236dd@2e902142-66ac-41e7-905c-d550a763e5b9" either does not exist or you do not have permission to use it.,},],'

Expected: Using the same Pulumi sourcecode for the module will provide the same behaviour in the UI. Actual: Causes permissions/schema access error as above & copy will not complete for table.

Might be related to: https://stackoverflow.com/questions/59098013/data-factory-error-trying-to-use-staging-blob-storage-to-pull-data-from-azure-sq

danielrbradley commented 2 years ago

Hello! To clarify; do you mean that you have created the equivilant pipeline in the UI and it has been granted the permissions to the database automatically?

Secondly, in relation to the StackOverflow link you shared, does manually creating the permission within the database resolve the issue?

Just to rule out other causes, were both the UI and Pulumi versions of the pipeline created from the same identity (user)?

mdhomer commented 2 years ago

Hi @danielrbradley, Thanks for taking a look at this!

Yes I created the same pipeline/copy activity & datasets via the UI. It didn't have any issues pulling down the source SQL servers schema as part of the dataset creation & with these Datasets the copy pipeline functioned as expected.

For clarification, once I created via the UI & pulumi imported the datasets the C# source was identical including the schema json-style blocks. I then removed them( source Datasets) from the DataFactory UI & ran pulumi up to re-create them via the same imported source code.

However when running the copy after the pulumi dataset creation it looks like it can't find the schema (or doesn't have access to create it). I'm not sure how it does it under-the-hood but perhaps it attempts to creates a new schema on the source Database at pipeline runtime? If so is this expected behaviour, as the table schema should already exist for the source DB?

I haven't tried adding the database permissions yet, are you aware how permissive this would need to be. Ideally I wouldn't want to provide DF itself high level privileges on the source if its not needed.

Just to rule out other causes, were both the UI and Pulumi versions of the pipeline created from the same identity (user)?

This is a good point, however it was my own user AD account that I was modifying the UI Datasets & also running pulumi up locally with.