Azure SQL Database
An Azure relational database service.
5,768 questions
Facing issues to connect with synapse workspace/azure sql db via oauth
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(166.4, 23%, 26%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESK%3C/text%3E%3C/svg%3E)
SUBHAM KUMAR
0
Reputation points
I want to connect to and access my Synapse Workspace and SQL Pool using OAuth. For this purpose, I have created an app in Azure and assigned the following API permissions:
- Microsoft Graph:
- Directory.Read.All (Delegated)
- Directory.ReadWrite.All (Delegated)
- Directory.AccessAsUser.All (Delegated)
- User.Read
- User.Read.All
- User.ReadWrite
- Azure Storage:
- user_impersonation (Delegated)
- Azure SQL Database:
- user_impersonation (Delegated)
I am requesting access tokens with the following parameters:
- Authorization URL:
https://login.microsoftonline.com/<tenant-id>/oauth2/authorize - Token URL:
https://login.microsoftonline.com/<tenant-id>/oauth2/token - Scope:
-
https://graph.microsoft.com/.default -
https://management.azure.com/.default-
https://storage.azure.com/user_impersonation -
https://sql.azuresynapse-dogfood.net/user_impersonation
-
With these parameters, I am able to obtain access tokens. However, when I use these access tokens to connect to my Synapse Workspace and Azure SQL Database via SQLServerDriver with the connection property accessToken set to the fetched access token value, I receive the following error:
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '<token-identified principal>'. Incorrect or invalid token.
I would like to add that with username and password i could connect to workspace and sql pool so we can rule out connectivity issues
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(288, 61%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP%3C/text%3E%3C/svg%3E)
phemanth 11,295 Reputation points Microsoft Vendor2024-07-15T11:00:53.8633333+00:00 Thanks for the question and using MS Q&A platform.
Here are some troubleshooting steps you can consider:
- Check for Azure service outages: There might be known issues that are affecting the service. You can check the Microsoft Azure Service Dashboard for any reported problem.
- Check your firewall settings: The dedicated SQL pool communicates over port 1433. If you’re trying to connect from within a corporate network, outbound traffic over port 1433 might not be allowed by your network’s firewall.
- Check your virtual network/service endpoint settings: If you see errors “40914” and “40615”, refer to the error description and resolution in the Common error messages reference section.
- Check for the latest drivers: Verify that you’re using the latest version of the tool and drivers to connect to your dedicated SQL pool.
- Check your connection string: Verify that your connection strings are set correctly.
- Re-register the workspace’s managed identity: If the “Allow Pipelines” option is already checked, you must uncheck this setting and save. Then, check the “Allow Pipelines” option and save.
- Check network access: Ensure that public network access is enabled for your Synapse Workspace.
for more details Troubleshoot: https://learn.microsoft.com/en-us/troubleshoot/azure/synapse-analytics/dedicated-sql/connectivity/dsql-conn-dropped-connections
If none of these steps resolve the issue, do let us know
-
SUBHAM KUMAR 0 Reputation points
2024-07-17T04:56:12.17+00:00 @phemanth can u tell me what API permissions i should be selecting and with what scope i should be requesting for my use case
-
phemanth 11,295 Reputation points Microsoft Vendor
2024-07-18T07:36:10.19+00:00 for Read-only access to data in Synapse Workspace and Azure SQL Database
API Permissions:
- Azure Synapse Analytics:
user_impersonation - Azure SQL Database:
user_impersonation
Scope:
-
https://management.azure.com/.default(for Synapse Workspace management) -
https://sql.azuresynapse-dogfood.net/user_impersonation(for SQL Database access)
for: Write and modify data in Synapse Workspace and Azure SQL Database
API Permissions:
- Azure Synapse Analytics:
user_impersonation - Azure SQL Database:
user_impersonation
Scope:
-
https://management.azure.com/.default(for Synapse Workspace management) -
https://sql.azuresynapse-dogfood.net/user_impersonation(for SQL Database access)
for Manage Synapse Workspace resources (e.g., create, update, delete pipelines)
API Permissions:
- Azure Synapse Analytics:
user_impersonation
Scope:
-
https://management.azure.com/.default(for Synapse Workspace management) : Read-only access to data in Synapse Workspace and Azure SQL Database
- Azure Synapse Analytics:
-
phemanth 11,295 Reputation points Microsoft Vendor
2024-07-19T09:26:18.05+00:00 @SUBHAM KUMAR 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.
-
SUBHAM KUMAR 0 Reputation points
2024-07-25T04:53:27.1466667+00:00 @phemanth i tried with above suggestions but its not working
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 8%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EST%3C/text%3E%3C/svg%3E)
Smaran Thoomu 16,890 Reputation points Microsoft Vendor2024-07-29T05:38:29.2133333+00:00 @SUBHAM KUMAR I agree that this issue looks strange and I wasn't able to reproduce this issue. If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan, please let us know here.
-
SUBHAM KUMAR 0 Reputation points
2024-07-31T02:52:57.67+00:00 Hi @phemanth , can u also tell me should we assign any role to the app in resources like workspace, db , sql pool. What should be the roles that the app should be assigned to in the following resources- workspace,db,sql pool?
-
phemanth 11,295 Reputation points Microsoft Vendor
2024-08-05T08:58:14.78+00:00 To connect to your Synapse Workspace and SQL Pool using OAuth, you need to assign the correct roles to the app in the respective resources.
For the Synapse Workspace, you should assign the Synapse RBAC roles to the app. The specific roles you need will depend on your use case, but some common roles include:
- Synapse Administrator: This role provides full access to the Synapse Workspace, including the ability to create and manage resources.
- Synapse Contributor: This role provides read and write access to the Synapse Workspace, including the ability to create and manage resources.
- Synapse Reader: This role provides read-only access to the Synapse Workspace.
For the Azure SQL Database, you should assign SQL roles to the app. The specific roles you need will depend on your use case, but some common roles include:
- db_owner: This role provides full access to the database, including the ability to create and manage database objects.
- db_datareader: This role provides read-only access to the database.
- db_datawriter: This role provides read and write access to the database.
For the SQL Pool, you should assign Synapse RBAC roles to the app. The specific roles you need will depend on your use case, but some common roles include:
- Synapse SQL Administrator: This role provides full access to the SQL Pool, including the ability to create and manage database objects.
- Synapse SQL Contributor: This role provides read and write access to the SQL Pool, including the ability to create and manage database objects.
- Synapse SQL Reader: This role provides read-only access to the SQL Pool.
In addition to assigning roles to the app, you also need to ensure that the app has the correct API permissions and scope to access the resources.
for more details follow:https://learn.microsoft.com/en-us/azure/synapse-analytics/security/how-to-manage-synapse-rbac-role-assignments
-
phemanth 11,295 Reputation points Microsoft Vendor
2024-08-06T14:29:34.3966667+00:00 @SUBHAM KUMAR 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.
Sign in to comment
2 answers
Sort by: Most helpful
-
Deleted
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more
-
Deleted
This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Comments have been turned off. Learn more