Debugging MS SQL Server Connection

[rberends]

Hey there,

For our internal application we are trying to connect Para to our MS SQL server 2017 through the plugin. Unfortunately, the connection isn't working yet. Most likely this is a configuration issue from our side.

When the following properties are set in application.conf:

para.sql.driver 
para.sql.url 
para.sql.user 
para.sql.password

Para seems to revert to the default config (H2DAO, LuceneSearch, CaffeineCache and port 8080).

This is not the case when the connection details are purposefully left out of the conf file, as we then get SQL DAOexceptions:

2022-07-13 13:58:07,948 [ERROR] c.e.p.server.persistence.SqlUtils - Missing required configuration parameter "para.sql.url" for the SqlDAO
2022-07-13 13:58:07,948 [ERROR] c.e.p.server.persistence.SqlUtils - Missing required configuration parameter "para.sql.driver" for the SqlDAO

Most likely this happens because the connection can't be made. Is it the default behaviour of para to fall back to what it knows to work? Can I get a more detailed logging level in the log file when a connection can't be made to the SQL server with the provided connection details?

[albogdano]

Try to add para.dao = "SqlDAO" to your configuration. Then add the MS SQL JDBC connector (driver) JAR file to ./lib next to your Para JAR file. Then start Para with -Dloader.path=./lib. You should see that SqlDAO is loaded and used. Then check the URL for connecting to SQL server and configure the other properties - it should look like this: sqlserver://localhost:1433;. I am not sure what the value of para.sql.driver but try com.microsoft.sqlserver.jdbc.SQLServerDriver.

[rberends]

Hey there! thanks for the assistance so far. Here's our application.conf, some sensitive details anonymised.

# Default configuration file for Para
para.env = "production"
para.dao = "SqlDAO"
para.search = "LuceneSearch"
para.port = 9000 

para.sql.driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
para.sql.url = "sqlserver://xxx;databaseName=xxx;"
para.sql.user = "xxx"
para.sql.password = "xxx"

para.security.protected.admin = ["/admin", "/admin/**", ["admin"]]
para.security.ignored = ["/", "/images/**", "/styles/**", "/scripts/**", "/v1/_setup", "/v1/_health", "/v1/_config/options"]

para.root_access_key = "xxx"
para.root_secret_key = "xxx"

The Microsoft JBDC and para SQL Dao files have both been placed in the webapps/xxx-war/meta-inf/lib and /lib locations on our local Tomcat installation that we've deployed the Para .war on.

These makes para try the default boot:

2022-07-14 09:20:47,726 [INFO ] c.erudika.para.core.utils.CoreUtils - Loaded new DAO, Search and Cache implementations - H2DAO, LuceneSearch and CaffeineCache.

What is the strangest thing about it is that removing the connection details for SQL but keeping the SQLDao line, does make the log mention the SQL dao, along with some exceptions:

2022-07-14 09:24:32,981 [INFO ] c.erudika.para.core.utils.CoreUtils - Loaded new DAO, Search and Cache implementations - SqlDAO, LuceneSearch and CaffeineCache.
2022-07-14 09:24:33,025 [ERROR] c.e.p.server.persistence.SqlUtils - Missing required configuration parameter "para.sql.url" for the SqlDAO

[rberends]

We have the connection working. It proved to be related to the "integrated security" part of the connection, requiring a specific windows user as well as some DLLs to be put into the right location.

[albogdano]

Great! If anyone else gets stuck here, I managed to connect to a local MS SQL server running on Docker by adding ;trustServerCertificate=true at the end of the connection URL. Without that parameter I got error messages like:

[ERROR] Failed to connect to SQL database: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:e7de95b8-25f6-4cd6-8808-49379e001dfd