accessToken support?

[adw555]

I was wondering if pyodbc will support connecting to an Azure SQL DB using the AD access token instead of user/password?

[mkleehammer]

Unfortunately I don't think so without some code changes

The ODBC connection string is passed as-is to the driver, so companies can support any keywords they want. Unfortunately MS didn't do the easy, obvious thing and allow an "accessToken=xyz" keyword in the connection string, at least as far as I can tell.

This indicate you need to set it as a connection attribute before connecting. Unfortunately it also shows you need to pass a binary structure (thanks MS!) which pyodbc does not support yet.

I could add this constant and recognize it as a pointer type, but it would probably be better to come up with Python data type that is known to be passed as a pointer (SQL_IS_POINTER). In Python 3, bytes would be a natural choice, but I'm not sure what I'd use in Python 2. buffer? Perhaps ctypes?

SQL_COPT_SS_ACCESS_TOKEN = 1256
token3 = b'...'
pyodbc.connect("Driver={..};Database=xyz", attrs_before={
   SQL_COPT_SS_ACCESS_TOKEN: token  
})

Encoding the token properly would require using the struct module, but I think an example would make it tolerable.

[v-chojas]

Unfortunately MS didn't do the easy, obvious thing and allow an "accessToken=xyz" keyword in the connection string, at least as far as I can tell.

The access token is usually very long (>1KB, and several KB is not uncommon), can contain arbitrary data, and driver managers may have hardcoded limits on how long a connection string may be. Even pyodbc currently has hardcoded limit of 600 (why? A quick glance through the code doesn't reveal any fixed-length buffers.)

This page describes how to use an access token. I believe bytes would be OK for Python 2 as well, despite the fact that it is not really a string, or perhaps bytearray for 2.6+ . I will look into trying to add this.

[v-chojas]

Give this a try... https://github.com/v-chojas/pyodbc/tree/connattrs

I implemented the following 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.)

[Pcosmin]

Hi! Something new about this issue? Could anyone merge the connattrs to master?

[hexadite-idan]

also interested having this feature, any news?

[v-chojas]

It's waiting for #259 to be merged.

[v-chojas]

The #259 has been merged, this issue can be closed now.

@adw555 @Pcosmin @hexadite-idan you can try this now in pyODBC 4.0.24.

[jhbuhrman]

This particular reply from @v-chojas really helped me to succeed in getting token based access to the database.

Give this a try... https://github.com/v-chojas/pyodbc/tree/connattrs

I implemented the following 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.)

Listed below you will find an alternative implementation for creating the tokenstruct. It takes a (Python 3) string as input. This works for Python 3.5+, otherwise you will have to remove the type annotations or convert them to comments.

As you can see, I changed the struct.pack parameter to "<i" following the line of reasoning that if you encode the characters Little-Endian, you will probably want to code the length as well Little-Endian.

def str2mswin_bstr(value: str) -> bytes:
    """Convert a string to a (MS-Windows) BSTR.

    See https://github.com/mkleehammer/pyodbc/issues/228#issuecomment-319190980
    for the original code (although the input in that example are bytes,
    assumed to contain characters in the range 0..255).  It appears the string
    is converted to an MS-Windows BSTR in 'Little-endian' format.

    Please note that neither this routine (nor the original code) will produce
    correct output if any code point in the input string is not in the
    ISO/IEC 10646 Basic Multilingual Plane (BMP), assuming the resulting
    structure is expected to hold UCS-2 characters.

    See https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-dtyp\
       /692a42a9-06ce-4394-b9bc-5d2a50440168
    for more info on BSTR.  It does not state however what kind of encoding
    it contains.

    See https://en.wikipedia.org/wiki/Universal_Coded_Character_Set for info
    on UCS-2.

    :param value: the string to convert
    :return: the converted value
    """
    # The original code from the provided github URL applies 'Little-endian'
    # to the character conversion in a python for-loop.
    # The following is much faster and works safely for the whole ASCII range.
    # BTW, there is no Byte Order Mark (BOM) inserted when specifying an
    # explicit endianness.
    encoded_bytes = value.encode("utf_16_le")
    # The original code applies native endianness for encoding the length
    # prefix. My working assumption that also the length should be encoded
    # 'Little-endian', regardless of the CPU (and OS) this code is running on.
    return struct.pack("<i", len(encoded_bytes)) + encoded_bytes

In due time, I will provide this function as a small utility package in PyPI.

[v-chojas]

There is no guarantee that values in an access token will be ASCII.
The correct specification of the token is in the TDS protocol:

https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/f40cca29-29b8-43ec-b9ff-2f8682486c29

The client then generates and sends a tokenless Federated Authentication Token message that contains binary authentication data that is generated by the federated authentication library.

https://docs.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/827d9632-2957-4d54-b9ea-384530ae79d0

[jhbuhrman]

Ooh, that's interesting!

First of all - for completeness' sake - I regrettably have to withdraw my statement that I got it working. I didn't check thoroughly enough. Still working on it...

But regarding your remark about binary authentication data, if the token could be binary, how is it possible that MSIAuthentication provides me a token (using Python 3.6) of type str?

I use the following code fragment to get the token that I'm passing as the value parameter in the function listed above:

    credentials = MSIAuthentication(resource="https://database.windows.net/")
    access_token = credentials.token["access_token"]

access_token has type str (not bytes). I think it is impossible to encode an arbitrary binary in a py3 str type.

[But perhaps I'm using the wrong code to obtain the token...]

[v-chojas]

The fact that an access-token-producing API is only producing ASCII tokens does not mean that consumers of tokens can assume that they are, given that this is a very generic authentication method. Or put another way, ASCII-only is just a subset of binary.

Also, note that msodbcsql17 since 17.3 supports MSI authentication itself:

https://docs.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory

(Use Authentication=ActiveDirectoryMSI in the connection string, and optionally for a user-assigned identity, set the UID to the object ID.)