SQL Server & Trusted Connection

How can I configure Ignition to connect to my SQL Server database using a trusted connection/SSPI? I’m looking for something similar to …

jdbc:sqlserver://localhost;integratedSecurity=true;

… I goofed around with the Connect URL and Extra Connection Properties but was unsuccessful.

I believe that use of “windows authentication” first requires a bit of manual labor. See this user manual page:
Connecting to MS SQL Server

If that doesn’t help, let me know and we’ll see what we can do.

Regards,

I tried the instructions in the User Manual but unfortunately was not successful. I initially tried using v1.2 of the JDBC driver … no luck. I saw that v1.2 made mention of SQL Server 2005 but not 2008, which happened to be the version I’m using. I found the latest version of the JDBC driver (3.0) and tried that … no luck. I figured maybe it was an issue with a mismatch of the DLL (from v3.0) and whatever Ignition’s using by default … I updated the .jar for the driver using sqljdbc4.jar … no luck.

In all the above scenarios, I encountered the following error …

[quote]org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (This driver is not configured for integrated authentication.)
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:184)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:124)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:937)
java.lang.Thread.run(Unknown Source)
com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.
com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:1352)
com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2329)
com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:1905)
com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:1893)
com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1045)
com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:817)
com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:842)
org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1247)
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:184)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:124)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:937)
java.lang.Thread.run(Unknown Source)
java.lang.UnsatisfiedLinkError: no sqljdbc_auth in java.library.path
java.lang.ClassLoader.loadLibrary(Unknown Source)
java.lang.Runtime.loadLibrary0(Unknown Source)
java.lang.System.loadLibrary(Unknown Source)
com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:32)
com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:1902)
com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:1893)
com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)
com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)
com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1045)
com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:817)
com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:700)
com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:842)
org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1247)
org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:184)
com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:124)
com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.run(DatasourceManagerImpl.java:937)
java.lang.Thread.run(Unknown Source)[/quote]
For reference, I’m running Ignition v.7.1.1.5078 on Windows Server 2008 …

The easiest way we’ve found to solve this (Mike just helped someone with this, don’t know if it was you):

  1. Install the Microsoft 2.0 JDBC driver
  2. Edit [tt]ignition.conf[/tt] to add something like following line, but adjusted for your install dir and architecture:
wrapper.java.library.path.2=C:\Microsoft SQL Server 2005 JDBC Driver\sqljdbc_<version>\enu\auth\x86

Note about the architecture: you need to use the architecture of the JVM… so, if you have a x64 machine, but are running the x86 JVM, use x86.

  1. Restart Ignition.

Hope this works…

Thanks Colby, that works … I’m documenting the steps I had to take and I had a couple follow-up questions …

  1. Why are these manual steps required? Does Ignition use a different version of Microsoft’s JDBC driver?

  2. Does the .2 portion of wrapper.java.library.path.2 correspond to the version of the JDBC driver?

  3. When I created a new Windows account, I found that I had to make the account a member of the Administrators group in order to get Ignition up and running. If I wished to minimize the permissions applied to my new Windows account, what privileges would the account require to allow things to function?

… I ask the questions above simply to gather a deeper understanding of the changes made … plus to satisfy my own curiosity :slight_smile:

  1. The manual steps are only required for windows authentication, using SQL authentication does not require them. Because windows authentication requires the use of native DLLs, some extra work is required to make sure the dll is install and Ignition can find it.

  2. The .2 portion is only because the parameters in that config file must be numbered, and there’s already an existing wrapper.java.library.path argument.

  3. This I’m not quite sure about, because off of the top of my head I don’t know where it’s failing. We’d probably have to do some testing to find out. But, this account only really needs to be used to run the service, so you might be able to prevent interactive login with it.

Regards,

Thanks for clarifying …

I set up the account I’m using to run only as a service (without interactive logon), however I’d rather not let the account operate as a member of the Administrators group … I’d perfer the account only had those permissions required to function, no more …

I’ll play with permissions on my end and see if I can’t find a bare minimum … I’m not using this setup on a project (currently) so there’s no pressure to find a solution ASAP, but I would like to use it for future projects.

Again, thanks for the info!

I created a new group (Inductive Automation) and added my Windows Account (Ignition) to it. I’ve given the Inductive Automation group the following permissions …

[quote]C:\Program Files\Java
[ul]Read & Execute[/ul]

C:\Program Files\Inductive Automation
[ul]Full Control[/ul]

C:\Program Files\Microsoft SQL Server JDBC Driver
[ul]Read & Execute[/ul][/quote]… and the Ignition Gateway starts without error. At the moment, I’ve got nothing more than one database connection configured (using Windows authentication!); no projects, no devices, etc. As opportunities arise, I’ll test my environment to see if I’m missing anything … I’ll (hopefully remember to) update this thread with anything I find …

Ignition will also (obviously) need network access, including opening up a listening port, and access to the temp dir (what the temp dir is depends on the user acct it is running onder)

[quote=“Colby.Clegg”]The easiest way we’ve found to solve this :

  1. Install the Microsoft 2.0 JDBC driver
    …[/quote]
    Will the Microsoft SQL Server JDBC Driver 3.0 work just as well? The link you gave sent me to the MS site to download v3.