I love new DB nodes. At least, they perform far much faster than the legacy ones.
They brought some limitations too. I used to employ a configurable Wrapped Metanode in my workflows, which was responsible for providing DB connection to a particular database I need to work with, in a particular part of the workflow. With new DB nodes, it stopped working as I described in my [Recommended way of configuring a database connectors](http://Recommended way of configuring a database connectors) forum topic. Well, I got learnt that I canāt get DB connections from wrapped metanodes or components anymore but Iām expected to add appropriate type of DB Connector right to my workflow. It usually looks as follows:
As I stated in the topic I referred, I had to use the Credentials Configuration Node in order to credentials in the PostgreSQL Connector because I was not able to fill a database password with a Flow Variable value. The problem is if I open the workflow that contains this sequence nodes in executed state I cant continue with the workflow since a problem KNIME describes as: āStatus: DataLoadError: Loading model internals failed: The server requested password-based authentication, but no password was provided.ā Please keep in mind the sequence can be a part of loop or have a number of already executed following nodes so itās not easy to re-execute the nodes with no impact to workflow.
This particular issue makes unavailable one of the crutial features we as a company have chosen KNIME as an integration platform, that was an ability to load, investigate and continue with a forkflow that has failed.
For this reason, Iād be glad if I have the option to continue with partially executed workflow back.
Hi @jan_lender,
the new DB nodes do not restore the database connection per default. You can enable the restoring in the āAdvancedā tab of the Connector node.
Here I need to reset the Credentials Configuration and the PostgreSQL Connector to get the DB Column Filter nodeās configuration dialog back. Pretty inconvenient.
The postgres connector is using a credentials variable that is defined within the upstream component. The password itself is not part of the āCredentials Configurationā but defined via the āString Manipulation (Variable)ā and clear-text (I understand that part should be some other magic that reads properties files etcā¦).
What I tested and worked:
Executing this whole thing works just fine
Executing this, saving the workflow, closing and restoring the workflow will mark the DB nodes to be invalid (see tooltip message) ā but downstream nodes will continue to work (none present here but you see the DB reader node is āgreenā)
Re-running the whole thing works also (also using a different value for the password)
What am I missing here? Would love to share the workflow so you can break it but I realize that itās not going to work for you in the first place.
Hi Bernd,
thank you for your contribution.
Can you open the configuration dialog of the node DB Table Selector in your workflow in the exact state it is in the picture you posted? I mean it is has green light below it and it follows a PostgreSQL Connector which has green light accompanied by the exclamation mark in a yellow triangle? The scenario would be: Invoke the whole workflow, close the workflow, open it again, check everything is green (and the exclamation mark is here), open the Configuration dialog using nodeās local menu or hitting the F6 key. I end up with a message box āThe dialog cannot be openedā.
@jan_lender OK the āissueā might be this:
The Credentials input will not save the password you entered unless you allow that. (And I would not recommend to use this, as the password is only (very) weakly encrypted in this case).
The DB tries to restore the connection (establish a new connection to the database), naturally it needs the password for this, which is not present because the credentials input is executed and therefore does not prompt for the password, so restoring the connection fails I assume.
A lot of DB node dialogs depend on the connection, this is why it is then not possible to open it.
I see, that this is not the most intuitive behaviour, however storing the password in your workflow is basically what you want to avoid by using the credentials input, that can prompt for the password. What would be the behaviour you would expect/like to see?
Note also: The nodes might be still green, but as the processing is in database processing the intermediate results might no more exist in the database, so you would have to re-excute them anyway, to see any results before the DB-Reader.
As Iāve already stated in this topic and in the topic I referred, I use in my solution property files for storing my connection params. As you can see in screenshots I provided here there is a component called DB Connection Parameters Provider which is responsible for loading connection parameters for a particular database I want to communicate with. The subsequent Credentials Configuration node is configured with the parameters the component provides as flow variables. Which is a problem when I switch the āSave password in configuration (Weakly encrypted)ā option as you recommend. If I do that, my solution just stops working and I face an error my DB Connector produces: āExecute failed: ORA-01005ā, which means that no password was provided. Well, if I have a closer look at Credentials Configuration nodeās configuration I can see the option mentioned above has a side effect when switched. This is how the Flow Variables tab of the configuration dialog looks like when the option is not checked:
And this is how it changes after the option gets checked:
You can see, the password parameter has changed its name to āpasswordEncrā¦ā (I usually donāt see whole parameter name if it gets longer on my mac) and it has cleared. Now I bind my workflow variable again (ignoring the password in the flow variable is not encrypted):
If I execute the Credentials Configuration and the subsequent PostgreSQLConnector after that, I get the same error as if I didnāt bind the variable at all. So I have a suspicion that if I check the āsave passwordā option the node starts ignoring the fact the parameter is configured by a workflow variable even though it claims it is aware of it.
I have to complain since I started using Credentials Configuration node at the end of 2019 I keep solving problems related to it which is the reason I opened my DB Connector - Give the password flow variable configuration option back. - #3 by jan_lender topic. And Iām pretty sure things would get easier for me if I really have the option of getting rid of the need of Credentials Configuration node.
You mentioned security is the reason you as KNIME team discourage users from saving passwords to configuration. Iād bet this could have been the reason you originally introduced the credentials-over-plain-password concept. Iād like to let you know, KNIME users can keep their passwords safe even without your effort. In my case, we plan running KNIME workflows in batch mode on Linux machines. We donāt expect unauthorized persons having access to these machines so we donāt concern about plain passwords stored on their file systems.
Agree. While begin secure should be the default there should be a way to opt-out. This whole credentials stuff I one of the main reason Iām hesitant of the new DB nodes.
Also why make the credentials and encrypt it weakly? If we could get a hint on how it is weakly encrypted (without looking at source code) we might be able to provide it in encrypted form to the node. I think that is the issue Jan is seeing is saving password is active. Then putting in the password in plain text flowvariable into passwordEncrypted field will obviously not work as it will get decrypted.
EDIT:
I think you can look up the encrypted value in the settings.xml of the credentials node. If you use that value in a flow variable it works. This way you donāt need to know how itās encrypted, you can look it up, manually, and then store encrypted value in your config. Of course this is only a temp workaround and actual encryption scheme should be known so the encrypted PW can be provided.
Iām replying to you to ensure KNIME team will read this.
This issue really needs to be addressed. I played around a bit and noticed also below explained problematic behavior.
Component with Credentials node and a connector (oracle, but that probably doesnāt matter). I add default values into the Credentials nodes and select to save the password and prompt for user name:
Then on the component I enter a different user and password and execute the component. I have the DB Session connected to a DB Query Reader that reads from a table without schema name. meaning reading will fail if I connect with wrong user (schema).
The default user is the correct user. The one entered into the Configuration Dialog is the wrong user. Therefore I have a connection with the wrong user and the reader fails:
I save the workflow and close it. Then I reopen the workflow and the connection seems OK and the reader is yellow:
And I can actually execute it:
Which means and is confirmed by looking at the session object, the session is created with the default user and not the user in the configuration dialog. This can actually be dangerous if I created a component with a highly privileged user as default and then share it. Or it can simply be confusing. The issue also is why the default values are saved but the password entered into the dialog is not even if the checkbox to save the password is checked? Itās confusing.
Having a configurable credentials simply does not work!
I also looked at the source and it seems the password is encrypted with DES. While I doubt even most skilled users would fails to crack it, why not simply updated this to AES? As far as I can tell that would be with testing, documentation etc. max 1 day of work and then you can remove the warning about weak encryption and also use AES to store the value entered in the Configuration dialog.
Or as Jan suggest. Simply as before allow to set plaintext passwords either on credentials or the connector directly. This would probably be the preferred solution. Think of scheduled workflows on the server. No way to enter DB credentials. So they will have to be stored.
The credentials entered into the component configurations are saved into settings.xml from the component. What I tried is to read out these settings from the settings.xml and pass them as flow variables to the Credentials Configuration. This also doesnāt work. The flow variables simply get ignored.
When reopening a workflow, the Credentials Configuration always reconnects with the default values!
Regardless of a different Configurations Input or flow variables. it always chooses the default values. This is clearly a bug! Reconnection must happen with the set values either from flow variables or the Configurations Inputs.
EDIT:
Plus what Jan said. Being able to set plaintext PW on the connector (or credentials configuration) should be possible.
And while credentials seem to be there to make stuff more secure, why use DES? Makes the whole thing questionable at best. Change it to AES.
I found today another disadvantage of using Credential Configuration where a Flow Variable maintains the password, in combination with enabled Restore database connection configuration in DB Connector node.
Iāve noticed an exclamation sign over the traffic light of the Connector node saying ORA-091017, Invalid Username/Password.
It obviously happened when the Credential Configuration node provided its subsequent DB Connector node with blank password ignoring the fact password had been set using its Flow Variables settings.
The important thing Iād like to emphasise is that this can result in locking of the DB account. Really frustrating when itās not me who administers the database.
Iād like the KNIME team would address this. In a number of topics here we complain ānewā DB nodes is hard to use unless we use they a very simple way.
Iām facing the same problem with Credentials Configuration node. Unfortunately, it seems like there is no solution for the topic you mentioned several times.
Hope KNIME team fix the issue in the nearest future.
Just adding a +1 that I also donāt find the new nodes very flexible.
Especially annoying in case of failure to restore the connection, I canāt open subsequent db nodes config dialog. Like I remember workflow x has a db query reader with an sql statement that I could fully or partially reuse only to realize I will need to reset the whole workflow to be able to do so because the connection failed to restore.
Hm, we could just copy the node into the new workflow. That works mostly except when the workflow is on a different workspace or machine entirely.
This actually brings me to the copy&paste enhancement threads. Nodes should be copy&pasteable between knime instances also over remote desktop, I mean this works for basically most applications, except knime.
Use Case:
In my case I have my laptop and a knime client install on a more powerful server. That server can be used for more demanding tasks or long running tasks. In this example with the DB Query node, if I at least could copy the whole node it would partially solve the issue with not being able to open the config dialog.
