advice for setting up a mssql db for HAPI fhir

[Kevin]

Is there any documentation on how to set up A HAPI service that uses MSSQL as its DB? has anyone set up there own?

I tried to follow the directions in the starter here  under "Microsoft SQL Server configuration"

But running the application against a live db would connect but not store data and the mssql docker logs container would be full of errors.

Any help would be appropriated. let me know if you need more info. thanks in advance

[Kevin Dougan]

Hi Kevin,

What values did you use for the following 5 properties?

spring:
   datasource:
      url: 'jdbc:sqlserver://<server>:<port>;databaseName=<databasename>'
      username: admin
      password: admin
      driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
hibernate.dialect: org.hibernate.dialect.SQLServerDialect

Also, can you also test your connection using a different tool, just to ensure that you have the values set correctly?

Thanks!

Kevin

[Kevin]

Thanks for the quick response and sorry for taking so long to respond myself. Unfortunately I lost my previous settings after they were reverted, but this look familiar. Though, I think I was missing the  hibernate.dialect as this is not explicitly defined in the documentation. I was able to connect using SQL Serve Management Studio when I had it set up, I just could not get the application communicating properly (connected but failed to store data). Could not having the dialect defined cause this issue?

When I get back to working on this issue, I will share my exact settings or if I get it working and how.

[Kevin]

Sorry for the very very late response. We had been using postgres since i had last responded and nwo we are looking into msslq again. 

I created a mssql server wih this command: docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=SQLDefaultPassword123" -p 1433:1433 --name sql42 --hostname sql42 -d mcr.microsoft.com/mssql/server:2022-latest

Then from within the docker image created the "hapi" db and verified it exixts.

These settings are what I have in my docker for the jpaserver image for local testing

hapi-fhir-jpaserver-start:
image: "hapiproject/hapi:v6.6.0"
container_name: hapi-fhir-jpaserver-start
restart: on-failure
ports:
- "8080:8080"
environment:
# spring.datasource.url: 'jdbc:postgresql://hapi-fhir-postgres:5432/hapi'
spring.datasource.url: 'jdbc:sqlserver://localhost:1433;databaseName=hapi'
spring.datasource.username: sa
spring.datasource.password: SQLDefaultPassword123
spring.datasource.driverClassName: com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.properties.hibernate.dialect: org.hibernate.dialect.SQLServer2012Dialect
# hapi.fhir.fhir_version: R4
# hapi.fhir.bulk_export_enabled: true
# hapi.fhir.bulk_import_enabled: true

I have also tried "localhost/sql42" and just "sql42"

but i get this same error when the container runs:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "Connection refused. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall."

I am able to connect to the sql server from outside docker with Microsoft SQL Server Management Studio with these setting:

I am not sure what I am missing in these settings to get it to connect. Any help would be appriciated.