Using TM1PY with the Windows Credential Manager to secure ODBCOpen passwords

Ideas and tips for enhancing your TM1 application
Post Reply
David Usherwood
Site Admin
Posts: 1419
Joined: Wed May 28, 2008 9:09 am

Using TM1PY with the Windows Credential Manager to secure ODBCOpen passwords

Post by David Usherwood » Tue Jun 09, 2020 10:19 am

This is not a full Blueprint-ey writeup, but it may be of interest.
A recent thread on Another TM1 Forum discussed the issues with using ODBCOpen to connect to external databases. I suggested there that TM1PY (of course) could be of use - since I saw that Mariusz recently introduced storing TM1 passwords in Windows Credential Manager. I will admit I didn't know much about WCM, but I took a look.
You can write passwords to WCM, and read them back in Python (they are ****-ed in the UI). The key (I believe, and my testing confirms) is that you need to store the credentials using the user under which the TM1Server, and (thus) the TM1PY script, is running. The user does not have to be logged on when you use it.
Accordingly I built a testbed for this:
a The standard GoNewStores server, adjusted to run under a named Windows user;
b A MYSQL database (running on my NAS), with a table reproducing the StoreSalesPlan cube, requiring a user and password for update;
c A TI with the ODBCOpen/ODBCOutput/ODBCClose, with the password as a parameter;
d a TM1PY script to read the password from WCM;
e A driver TI to call TM1PY.
I have tested this running under a different windows user from that running GoNewStores, and it works. Note that this user cannot get at the WCM content.
Feedback welcome. I will admit there are rather a lot of moving parts.

Snippets from the TIs:
UpdateODBC - takes Store Sales Plan view as source:
Password as parameter
Prolog:

Code: Select all

vdsn = 'Chowder2';
odbcopen(vdsn,'tm1server',password);
vcounter = 0;
Data:
vcounter = vcounter + 1;
if(vcounter > 10000);
  processquit;
  endif;
vsql =   
  'INSERT INTO StoreSalesPlan VALUES (''' |
  retailers |  ''',''' |
  countries_currency | ''',''' |
  products | ''',''' |
  vmonth  | ''',''' |
  Version | ''',''' |
  measure  | ''',' |
  numbertostring(value)  | 
  ')';
odbcoutput(
  vdsn,
  vsql
  );
ExecutePython TI:

Code: Select all

scriptpath = 'd:\windev\tm1py\tm1py-samples\dutest\';
script = 'Credentials_DU.py';
executecommand('cmd /c python ' | scriptpath | script,1);
TM1PY script:
Note I have _not_ used WCM to hold the TM1 credentials, instead holding them in an INI file.

Code: Select all

import configparser
from getpass import getpass
import keyring
from TM1py.Services import TM1Service
INSTANCE = "GO_New_Stores"
config = configparser.ConfigParser()
configpath = 'd:/windev/tm1py/tm1py-samples/config.ini'
print (configpath)
config.read(configpath)

# interact with Windows Credential Manager through the keyring library
DBSERVER = "ChowderMySQL"
mysql = config[DBSERVER]
user = config[DBSERVER]["user"]
password = keyring.get_password(DBSERVER, user)
if not password:
    password = getpass(f"Please insert password for user '{user}' and instance '{DBSERVER}':")
keyring.set_password(DBSERVER, user, password)

#with TM1Service(**config[INSTANCE]) as tm1:
with TM1Service(**config["GO_New_Stores"]) as tm1:
    tiprocess = 'UpdateODBC'
    parameters = {
        'Parameters': [{
            'Name': "Password",
            'Value': password
        }]
    }
    tm1.processes.execute(tiprocess, parameters)

  

User avatar
ykud
Posts: 112
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: Using TM1PY with the Windows Credential Manager to secure ODBCOpen passwords

Post by ykud » Wed Jun 10, 2020 12:00 am

Hi David,

Your goal is to avoid storing the passwords in the cube with | as format as per usual 'best practice'?
Using what you describe is still not 100% secure as an AsciiOutput with pPassword would reveal it in this case as well?

If revealing password with an AsciiOutput is an acceptable risk, I'd say another viable solution would be to use Powershell's convert-to-secure-string (or similar linux crypto) to:
1) encrypt your datasource password in powershell and store in an encrypted file somewhere around data directory (encrypting without -key parameter will encrypt it only for the user running under, so you'll lock it for server account running Tm1 service). You can call this file 'datasource.crypt'
2) create a new TI to read data source password based on the datasource name, which will decrypt the password and read the output as data file & delete the file after it's read
3) Use a TI global variable to pass the decrypted parameter to the caller TI

I.e. sample TI will be:

Code: Select all

DatasourceName = 'MyODBCConnection';
sVariableName = 'MyODBCConnection_password';
StringGlobalVariable(sVariableName);
ExecuteProcess('SYS Read Datasource Password', 'pDatasourceName', DatasourceName);
DatasourcePassword = MyODBCConnection_password;
Sample powershell code from MS pages:

Code: Select all

$secureString = ConvertTo-SecureString -String 'Example' -AsPlainText
$secureString # 'System.Security.SecureString'
ConvertFrom-SecureString -SecureString $secureString -AsPlainText # 'Example'
I like keyring a lot, but this avoids any external dependencies (i.e. python), albeit by replacing it with Powershell ;)

Cheers,
Yuri

David Usherwood
Site Admin
Posts: 1419
Joined: Wed May 28, 2008 9:09 am

Re: Using TM1PY with the Windows Credential Manager to secure ODBCOpen passwords

Post by David Usherwood » Wed Jun 10, 2020 7:59 am

Interesting alternative. Good for PA SaaS as Powershell is available but Python would not normally be.
I'm not concerned about AsciiOutput as the aim is not to protect it from devs/admins, but from users.

User avatar
ykud
Posts: 112
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: Using TM1PY with the Windows Credential Manager to secure ODBCOpen passwords

Post by ykud » Wed Jun 10, 2020 11:17 pm

David Usherwood wrote:
Wed Jun 10, 2020 7:59 am
I'm not concerned about AsciiOutput as the aim is not to protect it from devs/admins, but from users.
Then I don't see any benefit of using tm1py / keyring over Powershell backed file concepts -- it's the same cryptography as in WCM and has fewer dependencies :)

David Usherwood
Site Admin
Posts: 1419
Joined: Wed May 28, 2008 9:09 am

Re: Using TM1PY with the Windows Credential Manager to secure ODBCOpen passwords

Post by David Usherwood » Wed Jun 17, 2020 10:51 am

There may be some benefit in using the Credential Store over encrypting a file on the disc - but not much, and as you say the PS-based approach has less components. Good to see that being published - I certainly wasn't aware of it.

Post Reply