Invalid value specified for connection string attribute 'PWD' for access token > 128 characters

[debaratna-nath-maersk]

I am trying to connect to MSOLAP cubes in Azure AS using pyodbc. As per a documentation by Microsoft, I'm generating the "bearer access token" and providing it to "PWD" in the connection string.
But the bearer access token is generated dynamically and it's length is > 1100..
pyodbc throws the following error if the length of the token is greater than 128 characters:
"OperationalError: ('08001', "[08001] [Microsoft][ODBC Driver 18 for SQL Server]Invalid value specified for connection string attribute 'PWD' (0) (SQLDriverConnect)")"

But there is no way to limit the number of characters in the bearer access token! Please help me in troubleshooting this

[v-chojas]

As per a documentation by Microsoft

Where? Do you have a link to that? Indeed there is a limit on how long a password can be, and that's highly unlikely to change.

If this is an Azure AD access token, it doesn't go there but in a connection attribute (SQL_COPT_SS_ACCESS_TOKEN). More details here: #228

[debaratna-nath-maersk]

Sure. Here is the link to the article I was referring to for establishing the connection. The section "User ID=...; Password=" mentions about using bearer access token in place of PWD.

With reference to the (SQL_COPT_SS_ACCESS_TOKEN), I tried that as mentioned but now I'm getting the following error:
"('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')"

[v-chojas]

The word "ODBC" doesn't even appear on that page. It has nothing to do with the ODBC Driver for SQL Server. What you're trying to do with pyODBC/ODBC won't work.

[debaratna-nath-maersk]

Yes I realised that. I'm currently following up with the workaround mentioned in #228. Here's how I'm approaching it:

1. Create an App Registration and add it to have read/write access to the existing Azure Analysis Server (the cube server)
2. Generate Access Token using the client ID, client Secret and the subscription tenant ID for the App Registration.
3. The token starts with "eyJ0eXA..." . Expand the token and prepend the length as mentioned in accessToken support? #228 .
4. Use the Access Token generated in a connection attribute as suggested:
   CONNSTRING = "DRIVER={};SERVER={};DATABASE={}".format("ODBC Driver 18 for SQL Server", <servername>, <database_name>)
conn = pyodbc.connect(CONNSTRING, attrs_before = { 1256:tokenstruct })

This throws the following error:
"('HYT00', '[HYT00] [Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired (0) (SQLDriverConnect)')"

I seem to be following up with what has been suggested! Not sure if I'm missing out on something. I also tried adding the "Authentication" in the connection string but either way it doesn't work.
Any suggestion will be highly appreciated

[v-chojas]

This article describes connection string properties used by client applications that connect to and query Azure Analysis Services (Azure AS), SQL Server Analysis Services (SSAS), and Power BI Premium dataset data. These properties are used by the Analysis Services client libraries, ADOMD.NET, AMO, and OLE DB (MSOLAP) provider for Analysis Services.

SQL/Azure AS (XMLA) is a completely different protocol from regular SQL Server/Azure SQL (TDS), and has nothing whatsoever to do with ODBC. As I said before ,what you're trying to do has no chance of working.