How accessing to a serverless SQL pool with a Microsoft Entra user without the related login

pmscorca 987

Hi,

I need to access to a serverless SQL pool with my Microsoft Entra user (with MFA).

My user is a Synapse Administrator for the related Synapse workspace.

In the pool I haven't any login built with my Microsoft Entra user.

Using SSMS I cannot access to the serverless SQL pool.

With a SQL admin user I've tried to create a login respect to my Microsoft Entra user obtaining the following error:
User's image

For another Synapse workspace with my Microsoft Entra user, that is a Synapse Administrator, I can access to the related serverless SQL pool without having the corresponding login in the pool.

Any suggests to me for solving this issue, please? Thanks

phemanth 10,240 Reputation points Microsoft Vendor

2024-08-27T15:24:59.6633333+00:00
@pmscorca

Welcome to the Microsoft Q&A and thank you for posting your questions here

It looks like you’re encountering an issue with accessing the serverless SQL pool using your Microsoft Entra user account. Here are a few steps you can try to resolve this:

Verify Synapse Role Assignments: Ensure that your Microsoft Entra user is assigned the necessary Synapse roles, such as Synapse Administrator and Synapse SQL Administrator.

Check Azure Active Directory Authentication: Make sure that Azure Active Directory (AAD) authentication is enabled for your Synapse workspace. This can be done during workspace creation by selecting "Use only Microsoft Entra authentication".

Storage Permissions: Ensure that your Microsoft Entra user has the required permissions to access the underlying storage files. This includes being a member of roles like Storage Blob Data Reader or Storage Blob Data Contributor.

Access Control Lists (ACLs): Verify that the necessary ACLs are set on the storage files and directories to allow your Microsoft Entra user to read and execute the files.

Use SSMS with AAD Authentication: When connecting to the serverless SQL pool using SSMS, ensure you are using Azure Active Directory - Universal with MFA authentication method.

Review Error Message: The error message you received indicates that only connections established with Azure Active Directory accounts can create database scoped credentials. Ensure that you are logged in with an AAD account that has the necessary permissions.

If these steps don’t resolve the issue, you might want to check the detailed documentation on setting up access control for Azure Synapse Analytics.

Hope this helps. Do let us know if you any further queries.
pmscorca 987 Reputation points

2024-08-27T16:54:31.8866667+00:00

Hi, thanks for your reply but I've accomplished some suggested checks.

My Microsoft Entra user is a Synapse Administrator, but in the serverless SQL pool it doesn't exist a related login.

As a first thing, as I said, I've tried to access using SSMS with my Microsoft Entra user (with MFA) obtaining a such error

Alternatively, I can access to the serverless SQL pool using SSMS with a SQL admin user; so I've tried to create a login respect to my Microsoft Entra user obtaining the error already reported above.

For another Synapse workspace with my Microsoft Entra user, that is a Synapse Administrator, I can access to the related serverless SQL pool without having the corresponding login in the pool.

Now, repeat my question: any suggests to me for solving this issue, please? Thanks

Another thing, accessing with a Microsoft Entra user isn't the same as accessing with a system-assigned managed identity (I've created a different post for this subject). Thanks
pmscorca 987 Reputation points

2024-08-27T18:25:41.7033333+00:00

Hi,

a my colleague has added my Microsoft Entra user as a login in the serverless SQL pool, so I can access to it by SSMS.

Now, I can try to create a specific database but I obtain this error:

When I access to the pool using SSMS I can see this situation:

I repeat that my Microsoft Entra user is a Synapse Administrator.

So, how could I create a specific database on the serverless SQL pool with my Microsoft Entra user in a such situation? Thanks
phemanth 10,240 Reputation points Microsoft Vendor

2024-08-28T15:41:42.8533333+00:00
@pmscorca

The error message “CREATE DATABASE permission denied in database ‘master’” indicates that your Microsoft Entra user does not have the necessary permissions to create a database in the ‘master’ database context.

Here are some steps to resolve this:

Check Role Assignments: Ensure your Microsoft Entra user is assigned the necessary roles, such as db_owner or db_creator, within the Synapse workspace.

Use Contained Database Users: Create a contained database user for your Microsoft Entra account. Connect to the database using your Microsoft Entra administrator account and run: SQL
CREATE USER [your_user@domain.com] FROM EXTERNAL PROVIDER; ALTER ROLE db_owner ADD MEMBER [your_user@domain.com];

Grant Permissions: If you have access to a higher-privileged account, you can grant the necessary permissions to your Microsoft Entra user: SQL
GRANT CREATE DATABASE TO [your_user@domain.com];
Verify Synapse Workspace Settings: Ensure there are no restrictions in your Synapse workspace settings that prevent database creation.
pmscorca 987 Reputation points

2024-08-28T16:50:37.8766667+00:00

Hi, thanks for your reply.

I repeat that my Microsoft Entra user is a Synapse Administrator and with this role I should do all in the serverless SQL pool like accessing to it, creating or dropping a database, etc.

