search

AzureAD / azure-activedirectory-library-for-python

ADAL for Python
http://adal-python.readthedocs.io
Other
259 stars 94 forks source link

Can't connect to SQL using app registration credentials #206

Closed michaelcapizzi closed 5 years ago

michaelcapizzi commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

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 commented 5 years ago

@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 commented 5 years ago

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:

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

Thanks

rayluo commented 5 years ago

@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 commented 5 years ago

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

alpacayao commented 4 years ago

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

rayluo commented 4 years ago

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 GitHubhttps://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 unsubscribehttps://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 commented 4 years ago

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 commented 4 years ago

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

answerquest commented 4 years ago

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

rayluo commented 4 years ago

@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.