I cannot connect to SQL server using windows authentication.
The windows user “svc_ignition” is a DB admin on the SQL server, it is the user running the ignition service and is an admin on the server hosting the ignition service (windows server 2003 R2).
I have tried all steps as discribed in the posts below. I have installed the sqljdbc_auth.dll in the tomcat and JRE bin folders. I have added the following statement to the ignition.conf file:
wrapper.java.library.path.2=C:\Program Files\Microsoft SQL Server JDBC Driver 3.0\sqljdbc_3.0\enu\auth\x86
Please help. I can connect to this db using SQL Studio and windows authentication. There is something about the ignition service that cannot. I was able to connect using SQL authentication from ignition to trouble shoot, but this is not allowed in my environment. Saying it is better to just use SQL authentication is just poor design on the part of ID and ignition.
Well, there are several steps to connecting to Microsoft SQL Server. Here are some things to check:
Microsoft SQL Server has TCP/IP enabled. To find out open the SQL Server Configuration Manager from Start > All Programs > Microsoft SQL Server Version # > Configuration Tools. Click on your database under the Network Configuration tree item. You will see if TCP/IP is enabled on the right. If not enabled it and restart Microsoft SQL Server.
Make sure the SQL Server Browser is running. Again in the SQL Server Configuration Manager select Services on the left. Make sure it is running. If not start it up.
Set the Ignition Windows service to logon using the same account you want to connect to in SQL Server. Open the Services Control Panel from Start > Control Panel > Administrative Tools. Right click on the Ignition service and choose Properties. Select the Log On tab and choose This Account. Type in the svc_ignition user and press OK. You need to restart the Ignition service for the setting to take effect.
Lastly, make sure Integrated Security=true is in the extra connection parameters of the connection.
If you follow those steps it should work. I have also left you a message on your voicemail. Feel free to call me if you have further questions.
In the future if you are having difficulties please give us a call. We want you to be successful with the software.
@Travis - thanks for the help. The OP states that I have already tried all those steps. I even took it steps further buy convincing our DB team to allow SQL authentication temporarily. I was able to connect using SQL authentication. The SQL server is set up just fine. To test the account I was able to log into a windows session and connect to the remote database with SQL studio using windows authentication with this account on the server running ignition. Can we try something new? Are there other services ignition depends on?
Do I need to define an environment variable:
CLASSPATH =.;C:\Program Files\Microsoft SQL Server JDBC Driver\sqljdbc_3.0\enu\sqljdbc.jar
or
CLASSPATH =.;C:\Program Files\Microsoft SQL Server JDBC Driver\sqljdbc_3.0\enu\sqljdbc4.jar
I don’t know why I would need this. The JDBC driver works well for SQL server using SQL authentication. It is the integrated security that is causing the problem.
com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Gateway Error 600: Read timed out
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:280)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:398)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:219)
at com.inductiveautomation.ignition.designer.gateway.PriviledgedDesignerTask$GatewayMessageTask.execute(PriviledgedDesignerTask.java:424)
at com.inductiveautomation.ignition.designer.gateway.PriviledgedDesignerTask$TaskThread.run(PriviledgedDesignerTask.java:327)
at java.lang.Thread.run(Unknown Source)
Caused by: java.net.SocketTimeoutException: Read timed out
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(Unknown Source)
at java.io.BufferedInputStream.fill(Unknown Source)
at java.io.BufferedInputStream.read1(Unknown Source)
at java.io.BufferedInputStream.read(Unknown Source)
at sun.net.www.http.HttpClient.parseHTTPHeader(Unknown Source)
at sun.net.www.http.HttpClient.parseHTTP(Unknown Source)
at sun.net.www.protocol.http.HttpURLConnection.getInputStream(Unknown Source)
at java.net.HttpURLConnection.getResponseCode(Unknown Source)
at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.getResponse(GatewayInterface.java:330)
… 4 more
Ignition v7.2.8 (b178)
Java: Sun Microsystems Inc. 1.6.0_29
I think one big problem lies in an issue with update 29 and the Microsoft JDBC driver. We should probably have a bigger warning up, but many people (all over the internet, not just us. For example, see here) have run into problems using SQL Server and Java update 29. This thread just mentions "problems", but there's a link to download the update 27 installer. You'll need to uninstall 29 and install 27.
Hard to say if that's the problem you're running into, but it's definitely a place to start. We should have this publicized a bit more visibly here on the forum somewhere.
As for everything else, you really probably should try to call Vanessa and talk about it. This last release involved a bit of confusion, but overall it's gone over very well once people understand everything.
Oh, it’s no problem. Of course, I suppose we should first confirm that downgrading works!
We’ve never seen a minor java update cause a problem like this, and I think it was just about the day that I said something like that to one of the training classes here that this was discovered! At least there are many many people who use SQL Server, so this came out pretty quick… hopefully they’ll fix it soon. Java 7 apparently doesn’t have this problem, but we’re not quite able to declare full compatibility yet, so we haven’t recommended upgrading yet.
