Connection MSSql using Azure AAD - Service Principal Auth
Issue
I am facing trouble connecting to an MS SQL Database hosted on Azure SQL Server.
Dependencies
- Python@3.10.0
- Django@4.2.21
- pyodbc@4.0.39
Other details
In the Azure environment:
- I have registered an application in my
Directory
- I have created
Client credentials
for my application - I have created a
SQL Logical Server
calledorganisation
and aSQL Database
calleduniversity
- I have added my application as the admin of the SQL server and assigned my role as a
Reader
- I have white-listed my public IP
CREATE USER [<<application_name>>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<<application_name>>];
P.S. I was able to connect using the same credentials in VS Code with no problem. I ran telnet your_server_name.database.windows.net 1433
and was able to find my SQL server.
In the development environment(local environment):
- I am running my application on a docker
- I am using MSAL to create an access token
- I connected using the following code:
# Connection string
CONNECTION_STRING: Final[str] = "DRIVER={{ODBC Driver 18 for SQL Server}};SERVER=tcp:{server_url},{port};DATABASE={database_name};Encrypt=yes;TrustServerCertificate=yes;"
# Create an access token for login using
# 1. Tenant ID
# 2. Application ID
# 3. Secret value
AUTHORITY_URL: Final[str] = "https://login.microsoftonline.com/{tenant_id}"
application_instance: ConfidentialClientApplication = (
ConfidentialClientApplication(
client_id= {application_id},
authority= AUTHORITY_URL,
client_credential= {secret_value},
)
)
ACCESS_TOKEN: Final[str] = application_instance.acquire_token_for_client(scopes=["https://database.windows.net/.default"])["access_token"]
TOKEN: Final[bytes] = bytes(ACCESS_TOKEN, "utf-16-le")
# Connect to Database using pyodbc
connection: pyodbc.Connection = pyodbc.connect(
CONNECTION_STRING,
attrs_before={1256: TOKEN},
timeout=30,
)
Traceback
Traceback (most recent call last):
pyodbc.connect(
pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection because an error was encountered during handshakes before login. Common causes include the client attempting to connect to an unsupported version of SQL Server, the server being too busy to accept new connections, or a resource limitation (memory or maximum allowed connections) on the server. (26) (SQLDriverConnect)')
I would like to:
- connect to my remote MS Sql using Python script
- fetch information from a table in the DB
Reference - This is not working