WebAPI `source` table `source_connection` value declaration

[scheah92 (Samuel Cheah)]

Hi all,

My team is exploring an internal implementation of the ATLAS dashboard. One question pertaining to the source table that needs to be populated is that the JDBC connection allowing ATLAS to connect to the correct schemas (via the source_daimon table) is explicitly declared as plain text under the source_connection field.

My question is whether there is a more a secure option available that doesn’t involve hardcoding credentials in plain text? Granted this is meant for a dedicated ‘OHDSI’ user that only serves ATLAS/WebAPI, but from a security perspective this is quite unsightly. I have not found any other posts regarding this so I’d appreciate any clarification around this point.

For avoidance of doubt, I refer to this section of the wiki.

Thanks in advance.

[Ajit_Londhe]

When adding new sources via the Atlas GUI (Configuration → New Source), the user and password fields will be encrypted.

If you add sources via SQL inserts, the user and password fields remain plaintext.

It is best to not add sources using SQL, but instead using the Atlas GUI.

[scheah92 (Samuel Cheah)]

Hi @Ajit_Londhe, thanks for getting back to me.

I was not aware that the ATLAS GUI allows for adding new sources this way, thanks for the info. Question to the team: If updating sources via the GUI is recommended, shouldn’t the Wiki be updated to reflect this? The only reason I was doing SQL inserts into the source table is because I was following the guide.

Adding sources via GUI also creates a problem of the workflow not being manageable as code. As far as possible, we try to manage all our internal processes via scripts, which includes adding new sources. Thus having to interact with the GUI for this purpose would break the workflow. Would you know of any method that would allow us to safely add sources via code?

Thanks in advance.

[Ajit_Londhe]

I like the idea of programmatic wrappers for these admin api calls the gui is making.

I’ve opened a new git enhancement:

Functions for WebAPI admins

[Chris_Knoll]

@Ajit_Londhe

If you add sources via SQL inserts, the user and password fields remain plaintext.

When WebAPI starts up, the SourceService will encrypt plaintext passwords if jasypt.encryptor.enabled is set.

See: https://github.com/OHDSI/WebAPI/blob/fd108571086fceea8513389fab426a6ea8101888/src/main/java/org/ohdsi/webapi/source/SourceService.java#L54

[Sanjay_Udoshi]

Which version of Atlas allows New Source configuration in the GUI? I am using the latest Broadsea 2.0 dockers and that feature is not enabled.

[Sanjay_Udoshi]

I have correctly configured connection to our MS SQL db, and tested that the connection string works.
However, I am not seeing any data in the dashboard. The connection string has ohdsi_user as the login. Does the app need another user? ohdsi_app_user and what permissions does that user need in the MS SQL db?

[Ajit_Londhe]

The new source GUI is given to admins. Are you going to be using security in this Atlas instance?

Regarding Data Sources, it looks like your IRSFonOMOP source isn’t accessible with the current db account. You can see the “Vocabulary Version” is blank for it in the Config screenshot. Please ensure that your db account can select from the cdm schema and results schema.

When those are verified, make sure you also have run Achilles, as that is what Data Sources will pull from, the achilles tables in the results schema for this source.

[Sanjay_Udoshi]

Thank you for this guidance! I’ll try those steps and report back on progress.
This is our dev instance. We intend to move to production around mid March.

[Sanjay_Udoshi]

I successfully connected to our MS SQL through RStudio (I am very new to this, so apologies for my naive questions)…

However when I use this:

connectionDetails ← createConnectionDetails(dbms = “sql server”,
server = “external-prod-hive-sql-server.database.windows.net”,
user = “ohdsi_user”,
password = “****************”)

achilles(connectionDetails = connectionDetails,
cdmDatabaseSchema = “OMOP”,
resultsDatabaseSchema = “RESULTS”,
outputFolder = “output”)

