Is Windows Authentication / Integrated Authentication connection supported to connect to MS SQL server 2005 on DD? We are trying to connect to a MS SQL server using the SQLJDBC.jar provided by Microsoft. The SQLJDBC.jar version is 1.2.

While adding a new DataSource in DD, we specify the following options:

In the Project Properties->Dialog Designer->Data Sources, we added a new JDBC Datasource as WinA. The Driver class name is com.microsoft.sqlserver.jdbc.SQLServerDriver and the connection URL that is specified is

jdbc:sqlserver://172.22.1.144:1433;databaseName=GroupDB;integratedSecurity=true;


In the Windows->Preferences->Dialog Designer->Database we added the driver corresponding to the SQL server (sqljdbc.jar). The sqljdbc.jar is copied in the TOMCAT_HOME/common/lib/ folder.

When we try to create the DBOP for the first time, we are able to see the Data Source name listed properly. Then the DD toolkit connects to the remote server and we are able to see the DB Schema/tables of the remote server. I am able to browse through and select the table to form my select query. In the DBOP window, i try to goto the SQL Query tab and "Execute" the query that is given. The following error occurs:

"The driver is not configured for integrated authentication." immediately after this, we get another exception saying "Error executing the SQL statement".

After this we are not able to browse through the DB Schema. Even while creating a new DBOP, we are facing the same issue. However, if the restart DD, the very first time, it is able to connect.

On checking the internet, there was a suggestion to copy the sqljdbc_auth.dll available inside the JDBC setup to the C:\Windows\system32\ folder. even this fails.

Request your assistance.

Thanks
B.Sriram

We haven't tried this here. However our solution is all depending on the jdbc driver. Microsoft didn't use to support this Wndows Authentication before. Now they seem to do so with the dll. It could be tricky to configure for a pure Java enviroment like Eclipse and Tomcat. I looked through their documentation. They claim at putting the dll in the folder should work. If not, try using -Djava.library.path=, which seems more reliable to me. You need to configure this for both Eclipse and Tomcat though.

Wilson

We tried to copy the dll in to the system32 folder after googling. even then it didnt work. We tried to configure the -Djava.library.path. Even then the problem was there. Since its during the development stage, i am not sure whether Tomcat setting will help. Anyway, i will try that out and update.

Thanks
Sriram

Did you configured the eclipse.ini by adding -Djava.library.path after -vmargs?

Wilson

Both the options failed. Is there any other way to achieve this?

Thanks
B.Sriram

I just gave this a try myself today. Here is what I found:

1. Indeed it works only the first time like what you've observed. This has something to do with how we re-initialize the driver every time when you open or re-open the dbop editor or run the dbop new-wizard. Both cases are trying to reload the driver, which in turn loads the dll for Windows Authentication. For some reason, Eclipse just can't handle doing just that the 2nd time. I simulated the same steps in a simple Java program without any problem.

2. The good news is I don't seem to have any problem running the dbops on Tomcat once I had the dbop created and added to the call flow. I tried both the -Djava.library.path jvm option and having the dll in the System32 directory.

What this all means is that if you have to create quite a few dbops, you would need to open and close Eclipse the same number of times. If you need to run the dbop in the SQL Query tab to test it or modify the query in the editor, the editor needs to remain open. When you try to open an other dbop, the driver will be in the funky state.

If you have a compliex applicate involving a lot of db operations, my suggestion is that you develop the application using SQL Server Authication. If the remote SQL Server doesn't allow you to do so, maybe you have your local SQL Server setup for development purpose. Once the app is done, you can modify the datasource connection URL back to use Windows Authentication for deployment.