Save Global Database Credentials in KNIME desktop

[stevelp]

I’m using KNIME to access a single MySQL database. I’d like to be able to enter in my credentials once so they can be used across all the Workflows I create.

I’ve tried using the Credentials Configuration Node, which I could then link to all of my workflows, but I can’t figure out how to make that work. I guess I could just create a single MySQL Connector node and then save that in a linked component. Would that be the recommended approach?

Also, I’d like to share my workflows with other coworkers so they can put in their credentials just once as well.

[Andrew_Steel]

Hi @stevelp,

we store our credentials locally in a JSON file. When starting the workflow, such a file must be selected from the user. So everyone have to use his own credential file.

Best regards
Andrew

[bruno29a]

Hi @stevelp , I would go with a linked component. You can pre-enter the credentials in the Credentials Configuration Node, with “Save Password in configuration…” and uncheck “Prompt user…”, which should create a Knime Credentials object. Convert the node to a linked component and allow the Credentials variable to pass.
This way, you don’t have to store the password in plain text.
Every time you are using a connector for these credentials, just use the component to pass the credentials object. Your password will never be visible this way.

The other advantage with this linked component is that if you change the credentials, you just need to change it in one place which should automatically update any of the linked components in all of your workflows.
That’s how I connect to my dbs usually.

[kienerj]

@bruno29a

The other advantage with this linked component is that if you change the credentials, you just need to change it in one place which should automatically update any of the linked components in all of your workflows.
That’s how I connect to my dbs usually.

This is true but it is also a huge issue if you are more than one person and can not use generic accounts. Eg. if you must login into the database using your own account. If you need different passwords between users, the linked-component approach does not work as changing it means unlinking the component and hence losing the ability for automatic updates say if the connection string changes.

Not storing the password is not really an option because you then have to reenter every time you Open a workflow. This means that the component and everything after it gets reset which is a no-go for long running workflows. if you don’t want to reset, you need to add a separate connector for each database action. But then you need to enter your password in each one of them.

@knime-team:

It’s a very bad situation of you can’t use generic accounts. This situationhas yet to be addressed. In general the credentials workflow is suboptimal. See all of @jan_lender posts about this.

And on the server you do need to store the credentials inside the component as no user entry so if you move a finished workflow to the server you prior need to replace all your connectors with this different component. The credentials worklfow needs some big time improvements.

[bruno29a]

Hey @kienerj , thanks for these details, you are correct about these limitations. However, I tried to provide a solution that avoids storing the credentials in plain text, and also to “centralize” the credentials via linked components which makes it easy to change the credentials when needed.

The limitations that you describe is the same in any application. Like in any app, you either use an app db user where you rarely need to change the credentials (unless you need to regularly for certain PCI compliance), or a user credentials, in which case when different users need to use their own credentials, they still need to enter their own credentials.
In a good app design, you would make this change in only 1 place, which the linked component is a close equivalent.
With the “Save password…” option, it’s not an issue with resetting everything, it will keep the password and the Credentials object will still get re-created and re-used by the connector.
Obviously, without the full context on how the workflow is used/shared (is it a shared instance? is it a copy of the original one? does it run directly off a Knime server? etc…), some things might just not be possible, or whatever has been suggested might be adapted, or might be good enough.

I’d definitely want to know what better suggestion you might have so I can learn from it.

[kienerj]

@bruno29a

With the “Save password…” option, it’s not an issue with resetting everything, it will keep the password and the Credentials object will still get re-created and re-used by the connector.

But that was my point. This doesn’t work at all if the username and password are added via the configuration dialog. When closing the workflow and loading it again, it’s gone.

It only works if the password and username are set as default values in the credential node. And this can only be done after unlinking the component.

Once you unlink, the node doesn’t get any updates anymore like a changed db connection string which is also an issue.

@bruno29a

I’d definitely want to know what better suggestion you might have so I can learn from it.

That’s the core issue. There isn’t any good solution available for sharing DB connectors with named user login.

[bruno29a]

Hi @kienerj , the idea is to use the Credentials Configuration node, configured as below:

You can reset, save, close, re-open the workflow, that credential for user1 in my case, will stay.

Convert this into a component and it will give you a Credentials object, in my case the variable is called credentials. Plug the component to the connector:

I set up a Linux box VM with mysql for this, created a table with 2 records.

Here’s the Connector setting:

