Database connection centralized

Hi there,

I’m new to KNIME and I’m looking for a clean way to centralize database connections.
What I call “clean” is: an administrator should be able to change the connection string, username & password somewhere when the database changes (could be related to dev / test / prod release or server change) without modifying any code. So far I didn’t find a clear way to do that.

My current guess is this: KNIME Database Extension - Server Setup but so fat I didn’t succeed to set this up and I’m not even sure that’s the proper way. Could someone help please ?

Thank you for your help,
Sébastien

Hello Sébastien,

Welcome to the forum!

For this use case, I would recommend utilizing the Shared Component functionality. With this, you can write a sub-workflow with nodes containing DB connection details that can be shared with different users. Thus, the administrator can maintain this component and change the connection information when it is needed.

You can also include Configuration Nodes within the component that would allow end-user input (username, password) to the component.

For more information about components, check the Documentation.

Let me know if this helps!
-Stephen

1 Like

Dear Stephen,

thank you for your answer.

Unfortunately I would like to avoid this solution, because it means:

  • the code is not the same in dev / test / prod, which is not a very good practice I think
  • devs have to access the prod, which should not be allowed
  • or admins have to be KNIME devs, which is often not the case

Thank you anyway for your help and your quick answer,
Sébastien

Sébastien,

Regarding your three points:

  • This component could be made dynamic. It’d be a simple change when the details of the DB change. Or you could make three separate components for dev/test/prod DBs.
  • You can configure the component to include log-in information (with the Credentials Configuration node). Thus if the devs don’t have credentials to the production DB they wouldn’t be able to access it.
  • Any sort of connection to a DB would require some KNIME Workflow development. This would be the simplest way to make sure the admins don’t get pulled in every time someone needs to access one of the DBs.

Does that help at all?
-Stephen

1 Like

Do you have access to a cloud solution like AWS? What I recommend to my clients is that they use such a capability to store passwords there. Let me give you an example using AWS but Google an d Azsure have similar solutions.

Create userid/password credentials in AWS parameter store. Your security administrator can limit access to the values by environment. Then you call a component which will identify your environment automatically (usually by server or workflow path) as DEV/TEST/QA/PREPROD/PROD and call the parameter store and will return back the correct userid and password as encrypted KNIME credentials. This way the user can connect to the database without hard coding. And the user will never know the userid and password they are connecting with.

1 Like

After working on this again this week I made significant progress !

Registering a jdbc driver on the server I was able to set up a db connection configuration on the server side. It’s acceptable to me hardcode host, port & instance name in the preference file. Then from a knime server to another, the same node can point to a DB or another.

@gregmurphy, I’m quite interested by your idea regarding the usage of a credential store. Still, I don’t get how to transfer the credentials from the store to the database connection component. Could you elaborate a bit please ?

Thank you for you help guys.

@sconilleau
Another feature I recommend looking into would be Client Profiles. These make it easy to store DB preferences on the server side and have the Executor grab them upon initiation. Take a look at this blog post and this part of the Server Admin documentation.

Setting up the DB preferences this way would centralize the driver location along with some preferences which users can import into their AP environments.

From there you could still use a component for user credentials on workflows.

Hope this helps!
-Stephen

Yes, you’re right, I created Client Profiles too actually.