Connecting to MS SQL Server [Windows Integrated Authentication] from Jdeveloper 12c

Recently I came across a request to create a service in Oracle SOA Suite to connect to a SQLServer data-source.

I had known earlier that Jdeveloper supports connections to SQLServer, but this is my first implementation of a non Oracle DB. The access to the SQLServer had been issued to my official account, which I use to login at work. [This is the area of interest behind this article]

So, starting to implement a service which queries a SQLServer got me into two issues:

• Creating a DB connection in Jdeveloper to the Windows Integrated Authentication enabled database
• Enabling the datasource communication from the weblogic server to test the service

Now, lets see the error and the solution to resolve them.

Resolving connection issues in Jdeveloper:

To enable communication with a SQLServer whose authentication mode is of Windows Integrated Authentication, few properties should be set in the JDBC URL

                  integratedSecurity=TRUE

                  username="<USERNAME>"

                  password="<PASSWORD>"

So, your JDBC URL should look like jdbc:sqlserver://machine-name:port;DatabaseName=database-name;username=USERNAME;password=PASSWORD;integratedSecurity=TRUE

When the integratedSecurity property is not set, Login failed for user <USER> error message will appear when the connection is tested.

This does not resolve the issue yet. This kind of authentication mode is not enabled by default. When the integratedSecurity property is used, you'll have to place a file "sqljdbc_auth.dll" to either the JVM runtime bin directory or set your PATH variable. As I was working on my desktop, i found it easier to place the library file in the jre\bin directory.

When the "sqljdbc_auth.dll" is not set, but the integratedSecurity is set to TRUE, Test failed: This driver is not configured for integrated authentication will appear when the connection is tested.

Note: sqljdbc_auth.dll has two variants. Choose based on your system architecture [32 or 64 bit] Place the file to the jre directory which is used as default for all your applications.

Official Oracle Documentation: Check 25.4.3.4 How to Create a Connection to Microsoft SQL Server in https://docs.oracle.com/middleware/12213/jdev/user-guide/connecting-and-working-databases2.htm#OJDUG2405

Restart your Jdeveloper IDE and your connection to the SQLServer should be fine.

• To enable the datasource communication, you'll have to set the property in the weblogic console too. You'll find the Properties text area in the connection pool of the data source. Add the property integratedSecurity=TRUE when you configure the datasource.

This will resolve the issues when your service uses the DBAdapter viz the DS Pool.

Hope you found this article helpful. Please post your suggestions/ opinions in the comments section.