SQL Connection string shared accross all Onelakes

[jbshml]

Hello,

I'm currently facing an issue while trying to query my SQL analytics endpoints from my local machine using Python script.
In my selected workspace, I have 3 onelakes (named Bronze, Silver and Gold: created historically in this given order)

However,  all of them share the same SQL Connection string provided, making it impossible to reach all of them, and they all seems to be related to the Bronze's one. I even tried to remove the database inside the connection_string below, but I still can't get my Silver and Gold list of tables or whatever - Is it an expected behavior or something that I'm missing ?

Below the python code

import pyodbc
from azure.identity import InteractiveBrowserCredential, AzureCliCredential
import struct
from itertools import chain, repeat
import pandas as pd

sql_endpoint = "thecommonendpoint.datawarehouse.fabric.microsoft.com"

credential = AzureCliCredential() # replace by InteractiveBrowserCredential() if needed

token = credential.get_token("https://database.windows.net/.default")
token_as_bytes = bytes(token.token, "UTF-8")
encoded_bytes = bytes(chain.from_iterable(zip(token_as_bytes, repeat(0))))
token_bytes = struct.pack("<i", len(encoded_bytes)) + encoded_bytes

connection_string = (
    f"Driver={{ODBC Driver 18 for SQL Server}};Server={sql_endpoint},1433;Encrypt=Yes;TrustServerCertificate=No"
)

conn = pyodbc.connect(connection_string, attrs_before={1256: token_bytes})

query = "SELECT * FROM sys.tables;" #List tables

df = pd.read_sql(query, conn)

Thanks in advance for your enlightment

[Anonymous · Accepted solution]

Hi @jbshml 

You can think of SQL Connection string as a server name, and each workspace is equivalent to a server. Therefore, all SQL analytics endpoints, data warehouses, SQL databases and mirrored databases in the same workspace share the same SQL Connection string.

I connect to a SQL Connection string in SSMS, and it shows all of above Fabric items as databases on the server. Just like below. 

UPDATE:

Based on your python code, I used the following code, which was able to read data from the database specified in the connection string. I don't use Pandas here. 

import pyodbc
from azure.identity import InteractiveBrowserCredential, AzureCliCredential
import struct
from itertools import chain, repeat
import pandas as pd

sql_endpoint = "xxxxxxxxxxxxx.datawarehouse.fabric.microsoft.com"
database_name = "your_lakehouse_name"

# credential = AzureCliCredential() # replace by InteractiveBrowserCredential() if needed
credential = InteractiveBrowserCredential()

token = credential.get_token("https://database.windows.net/.default")
token_as_bytes = bytes(token.token, "UTF-8")
encoded_bytes = bytes(chain.from_iterable(zip(token_as_bytes, repeat(0))))
token_bytes = struct.pack("<i", len(encoded_bytes)) + encoded_bytes

connection_string = (
    f"Driver={{ODBC Driver 18 for SQL Server}};Server={sql_endpoint},1433;Database={database_name};Encrypt=Yes;TrustServerCertificate=No"
)

conn = pyodbc.connect(connection_string, attrs_before={1256: token_bytes})

cursor = conn.cursor()

cursor.execute("select * from sys.tables")

for row in cursor:
    print(row)

conn.close()

Using Pandas version:

connection_string = (
    f"Driver={{ODBC Driver 18 for SQL Server}};Server={sql_endpoint},1433;Database={database_name};Encrypt=Yes;TrustServerCertificate=No"
)

conn = pyodbc.connect(connection_string, attrs_before={1256: token_bytes})

query = "SELECT * FROM sys.tables"

df = pd.read_sql(query, conn)

conn.close()

print(df.head())

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[jbshml]

Thanks for your answer and the explanation about SQL analytics endpoints !

I tried requesting my selected lakehouse within the query to make it more flexible like this:

cursor.execute("SELECT * FROM Lakehouse_name.dbo.my_table")

Works like a charm,

Thanks again !