Synapse dedicated pool database permissions for a USER and its association with azure data factory executions

Vivek Komarla Bhaskar 911

Hi,

What I have:

I have a single instance of dw200c dedicated pool used for both Production and Development activities.
I have a separate ADF services for Production & Development.
I created two sql user accounts for ADF data load activities.
I have multiple schema's inside the pool and each schema has got tables. Lets say for Production I have schemas Products, Services, Logistics & for Development I have Products_Test, Services_Test, Logistics_Test

What I wanted to achieve

I wanted to restrict sql user accounts access to either Production / Development.
One account having DDL, DML, TCL, DQL, DCL on only Production Schemas and the other only Development Schemas.

I was able to achieve this through granting CONTROL permissions at schema level to each user. After granting I'm able to execute DML/DDL statements directly from the dedicated sql pool but when I do this from ADF I keep getting the error 'There are no permissions to user to perform this operation'. I tried a lot and wasn't sure why and I ended up granting both the sql user accounts access to ADMINISTER DATABASE BULK OPERATIONS after which I'm not receiving any error on ADF. But doing this I ended up giving both the users access to complete database and I'm unable to restrict access at production/development schema level.

How can I achieve my requirement now?
What is the difference between CONTROL & ADMINISTER DATABASE BULK OPERATIONS?
Why CONTROL is able to perform DDL/DML from Synapse but not from ADF?

Please help me out here.

Bhargava-MSFT 30,906 Reputation points Microsoft Employee

2022-12-02T23:02:22.817+00:00
Hello @Vivek Komarla Bhaskar ,

Welcome to the MS Q&A platform.

Please correct me if my understanding is wrong. With restricted access(DDL, DML, TCL, DQL, DCL) SQL user accounts, you could connect to synapse dedicated pools via a synapse. But you were not able to connect via ADF.

Were you able to connect using SSMS with the restricted access SQL accounts?

When you say not working from ADF, do You mean the linked server connection is failing? If yes, could you please let us know the error message?

Did you create the SQL user account both on Master and the user DB?

ex: on master CREATE LOGIN [testwg] WITH PASSWORD=N'somepassword' GO on userdb CREATE USER testwg FROM LOGIN testwg GO EXEC sp_addrolemember 'db_datareader', 'testwg'

Could you please provide the SQL statement that you have used to create SQL users and the permissions?
Vivek Komarla Bhaskar 911 Reputation points

2022-12-04T14:54:00.933+00:00
Hi @Bhargava-MSFT ,

No you understood it wrong, plus issue is not related to user login. I was able to create a sql user exactly the way you mentioned in your comment and after that I granted CONTROL privelege to the user at each database.

NOTE: Data-reader permissions will not allow user to perform any DDL/DML and only allow to SELECT data and since this is not my requirement I granted user with CONTROL

A sql user with CONTROL privileges is able to perform DDL/DML/DCL operations when the user is connected to the database directly with SSMS/Azure Data Studio.

Created a linked service on ADF using the same user, but from ADF when I try to do any DDL/DML using data flows I get the error saying - "User don't have required privileges'.

I hope the issue is clear now. Let me know if in case of any further clarification is required
Bhargava-MSFT 30,906 Reputation points Microsoft Employee

2022-12-06T00:26:37.323+00:00

Hello @Vivek Komarla Bhaskar ,
Thank you so much for the clarification. Let me reproduce this from my end and get back to you.
Bhargava-MSFT 30,906 Reputation points Microsoft Employee

2022-12-06T18:02:04.777+00:00

Hello @Vivek Komarla Bhaskar ,
I was able to reproduce the issue from my end. I am reaching out to my internal team. I will get back to you once I hear back from them.
Bhargava-MSFT 30,906 Reputation points Microsoft Employee

2022-12-08T23:03:16.103+00:00

Hello @Vivek Komarla Bhaskar ,

Here is the response received from my internal team.

Since ADF is just a SQL Client executing scripts, if the scripts work from SSMS then the problem is either.

(a) Different scripts

(b) Different user

(c) Different DB

The DMV's will show both of these pieces of information.

https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-monitor

I hope this helps.
Vivek Komarla Bhaskar 911 Reputation points

2022-12-09T07:17:00.653+00:00

Hi @Bhargava-MSFT ,

It's difficult for me to understand what you meant by this. Could you simplify your words and explain the steps I need to take to meet my requirements?
Bhargava-MSFT 30,906 Reputation points Microsoft Employee

2022-12-09T16:34:31.287+00:00

Hello @Vivek Komarla Bhaskar ,
ADF is just like a client tool. If we could perform any operations using an SQL account on SSMS, the same operations could be done via ADF or synapse with the same permissions.
As per the suggestion from my internal team, the issue could be one of the three below

(a) Different scripts

(b) Different user

(c) Different DB

Please check if you are using a different T-sql statement or a different user account or pointing to a different DB on your dataflow

The easiest way to find the issue is to see what was being executed on the Synapse dedicated pool via DMVs.

In case, If you are unable to find out the issue, I would suggest filing a support request, and a support engineer can take a deeper look to troubleshoot the issue further.
If you don't have a support plan, please let me know. I can enable one-time free support for you to work closely on this matter.

I am looking forward to hearing from you.
Vivek Komarla Bhaskar 911 Reputation points

2022-12-13T09:20:44.247+00:00

Thanks @Bhargava-MSFT ,

Thank you. You were able to replicate the issue, is it because of any of the following?
(a) Different scripts
(b) Different user
(c) Different DB

In addition, please assign me a support engineer with a support plan, and let me know what the ETA will be for working on this issue when one is assigned.
Bhargava-MSFT 30,906 Reputation points Microsoft Employee

2022-12-13T16:22:21.127+00:00

Hello @Vivek Komarla Bhaskar ,
Sorry, I saw the same behavior as yours, but I got a confirmation from the product team that this could be due to user operation only and was advised to work with the support in case of any further assistance.
By the way, I have sent you a private message. Could you please check and respond?

Share via

Synapse dedicated pool database permissions for a USER and its association with azure data factory executions

Your answer