Azure SQL Database
An Azure relational database service.
5,413 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 8,645 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 8,645 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 8,645 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.
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