Sidebar

How do you set up a database connection using windows authentication?

0 votes
5.3K views
asked Jun 5, 2015 by jay-v-5573 (150 points)
edited Aug 27, 2020 by rich-c-2789
How do you set up a database connection using windows authentication or integrated security?  I am not sure where to add integratedSecurity=true.  Or where to configure QIE to use the NT user.

2 Answers

0 votes
 
Best answer

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?

answered Jun 16, 2015 by rich-c-2789 (16,180 points)
edited Mar 7 by jon-t-7005
commented Nov 18, 2015 by rich-c-2789 (16,180 points)
Also make sure the port is valid as mentioned in this question: https://www.qvera.com/kb/index.php/1074/resolve-the-tcp-connection-host-sqlserver-port-1433-failed
+1 vote

Additionally if you need to set up a "Database Connection" in QIE using Windows authentication use this example.  Change the "databaseName" to the database you wish to connect to, select the checkbox to "manually set the connection URL", add "integratedSecurity=true;" to the Connection URL, leave the username and password blank  See screen shot: 

For remote servers see the notes in the previous answer.  Again, for remote servers change localhost to the remote server.  This will also require QIE Service to run with a domain or common user.  So, as it is only one SQL Server can be used with Windows Authentication.  If QIE is configured to run with Windows Authentication to the qie database then "Database Connections" using Windows Authentication can only be configured for database running on that same server.  Sql Authentication can still be used to connect to any other Sql Server.

answered Jun 16, 2015 by rich-c-2789 (16,180 points)
...