MSSQL Express with Window Authentication

General Discussion
1

I am facing problem trying to set up connection with MSSQL Express
here is the Error code

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (This driver is not configured for integrated authentication.)
	at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
	at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:242)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:196)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer.lambda$newRetryRunnable$0(DatasourceManagerImpl.java:998)
	at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:565)
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: This driver is not configured for integrated authentication.
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:170)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:2338)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:1929)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(SQLServerConnection.java:41)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:1917)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4026)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1416)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1061)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:833)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:716)
	at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:841)
	at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
	at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
	at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1247)
	at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
	... 12 more

7.9.9 (b2018081621)
Oracle Corporation 1.8.0_191

I had downloaded the driver and copy to C:\Program Files\Inductive Automation\Ignition\lib\

the documentation said

The account used to connect will be the account that Ignition is running under in the services menu. To setup Ignition to logon using the right Windows account, open the Services Control Panel from Start > Control Panel > Administrative Tools > Services

Right-click the Ignition service and choose Properties.

Select the Log On tab.

Choose the This account radio button and enter in your Windows username and password. --> ???

Click OK to save.

Now restart the Ignition service to make this change take effect. Click the Action > Restart button in the menubar to restart the Ignition service (or your can stop and start from the right-click menu).

Note :I am using trial version

Simon

2

You need to add the Microsoft authentication libraries to your gateway to add support for integrated authentication.
Download the SQL Server JDBC driver package from Microsoft that matches the JDBC driver running on the Gateway.

image.png1180Ă—339 38.1 KB

In the driver you download there should be an auth folder with a dll sqljdbc_auth.dll. Drop this into your lib\ folder of your gateway install and restart the gateway. You only need to put the auth dll in the lib folder.

That will configure the driver. After that you should add the correct properties to the connection settings.

After restarting the Gateway, Ignition will then connect to the DB as whatever account the Ignition service is running as.

3 Likes
3

image.png1366Ă—768 103 KB

I don’t see any Jar file listed. Any setting I need to do?

Simon

4

Ok:
Now I have different error

Cannot create PoolableConnectionFactory (Cannot open database “IgnitionTest” requested by the login. The login failed. ClientConnectionId:2f8351e1-7865-480e-8f72-bcc587b992d9)

Anyone

5

That is an error direct from MSSQL server. You need to configure the login account (Ignition service account) to have access to the “IgnitionTest” database. SQL server permissions have lots of layers and are annoying.

6

Where is this suppose to be?

I had been struggling to learn Inductive Scada

Thanks

Simon

7

You need to configure the account you are using to connect in SQL Server. This is outside of Ignition. It appears you setup Ignition connect to the server, but on the database server you need to setup that users permissions. You would need to refer to the Microsoft docs for this.

This video shows the basic process, but every environment is a bit different. https://www.youtube.com/watch?v=dU3lMPrrSRs

8

The sqlserver express is set up with window authentication, not mixed mode or sqlmode.

9

Never Mind. I git it done with the Ignition service with Sql Window Authentication.

Thanks Ryan for your help.