Azure Synapse - Set default serverless sql pool database to something else not "master"

Question

Azure Synapse - Set default serverless sql pool database to something else not "master"

Rodney Chan 36

My team is just starting to test drive Azure Synapse and most of us come from an Oracle background so forgive me if I dont quite make sense.

We noticed when we connect to the built in serverless SQL pool for Azure Synapse we all seem to be able to connect to the "master" database.

As I understand it, the master database is where you want to start if you want to create another database, logins etc. The master database is needed and can not be removed. Also best practice is to not create any personal objects on the master database as well.

So from what I know so far, it sounds like it may be a good idea to not have everyone by default log in to the master database. So a few questions

-Is there any concern allowing folks to just use the default "master" database?

-If there is a concern - I know we can create more then one database on the serverless SQL pool. But how can I get certain roles/users to default to another database and NOT be able to login to the master database at all?

Any insight or advice would be much appreciated

PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2022-07-11T05:31:42.067+00:00
Hello @Rodney Chan ,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you.

Accepted answer

4 additional answers

Your answer

PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2022-07-11T05:31:42.067+00:00

Hello @Rodney Chan ,

Following up to see if the below suggestion was helpful. And, if you have any further query do let us know.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you.

Answer 1

Hello @Rodney Chan ,

First scenario: If we grant a user "SQL permissions" (so we CREATE USER.... and provide a user ID/password) - I am assuming they would then be able to use a tool like SSMS for example to connect to the serverless SQL pool with this id/password.

Yes, you will be able to use via Synapse Studio and SSMS.

If this same user then says "hey I want to access the serverless SQL pool through Synapse Studio Workspace" - What would the minimum role they would need to do this?
You should require Synapse SQL Administrator role.

Second Scenario: I believe if we grant someone the "Synapse SQL Administrator" role from withinn Synapse Studio Workspace - This user can then access Synapse studio and also the serverless SQL pool from Synapse.

Synapse SQL Administrator: Full Synapse access to serverless SQL pools. Create, read, update, and delete access to published SQL scripts, credentials, and linked services. Includes read access to all other published code artifacts. Doesn't include permission to use credentials and run pipelines. Doesn't include granting access.

Can do all actions on SQL scripts
Can connect to SQL serverless endpoints with SQL db_datareader, db_datawriter, connect, and grant permissions

For this same user if they then want to connect via SSMS - Would they need to create a login to a database?

You can use Serverless SQL endpoint to login via SSMS

For Synapse Studio access what I do currently is login using my work email/password, then I connect to the Synapse workspace and then have access to the serverless SQL pool. I am not sure how I can connect now via SSMS for example.

As I said above, you can use Serverless SQL endpoint to login via SSMS

I guess I am confused between when to use SSMS or Synapse Studio

SSMS: You can use SQL Server Management Studio (SSMS) to connect to and query Synapse SQL in Azure Synapse Analytics through either serverless SQL pool or dedicated SQL pool resources.

Synapse Studio: Synapse Studio provides a single way for enterprises to build solutions, maintain, and secure all in a single user experience

Perform key tasks: ingest, explore, prepare, orchestrate, visualize
Monitor resources, usage, and users across SQL, Spark, and Data Explorer
Use Role-based access control to simplify access to analytics resources
Write SQL, Spark or KQL code and integrate with enterprise CI/CD processes

Actually one more thing that came to my mind - If someone has access to the serverless SQL pool through the Synapse Studio Workspace - Is there anyway to "restrict" which databases this user can access via the Synapse Studio Workspace?

As previously answered above, you can configure database -scoped permissions to the user to restrict the user to access any databases.

So for example if we have user A whom has access to the Synapse Studio Workspace serverless SQL pool and I want them to be admin for database A only - Is there anyway to have this user have access ONLY to the database A and NOT be able to see any other databases, like database B, C etc?

Yes, you can grant users access to a single serverless SQL database using database-scoped permissions.

Hope this will help. Please let us know if any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Schmidt, Christian 0 Reputation points

2023-09-22T19:32:17.6033333+00:00

I can't make sense of this response. It looks like a question sometimes, but looks like an answer in other places. It seems like there are multiple "voices" talking at different times but it doesn't provide enough context to make heads or tails of it.

Answer 2

Hi @PRADEEPCHEEKATLA

Thank you for following up - I have one more question while I have you and I do apologize as you have helped significantly thus far.

My team was attempting to build a pipeline in the dedicated SQL pool based on a CSV file that we have uploaded.

When we were testing the connection to our ADLS we noticed the following error: Connection Failed

Error code
24200
Details
ADLS Gen2 operation failed for: Storage operation '' on container 'testconnection' get failed with 'Operation returned an invalid status code 'Forbidden''. Possible root causes: (1). It's possible because the service principal or managed identity don't have enough permission to access the data. (2). It's possible because some IP address ranges of Azure Data Factory are not allowed by your Azure Storage firewall settings. Azure Data Factory IP ranges please refer https://learn.microsoft.com/en-us/azure/data-factory/azure-integration-runtime-ip-addresses.. Account: 'azsadasynsnd01'. ErrorCode: 'AuthorizationPermissionMismatch'. Message: 'This request is not authorized to perform this operation using this permission.'. RequestId: 'e6ec2de6-a01f-0018-0e0f-97e2a6000000'. TimeStamp: 'Wed, 13 Jul 2022 23:25:51 GMT'..
Operation returned an invalid status code 'Forbidden'
Activity ID: bda6886c-c272-4a15-8299-8756b6a81f55.

I am pretty sure we provisioned our roles and access appropriately but it seems to still be complaining - How can we resolve this error?

Is there another permission to assign?

PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2022-07-14T04:52:25.807+00:00

