Skip to main content
Solved

JDBC connection string - password masking

L
Forum|alt.badge.img
  • lc

Hi,

When using JDBC connections, there is an option to enter in user/pass as parameters, but I am unsure how to format the connection string to leverage these parameters. So far, I can only connect if I embed my user/pass (as plain text) in the JDBC connection string. Any ideas?

jdbc:scwapi:<server_name>;<powerhub_user>;<passwd>;<database_instance><database_name;<username>;<dbpasswd>

Best answer by ravenkopelman

@lc We should be able to get at least one of your proposed solutions working.

User Parameter

 

  1. Add your reader (or writer) with the full JDBC Connection String in an Embedded Connection
  2. In the Navigator Pane on the left, open the Connection String parameter and erase the contents
  3. The Connection String will have been auto-published as a User Parameter that you can use to set it externally

Attribute Value

  1. Use FeatureReader and FeatureWriter transformers instead of adding readers and writers directly
  2. Choose Embed Connection Parameters when picking a connection
  3. Now the JDBC Connection String can be retrieved from an incoming attribute (or built dynamically if you want to store each of the different passwords in their own attributes)

Note that no matter how you pass in the connection string we will currently log it in plain text! That is what we will improve in PR#82990.

Also note that if you store your JDBC Connection String in a Database Connection (rather than an Embedded Connection) it will actually be quite secure in terms of storage. The connection string will not exist anywhere on disk in plain text, and will not travel with your workspace if you share it.

View original
Did this help you find an answer to your question?

4 replies

ravenkopelman
Safer
Forum|alt.badge.img+1

Hi @lc, this looks like an unusual JDBC driver. My searching indicates it is for a product called "PowerHub", but I can't find much more information than that.

I see three interesting things in your connection string:

  1. It doesn't include //, for example jdbc:scwapl://<server_name>/<name value pairs>
  2. It contains two pairs of username/passwords
  3. None of the parameters are name value pairs, for example username=myusername;password=mypassword

JDBC usernames and passwords are normally sent as name value pair properties, which are for the most part equivalent to a connection string that contains the example in point 3 . If the PowerHub JDBC driver doesn't support name value pairs in any form it would explain why you are only having success directly embedding them in the connection string.

In short, I don't think we have an immediate solution for you to avoid having your passwords logged in plaintext. I have proposed a solution to this that might make it into FME 2018.1. Please raise an issue and reference PR#82990 if you want progress updates.

L
Forum|alt.badge.img
  • lc
  • Author
  • April 5, 2018

Thanks @ravenkopelman. I'm not sure it doesn't supports name value pairs, this is an example of a path:

jdbc:scwapi://amr-phpr.americas.comp.com/prod-server;user;pass;SAP_BW;SAP_PRODUCTIONL_TESTS

I tried to leave the user and pass parts empty, and put those values in the paramters, which results in error. How do I let FME know to use the Username and Password I entered as parameters?

This could also be easily solved if i can set this field dynamically through as User Param or Attribute value. But I don't have that option.

ravenkopelman
Safer
Forum|alt.badge.img+1
  • ravenkopelmanSafer
  • Safer
  • Best Answer
  • April 5, 2018

@lc We should be able to get at least one of your proposed solutions working.

User Parameter

 

  1. Add your reader (or writer) with the full JDBC Connection String in an Embedded Connection
  2. In the Navigator Pane on the left, open the Connection String parameter and erase the contents
  3. The Connection String will have been auto-published as a User Parameter that you can use to set it externally

Attribute Value

  1. Use FeatureReader and FeatureWriter transformers instead of adding readers and writers directly
  2. Choose Embed Connection Parameters when picking a connection
  3. Now the JDBC Connection String can be retrieved from an incoming attribute (or built dynamically if you want to store each of the different passwords in their own attributes)

Note that no matter how you pass in the connection string we will currently log it in plain text! That is what we will improve in PR#82990.

Also note that if you store your JDBC Connection String in a Database Connection (rather than an Embedded Connection) it will actually be quite secure in terms of storage. The connection string will not exist anywhere on disk in plain text, and will not travel with your workspace if you share it.

ravenkopelman
Safer
Forum|alt.badge.img+1
ravenkopelman wrote:

@lc We should be able to get at least one of your proposed solutions working.

User Parameter

 

  1. Add your reader (or writer) with the full JDBC Connection String in an Embedded Connection
  2. In the Navigator Pane on the left, open the Connection String parameter and erase the contents
  3. The Connection String will have been auto-published as a User Parameter that you can use to set it externally

Attribute Value

  1. Use FeatureReader and FeatureWriter transformers instead of adding readers and writers directly
  2. Choose Embed Connection Parameters when picking a connection
  3. Now the JDBC Connection String can be retrieved from an incoming attribute (or built dynamically if you want to store each of the different passwords in their own attributes)

Note that no matter how you pass in the connection string we will currently log it in plain text! That is what we will improve in PR#82990.

Also note that if you store your JDBC Connection String in a Database Connection (rather than an Embedded Connection) it will actually be quite secure in terms of storage. The connection string will not exist anywhere on disk in plain text, and will not travel with your workspace if you share it.

Update: The flag to control connection string logging in the generic JDBC format will appear in FME beta 18454.

 

Reply


Helpful Members This Week

Community Stats

31,345
Posts
118,879
Replies
38,941
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.