1

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?

Improve this question

1 Answer 1

Reset to default
2

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

By the way, you can create an AAD group, and add users or other service principals to the group. Then add the group service principal to your SQL Server. In this way, all members of the group will be able to connect to your SQL.
how do I know the client id and secret?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.