Hello @Rodney Chan ,

The scope of your question is different compared to the original scope of the question asked.
I would recommend creating a new thread on the same forum with as much details about your issue as possible. That would make sure that your issue has better visibility in the community.

Answer 3

Hello @Rodney Chan ,

Thanks for the question and using MS Q&A platform.

The Synapse Administrator is by default given the SQL db_owner role for serverless SQL pools in the workspace as well.

Access to SQL pools for other users is controlled by SQL permissions. Assigning SQL permissions requires SQL scripts to be run on each SQL database post-creation. The following are examples that require you to run these scripts:

To grant users access to the serverless SQL pool, 'Built-in', and its databases.
To grant users access to dedicated SQL pool databases.
To grant access to a dedicated SQL pool database, scripts can be run by the workspace creator or any member of the workspace1_SynapseAdministrators group.
To grant access to the serverless SQL pool, 'Built-in', scripts can be run by any member of the workspace1_SQLAdmins group or the workspace1_SynapseAdministrators group.

For more details, refer to Azure Synapse Analytics - Grant access to SQL pools.

Hope this will help. Please let us know if any further queries.

------------------------------

Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

Answer 4

Hi @PRADEEPCHEEKATLA

I appreciate the reply and actually do have a few more questions.

When we talk about "SQL Permissions" - I imagine we create a user id/password then an end user would be able to use this to connect to the serverless SQL pool and which ever database from a tool like SSMS for example.

So two scenarios/questions:

First scenario: If we grant a user "SQL permissions" (so we CREATE USER.... and provide a user ID/password) - I am assuming they would then be able to use a tool like SSMS for example to connect to the serverless SQL pool with this id/password.

If this same user then says "hey I want to access the serverless SQL pool through Synapse Studio Workspace" - What would the minimum role they would need to do this?

Second Scenario: I believe if we grant someone the "Synapse SQL Administrator" role from withinn Synapse Studio Workspace - This user can then access Synapse studio and also the serverless SQL pool from Synapse.

For this same user if they then want to connect via SSMS - Would they need to create a login to a database?

For Synapse Studio access what I do currently is login using my work email/password, then I connect to the Synapse workspace and then have access to the serverless SQL pool. I am not sure how I can connect now via SSMS for example.

I guess I am confused between when to use SSMS or Synapse Studio

Actually one more thing that came to my mind - If someone has access to the serverless SQL pool through the Synapse Studio Workspace - Is there anyway to "restrict" which databases this user can access via the Synapse Studio Workspace?

So for example if we have user A whom has access to the Synapse Studio Workspace serverless SQL pool and I want them to be admin for database A only - Is there anyway to have this user have access ONLY to the database A and NOT be able to see any other databases, like database B, C etc?

Any advice much appreciated

Answer 5

Rodney Chan 36

Hi @PRADEEPCHEEKATLA

Thank you very much for the insight so far, its helped me along quite a bit - But I actually have another question or two please

So it looks like based on this document: https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/sql-authentication?tabs=serverless#administrative-accounts

Database -scoped permissions to the user to restrict the user to access any databases is possible which is fine.

So heres a scenario:

Lets say I have user A - They are part of the Synapse Studio Workspace and can login. From here they are given Synapse SQL Admin role - So they can access either the serverless SQL pool.

Now lets say from here, our admin creates a new database A using the following script

CREATE LOGIN [******@domainname.net] FROM EXTERNAL PROVIDER;

User A has an AAD account, so they can authenticate and get to database A no problem via the Synapse Studio Workspace.

Now lets say another new database B is created but this time they use the following script

CREATE LOGIN USER_A WITH PASSWORD = '<strong_password>';

If they are using a SQL script from within the Azure Synapse Studio workspace - Is there a way to input a "login" with a user ID and password? As I dont seem to be able to find anywhere to input a login, at least no GUI interface.

Or is there no way to input a login user id/password through Azure Synapse Studio Workspace? Which means they HAVE to "CREATE LOGIN.....EXTERNAL PROVIDER"?

I know through SSMS before doing anything you input a login user id/password, but not sure if the same thing is possible through Synapse Studio.

Thank you!

PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2022-07-07T10:43:20.82+00:00

Hello @Rodney Chan ,

Unfortuantely, you cannot login to Azure Synapse studio with Non-administrator users.

Note: Non-administrator users are allowed to login only via SSMS.

For more details, refer to Create Login and User for Server less SQL Pool in Azure Synapse Analytics.

Hope this helps.
Rodney Chan 36 Reputation points

2022-07-07T14:08:05.07+00:00

Hi @PRADEEPCHEEKATLA

Ah this is starting to make a lot more sense to me now!

My team mate actually provisioned our Azure Synapse Workspace and he has been adding me on as a "user" to the workspace. Since he made the workspace he is the "admin" to everything and we have been struggling with a number of permission issues when trying to add me as a "user".

Just to clarify for the option to "CREATE LOGIN......" - Should this only be used for non-admin users to login via SSMS? So non-admin users do not use SQL script in Synapse Studio Workspace?

We actually just hit the following error

User does not have permission to perform this action. Changed database context to '<database name>'.

How would we go about resolving this error?

Thank you!
PRADEEPCHEEKATLA 90,651 Reputation points Moderator

2022-07-08T04:39:25.367+00:00
Hello @Rodney Chan ,

To give additional users full control of the database, make them a member of the db_owner fixed database role.

In Azure SQL Database or synapse serverless, use the ALTER ROLE statement.

ALTER ROLE db_owner ADD MEMBER Mary;

Hope this helps.

Share via

Azure Synapse - Set default serverless sql pool database to something else not "master"

4 additional answers

Your answer