Permissions error in SSMS with a dataverse datalake

Jesse Ruiz 0

Hello,

I'm having an issue with creating a PowerBI report - specifically connecting to a data source. I created a view in a datalake in SSMS based on dataverse data but keep experiencing this issue. My colleague cannot replicate the error and I'm not sure where else to look.

Here is the error:
(The credential definitely exists. )

DataSource.Error: Microsoft SQL: Cannot find the CREDENTIAL 'dynamics365_financeandoperations_1234', because it does not exist or you do not have permission.
Could not use view or function 'Retail.vwRetailTrans' because of binding errors.
Details:
    DataSourceKind=SQL
    DataSourcePath=tcp:abs123-ondemand.sql.azuresynapse.net,1433;DataWarehouse-Serverless
    Message=Cannot find the CREDENTIAL 'dynamics365_financeandoperations_1234', because it does not exist or you do not have permission.
Could not use view or function 'Retail.vwRetailTrans' because of binding errors.
    ErrorCode=-2146232060
    Number=15151
    Class=16
    State=1



Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Cannot find the CREDENTIAL 'dynamics365_financeandoperations_1234', because it does not exist or you do not have permission.
Could not use view or function 'Retail.vwRetailTrans' because of binding errors..
An unexpected exception occurred.
'.

I have tried GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[{credential_name}] TO [{user}];
But this also led to an error that said user doesn't exist.

Amira Bedhiafi 26,261 Reputation points

2024-11-05T18:54:29.32+00:00
It sounds like the issue is with the database-scoped credential permissions in Azure Synapse Analytics, and since the error states that the credential either "does not exist" or there is no permission to access it, here are a few steps to troubleshoot:

Verify Credential Existence: Confirm that the credential dynamics365_financeandoperations_1234 exists in your database. You can check this by querying the sys.database_scoped_credentials table:
SELECT * FROM sys.database_scoped_credentials WHERE name = 'dynamics365_financeandoperations_1234';

Check Role Membership: Ensure your user account has the necessary permissions to access the credential. You might need at least ALTER ANY CREDENTIAL permission or REFERENCES permission on the credential for your role.

GRANT REFERENCES Permission: Ensure you’re using the correct syntax and that your username is spelled correctly. You can try running:
GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::[dynamics365_financeandoperations_1234] TO [your_user];

Check for Binding Errors in the View: The error about binding issues in Retail.vwRetailTrans suggests that the view might reference objects or credentials that your user lacks access to. You might need to recompile or check dependencies on this view.

Refresh Metadata and Credentials in Power BI: Sometimes, Power BI caches credentials. Try clearing permissions and cache under File > Options and Settings > Data Source Settings.
Jesse Ruiz 0 Reputation points

2024-11-05T19:37:26.5066667+00:00

I get another error when trying to change or grant permissions:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
phemanth 11,200 Reputation points Microsoft Vendor

2024-11-06T15:51:16.9133333+00:00

@Jesse Ruiz

Thanks for using Microsoft Q&A forum and posting your query.

By default SQL users doesn't have permission to use any credential. we should grant permission to SQL account use that credential

please see this document: Access files on storage in serverless SQL pool - Azure Synapse Analytics | Microsoft Learn

EXECUTE AS somepoweruser

CREATE CREDENTIAL [https://<storage_account>.dfs.core.windows.net/<container>] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sas token';

GRANT REFERENCES ON CREDENTIAL::[https://<storage_account>.dfs.core.windows.net/<container>] TO sqluser

Hope this helps. Do let us know if you any further queries.
phemanth 11,200 Reputation points Microsoft Vendor

2024-11-07T17:59:43.28+00:00

@Jesse Ruiz 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.
Jesse Ruiz 0 Reputation points

2024-11-07T21:03:57.5933333+00:00

Have not been able to solve it....

It might be due to an account or identity accidentally being deleted by someone.

Share via

Permissions error in SSMS with a dataverse datalake

Your answer