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.

accessToken support? #228

Description

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions