Hi Patrick, Tnx for the great video! I’ve learned a lot throughout the years from the Guy in a Cube channel. I was happy to find this video, since it was exactly what I was looking for (I want to make data available to an external company, that can’t work with Azure AD, and want SQL authentication), however, what you are showing doesn’t work exactly as you mention. The problem is this: The solution works, BUT, only when using the “GRANT CONTROL TO ” statement. I don’t particularly like this option though, since it gives too much control to the end user over the database (which I don’t intent to give them). The command “GRANT REFERENCES ON CREDENTIAL::[cred] TO ” seems unusable. When I run this statement within the desired database, I get this message: Permissions at the server scope can only be granted when the current database is master Then if I run this statement with the database on master, it (logically, because I created the database scoped credential in a specific database, not on the master) says this: Cannot find the CREDENTIAL 'cred', because it does not exist or you do not have permission. So then I thought, I’ll just create the database scoped credential on the master database, but that also doesn’t work, because it will show this: CREATE DATABASE SCOPED CREDENTIAL is not supported in master database. That made me decide to create a regular credential (so “CREATE CREDENTIAL”), which does work, but then it seems impossible to actually use this credential to create an external datasource in any of my databases, since it will always show this (and creating an external datasource within the master is not supported): The specified credential cannot be found or the user does not have permission to perform this action. It therefore feels as if the “GRANT REFERENCES ON CREDENTIAL” option is kind of a dead end, whereas I would really prefer to use that over the CONTROL option. My current solve is to just create a separate database, over which I grant the intended end user the CONTROL rights, but I would still prefer to work with the grant references option. Tnx for your time, I’m really curious to hear your (or someone of the Synapse team) their response! Kind regards, Lucas
Please could you do a video on the pros and cons of Azure Active Directory authorization VS SQL authorization for Synapse? I've had terrible trouble giving just read only access with Azure AD. Came to this video to look for another option!
Hi Patrick, was great to see you guys are SQLBits! While this video helped, but we still ran into an issue with SQL Auth on Serverless We're trying to use SQL Auth in Synapse, on top of a Synapse Link from Dataverse, where we've ended up having to hit the CSV level in SQL Views definition to grant permissions for it to work. This appears to need fully defined column names in the Openrowset (in appropriate order) since the Column names arent in CSVs, they exist elsewhere only in the Lake database tables (these are the magic ones created by MS Synapse Link from DV). We know that Delta lake version of Synapse Link to DV is coming.. but in meantime, we have hard coded views to maintain with Dataverse Devlopment in paralell, its a bit gross, is there any way to make the SQL Auth work with this Data better/easier/dynamic at this level? (for context we're using SQL Auth as have a Azure Analysis Services Model reading data from Serverless Synapse.)
Awesome! I’m trying to wrap my head around the functional differences between a lake database and a SQL database. They seem like they can do very similar actions. When is it appropriate to choose lake or SQL db?
Depends on the skillset. Lakehouse is the future because of scaling and costs, however, requires reskilling all your developers to use serverless with data lake methodology.
Do i see it right that then the EDS Connection is always using the Managed Identity? So even if i am logged in with my AD User? I would like that AD Users use their own security that have been granted in the Storage And for SQL Users i would like to be able to use different storage permissions (e.g. different Read permission on Folders). Also i am wondering how i change an Existing external Data Source? Drop is not possible as it is used and Alter is not supported (this is thrown as Error).
Hi Patrick,
Tnx for the great video! I’ve learned a lot throughout the years from the Guy in a Cube channel. I was happy to find this video, since it was exactly what I was looking for (I want to make data available to an external company, that can’t work with Azure AD, and want SQL authentication), however, what you are showing doesn’t work exactly as you mention. The problem is this:
The solution works, BUT, only when using the “GRANT CONTROL TO ” statement. I don’t particularly like this option though, since it gives too much control to the end user over the database (which I don’t intent to give them).
The command “GRANT REFERENCES ON CREDENTIAL::[cred] TO ” seems unusable. When I run this statement within the desired database, I get this message:
Permissions at the server scope can only be granted when the current database is master
Then if I run this statement with the database on master, it (logically, because I created the database scoped credential in a specific database, not on the master) says this:
Cannot find the CREDENTIAL 'cred', because it does not exist or you do not have permission.
So then I thought, I’ll just create the database scoped credential on the master database, but that also doesn’t work, because it will show this:
CREATE DATABASE SCOPED CREDENTIAL is not supported in master database.
That made me decide to create a regular credential (so “CREATE CREDENTIAL”), which does work, but then it seems impossible to actually use this credential to create an external datasource in any of my databases, since it will always show this (and creating an external datasource within the master is not supported):
The specified credential cannot be found or the user does not have permission to perform this action.
It therefore feels as if the “GRANT REFERENCES ON CREDENTIAL” option is kind of a dead end, whereas I would really prefer to use that over the CONTROL option. My current solve is to just create a separate database, over which I grant the intended end user the CONTROL rights, but I would still prefer to work with the grant references option.
Tnx for your time, I’m really curious to hear your (or someone of the Synapse team) their response!
Kind regards,
Lucas
Please could you do a video on the pros and cons of Azure Active Directory authorization VS SQL authorization for Synapse? I've had terrible trouble giving just read only access with Azure AD. Came to this video to look for another option!
Hi Patrick, was great to see you guys are SQLBits!
While this video helped, but we still ran into an issue with SQL Auth on Serverless
We're trying to use SQL Auth in Synapse, on top of a Synapse Link from Dataverse, where we've ended up having to hit the CSV level in SQL Views definition to grant permissions for it to work.
This appears to need fully defined column names in the Openrowset (in appropriate order) since the Column names arent in CSVs, they exist elsewhere only in the Lake database tables (these are the magic ones created by MS Synapse Link from DV).
We know that Delta lake version of Synapse Link to DV is coming.. but in meantime, we have hard coded views to maintain with Dataverse Devlopment in paralell, its a bit gross, is there any way to make the SQL Auth work with this Data better/easier/dynamic at this level?
(for context we're using SQL Auth as have a Azure Analysis Services Model reading data from Serverless Synapse.)
Patrick is always in a hurry
Good stuff, Patrick! :{>
Thanks Andy! 👊
Awesome! I’m trying to wrap my head around the functional differences between a lake database and a SQL database. They seem like they can do very similar actions. When is it appropriate to choose lake or SQL db?
Depends on the skillset. Lakehouse is the future because of scaling and costs, however, requires reskilling all your developers to use serverless with data lake methodology.
Creat video, this helped a ton!
Do i see it right that then the EDS Connection is always using the Managed Identity? So even if i am logged in with my AD User?
I would like that AD Users use their own security that have been granted in the Storage
And for SQL Users i would like to be able to use different storage permissions (e.g. different Read permission on Folders).
Also i am wondering how i change an Existing external Data Source? Drop is not possible as it is used and Alter is not supported (this is thrown as Error).