CODEWITHSUNDEEP
azureazure-active-directoryazure-sql-database
Panda
Panda

Reputation: 103

Token-based authentication support for Azure SQL DB using Azure AD auth

According to this page.

https://learn.microsoft.com/en-us/archive/blogs/sqlsecurity/token-based-authentication-support-for-azure-sql-db-using-azure-ad-auth

AAD Token-based authentication to access Azure SQL DB is supported only if client is under windows environment.

Could MacOS and Linux support AAD Token-based authentication to access Azure SQL DB?

https://github.com/mkleehammer/pyodbc/issues/228

    token = context.acquire_token_with_client_credentials(
        database_url,
        azure_client_id,
        azure_client_secret
    )
    print(token)

    tokenb = bytes(token["accessToken"], "UTF-8")
    exptoken = b''
    for i in tokenb:
        exptoken += bytes({i})
        exptoken += bytes(1)
    tokenstruct = struct.pack("=i", len(exptoken)) + exptoken
    tokenstruct

    SQL_COPT_SS_ACCESS_TOKEN = 1256
    CONNSTRING = "DRIVER={};SERVER={};DATABASE={}".format("ODBC Driver 17 for SQL Server", prod_server, prod_db)

    db_connector = pyodbc.connect(CONNSTRING, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: tokenstruct})

This is the code I run under MacOS and it is python.

I keep getting this issue

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

Anyone has a idea?

Upvotes: 1

Views: 3934

Answers (1)

Jack Jia
Jack Jia

Reputation: 5549

It seems that you have not added your application service principal to your Azure SQL database .

What you need to do is to:

1. Enable AAD authentication for your Azure SQL Server. Please select an AAD user in this step.

enter image description here

2. Connect to your Azure SQL Database with the user account you set in step 1.

3. Add your application service principal to your SQL Server, and alert appropriate role to it.

CREATE USER [Azure_AD_principal_name] FROM EXTERNAL PROVIDER;
EXEC sp_addrolemember 'db_owner', 'Azure_AD_principal_name';

Here, the Azure_AD_principal_name should be the application's name.

4. Connect to your Azure SQL Database with AAD

enter image description here

Upvotes: 2

Related Questions