• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Blog
  • Power Platform
    • Power Apps
    • Power Automate
    • Power BI
    • Power Virtual Agents
  • Dynamics 365
  • Azure
  • About Me
Joe Gill Logo

Joe Gill

Microsoft MVP - Power Platform Consultant

Synapse – SQL Admin

24th January 2023 by Joe Gill

It looks like the rights of the SQL Admin user in the database created by Synapse Link for Database have recently been changed. Prior to 2023, the SQL Admin user account has able to query all the external tables created by Synapse Link for Dataverse. Now if you query a Dataverse external table using the SQL Admin account  you will get an error   “Cannot find the CREDENTIAL  []  because it does not exist or you do not have permission.”  This error indicates that the account does not have access to the underlying storage.

Synapse - SQL Admin - Joe Gill

Having previously installed Synapse Link for Dataverse on numerous occasions I was a bit perplexed. It took me a while to figure out the difference. By running the following query in the master database of my existing Synapse Link for Dataverse environments I discovered they contained a credential record that provided access to the storage account.

Synapse - SQL Admin - Joe Gill

To give the SQL Admin user access to the storage I created a credential for the Managed Identity of the Synapse service. The url comprises of the storage account’s Data Lake Storage endpoint and the container for the Dataverse environment.

CREATE CREDENTIAL [https://joepartnersynapse.dfs.core.windows.net/dataverse-powerplatfor-org2b19a248]
WITH IDENTITY = 'Managed Identity';

The SQL Admin account is a SQL user and so it cannot be given permissions to the storage account using Azure Active Directory. With the credentials in place when the SQL Admin user runs a query the Managed Identity delegates permissions to the underlying storage.

I have not noticed anybody reporting this issue. I have seen it in two different tendencies so I am assuming this is a platform change and not just me! It could well be that not many people use the SQL Admin account and this is highly likely as Azure Active Directory is the preferred authentication method for Synapse. I have covered this in another post.

Share This On Social:
  • Tweet
  • Synapse - SQL Admin - Joe Gill

Filed Under: Power Platform

Primary Sidebar

More to See

Power Platform Requests – Base Request Capacity

17th December 2020 By Joe Gill

Power Automate Desktop

Power Automate Desktop – UI Flow

26th November 2020 By Joe Gill

Synapse Link for Dataverse Security

16th December 2022 By Joe Gill

Power Fx -Power Virtual Agents

28th November 2022 By Joe Gill

Footer

Joe Gill

Microsoft Business Applications MVP – Power Platform, Dynamics 365 and Azure.

An architect with over twenty years experience designing and developing technology solutions. Specializing in the Microsoft technology stack including Power Platform, Dynamics 365 and Azure. Microsoft MVP Profile

Connect on Social

Useful Links

  • Home
  • Blog
  • About Joe Gill
  • Power Platform
  • Dynamics 365
  • Azure

Featured Posts

Power Platform Requests – Base Request Capacity

Power Automate Desktop – UI Flow

Synapse – SQL Admin

Synapse Link for Dataverse Security

Power Fx -Power Virtual Agents

Synapse Link for Dataverse – Append-Only

© 2023 · Joe Gill