Requesting Assistance - ODBC connections with expiring Tokens

Post Reply
rAzoR
Posts: 7
Joined: Thu Apr 16, 2015 3:37 am
OLAP Product: TM1
Version: PA 2.0
Excel Version: Do not use often now
Location: Sydney

Requesting Assistance - ODBC connections with expiring Tokens

Post by rAzoR »

Hi all,

Thanks in advance wonderful TM1 community if any of you take the time to read this, and even more so if you have any suggestions!



CRUX OF ISSUE (without all the waffe!):
I have zero idea how to programmatically refresh expiring oAuth Personal Access Tokens inside a permanent Simba Spark ODBC object on the TM1 box.



LONG VERSION OF ISSUE (with full context):
Our business has just made the decision to rollout DataBricks as our new DataLake platform. My one insistence (as our company's only TM1 developer) was that whatever product we utilise, it must provide an ODBC connection for TM1 to run SQL queries through.

We have our new DataBricks platform rolling out. I have my Simba Spark ODBC Driver software installed and a new Simba Spark ODBC DSN connection created in the ODBC Data Source Administrator 64 bit console on the TM1Dev box.

Our network security team is insisting we set the Simba Spark ODBC driver to run on oAuth 2.0 authentication. This requires DataBricks to generate a Personal Access Token specific to the TM1 Service account, which I then need to manually save into the ODBC object in order to get a working connection. To this point I'm fine.

HOWEVER...Network security is also insisting the token have a limited lifespan, with generation of new tokens and update of the ODBC driver a mandatory requirement. This is where I come undone!

Zooming out on this a bit, I'm confident that with some hard work I can research keystores and find one that can hold the token in a suitably-encrypted fashion. DataBricks has an API and I'm somewhat confident I can write a Python script access the keystore, get the current Personal Access Token, use that to connect to the API, get a new Personal Access Token, and then update the keystore. This would allow me to obtain a new token from the old one before anything expires.

HOWEVER, once I have that new Personal Access Token, I have zero idea how to keep the expiring access token updated inside the permanent Simba Spark ODBC, other than remoting into the box manually and doing it constantly by hand in 64-bit ODBC Data Source Administrator. This is where I come unstuck.



POTENTIAL SOLUTIONS I'M EXPLORING (assuming I've developed the keystore and API parts already..which I haven't yet):
1) Can PowerShell update the Simba ODBC object with a new Access Token? I know PowerShell can do some ODBC manipulation, but haven't seen it listed anywhere that it can update oAuth tokens.

2) Can I link the ODBC to the keystore by reference (so it will never expire so long as the keystore remains updated) rather than having to put the actual token's value (which will expire) into the ODBC. I have seen some other ODBCs where this functionality seems built straight into the ODBC driver (AWS ODBC links to AWS keystore natively from memory?). However I'm not yet seeing anyway to do this in a Simba Spark ODBC.

3) Rather than using the traditional pre-configured ODBC on the dev box, try to create an on-the-fly ODBC object in the prolog of each of my TI's that require it. Never done this before and I'm pretty sure TM1 has zero functionality to do this (all it seems to have is the ODBCOpen function, which... is not it). This is the way the world is moving in all other systems though it seems, so if TM1 can't do this yet... at what point will it update and add this functionality?

4a) Use Python with the DataBricks API and TM1 REST API (TM1Py) to do all data transfers into Staging Cubes. Then run my current TI logic on the staging cube data. Give away with the concept of TM1 having any out-of-the-box ability to connect to anything at all and go entirely outside of TM1 for all ETL. This solution is technically possible, however there are a myriad reasons why this solution is not practical. I would need everything loaded into Staging cube first and my datasets are MASSIVE. There are also other reasons why this option is not feasible, but for brevity I won't list them all here.

4b) As per last option, but completely remove any semblance of using TM1 directly. Use Python with the DataBricks and TM1 REST APIs and use TM1Py to move EVERYTHING into Python! Have a WHILE loop to represent the Meta tab and another one to represent the Data tab and ALL the complex business logic gets rewritten utterly everywhere to be TMPy Python commands as well. This option (if even feasible) sounds very unappealing.

5) Get DataBricks to just run jobs which export everything to CSV. TM1 then becomes a CSV-ingesting-only database. Lots of reasons why I don't like this option though!

6) I convince the network security team to let me have an oAuth token that does not expire and I save it permanently into my ODBC. This is the traditional way TM1 works. This is totally possible for the guys to do. They just don't want to do this.


Anyway, I know this is a long post and I'm only giving problems, not solutions... but I have the CFO and the board very keen to know why all our new DataLake data is not yet in TM1. So if anyone has any advice on this one, would really appreciate any thoughts!! Thanks guys!!
rAzoR
Posts: 7
Joined: Thu Apr 16, 2015 3:37 am
OLAP Product: TM1
Version: PA 2.0
Excel Version: Do not use often now
Location: Sydney

