Forum Discussion

Ryan_OC's avatar
Ryan_OC
Helper I
2 years ago
Solved

Error connecting to SQL Database using Fabric notebook

I was completing the Microsoft Learn module titled Ingest data with Spark and Microsoft Fabric notebooks - Training | Microsoft Learn   I tried to connect to my SQL DB using the example in the modu...
data engineering
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

Ryan_OC's avatar
Ryan_OC
Helper I
2 years ago

Hi Anonymous, 

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?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

    • Ryan_OC's avatar
      Ryan_OC
      Helper I
      2 years ago

      OK I gave it a try and I received this:
      [{'a': 1, 'b': 2}, {'a': 2, 'b': 3}] and it stated it was successful. Was it successful? 

      SO now do I need to change the QueryStr to access a table?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Great Ryan_OC .

        Yes it was successfull, you were able to access the database. You can change the QueryStr to access a table.