Share via

Not Able to connect to Azure Synapse dedicated SQL Pool using Service Account from Azure Synapse Spark Notebook by pyodbc Library

Dev, Surya 20 Reputation points
2023-08-29T12:28:30.1+00:00

Hi,

We are not able to connect to Azure Dedicated SQL Pool using the Service account from the Azure Synapse Spark notebooks. For establishing this account we are using the pyodbc Library.

ERROR MSG - InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'XXX'. (18456) (SQLDriverConnect)")

SYNAPSE NETWORKING SIDE CONFIG:

  1. Public network access - Disable
  2. We have a created a private endpoint for synapse environment to the dedicated SQL pool. However, Service account is still now working

User's image

Sample Code us following

import pyodbc 
server = 'tcp:XXX,XXX' 
database = "XXX" 
username = "XXX" 
accessKey = "XXX"
password = mssparkutils.credentials.getSecretWithLS("AzureKeyVault", accessKey)
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';ENCRYPT=yes;UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,696 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee
    2023-08-30T22:08:13.12+00:00

    @Dev, Surya Welcome to Microsoft Q&A forum and thanks for reaching out here.

    The error message you are seeing indicates that the login for the service account is failing when trying to connect to the Azure Synapse dedicated SQL pool using the pyodbc library in Synapse spark.

    Have you double checked if that service account has necessary permissions to establish a connection to your dedicated SQL pool? If not, I would recommend verifying that first.

    Let us know how it goes.

    0 comments