Can not connect to views in Synapse Analytics

I can not connect and retrieve data from a specific view of a database which resides in Synapse Analytics. Database is as same as this screenshot shown

I want to

connect to database: DataverseEnhanced(I already provide database im the source code, but, it does not to work)
How do I retrieve data from a view, let us say, account.

here is my source code

import pyodbc

server = '**********-ondemand.sql.azuresynapse.net'
database = 'DataverseEnhanced'
username = '********'
password = '*********'   
driver= '{ODBC Driver 17 for SQL Server}'


def main():
    connection_string = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"
    with pyodbc.connect(connection_string) as conn:
        with conn.cursor() as cursor:
            #cursor.execute("SELECT * FROM sys.databases")
            query_1 = 'SELECT * FROM  account'
            cursor.execute(query_1)
            
            query_2 = 'SELECT * FROM sys.databases'
            cursor.execute(query_2)
            # Fetch the results
            results = cursor.fetchall()
            # Print the results
            print(results)
            
            
if __name__ == "__main__":
    main()

Thank you guys for any clue.

------------------ AGAIN ------------------------------------------

I just wonder if the solution posted here works for me.

my Database and Views are created in different way.

create a azure synapse analytics workspace, and storage accounts
link all tables which are in dataverse,
and database, dataverse_dynam*****, is created
run a script file to create all views.

USE [dataverse_dynamicsprod_b07143b1d36945e0ae198260d58696] --Specify the name of the database corresponding to your Synapse Link for Dataverse

--=================================================================================================
--PROVIDE INPUT PARAMETERS:
--=================================================================================================
DECLARE
    @EnrichedViewDatabase sysname, --Specify the name of the database in which views with enriched entities will be created
    @EnrichedViewSchema sysname, --Specify the name of the database schema in which views with enriched entities will be created
    @EnrichedColumnSuffix varchar(50),  --Specify the suffix for columns enriched with human-readable descriptions. For example, the suffix of "label" will change a statecode column in the base table to a statelabel column in the enriched view.
    @LanguageCode varchar(10), --Specify the language code for localized labels. For example, English - United States is 1033 (https://docs.microsoft.com/en-us/openspecs/office_standards/ms-oe376/6c085406-a698-4e12-9d4d-c3b0ee3dbc4a)
    @BaseTableSuffix varchar(50), --If applicable, specify the suffix in the names of the base tables or views (e.g., '_partitiond'). The default is an empty string.
    @PreviewOnly bit --Indicate whether to preview the SQL Script (without creating the views) = 1 ; Create views = 0;

SET @EnrichedViewDatabase = 'DataverseEnhanced'
SET @EnrichedViewSchema = 'dbo'
SET @EnrichedColumnSuffix = 'label'
SET @LanguageCode = 1033
SET @BaseTableSuffix = ''
SET @PreviewOnly = 0 

--=================================================================================================
-- Do not edit the script below this point
--=================================================================================================

--Get column metadata from the Lake Database managed by Synapse Link for Dataverse
--The column metadata will be stored as a JSON document in a scalar variable
--This is needed as a workaround for the limitation of not allowing system objects to be used in distributed queries
DECLARE @ColumnMetadata nvarchar(MAX), @ColumnMetadataSQL nvarchar(MAX)

--Define the SQL statement to retrieve column metadata from the Lake Database managed by Synapse Link for Dataverse
--Results will be stored as a JSON document in a variable
SET @ColumnMetadataSQL = 'SET @ColumnMetadataOUT = (
SELECT TABLE_SCHEMA, 
    TABLE_NAME, 
    COLUMN_NAME, 
    ORDINAL_POSITION, 
    DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = ''dbo''
    AND TABLE_NAME NOT IN (''OptionsetMetadata'', ''GlobalOptionsetMetadata'',''StateMetadata'',''StatusMetadata'', ''TargetMetadata'')
    AND TABLE_NAME LIKE ''%' + @BaseTableSuffix + '''
FOR JSON AUTO)'
DECLARE @ParmDefinition NVARCHAR(MAX);  
SET @ParmDefinition = N'@ColumnMetadataOUT NVARCHAR(MAX) OUTPUT';  
EXECUTE sp_executesql @ColumnMetadataSQL, @ParmDefinition, @ColumnMetadataOUT=@ColumnMetadata OUTPUT;  

--Declare a variable to store a SQL statement for creating enriched views
DECLARE @SQL nvarchar(MAX) = ''

; WITH CM AS (
--Parse column metadata variable and construct a table based on its content
SELECT JSON_VALUE(CM.value, '$.TABLE_SCHEMA') AS TableSchema,
     JSON_VALUE(CM.value, '$.TABLE_NAME') AS TableName,
     LEFT(JSON_VALUE(CM.value, '$.TABLE_NAME'), LEN(JSON_VALUE(CM.value, '$.TABLE_NAME'))-LEN(@BaseTableSuffix)) AS EntityName,
     JSON_VALUE(CM.value, '$.COLUMN_NAME') AS ColumnName,
     CAST(JSON_VALUE(CM.value, '$.ORDINAL_POSITION') AS INT) AS OrdinalPosition,
     JSON_VALUE(CM.value, '$.DATA_TYPE') AS DataType
FROM OPENJSON (@ColumnMetadata) AS CM
)

, OSM AS (
--Get Option Set Metadata
SELECT DISTINCT 
    EntityName, 
    OptionSetName, 
    QUOTENAME(EntityName + '_' + OptionSetName) AS Alias
FROM dbo.[OptionsetMetadata]
WHERE LocalizedLabelLanguageCode = @LanguageCode
)

...

So, there are no crediential-name. that means, two GRANT can not be executed

any other suggestions or advices?

Much appreciated

Solution

Can not connect to views in Synapse Analytics

As per information you provided, you want to read data from view in synapse Serverless SQL pool.

As we know synapse serverless SQL pool mostly handles only data with external sources and to read the data from that our user need the permission on the object and the scoped credentials.

To give the access to user you can use the below code:

GRANT  REFERENCES  ON  DATABASE  SCOPED  CREDENTIAL::<credential-name> to <user>

GRANT  SELECT  ON  OBJECT::<view-name> TO <user>;

Otherwise you will face this error:

enter image description here

Code with which I created sample view:

#created scoped credentials
CREATE  DATABASE  SCOPED  CREDENTIAL AppCred
WITH  IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = '<sas-token>';
#created eexternal data source
CREATE  EXTERNAL  DATA  SOURCE [fsn2p_dlsg2p_dfs_core_windows_net]
WITH (
LOCATION = '<file-location>',
CREDENTIAL = AppCred
)
GO
#created view
CREATE  VIEW demoview1 as  SELECT * from
OPENROWSET(
BULK  'file1.csv',
DATA_SOURCE = 'fsn2p_dlsg2p_dfs_core_windows_net',
FORMAT = 'CSV',
HEADER_ROW = TRUE
) WITH (
[Id] VARCHAR (500),
[name] VARCHAR (100))AS [result];

Code to access the view:

import  pyodbc
# Define the connection string
server = '<synapse-workspace-name>-ondemand.sql.azuresynapse.net'
database = '<database-name>'
username = '<username>'
password = '<password>'
driver= '{ODBC Driver 17 for SQL Server}'
connection_string = f"DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password}"
# Establish a connection to the SQL pool
conn = pyodbc.connect(connection_string)
# Create a cursor object
cursor = conn.cursor()
# Execute a SQL query
query = 'SELECT * FROM demoview1'
cursor.execute(query)
# Fetch the results
results = cursor.fetchall()
# Print the results
print(results)

Output:

enter image description here