How Do I Protect Password on ODBCOpen
-
- 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
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
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
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: How Do I Protect Password on ODBCOpen
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
-
- 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
Thanks Robin! I'll try that.
Greg
Greg
-
- 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
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!
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!
-
- 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
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.
-
- 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
Also using an ODBC datasource and specifying the password in the datasource password field avoids storing the password in plaintext.
-
- Site Admin
- Posts: 1453
- Joined: Wed May 28, 2008 9:09 am
Re: How Do I Protect Password on ODBCOpen
I hadn't thought setting datasourcepassword worked for ODBCOutput, rather for data loading. But it still needs to be populated from somewhere, surely
-
- 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
Sorry, I should've been more explicit.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
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);
- 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
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
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
-
- 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
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
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
-
- 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
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
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
- 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
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.
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 pmSorry, I should've been more explicit.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
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 useto make the connection. Doing it this way avoids storing the password in plaintext in the .pro file.Code: Select all
ODBCOpen('name','client',DatasourcePassword);
- 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
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,
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
-
- 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
Hello Brian,BrianL wrote: ↑Wed Mar 23, 2016 6:44 pmSorry, I should've been more explicit.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
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 useto make the connection. Doing it this way avoids storing the password in plaintext in the .pro file.Code: Select all
ODBCOpen('name','client',DatasourcePassword);
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
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
-
- 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
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.