For this experiment, I am going to retrieve the data from the test table, and also do a SELECT USER() which will return the current mysql user:

And the result is:

If I reset this workflow, save it as reset, close it, and re-open it, I can re-run the workflow without having to re-enter the credentials.

Pushing the test further, I want to connect with user2. I change the credentials in the component source:

So, now my linked component will need to be updated, which Knime will take care of:

After the component is updated automatically by Knime, I just need to run the workflow without having to enter the credentials, and the results will show that the current user is user2:

I am not sure why you are saying the the credentials are not saved. They are saved, and I have used this method hundreds of time.

And obviously, any other workflows that are using this Credentials component will also get updated automatically with whatever credentials we want to pass

[kienerj]

@bruno29a

I am not sure why you are saying the the credentials are not saved. They are saved, and I have used this method hundreds of time.

Different use-case. In fact yours is the only one that does work. Exactly as I wrote. If you “hard-code” the credentials into the credentials node directly, yes it works. What doesn’t work is adding the credentials via the component configuration, well it works but loses the password when closing the workflow.

Let me explain my use-case and why I say the credentials workflow is broken and needs some serious improvements.

The use-case - Database Connection Changes

In my component I have the credentials node and a DB Connector node and the output is a DB Session Port.

Why this way? Well the whole point of having the linked component to begin with is that you have to update/change the database connection information at exactly 1 place. And yes that does happen more often than one would think and then adjusting 100s of connectors manually is extremely troublesome.

Multi-User

On top of this, the component should be usable by multiple people which means your use-case of hard-coding the credentials as defaults into the node does not work. They have to be added via the Component Configuration and not in the Credentials node directly. And again, password entered in configuration dialog is never saved (there are many older posts about this already).

Also be aware that Credentials Node will use the hard-codes ones upon workflow reopening and ignore whatever was set in the configuration dialog. Try it out. No joke. So you should only ever hardcode if it’s a generic db login or you are the only user.

However there is no way to get connection restoration in a multi-user environment.

I even tried something new based on your screenshots to separate connector and credentials:

This again shows why credentials are confusing. When connecting to the Connector directly, restoration works fine. However as you can see when the Connector is in a Component, it doesn’t work and yes flow variables are set up correctly to flow out and into the components. Why isn’t it working with the component?

Additional mysteries

Why does the credentials dialog state that the password is encrypted weakly? What does it mean exactly? 3DES? Then I wonder why not AES. Not like AES would be any harder to use. Or is it because the key must be stored somewhere so it could be found if one looked hard enough? We don’t know. Clarification would be needed.

Suggestion

Store the passwords from the configuration as well if the user asks for it. Store it in a platform standard way (Windows Vault? GNOME Keyring? On Linux plain-text file with proper permissions might be enough. Anyway when copying to a different machine the credentials don’t get copied as well.).
Or simply make the workflow with the 2 components in my last image work.

[bruno29a]

Hi @kienerj , this is getting very interesting As I said, not knowing the exact context of @stevelp request, I provided a way to help with what he was looking for and does not store passwords in plain text. The use of the component was to be able to change the credentials in one place for multiple workflows if another coworker of his uses the workflows. @stevelp , I hope you can adapt what is being discussed here to your needs - I feel like we’re “hacking” this thread lol

@kienerj , I would not include the connector in the component simply because I tend to separate processes/functionalities, although in this case, come to think of it, it does make sense that the credentials go with the connector since they’re exclusive to the connector.

Regarding the input from the component itself, are you using the “Save Password” option? It does not lose the credentials if you have the Save Password (I tried it and confirm). Well, to clarify, if I save the workflow after changing the credentials via the component UI like you did, the new credentials are saved in the workflow. And obviously, if another user opens the workflow, he/she will have the last saved credentials - again, I don’t know the exact context on how you guys use your workflow. And obviously also, if someone changes the credentials in the source component, this change will overwrite the values of the linked components in the workflow.

Regarding the weak password encryption, I don’t have much info about it and I am not a fan about it because of this. I’ve wondered the same questions you are asking, but it’s still better than plain text in the end.

In general (not always), I usually use the method I mentioned for app db user (basically a shared user), and for workflows that use user credentials, I usually set it and the connection string at the workflow level:

This will prompt the user to enter his/her credentials and not save the password

Another way I use and want to share with you is in relation for adding multiple environment credentials to the workflow (obviously you don’t want everyone to have access to these credentials, only a couple of superusers). I put together a quick example (that is why you do not see them as linked components, but in my real workflows, I have linked components for these):

