Synapse Serverless - error when trying Windows Server AD -based login/user tries to query "Cannot find the CREDENTIAL 'WorkspaceIdentity'

Question

Having trouble in Synapse Serverless when trying to create an Windows Server AD based login/user.

These were my creation steps for the AAD users;

USE Master
GO
CREATE LOGIN [Datawarehouse - Blabla]  FROM EXTERNAL PROVIDER;

use DataWareHouseServerless
GO
CREATE USER [Datawarehouse - Blabla] FROM LOGIN  [Datawarehouse - Blabla]


use DataWareHouseServerless
ALTER ROLE db_datareader ADD MEMBER [Datawarehouse - Blabla]

A user from the security group [Datawarehouse - Blabla] is able to connect. But when they try to query a view in DataWareHouseServerless, they get the error:

Msg 15151, Level 16, State 1, Procedure ViewTheyTriedToQuery, Line 1 [Batch Stare Line 0] 
Cannot find the CREDENTIAL 'WorkspaceIdentity', because it does not exist or you do not have 
permission. 

Msg 4413, Level 16, State 1, Line 282
Could not use view or function 'ViewTheyTriedToQuery' because of binding errors. 

1. Looking visually in SSMS - I see the login, and I see the user.
2. Creating a SQL-server based login/user worked fine.
3. I'm using a Windows Server AD security group for the Synapse Administrators - and
   that's working fine / those folks can query.

Answer 1

Msg 15151, Level 16, State 1, Procedure ViewTheyTriedToQuery, Line 1 [Batch Stare Line 0] Cannot find the CREDENTIAL 'WorkspaceIdentity', because it does not exist, or you do not have permission.

The error you are facing it says the user don't have permission on scoped credentials or it does not exist.

In Synapse Serverless SQL the views are on the external table or external file for that we need to create credentials. To access those credentials user must have permission on it.

To provide permission on scoped credential to user you can use below query:

GRANT REFERENCES ON DATABASE SCOPED CREDENTIAL::<credential_name> TO <User>;

If still issue persist you can create credentials with below query:

CREATE DATABASE SCOPED CREDENTIAL <credential_name>
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
 SECRET = 'SAS Token';

Msg 4413, Level 16, State 1, Line 282 Could not use view or function 'ViewTheyTriedToQuery' because of binding errors.

This occurs because a view may become out of date as a result of modifications made to the underlying objects on which it depends.

To resolve this check if the underlying table got changed or not if yes then alter the view accordingly..