In the RBAC roles don't exist a db_owner or db_creator role.

Is having the Synapse Administrator role enough to create a database in a serverless SQL pool or not? Are there any complementary RBAC roles respect to the Synapse Administrator role for creating/dropping a database?

Thanks
phemanth 10,240 Reputation points Microsoft Vendor

2024-08-29T15:12:18.56+00:00
@pmscorca

The Synapse Administrator role does grant extensive permissions, including full access to serverless SQL pools. However, there are some nuances to be aware of:

Synapse Administrator Role: This role provides full access to serverless and dedicated SQL pools, Data Explorer pools, Apache Spark pools, and Integration runtimes. It includes permissions to create, read, update, and delete published code artifacts.

SQL Permissions: While the Synapse Administrator role covers many permissions, specific SQL permissions might still be required for certain actions within the SQL pool. For example, creating or dropping databases might require additional SQL permissions that are not explicitly covered by Synapse RBAC roles.

Complementary Roles: There are no specific RBAC roles like db_owner or db_creator within Synapse RBAC. However, the Synapse SQL Administrator role provides full access to serverless SQL pools, including creating, reading, updating, and deleting SQL scripts and credentials.

Azure Permissions: In some cases, Azure RBAC roles such as Azure Owner or Azure Contributor on the resource group might be necessary to perform certain administrative tasks.

Given your situation, it seems that your Synapse Administrator role should be sufficient for most tasks. However, if you’re encountering specific permission issues, it might be worth checking if there are any additional SQL permissions required or if there are any Azure RBAC roles that need to be assigned.

If you continue to face issues, reviewing the detailed documentation on Azure Synapse RBAC roles and setting up access control might provide further insights.
pmscorca 987 Reputation points

2024-08-29T16:40:59.0766667+00:00

Hi, I hoped in a specific reply for a such issue and not a reference to MS documentation.

Thanks
phemanth 10,240 Reputation points Microsoft Vendor

2024-09-02T17:00:33.0033333+00:00
@pmscorca

It seems that the Synapse Administrator role should be sufficient for creating a database in a serverless SQL pool. However, the error message "CREATE DATABASE permission denied in database ‘master’" suggests that there might be some additional SQL permissions required.

One possible solution is to try creating the database using the CREATE DATABASE statement with the AUTHORIZATION clause, specifying your Microsoft Entra user as the owner of the database. This might help resolve the permission issue.

CREATE DATABASE [mydatabase] AUTHORIZATION [your_user@domain.com];

Replace [mydatabase] with the desired database name and [your_user@domain.com] with your Microsoft Entra user.

If this doesn't work, please provide more details about your Synapse workspace settings, Azure RBAC roles, and SQL permissions, and I'll try to help you troubleshoot further.
phemanth 10,240 Reputation points Microsoft Vendor

2024-09-04T15:58:11.36+00:00

@pmscorca We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
pmscorca 987 Reputation points

2024-09-04T20:26:02.4633333+00:00

Hi, the real goal is to access to the serverless SQL pool and create a database using the Synapse RBAC roles.

When a workspace is created in the serverless SQL pool doesn't exists any logins or database users.

Reading the MS documentation it seems that having the Synapse Administrator role is enough to access to the serverless SQL pool. In my case I need to be a Synapse Administrator plus a Synaspe SQL Administrator. The Synapse Administrator role includes also the Synapse SQL Administrator permissions.

Is Synapse SQL Administrator a complementary role than the Synapse Administrator one?

Does it occur any Synapse Analytics update?

For another Synapse workspace with my Microsoft Entra user, that is only a Synapse Administrator and not a Synapse SQL Administrator, I can access to the related serverless SQL pool without having the corresponding login in the pool.

It is very important to know if the Synapse SQL Administrator role has some permissions not available for Synapse Administrator role.
phemanth 10,240 Reputation points Microsoft Vendor

2024-09-06T17:22:15.7166667+00:00
@pmscorca

Regarding your questions:

The Synapse SQL Administrator role is not a complementary role to the Synapse Administrator role. Instead, the Synapse Administrator role includes the Synapse SQL Administrator permissions. This means that having the Synapse Administrator role should be sufficient for accessing the serverless SQL pool and creating a database.

It's possible that there might have been some updates to Synapse Analytics that could be affecting the behavior. However, based on the MS documentation, having the Synapse Administrator role should be enough to access the serverless SQL pool.

The fact that you can access the serverless SQL pool in another Synapse workspace with your Microsoft Entra user, which is only a Synapse Administrator, suggests that there might be some differences in the configuration or settings between the two workspaces.

To clarify, the Synapse Administrator role should include all the necessary permissions to access the serverless SQL pool and create a database. If you're still facing issues, it might be worth checking the Synapse workspace settings, Azure RBAC roles, and SQL permissions to ensure that there are no restrictions or additional requirements.

Share via

How accessing to a serverless SQL pool with a Microsoft Entra user without the related login

Your answer