The Environment Selector allows you to choose which env you want to connect to:

And here’s the inside/details of the “Set Connection Configuration” component:

In the end, what we choose or how we choose to implement something all depends on the situation and use-case. I hope you can adapt some of these to your needs.

[kienerj]

@bruno29a

Regarding the input from the component itself, are you using the “Save Password” option? It does not lose the credentials if you have the Save Password (I tried it and confirm).

It doesn’t work for multi-user when you do not have anything set as defaults:

I actually think the username could be the issue as the error I get is:

Status: DataLoadError: Loading model internals failed: FATAL: no PostgreSQL user name specified in startup packet

And using the Vernalis Node “Credential Variable Extractor” we can see the credentials have no username:

Please try without any defaults in credentials node to understand the issue. If you want more than 1 user to ise the component each with their own credentials, it doesn’t work.

EDIT:

In fact if you set a default user and override it in configuration upon reload it will try to connect with the default user using the password configured in the dialog → wrong password and if you have multiple such connections in a workflow you can lock your account that way (wrong password x times).

[bruno29a]

Hi @kienerj , I am not sure why it is not working on your side. As I stated before, it is working on my side, and I tried again, even with empty default values.

I have removed username and password from my source component and saved it:

I updated the link to the component in my workflow, and open the component interaction and confirm that the username and password are both empty:

I enter the credentials for user1:

And I am able to successfully connect:

I reset the workflow and close it:

I re-open the workflow:

And there is nothing to update since I did not change the component:

Without entering any credentials, I run Node 18 successfully:

As you can see, it used the previously saved credentials, which was user1.

I’ve repeated the same thing with the following use-case:

• Enter credentials for user 2, run, reset, save, close, re-open and re-run Node 18:
  

As per the first example, it ran using the previously saved credentials, in this case user2 (fyi, each user has its own different password)

• Modify the source component by adding user1 credentials, update linked component in the workflow, re-run Node 18 and it ran with user1 credentials, so it overwrote the previously saved user2 credentials, which is the expected behaviour.

I am not sure how you are getting this error. Obviously if you do not pass any username at all during the process, it will not send any username to the connector.

[kienerj]

@bruno29a

I reset the workflow and close it:

The point is not to reset before close but to keep it executed. Then the DB connection isn’t restored correctly once you open the workflow again which is the actual problem.

Say you have a long running database call or in general analysis. then the day is over. you save close everything and continue next day. However you realize your DB connection doesn’t work anymore. If you reset anything connected to this same initial DB connector, you either need to run everything again because you also need to reset the DB connector your you add a new DB connector and connect that one. Which means you will end up with multiple connectors and for each you will have to retype the credentials ever time you open the workflow.

Having a separate credentials component for the whole workflow will only make it worse because the credentials node is whats faulty upon reopening a workflow. And from what I can tell it actually is a problem with the username and not the password (or both).

So in your example, execute the 3 nodes, save, close and then reopen again and see what happens. The connector will get the warning sign (but stay green) and become “unusable” as it actually doesn’t have a db connection in it’s output.

[bruno29a]

Hi @kienerj , now I understand a bit more where you are coming from, and I was able to reproduce what I think was the point that you are raising.

At first, I thought the issue that you had was to re-initiate a new db connection further down your workflow, which for me was “common practice” if you will, since db connections last only for a limited amount of time.

While trying to replicate that you could re-use the same credentials for the new connection after closing and re-opening the workflow, I actually ended up reproducing the issue that you are raising, and I am quite surprise myself with this behaviour.

So, here goes:

Node 17 represents an expired db connection, so I tried establishing a new connection (Node 21) using the previously created Credentials object, but it did not work, even though the Credentials object is still there:

And it produces this error:
ERROR MySQL Connector 3:21 Execute failed: Access denied for user ''@'gateway' (using password: NO)

This is unexpected on my part. I am not sure why it behaves like this. As you pointed out, it’s green but is indeed unusable. It would seem that Credentials objects are valid only during a Knime session.

A work-around that you can do though, is to add the component again, as it looks like a reset state is ok to re-run:

After adding a copy of the Credentials component (which is at reset state when added), it looks like the connector is able to get a proper credentials value:

Not ideal, but this would at least avoid having to re-enter the credentials, to a certain point of course.