How Do I Protect Password on ODBCOpen

Post Reply
GregK
Posts: 3
Joined: Wed Apr 16, 2014 10:28 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: Excel 2007
Location: Fort Worth, TX
Contact:

How Do I Protect Password on ODBCOpen

Post by GregK »

I am new to Cognos TM1 and I have a TurboIntergrator process which makes use of and ODBC connection to add budget data back to our ERP. We have been directed not to use an interim file (e.g. no CSV file) so I am using the ODBCOpen in the Prolog, the ODBCOutput in the Data, and the ODBCClose in the Epilog tab. Everything is working as expected in test. However, we don't want to have the password for the SQL Server user which has rights to update the budget table on our production system in plain text inside the TI process. Currently I am using:

ODBCOpen('DsnName', 'SqlUserName','SqlUserPwd');

Is there a method I can use to issue the ODBCOpen command without having the password in plain text?

Regards,
Greg
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: How Do I Protect Password on ODBCOpen

Post by rmackenzie »

You can make the service account that runs the TM1 instance a user in the database - then there's no need to pass either a username or password.
Robin Mackenzie
GregK
Posts: 3
Joined: Wed Apr 16, 2014 10:28 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: Excel 2007
Location: Fort Worth, TX
Contact:

Re: How Do I Protect Password on ODBCOpen

Post by GregK »

Thanks Robin! I'll try that.

Greg
hiits100rav
Posts: 8
Joined: Fri Feb 26, 2016 8:22 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: Excel 2013

Re: How Do I Protect Password on ODBCOpen

Post by hiits100rav »

Hi Greg,

Were you able to identify a solution to address the password in plain text issue. I am a newbie to TM1 TI and trying to address exactly the same issue.

It would be very helpful if you can share your findings.

Thanks!
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: How Do I Protect Password on ODBCOpen

Post by lotsaram »

This is a fairly old thread to be resurrected but yes if windows integrated login is not an option then it is very easy to hold the password as a cube value and retrieve as a variable with CellGetS then pass the variable into ODBCOpen.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
BrianL
MVP
Posts: 264
Joined: Mon Nov 03, 2014 8:23 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2 PA2
Excel Version: 2016

Re: How Do I Protect Password on ODBCOpen

Post by BrianL »

Also using an ODBC datasource and specifying the password in the datasource password field avoids storing the password in plaintext.
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: How Do I Protect Password on ODBCOpen

Post by David Usherwood »

I hadn't thought setting datasourcepassword worked for ODBCOutput, rather for data loading. But it still needs to be populated from somewhere, surely :)
BrianL
MVP
Posts: 264
Joined: Mon Nov 03, 2014 8:23 pm
OLAP Product: TM1
Version: 9.5.2 10.1 10.2 PA2
Excel Version: 2016

Re: How Do I Protect Password on ODBCOpen

Post by BrianL »

David Usherwood wrote:I hadn't thought setting datasourcepassword worked for ODBCOutput, rather for data loading. But it still needs to be populated from somewhere, surely :)
Sorry, I should've been more explicit.

I was trying to suggest that one could specify the password in the DatasourcePassword in the datasource definition UI. You would then still need to use

Code: Select all

ODBCOpen('name','client',DatasourcePassword);
to make the connection. Doing it this way avoids storing the password in plaintext in the .pro file.
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: How Do I Protect Password on ODBCOpen

Post by paulsimon »

Hi

In my view the best option is probably to give the Service Account that the TM1 Service is running under the appropriate access to the SQL Server database. Then in the ODBC DSN define the connection type to SQL Server as using Windows Authentication. In that way there is no need to store a password anywhere. If this is done, then you need to control who knows the password for the Service Account. This could be limited to IT Ops.

Not all databases support WIndows Authentication. If you have to store a password, then the other option is to store the User Name and Password in a cube in TM1 and to make sure that you set the security so that only TM1 Admins can view this cube. It is probably best to use a rule in }CubeSecurity so that all Groups get NONE access. This won't apply to users in the Admin groups. Using a rule prevents an Admin from accidentally changing the security.

It is also important to ensure that no users can access the cubes folder on the server either via a network share or RDP, as then security can be bypassed. That is always a consideration with any type of database.

Once you have the Security in a cube,The TI process just needs to CellGetS the User Id and Password from the cube and to set the DataSourceUserName and DataSourcePassword local TI variables to the values obtained.

Even if you can use Windows Authentication is it still good practice to get the User Name from a secure cube, as then, if the User Name ever needs to change, you only need to change it in one place and all your TI processes will automatically update. You can still get the Password from the cube, even if you are using Windows Authentication. Just set a blank password in the cube, and this will work fine.

At some clients I put three measures for ODBC DSN, User Name and Password into the secure cube with a dimension for the ODBC Environment, so the cube could for example hold different parameters for the Dev, Test, and Production SQL databases. Another cube then sets the environment to be used. The TI reads the environment to be used and then gets the ODBC parameters for that environment. . This for examples allows the Test TM1 Server to be set to pull in data from the Production SQL database, as accountants often won't sign off a system change on test data. They want to see the production data so that they can reconcile it to reports from the General Ledger. However, other clients will only allow a Dev server to access a Dev SQL database, and in some cases have even set up separate Network Domains to prevent any cross environment access. The above scheme can still be used. You just don't complete the details for Test and Production on the Dev Server and you don't create the ODBC DSNs. It all really depends on the requirements and security policies.

