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 ?
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.
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.
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.
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 ?
@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.