Connect to Azure SQL Database

It is doable to connect to Azure SQL Database by obtaining a token from Azure Active Directory (AAD), via ADAL Python. We do not currently maintain a full sample for it, but this essay outlines some key ingredients.

1. You follow the instruction of Connecting using Access Token to provision your application. There is another similar blog post here.

2. Your SQL admin need to add permissions for the app-registration to the specific database that you are trying to access. See details in this blog post Token-based authentication support for Azure SQL DB using Azure AD auth by Mirek H Sztajno.

3. It was not particularly highlighted in either of the documents above, but you need to use https://database.windows.net/ as the resource string. Note that you need to keep the trailing slash, otherwise the token issued would not work.

4. Feed the configuration above into ADAL Python's Client Credentials sample.

5. Once you get the access token, use it in this way in pyodbc to connect to SQL Database.

type mapping:

Python object	Value type
buffer(2.x)	SQL_IS_POINTER
bytearray(>=2.6)	SQL_IS_POINTER
bytes(2.x)	string length
bytes(3.x)	SQL_IS_POINTER
integers	SQL_IS_INTEGER or SQL_IS_UINTEGER depending on sign
unicode	string length

This works with AAD access tokens. Example code to expand the token and prepend the length as described on the page linked above, in Python 2.x:

token = "eyJ0eXAiOi...";
exptoken = "";
for i in token:
    exptoken += i;
    exptoken += chr(0);
tokenstruct = struct.pack("=i", len(exptoken)) + exptoken;
conn = pyodbc.connect(connstr, attrs_before = { 1256:bytearray(tokenstruct) });

3.x is only slightly more involved due to annoying char/bytes split:

token = b"eyJ0eXAiOi...";
exptoken = b"";
for i in token:
    exptoken += bytes({i});
    exptoken += bytes(1);
tokenstruct = struct.pack("=i", len(exptoken)) + exptoken;
conn = pyodbc.connect(connstr, attrs_before = { 1256:tokenstruct });

(SQL_COPT_SS_ACCESS_TOKEN is 1256; it's specific to msodbcsql driver so pyodbc does not have it defined, and likely will not.)