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 @v-gchenna-msft,
Thank you for your assistance.
I already have an app registered and have the tenant_ID, client_id and client_secret. These were the values I was using:
Client_Id = 1 above
Tenant_id = 2 above
Client secret = 3 or 4 above (Not sure which)?
I then added this code as it relates to the above registered app:
--DB
CREATE USER [Appenate] FROM EXTERNAL PROVIDER;
GO
ALTER ROLE db_owner ADD member [Appenate]
GO
I then ran this code:
# 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()
BUT the same error occurred 😞
Any ideas?
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
Did you manage to get this working? I think I'm essentially doing the same thing as you (the DP-600 course) and now attempting to connect to my own instance.
As you can see from the above, I'm able to connect to the database and run the query without any error.
However, when I then change the query to select from a table in the DB, I get the following:
I'm basically trying to run the contents of a table into the lakehouse via a notbook - I can probably do this via a Gen2 pipleline and use my existing content gateway, but would be much better to run via notebook.
Thanks
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.
Helpful resources
New forum boards available in Synapse
Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Fabric Monthly Update - May 2024
Check out the May 2024 Fabric update to learn about new features.