Regards

Paul Simon
hiits100rav
Posts: 8
Joined: Fri Feb 26, 2016 8:22 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: Excel 2013

Re: How Do I Protect Password on ODBCOpen

Post by hiits100rav »

Thank you Paul, David, Brian for your response. This is really helpful.
We are now trying to implement windows authentication process based on Paul's thoughts. We make use of AWS RDS and trying to check the feasibility for the same.

I shall share update on how it goes.

Thanks,
Saurabh
hiits100rav
Posts: 8
Joined: Fri Feb 26, 2016 8:22 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: Excel 2013

Re: How Do I Protect Password on ODBCOpen

Post by hiits100rav »

We tried various approaches to handle this. I am listing down below.
1) Tried to locate if we can attach service accout with MySQL server, however couldn't find much documentation on mysql for this. There is indeed a 'windows authentication plugin' available bowever seems to work only with.net applications.
2) Stored our credentials in TM1 cube with access only to TM1 admins, however the passwords are still in plain text and our information security team did not allow it. (against corporate policy)
3) We setup an intermediate proxy system to our infosec system that stores passwords in encrypted format. We wrote a script to pull those and update the MySQL connector DSN username and password. We no more require to enter username and password either in data source definition tab and ODBCopen(dsn,'','').

I have unit tested and shall be making changes to all our TI processes.

Thanks,
Saurabh
User avatar
mce
Community Contributor
Posts: 352
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: Planning Analytics Local 2.0.x
Excel Version: 2013 2016
Location: Istanbul, Turkey

Re: How Do I Protect Password on ODBCOpen

Post by mce »

Hi Brian,

I tried this but it does not work. DatasourcePassword gives the encrypted password text, hence does not work with ODBCOpen command as in the way you described below. Did you managed to get this work? I need to find a solution to this problem. DB Password has to be encrypted.
BrianL wrote: Wed Mar 23, 2016 6:44 pm
David Usherwood wrote:I hadn't thought setting datasourcepassword worked for ODBCOutput, rather for data loading. But it still needs to be populated from somewhere, surely :)
Sorry, I should've been more explicit.

I was trying to suggest that one could specify the password in the DatasourcePassword in the datasource definition UI. You would then still need to use

Code: Select all

ODBCOpen('name','client',DatasourcePassword);
to make the connection. Doing it this way avoids storing the password in plaintext in the .pro file.
User avatar
mce
Community Contributor
Posts: 352
Joined: Tue Jul 20, 2010 5:01 pm
OLAP Product: Cognos TM1
Version: Planning Analytics Local 2.0.x
Excel Version: 2013 2016
Location: Istanbul, Turkey

Re: How Do I Protect Password on ODBCOpen

Post by mce »

Hi,

Are there any solution to this problem with Oracle DB?
As it is not possible to save password in ODBC connection for Oracle client, I still could not find a solution to this problem.
Any ideas will be appreciated.

Regards,
hiits100rav wrote: Sat Jun 04, 2016 12:15 am We tried various approaches to handle this. I am listing down below.
1) Tried to locate if we can attach service accout with MySQL server, however couldn't find much documentation on mysql for this. There is indeed a 'windows authentication plugin' available bowever seems to work only with.net applications.
2) Stored our credentials in TM1 cube with access only to TM1 admins, however the passwords are still in plain text and our information security team did not allow it. (against corporate policy)
3) We setup an intermediate proxy system to our infosec system that stores passwords in encrypted format. We wrote a script to pull those and update the MySQL connector DSN username and password. We no more require to enter username and password either in data source definition tab and ODBCopen(dsn,'','').

I have unit tested and shall be making changes to all our TI processes.

Thanks,
Saurabh
Wim Gielis
MVP
Posts: 3103
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: How Do I Protect Password on ODBCOpen

Post by Wim Gielis »

BrianL wrote: Wed Mar 23, 2016 6:44 pm
David Usherwood wrote:I hadn't thought setting datasourcepassword worked for ODBCOutput, rather for data loading. But it still needs to be populated from somewhere, surely :)
Sorry, I should've been more explicit.

I was trying to suggest that one could specify the password in the DatasourcePassword in the datasource definition UI. You would then still need to use

Code: Select all

ODBCOpen('name','client',DatasourcePassword);
to make the connection. Doing it this way avoids storing the password in plaintext in the .pro file.
Hello Brian,

Just a question. Do you mean that you store the password in the ODBC (64-bits) System DSN configuration for the source at hand ?

Thanks,

Wim
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: How Do I Protect Password on ODBCOpen

Post by tomok »

mce wrote: Thu Nov 29, 2018 10:07 pm Are there any solution to this problem with Oracle DB?
The only thing I know that you can do is store the Oracle and ID and password in a text file in a share that only the TM1 service account has access to. You have a wrapper process that uses this file as a data source that reads the ID and password and sends them as parameters to the real process. No encryption though unless you bake your own into the deal by encrypting the password in the file and use your own algorithm to unencrypt in the first process.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Post Reply