Hi,
I am trying to create a new database connection for Microsoft SQL Server.
In SQL Server Management Studio I have Windows Authentication Mode.
While creating a database connection in ignition i am leaving username field blank. Also i have copied the "mssql-jdbc_auth-12.10.1.x64.dll" to my ignition directory.
But still the connection is showing as Faulted.
What am i doing wrong?
Could you provide the error you are getting?
Now it's guess work for everyone.
java.sql.SQLException: Cannot create PoolableConnectionFactory (This driver is not configured for integrated authentication. ClientConnectionId:04e621a2-1c2e-49cb-aeed-95002525458c)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:656)
at org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:534)
at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:734)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:300)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:253)
at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.lambda$newRetryRunnable$0(DatasourceManagerImpl.java:1097)
at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:593)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.runAndReset(Unknown Source)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication. ClientConnectionId:04e621a2-1c2e-49cb-aeed-95002525458c
at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3422)
at com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:72)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4238)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:4227)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7417)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3488)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2978)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2628)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2471)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1470)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:915)
at org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:52)
at org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:357)
at org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:103)
at org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:652)
... 12 more
Caused by: java.lang.UnsatisfiedLinkError: no mssql-jdbc_auth-9.4.0.x64 in java.library.path: lib;lib/core/gateway
at java.base/java.lang.ClassLoader.loadLibrary(Unknown Source)
at java.base/java.lang.Runtime.loadLibrary0(Unknown Source)
at java.base/java.lang.System.loadLibrary(Unknown Source)
at com.microsoft.sqlserver.jdbc.AuthenticationJNI.(AuthenticationJNI.java:51)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4237)
... 24 more
8.1.48 (b2025042910)
Azul Systems, Inc. 17.0.14
Can you share the connection string parameters.
The integratedSecurity parameter is required to make it work.
jdbc:sqlserver://sqlserver-hostnaam:1433;databaseName=IgnitionDB;integratedSecurity=true
The gateway must run on a windows account, under which account does it run?
Do you use Windows or Linux as the OS?
jdbc:sqlserver://localhost\SQLEXPRESS
i use windows.
there is only 1 account 'Administrator'
By default ignition will run as a LocalAccount without any privileges, it basically runs as anonymous.
The Service must be configured to run as a specific user, the SQL server must also be able to identify that account.
Using windows Active Directory you can achieve this using multiple servers (one for Ignition, one for SQL)
Using a single machine you can use local accounts.
That looks as expected.
Can you show the details of the service in Windows services? There is a tab for the account it is running under.
Ok,
Create a new local account next tot the administrator (Never use an admin account).
And let the ignition server run as that new user.
In SQL you can grant the new user the privileges to connect and interact withe the Database.
Ignition
for sql
I have given the user full control of Ignition Folder
Still showing faulted connection
I did not mention the SQL server account, that must runder under local system account.
Did you restart the gateway? This is required when changing the account under which is runs.
In MSSQL did you add the account? And give him the privileges to select a database?
That part looks promising.
But did you restart the gateway after you changed the running account?
Can you show the general tab in SQL where the account is defined? It shows the authentication mode.
I restarted the gateway as well as sqlexpress service.
where can i see the account definition?
Resolved...The connection is showing Valid
jdbc:sqlserver://localhost\SQLEXPRESS;databaseName=DATABASE_1;integratedSecurity=true;encrypt=true;trustServerCertificate=true
Great notice, I forgot to mention that one.
Is used when using certificates.
A random recollection from the back of my head somewhere is that you can omit the databaseName parameter to enable connection to any database accessible to that server. As you've configured it you'd need a separate connection to connect to another DB.
Omitting the databaseName parameter does mean that you'd have to specify it in your queries.
This might all be nonsense!
I'm getting forgetful. Did I tell you that already?
will connecting to master db and then mentioning the required dbname in the query help?