Forum Discussion
Error connecting to SQL Database using Fabric notebook
Hi Ryan_OC ,
I have tried connecting to it using pyodbc, Can you give a try?
import pyodbc tenant_id = "tenant_id" service_principal_id = f"client_id@{tenant_id}" # important to include your @fully qualified domain or tenant_id service_principal_secret = "client_secret" # Define your SQL Server details server_name = "server_connection_string" database_name = "database_name" queryStr = 'SELECT 1 AS a, 2 AS b UNION ALL SELECT 2 AS a, 3 AS b' # Define the SQL Server ODBC connection string conn_str = ( f"DRIVER={{ODBC Driver 18 for SQL Server}};" f"SERVER={server_name};" f"DATABASE={database_name};" f"UID={service_principal_id};" f"PWD={service_principal_secret};" f"Authentication=ActiveDirectoryServicePrincipal" ) # Establish the connection conn = pyodbc.connect(conn_str) # Execute a query cursor = conn.cursor() cursor.execute(queryStr) resultList = cursor.fetchall() resultColumns = columns = [column[0] for column in cursor.description] print(str([dict(zip(columns, row)) for row in resultList]))
Incase if this doesn't help, I will try to do a deeper investigation.
Thank you
Hi Ryan_OC ,
Thanks for using Fabric Community,
As I understand you are trying to connect Azure SQL using pyspark in Fabric via Service Principle (tenant_id + client_id +client_secret)
Here are the steps you can follow inorder to connect:
Step 1: Create a Service Principle in Azure Active Directory (Microsoft Entra ID).
Step 2: Create your tenant_id, client_id and client_secret.
Step 3: Execute below queries in your Azure SQL Query Editor.
--DB
CREATE USER [v-gchennaSP] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_owner ADD member [v-gchennaSP]
GO
Note: you should enter your App Registration name over here and also need to login using AAD account.
Step 4: After executing successfully, try to check for an entry in this table using below query.
SELECT * FROM sys.database_principals
WHERE name = 'v-gchennaSP';
Step 5: Then use below code to connect
# Placeholders for Azure SQL Database connection info
server_name = "your_server_name.database.windows.net"
port_number = 1433 # Default port number for SQL Server
database_name = "your_database_name"
table_name = "YourTableName" # Database table
client_id = "YOUR_CLIENT_ID" # Service principal client ID
client_secret = "YOUR_CLIENT_SECRET" # Service principal client secret
tenant_id = "YOUR_TENANT_ID" # Azure Active Directory tenant ID
# Build the Azure SQL Database JDBC URL with Service Principal (Active Directory Integrated)
jdbc_url = f"jdbc:sqlserver://{server_name}:{port_number};database={database_name};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;Authentication=ActiveDirectoryIntegrated"
# Properties for the JDBC connection
properties = {
"user": client_id,
"password": client_secret,
"driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"tenantId": tenant_id
}
# Read entire table from Azure SQL Database using AAD Integrated authentication
sql_df = spark.read.jdbc(url=jdbc_url, table=table_name, properties=properties)
# Show the Azure SQL DataFrame
sql_df.show()
Hope this is helpful. Please feel free incase of further queries.
Do you have any solution to connect to fabric database via Managed Identity / workspace identity instead of client ID & Secret?
Hi,
facing the same issue -
have you somehow resolve connection using Workspace Principal?
Thx!
Roman