Re: Requesting Assistance - ODBC connections with expiring Tokens

Post by rAzoR »

So... not many responses. I didn't really expect many, as I wouldn't know how to help if someone else asked me this!

Can I ask instead then... is anyone here using ODBC connections utilising OAuth2.0 encryption?

Are you just not letting the tokens expire?

Are you logging in periodically and updating the tokens manually?

Are you using simpler drivers and/or simply using name and password only for authorisation?
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Requesting Assistance - ODBC connections with expiring Tokens

Post by David Usherwood »

I have the CFO and the board very keen to know why all our new DataLake data is not yet in TM1.
...
I convince the network security team to let me have an oAuth token that does not expire and I save it permanently into my ODBC. This is the traditional way TM1 works. This is totally possible for the guys to do. They just don't want to do this.
Perhaps you could explain to the CFO and the board that if the network security team would like to cooperate it would work fine :)
burnstripe
Regular Participant
Posts: 197
Joined: Wed May 06, 2020 2:58 pm
OLAP Product: Planning Analytics
Version: 2.0.9
Excel Version: 2016

Re: Requesting Assistance - ODBC connections with expiring Tokens

Post by burnstripe »

https://cprosenjit.medium.com/azure-dat ... 5828b66401

This may be worth a read. I've not touched this area so can't give any guidance. But from a quick read you could link the odbc to the password to the azure key vault. The password to the vault remains the same but the password stored in the vault would be updated through rest API and you'd schedule the command at required intervals...
rAzoR
Posts: 7
Joined: Thu Apr 16, 2015 3:37 am
OLAP Product: TM1
Version: PA 2.0
Excel Version: Do not use often now
Location: Sydney

Re: Requesting Assistance - ODBC connections with expiring Tokens

Post by rAzoR »

Thanks BurnStripe!

That's basically my option 2 of my seven options. I had seen some stuff like that already in my study, but hadn't determined how my Simba Spark driver in the standard old ODBC Data Administrator console could be linked to the secret. I have seen links in some other ODBCs out of the box where they link to key vaults, but don't see it in the Spark Driver.

We actually use AWS Databricks, not Azure (apparently there's a difference). But they do have a key store too. I guess I could set up a secret token there and try just entering the secret name and seeing if the ODBC is smart enough to translate that to a token? (Even though the driver really looks like it's saying it wants the actual token directly).

The other option is likely to just escalate to the executives and demand a non-expiring token. The token is saved encrypted in a static ODBC and only valid for one user, who also has an encrypted password saved there as well. At least it wouldn't be getting sent in plain text via adhoc scripts (python, etc) to build on-the-fly connections in memory. I think political escalation is really my best option in this case I guess.
rAzoR
Posts: 7
Joined: Thu Apr 16, 2015 3:37 am
OLAP Product: TM1
Version: PA 2.0
Excel Version: Do not use often now
Location: Sydney

Re: Requesting Assistance - ODBC connections with expiring Tokens

Post by rAzoR »

So the executives and security guy had the meeting today. Their decision... I can manually create a token myself each fortnight and then manually log into the ODBC's and manually update the tokens in the ODBCs myself. Every fortnight. By hand. And TM1 can just be "down" from the point at which I manually log into DataBricks to generate a new token until the point at which I update the ODBC's with the new token. And all the chores and on-demand processes that use the ODBC can just fail in that window and that's cool.

Can't say I'm overly stoked with that outcome for a number of reasons. Aside from the manual task that's now been assigned to my workload in perpetuity, the ODBC will now go down each time I log on to do this task. And also I'm now single point of failure for the entire system as the request of token generation is going to be locked to my personal account, not a service account. That is apparently a deliberate decision. So if I'm ever away for 2 weeks everything is going to break everywhere. Hmm...

So, they've made their choice. That's their approved solution. I have to live with it apparently.

I'm not going to live with that though. That's a horrendous nonsensical solution in my eyes. I'm hoping that there's something in this I can use:
https://learn.microsoft.com/en-us/power ... ver2022-ps

I'm hoping maybe the token goes in the PWD property? If it does then perhaps a single one-line PowerShell script would do it?:

Set-OdbcDsn -Name 'My Odbc Name Here' -SetPropertyValue 'PWD=myTokenGoesHere'

Then, if that actually worked, the back of it would be broken and I could move onto getting PowerShell to read from a key vault instead of in plain text in the script, and also writing some Python to use the DataBricks API to retrieve the token and store it in the key vault, etc.

I refuse to accept that me manually updating it every fortnight is really the solution our entire new shiny data platform is going to be based on!!
Post Reply