Can't connect to SQL using app registration credentials

[michaelcapizzi]

I am following the steps here, but I'm not able to access my server and database.

import adal
import pyodbc
import struct

# credentials generated by app-registration
USER = "XXXXXXXXXXXXXXXXXX"
PW = "XXXXXXXXXXXXXXXXXX"

database_url = "https://database.windows.net/"

authority_url = "https://login.microsoftonline.com"
tenantId = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
authority_url = authority_url + "/" + tenantId

context = adal.AuthenticationContext(authority_url, api_version=None)

token = context.acquire_token_with_client_credentials(
    database_url,
    USER,
    PW
)
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", SERVER, DATABASE)

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

cursor = conn.cursor()
cursor.execute(QUERY)
row = cursor.fetchone()

The token is generated (accessToken below):

{'tokenType': 'Bearer', 'expiresIn': 3600, 'expiresOn': '2019-06-27 10:36:58.175894', 'resource': 'https://database.windows.net/', 'accessToken': 'XXXXXXXXXXXXXXXXXXXXXXXXXX', 'isMRRT': True, '_clientId': 'XXXXXXXXXXXXXXXXXXXXXX', '_authority': 'https://login.microsoftonline.com/XXXXXXXXXXXXXXX'}

But I get this error when trying to make connection:

    conn = pyodbc.connect(CONNSTRING, attrs_before = { SQL_COPT_SS_ACCESS_TOKEN:tokenstruct })
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user ''. (18456) (SQLDriverConnect)")

(Note, if from a Windows machine, the error is slightly different....see below):

    conn = pyodbc.connect(CONNSTRING, attrs_before = { SQL_COPT_SS_ACCESS_TOKEN:tokenstruct })
pyodbc.InterfaceError: ('28000', "[28000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\\ANONYMOUS LOGON'. (18456) (SQLDriverConnect); [28000] [Microsoft][ODBC Driver 17 for SQL
Server][SQL Server]Login failed for user 'NT AUTHORITY\\ANONYMOUS LOGON'. (18456)")

My IT guy has confirmed that the app-registration credentials are valid, so the issue is downstream from there, but I don't know where.

[michaelcapizzi]

The solution was explained here, but basically, my SQL admin had to add permissions for the app-registration to the specific database that I was trying to access.

[rayluo]

Hi Michael, thanks for reaching out. That wiki page: (How to) Connect to Azure SQL Database was derived from an upstream pyodbc conversation, which you are now also aware of.

Given that you can successfully obtain an access token, I would also agree with your thought "the issue is downstream from there".

I'm not myself an SQL expert but your CONNSTRING looks good based on this yet another doc "Authenticating with an Access Token". Perhaps you can try to follow its very last See Also link to see if you can find some help.

[rayluo]

@michaelcapizzi Glad that you also figured it out! You beat my suggestion (which points to the same solution page) by 4 minutes. LOL

And our wiki page has been updated with your finding. Thank you again Michael!

[imjoseangel]

The solution was explained here, but basically, my SQL admin had to add permissions for the app-registration to the specific database that I was trying to access.

Hi,

I know this is closed but as far I understand, if you want to automate end to end the creation of a DB, you have the following steps:

• Server Creation
• DB Creation
• DB AD Admin Assignation
• Manual intervention to DB to give the right access to the automation user
• DB Automation

Is that right? My next step is trying MSI Authentication, did you try it?

Thanks

[rayluo]

@imjoseangel Was that question for @michaelcapizzi ?

There is no extra info from me other than the earlier comment.

Regarding to MSI, that is a feature request currently in the backlog of MSAL Python, which is the successor of ADAL Python. There is no new features planned for ADAL Python.

[imjoseangel]

Cool, didn't know about MSAL Python. Thanks for answering.

[alpacayao]

Is app registration credentials supported if client is under MacOS or Linux?

[rayluo]

Yes, app registration credentials would definitely work across platforms. It is platform-agnostic, actually. So you can get an access token. The usage of that access token is also platform-agnostic. We haven't tested in the scenario described earlier in this conversation, though.

…

________________________________ From: Alpaca Young <notifications@github.com> Sent: Wednesday, April 8, 2020 2:04 PM To: AzureAD/azure-activedirectory-library-for-python <azure-activedirectory-library-for-python@noreply.github.com> Cc: Ray Luo <rayluo@microsoft.com>; Comment <comment@noreply.github.com> Subject: Re: [AzureAD/azure-activedirectory-library-for-python] Can't connect to SQL using app registration credentials (#206) Is app registration credentials supported if client is under MacOS or Linux? — You are receiving this because you commented. Reply to this email directly, view it on GitHub<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2FAzureAD%2Fazure-activedirectory-library-for-python%2Fissues%2F206%23issuecomment-611193778&data=02%7C01%7Crayluo%40microsoft.com%7C7dfdafbd8d7b4abc3bb108d7dc00759c%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637219766848406134&sdata=Uir61b5Nq5Sf75IHvYBpWd9uot9nnslDfNgSjEnwQFw%3D&reserved=0>, or unsubscribe<https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Fnotifications%2Funsubscribe-auth%2FAAGISLQZT7C4NWQHHNZYNWDRLTRGVANCNFSM4H36CKXA&data=02%7C01%7Crayluo%40microsoft.com%7C7dfdafbd8d7b4abc3bb108d7dc00759c%7C72f988bf86f141af91ab2d7cd011db47%7C1%7C0%7C637219766848416129&sdata=YSdiufJj5Iw6UnjoCKFPAUpTrEJxJtqXYajhVbw%2B398%3D&reserved=0>.

[answerquest]

My God why am I not finding this sample code anywhere else? I've been looking all over for how to connect to MSSQL from a Python program when my credentials are Active Directory based. All the official documentation does deep into registering your application, getting client / tenant id etc but never mentions how to actually connect to MSSQL after that.

[answerquest]

Hi, what do I need to tell my DBA to do so I can get this "tenantId" from them?

[answerquest]

Can I derive this "tenantId" from the servername or something?

[rayluo]

@answerquest I thought your earlier comment "All the official documentation does deep into registering your application, getting client / tenant id etc ..." hinted that you was able to find those information?

Regardless, "how to know my tenant id" sounds like a different question in itself. I would suggest you to create a new issue, rather than commenting on a closed issue which may not immediately draws attention.