Share via

How to reset password for Azure synapse dedicated SQL Pool "when Reset SQL Admin password" is disabled?

Apurva Tiwari 20 Reputation points
2024-05-24T08:15:33.2033333+00:00

Hello,

When I'm creating a linked service to fetch the data from Dedicated SQL Pool within the Azure Synapse Analytics workspace, it ask for the SQL authentication. Since I hadn't created the username and password at that time, I want to create or reset it now. The "Reset SQL Admin password" is disabled in my case. Could you please advise if there are any alternative procedures available to reset password?

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,553 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. PRADEEPCHEEKATLA-MSFT 82,196 Reputation points Microsoft Employee
    2024-05-24T09:32:37.6266667+00:00

    @Apurva Tiwari - Thanks for the question and using MS Q&A platform.

    This is an excepted behavior, when you selected Authentication method as "Use only Microsoft Entra ID authentication" while creating the workspace.

    How can I enable Reset SQL Admin password option?

    Note: Local authentication can be enabled/disabled later by an Azure Owner or Contributor of the Synapse workspace. You can also disable local authentication after a workspace is created through the Azure portal. Local authentication cannot be disabled until a Microsoft Entra admin is created for the Azure Synapse workspace.

    Synapse-ResetPassword

    According to official documentation: Use Microsoft Entra authentication for authentication with Synapse SQL

    By allowing only Microsoft Entra authentication, centrally manage access to Azure Synapse resources, such as SQL pools. To disable local authentication in Synapse during workspace creation, select Use only Microsoft Entra authentication as the authentication method. A SQL Administrator login will still be created but it will be disabled. Local authentication can be enabled later by an Azure Owner or Contributor of the Synapse workspace.

    There are two methods of authentication supported for Azure Synapse Analytics workspaces.

    Example: I'm trying to creating Azure Synapse Analytics workspaces named chepra with Authentication method as "Use both local and Microsoft Entra ID authentication" while creating the workspace. And another workspace named chepra2 Authentication method as "Use only Microsoft Entra ID authentication" while creating the workspace.

    Azure Synapse workspace with Authentication method as "Use both local and Microsoft Entra ID authentication" while creating the workspace.

    While creating selecting Authentication method as "Use both local and Microsoft Entra ID authentication" while creating the workspace.

    User's image

    Since this workspace supports Use both local and Microsoft Entra ID authentication, you will be able to see the Reset SQL admin password option as shown:
    User's image

    Azure Synapse workspace with Authentication method as "Use only Microsoft Entra ID authentication" while creating the workspace.

    User's image

    Since this workspace supports Use only Microsoft Entra ID authentication, you will be able to see the Reset SQL admin password is greyed out:

    User's image

    Hope this helps. Do let us know if you any further queries.

    If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.