I agree. Contributer seems to high of a permission to just read data. I need to check, but believe I am using reader, and in fact, leverage the managed identity of the Synapse workspace to grant read permissions rather than individual user access to the underlining storage.
I just did precisely this last week at one of my clients while setting up an azure synapse link on their D365 CE. As an external consultant, I did this for myself. Helpful video for other folks.
Thanks for the vid, we were just trying this out yesterday. I actually found that you can also create login on the instance level (instance of serverless, the irony), something that Synapse does automatically for members of workspace Sql admin rbac role. Lastly, why not just storage blob data reader?
Love the video! I got to say though that I really wish Microsoft would have a way for us to grant the user access just to the SQL endpoint and not the underlying storage account container. This would seem to be an issue if I have a reporting (gold level) container and I don't want everyone to be able to see all of the data present, just what they've been granted in SQL.
One query: If in Synapse the public access been disabled and private endpoint been added, can we still connect in this way? Or we need to have a VM inside the VNET where the Private ENDPOINT is there and installed SSMS/Azure Data Studio in that VM and only then can access?
I would like to build a multi-tenant delta lake. I am wondering if I should create a lake database per tenant, or create one lake database with multiple tenant-related users? Any idea's? We have +/- 800 tenants.
By giving Adam the data contributor role over your storage account, haven't you given him the ability to delete Containers/Blobs in that storage account? He could then accidentally (or maliciously) delete all the source data that your serverless sql db is comprised of.
In case the purpose of the access is loading data into Power BI or Excel and the delay of a scheduled refresh is acceptable, then I prefer creating a Power BI dataflow and give users access to the dataflow: easier to manage, and easier to teach to most Excel or Power BI user. And way easier cost control.
This video is very helpful! Is it possible to connect to tables in the Synapse lake database also? Somehow I was only able to view the contents in SQL database but received " Microsoft SQL: Cannot find the CREDENTIAL [likeofsynapselakedb/*.paruet], because it does not exist or you do not have permission. "
![Azure Synapse Analytics: Intro to Azure Data Lake [Introduction to Synapse Analytics - Ep. 4]](http://i.ytimg.com/vi/LY6O7sfeF1E/mqdefault.jpg)
![Azure Synapse Analytics: Intro to Azure Data Lake [Introduction to Synapse Analytics - Ep. 4]](/img/tr.png)
You both are the best ambassadors for big data on Azure.
Love this video with all the backstage between you guys! And learning around this frequently asked scenario!
Would Storage Blob Reader permission work as well, since Contributers gives edit and delete permissions as well?
I agree. Contributer seems to high of a permission to just read data. I need to check, but believe I am using reader, and in fact, leverage the managed identity of the Synapse workspace to grant read permissions rather than individual user access to the underlining storage.
I used Blob Reader and it works fine for us
I just did precisely this last week at one of my clients while setting up an azure synapse link on their D365 CE. As an external consultant, I did this for myself. Helpful video for other folks.
Is it possible to provision access at folder level or Table level? I see this access was granted on entire workspace
Instead of adding individual user can we give access to security group with multiple users ?
Thanks for the vid, we were just trying this out yesterday.
I actually found that you can also create login on the instance level (instance of serverless, the irony), something that Synapse does automatically for members of workspace Sql admin rbac role.
Lastly, why not just storage blob data reader?
Great video,
Does this also work on views?
Seems the user can only access external tables but cannot access view
Is that possible to do the same for a service principal or a managed identity ?
Love the video! I got to say though that I really wish Microsoft would have a way for us to grant the user access just to the SQL endpoint and not the underlying storage account container. This would seem to be an issue if I have a reporting (gold level) container and I don't want everyone to be able to see all of the data present, just what they've been granted in SQL.
where did Adam get that "Choose your weapon" shirt? Tis great.
One query: If in Synapse the public access been disabled and private endpoint been added, can we still connect in this way? Or we need to have a VM inside the VNET where the Private ENDPOINT is there and installed SSMS/Azure Data Studio in that VM and only then can access?
Hi quick question , I uploaded some Csv files and wanted query using the ssms , i myself is unable to connect ,what steps should I take
More collab videos pleeeeeeeease! You keep being awesome too!
Damn! Worked this out for myself 2 months ago, wish I'd had this video at the time 😄
I would like to build a multi-tenant delta lake. I am wondering if I should create a lake database per tenant, or create one lake database with multiple tenant-related users? Any idea's? We have +/- 800 tenants.
Um.... Yeah, Kerberos would TOTALLY fix that issue! 🤣
Good to see ya, @Adam!
--Gene.
Hey guys! Love your content! One question, when is the Producers list updated? Asking for a (VDC) friend :)
Million thanks
By giving Adam the data contributor role over your storage account, haven't you given him the ability to delete Containers/Blobs in that storage account? He could then accidentally (or maliciously) delete all the source data that your serverless sql db is comprised of.
Same question here.
In case the purpose of the access is loading data into Power BI or Excel and the delay of a scheduled refresh is acceptable, then I prefer creating a Power BI dataflow and give users access to the dataflow: easier to manage, and easier to teach to most Excel or Power BI user. And way easier cost control.
thanks
This video is very helpful! Is it possible to connect to tables in the Synapse lake database also? Somehow I was only able to view the contents in SQL database but received " Microsoft SQL: Cannot find the CREDENTIAL [likeofsynapselakedb/*.paruet], because it does not exist or you do not have permission.
"