Azure Synapse login using database users gives Azure blob storage error

I'm currently working on an Azure function that connects to an Azure Synapse serverless database that takes data from a blob storage in Azure.

The function was working perfectly fine, connecting to Synapse with a user that is created in the database (security tab inside the database, users), that was developed by a third party, there is not much documentation about it. Turns out that the user is limited to only be able to gather data from one view (filtered_info) in one database (infodb). If I try to pull information from any other view or table, it will get me this error:

Database connection failed: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Content of directory on path 'https://azuresystem.dfs.core.windows.net/curated/macro/infodb/_delta_log/.' cannot be listed. (13807) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Statement(s) could not be prepared. (8180)")

I already tried to create the user on a different database, granted permissions and roles, but it keeps on showing the same error. Something found in the documentation is as follows (but did not work or I was not able to run it successfully):

GRANT SELECT ON OBJECT dbo. TO [user]
ALTER ROLE secure_role ADD MEMBER [user]

And after granting permissions and adding roles, the query always takes down the request showing the same error that it cannot access the information on the blob storage.

I ran out of options and that's why I come here to see if you can help me get through it.

Or if you can tell me how to login using a regular azure user with email@mydomain.com, because it gives me a different error when I try to connect using it:

Database connection failed: ('HY000', '[HY000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot open server "mydomain.com" requested by the login. The login failed. (40532) (SQLDriverConnect); [HY000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Cannot open server "mydomain.com" requested by the login. The login failed. (40532)')

Don't know if this is relevant, specially for this last alternative, Synapse and the blob storage are in the same tenant, but, the Azure function is in a different tenant.

ERROR: Blob Storage Access Issue (Error 42000)

The error indicates that the database user do not have the necessary permissions to access the underlying storage. Since Synapse serverless SQL pools use external tables, you need to explicitly grant access to the storage account. Here are some steps to help troubleshoot the issue:

Check External Data Source Credentials Make sure the external data source is properly set up with a database-scoped credential. You can verify this using:

 SELECT * FROM sys.external_data_sources;

If you find the credential is missing: Then you can use the below:

CREATE DATABASE SCOPED CREDENTIAL [WorkspaceIdentity]
WITH IDENTITY = 'Managed Identity';

Also make sure you granted access to synapse workspace as Storage blob data contributor for the ADLS Gen2 account.

ERROR: Login Failure with Regular Azure User (Error HY000)

You can use the below:

If your server name is myserver123.sql.azuresynapse.net and your login is john.doe@example.com,
then you may need to provide your login as:

john.doe@example.com@myserver123.sql.azuresynapse.net

username = 'user@email.com'
password = 'password'  
dbport = 1433
server = f'myserver.sql.azuresynapse.net'  
database = 'dbname'  
driver = '{ODBC Driver 18 for SQL Server}'


connection_string = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password};PORT={dbport};Authentication=ActiveDirectoryPassword'
print(f"Connecting with connection string: {connection_string}...")

try:

    conn = pyodbc.connect(connection_string)
    if not conn: 
        errstr = f"ERROR: connection not made to {connection_string}"
        print(f"{errstr}")
        sys.exit()

    cursor = conn.cursor()