OHDSI Home | Forums | Wiki | Github

Broadsea Initiailization failure with MS Sql data source

Implementers
,
#1

We have stood up the Broadsea container in an Azure environment and are trying to connect an MS Sql Server cdm instance. Despite being able to connect to the Sql Server and run Achilles, when we add the connection string to sources and restart, the Atlas web app gives Initialization failure due to inability to connect with a default sql server connection string with database credentials which works in R. We back tracked and got the same cdm data load to work in the postgres instance in the broadsea container. I have seen similiar posts, but unable to find one that explains if there is some flag or security setting that is needed in the sql server connection string or other trick to getting sql server to connect when not in an environment with AD security.
though we have tried
encrypt=true;trustServerCertificate=true

connection string format:
jdbc:sqlserver://:;databaseName=cdm;user=;password=

Here is sources endpoint details,
if we drop the sql server and reboot webAPI container, we can restore Atlas:

source_daimon (minus connectionstrings)
source_id source_name source_key
1 1 OHDSI Eunomia Demo Database EUNOMIA
2 3 Datamart PostgresTestDB_CDM
3 4 Datamart 2 SQLServer_CDM

password krb_auth_method keytab_name krb_keytab krb_admin_server deleted_date created_by_id created_date
1 PASSWORD NA
2 PASSWORD NA
3 PASSWORD NA
modified_by_id modified_date is_cache_enabled
1 NA t
2 NA t
3 NA t

and the return of sources api endpoint :
[{“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”:3,“sourceName”:“Transplant Datamart”,“sourceDialect”:“postgresql”,“sourceKey”:“PostgresTestDB_CDM”,“daimons”:[{“sourceDaimonId”:16,“daimonType”:“CDM”,“tableQualifier”:“cdm”,“priority”:0},{“sourceDaimonId”:19,“daimonType”:“Temp”,“tableQualifier”:“temp”,“priority”:0},{“sourceDaimonId”:17,“daimonType”:“Vocabulary”,“tableQualifier”:“cdm”,“priority”:10},{“sourceDaimonId”:18,“daimonType”:“Results”,“tableQualifier”:“cdm_results”,“priority”:0}]},{“sourceId”:4,“sourceName”:“Transplant Datamart 2”,“sourceDialect”:“SQLServer”,“sourceKey”:“SQLServer_CDM”,“daimons”:[{“sourceDaimonId”:20,“daimonType”:“CDM”,“tableQualifier”:“cdm”,“priority”:0},{“sourceDaimonId”:21,“daimonType”:“Vocabulary”,“tableQualifier”:“cdm”,“priority”:10},{“sourceDaimonId”:22,“daimonType”:“Results”,“tableQualifier”:“cdm_results”,“priority”:1}]}]

Appreciate thoughts
Holt Oliver

#2

The SQL Server connection string documentation can be found here and Here

I wrote a fairly comprehensive connection string that can be modified just make sure to look up what each argument you include is doing and exclude those you don’t need.

1 Like
#3

Update:

Finally, found the bug with some encouragement from @lee_evans ,
In addition to adding the flags on the connection string to replicate connecting with DatabaseConnector package from the Hades environment. We noticed that the

“sourceDialect”:“SQLServer” in webapi.sources table

1)updating “sourceDialect”:“sql server”
2) connecting to the wepAPI sources refresh endpoint allowed initiatilization.

Thanks for support
Holt Oliver

t