Connection to MS SQL Server using Windows Authentication (Integrated Security) is configured through the JDBC driver and the account used to run the QIE service. These instructions are based on the use of the Microsoft JDBC driver for SQL Server.
1. Create a new pre load folder that QIE has access to or reuse an existing preload folder:
Example: C:\ProgramData\QIE\PreLoad
2. Download to the pre load folder the same version of the Microsoft JDBC driver mssql-jdbc-X.X.X.jreX.jar and mssql-jdbc_auth-XX.X.X.x64.dll from microsoft using the following links:
https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/
https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc_auth/
NOTE: The JDBC and JDBC_AUTH files need to be the same version
This example will use 10.2.3 for jre8 for a Windows 64 bit OS so we need:
mssql-jdbc-10.2.3.jre8.jar
mssql-jdbc_auth-10.2.3.x64.dll
3. In QIE Service Manager -> Startup tab -> arguments add or modify the database connection information options. Add "integratedSecurity=true" to the connection url. And add the two options that point to the pre load folder. Example:
-Dconnection.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
-Dconnection.url=jdbc:sqlserver://localhost:1433;databaseName=qie;integratedSecurity=true;
-Dhibernate.dialect=com.qvera.qie.persistence.SQLServer2019UnicodeDialect
-Dqie.preloadLibDir=C:\ProgramData\QIE\PreLoad
-Djava.library.path=C:\ProgramData\QIE\PreLoad
Remove username and password if present
-Dconnection.username=qie
-Dconnection.password=qie
NOTE: This -Djava.library.path option overrides the java.library.path defaults. Code that depends on .dlls in windows system32 folder will break unless all paths to dependencies are included (seperated by a semicolon). Example:
-Djava.library.path=C:\ProgramData\QIE\PreLoad;C:\Windows\System32;
NOTE: If the paths above are not configured properly you might find an error in the qieLauncher.log.0 file stating:
java.lang.UnsatisfiedLinkError: Unable to load authentication DLL mssql-jdbc_auth-10.2.3.x64
4. Give the windows account access to the QIE database via User Mapping for user account/login (I used the local system account to start qie which is "NT AUTHORITY\SYSTEM" in Sql Server Management Studio). See screen Shot:
NOTE: To use another account find the QIEService(64) in windows services. Goto properties -> Log On tab, configure it to use the desired user. The service will then need to be restarted. For remote servers without a shared domain server try selecting a login with the same username and password on both servers. See the last reference below for additional options without a domain server.
NOTE: For remote servers see the NOTE above and change localhost in the connection URL to point to the remote server.
References:
https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/
https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc_auth/
https://msdn.microsoft.com/en-us/library/ms378428.aspx
http://blog.sqlauthority.com/2009/08/20/sql-server-fix-error-cannot-open-database-requested-by-the-login-the-login-failed-login-failed-for-user-nt-authoritynetwork-service/
https://thusithamabotuwana.wordpress.com/2012/07/19/connecting-to-sql-server-from-java/
http://www.mssqltips.com/sqlservertip/3250/connect-to-sql-servers-in-another-domain-using-windows-authentication/
See also:
How can I change SQL Server authentication mode?