It errors:
Error: unexpected input in “connectionDetails ← createConnectionDetails(dbms = “”

[Ajit_Londhe]

See my response in the other thread, I see formatted quotes in your createConnectionDetails call. They should be regular quotes.

[Sanjay_Udoshi]

log_achilles.txt (92.8 KB)
runAchilles-RScriptv3.txt (1017 Bytes)

Hi Ajit!

Thanks for your help. I corrected the quotes issue. Never use MacOS Notes app to copy and paste anything like scripts and such! So finicky.

I am happy to report that I ran Achilles successfully. Attached is the log file. Unfortunately when I logged into Atlas, still no data is showing in the Dashboard report. I checked to see the ohdsi_app user has read/write/execute. Still struggling to get that working even though the Achilles tables are correctly written in the results schema.

-Sanjay

[Chris_Knoll]

which version of WebAPI are you running? Tehre’s an issue that if you get an exception when pulilng the data, it stores a ‘no data’ result. To address this, you can truncate the webapi table: achilles_cache,

[Sanjay_Udoshi]

I tried your suggestion but I keep getting this error:

2023-02-26 12:13:47 2023-02-26 17:13:47.959 INFO http-nio-8080-exec-2 com.odysseusinc.logging.LoggingService - [] - Could not get JDBC Connection; nested exception is com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ‘ohdsi_user’. ClientConnectionId:881291c2-f015-4f4c-aa2d-cc76f0ee45dd

I’m running the latest version of WebAPI. http://127.0.0.1/WebAPI/info

{“version”:“2.12.1”,“buildInfo”:{“artifactVersion”:“WebAPI 2.12.1”,“build”:“NA”,“timestamp”:“Wed Feb 01 18:44:39 UTC 2023”,“branch”:“2.12.1”,“commitId”:“985e8722”,“atlasRepositoryInfo”:{“milestoneId”:45,“releaseTag”:""},“webapiRepositoryInfo”:{“milestoneId”:46,“releaseTag”:""}},“configuration”:{“security”:{“samlActivated”:false,“enabled”:false,“samlEnabled”:false},“vocabulary”:{“solrEnabled”:false},“plugins”:{“atlasgisEnabled”:false},“person”:{“viewDatesPermitted”:false},“heracles”:{“smallCellCount”:“5”}}}

http://127.0.0.1/WebAPI/source/sources

[{“sourceId”:1,“sourceName”:“OHDSI Eunomia Demo Database”,“sourceDialect”:“postgresql”,“sourceKey”:“EUNOMIA”,“daimons”:[{“sourceDaimonId”:1,“daimonType”:“CDM”,“tableQualifier”:“demo_cdm”,“priority”:0},{“sourceDaimonId”:2,“daimonType”:“Vocabulary”,“tableQualifier”:“demo_cdm”,“priority”:10},{“sourceDaimonId”:3,“daimonType”:“Results”,“tableQualifier”:“demo_cdm_results”,“priority”:0}]},{“sourceId”:2,“sourceName”:“IRSFonOMOP”,“sourceDialect”:“sql server”,“sourceKey”:“OMOP”,“daimons”:[{“sourceDaimonId”:4,“daimonType”:“CDM”,“tableQualifier”:“OMOP”,“priority”:0},{“sourceDaimonId”:5,“daimonType”:“Vocabulary”,“tableQualifier”:“OMOP”,“priority”:10},{“sourceDaimonId”:6,“daimonType”:“Results”,“tableQualifier”:“RESULTS”,“priority”:0}]}]

[Sanjay_Udoshi]

Hi Ajit,

You mentioned “the new source GUI is given to admins”. How do I get/enable that functionality in my Atlas install?

-S

[Sanjay_Udoshi]

I just realized I missed a critical configuration step. Running it now.

http://127.0.0.1/WebAPI/ddl/results?dialect=sql%20server&schema=RESULTS&vocabSchema=OMOP&tempSchema=TEMP&initConceptHierarchy=true

This DDL has to be run in my MS SQL CDM environment.

[Chris_Knoll]

@Sanjay_Udoshi

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ‘ohdsi_user’.

This error is pretty clear that you have an authentication error with ohdsi_user. Please check the credentials you are using for connecting tot he server.

[Sanjay_Udoshi]

Success! Finally. It was the formatting of the JDBC string.
Thank you for the guidance!

Atlas is connected running beautifully.

[Sanjay_Udoshi]

https://www.ohdsi.org/web/wiki/doku.php?id=development:security
To enable Admin GUI

[greshje.gmail (John Gresh)]

Hi @Sanjay_Udoshi,

I’m having similar issues trying to connect Broadsea to an instance of a database running outside of Broadsea. I’m running PostgreSql on localhost. I am getting a connection refused error when I try to start up Broadsea. My docker-compose.yml and output from the WebAPI launch in docker are attached.
docker-compose.yml.txt (3.6 KB)
web-api-output.txt (280.0 KB)

@Sanjay_Udoshi, @Ajit_Londhe and @lee_evans, is there documentation for:

• How to connect Broadsea to an existing external database (CDM and webapi)
• How to roll and existing external database (CDM and webapi) into a new Docker distribution

Thanks again for all of your help with this everybody,
John