How to configure the JDBC connection to the QIE database for MSSQL?

Question

I am trying to configure QIE to use a MSSQL database. What is the correct way to configure the connection? Specifically what values should I use for:

-Dconnection.driver=
-Dconnection.url=
-Dhibernate.dialect=

Answer 1

That depends on several variables.

     1. The version of QIE
     2. The version of the MSSQL database
     3. The version of the JDBC Driver for MSSQL (the latest driver versions have a different jar depending on the JAVA JRE used to run QIE)
     4. How the MSSQL server is configured. (Windows Authentication or SQL Server Authentication etc. See resources below or the database documentation for more info.)
     etc.

For QIE version .49, with MSSQL 2019, and MSSQL JDBC driver 8.4.0 for JRE 8 (mssql-jdbc-8.4.0.jre8.jar), Windows Authentication:

     -Dconnection.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
     -Dconnection.url=jdbc:sqlserver://[your mssql instance name]:[port to your mssql instance];databaseName=[your database name];integratedSecurity=true;
     -Dhibernate.dialect=com.qvera.qie.persistence.SQLServer2019UnicodeDialect

For QIE version .49, with MSSQL 2016, and MSSQL JDBC driver 8.4.0 for JRE 8 (mssql-jdbc-8.4.0.jre8.jar), SQL Server Authentication:

     -Dconnection.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
     -Dconnection.url=jdbc:sqlserver://[your mssql instance name]:[port to your mssql instance];databaseName=[your database name];
     -Dhibernate.dialect=com.qvera.qie.persistence.SQLServer2016UnicodeDialect

For QIE version .48, with MSSQL 2014, and MSSQL JDBC driver 8.4.0 for JRE 8 (mssql-jdbc-8.4.0.jre8.jar), SQL Server Authentication:

     -Dconnection.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
     -Dconnection.url=jdbc:sqlserver://[your mssql instance name]:[port to your mssql instance];databaseName=[your database name];
     -Dhibernate.dialect=com.qvera.qie.persistence.SQLServer2014UnicodeDialect

For QIE version .48, with MSSQL 2008R2, and MSSQL JDBC driver 6.4.0 for JRE 8 (mssql-jdbc-6.4.0.jre8.jar), SQL Server Authentication:

     -Dconnection.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
     -Dconnection.url=jdbc:sqlserver://[your mssql instance name]:[port to your mssql instance];databaseName=[your database name];
     -Dhibernate.dialect=com.qvera.qie.persistence.SQLServer2008UnicodeDialect

Note: 3 of the 4 examples above use SQL Server Authentication. These use the default qie user unless specified with: -Dconnection.username=[your sql server user], and -Dconnection.password=[your sql server password]. The first example uses Windows Authentication and adds "integratedSecurity=true" to the connection url. Refer to one of the links below to configure the user for Windows Authentication using integrated security.

See also:
     What dialects are available in QIE?
     How do you set up a database connection using windows authentication?
     Which should I use in QIE to connect to Microsoft SQL Server: Windows Authentication or SQL Server Authentication?
     How can I connect to a "MS SQL Server Express" database using QIE?
     How can I change SQL Server authentication mode?

Resources:
     https://docs.microsoft.com/en-us/sql/connect/jdbc/overview-of-the-jdbc-driver?view=sql-server-ver15
     https://docs.microsoft.com/en-us/sql/connect/jdbc/using-the-jdbc-driver?view=sql-server-ver15
     https://support.microsoft.com/en-us/help/321185/how-to-determine-the-version-edition-and-update-level-of-sql-server-an