Hi team,

Need assistance on the following issue:

Issue:
Customer needs to connect to a MS SQL Server 2012 server from an application designed in AAOD.

In the orchestration designer, this has always worked with older versions of MS SQL Server i.e 2000.

Now they get the error:
“The server version is not supported. The target server must be SQL Server 2000 or later” when they try to connect.

They have added 3 new sql jar files from Microsoft website.
sqljdbc.jar
sqljdbc4.jar
sqljdbc41.jar

AAOD version 6.0.0.1103

I have attached a doc ument which contains screen shots from AAOD and error message.

Thanks,
sandeep.

There are couple things I notice here that needs to be fixed:

1. There is no need to have the jdbc jar file added to the class path. Get rid of them.
2. I don't see how or if the the jdbc jar is added in the preferences->Avaya Aura->Orchestration Designer->Database. Take a look at the OD DevGuide doc. Make sure you use only one jar (not 3). sqljdbc4.jar will do.
3. The Connection URL should have the format as the following:

jdbc:sqlserver://<ip>:1433;databasename=<dbname>

Hi Team,

Customer has performed the changes and now they see a different exception messages.
Looks like the user/pass credentials used are not able to connect to the DB. But customer is using the same to credentials to login to
SQL Server Management studio .

I have attached the email message which contains the screen shots of the exceptions.
Could you please advise.

Thanks,
Sandeep.

By the look of the username (windows username) he uses, he is using trusted connection to login. Have him try adding "integratedSecurity=true" to the connection URL. For example:

jdbc:sqlserver://IP;databaseName=AdventureWorks;integratedSecurity=true

Also, the following Microsoft link would give more information about their JDBC, can be used as reference.

https://msdn.microsoft.com/en-us/library/ms378428%28v=sql.110%29.aspx

Hi Wilson,

Now they get the error that the driver is not configured for integrated security… after changing as per you suggestion.
Attached the error screen shot. Appreciate your advice Thanks.

This error comes from the microsoft jdbc. Have the customer take look at the documentation following the link I provided above. Below is the section from the documentation about using integrated security:

Connecting with Integrated Authentication On Windows
The JDBC driver supports the use of Type 2 integrated authentication on Windows operating systems through the integratedSecurity connection string property. To use integrated authentication, copy the sqljdbc_auth.dll file to a directory on the Windows system path on the computer where the JDBC driver is installed.
The sqljdbc_auth.dll files are installed in the following location:
<installation directory>\sqljdbc_<version>\<language>\auth\
For any operating system supported by the Microsoft JDBC Driver for SQL Server, see Using Kerberos Integrated Authentication to Connect to SQL Server for a description of a feature added in Microsoft JDBC Driver 4.0 for SQL Server that allows an application to connect to a database using integrated authentication with Type 4 Kerberos.

Thanks Wilson for your valuable inputs. The issue has been resolved!

However customer says that it throws an exception "The driver is not configured for integrated security” sometimes.

Below is the update from the customer:
The only issue now is that once I have made a successful connection, if I then try to connect again I get the error “The driver is not configured for integrated security” which is strange as it worked just 20 seconds ago. If I close eclipse and start it up again it works. Is there some kind of timeout error, or anything else that would limit the connection?

Could you please advice on this.


Steps followed by Customer to resolve the issue
Downloaded the sqljdbc_4.1.5605.100_enu.exe from http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=11774 and ran the executable.
Extracted the file to the root of C:
That gives the following path: C:\sqljdbc_4.1\enu
Added sqljdbc4.jar from that directory into Eclipse (Windows – Preferences – Avaya Aura – Orchestration Designer – Database
Checked my Java version. Mine was installed in C:\Program Files (x86) so I know it’s a 32bit version
Added the path C:\sqljdbc_4.1\enu\auth\x86 to the Path variable (My Computer – Properties – Advanced – Environment variables)

Reboot! (this is important as the Path change won’t take effect until after a reboot)

On the SQL Server, check the port it is listening on. Open SQL Server Configuration Manager, expand the databases, find the one you want to connect to, double click the TCP/IP string, IP Addresses, TCP Port. Mine was set to 1416.

In My application, created the Database connection string (Project – Properties, Orchestration Designer – Pluggable Connectors – Database)

Driver Class Name: com.microsoft.sqlserver.jdbc.SQLServerDriver
Connection URL: jdbc:sqlserver://”servername”:1416;integratedSecurity=true;
Username: DOMAIN\user_name
Password: Password


Thanks,
Sandeep.

OD does not impose any timeout on jdbc connection. Seems like in finding and loading of the auth dll native library is not consistent. Maybe try some suggestions from a post I found online:

http://stackoverflow.com/questions/14471150/loading-dll-file-inside-eclipse-java-project

Hi Wilson,


Customer tried the suggestions, but unfortunately they still have the same issue.
If they create a new Database Operation file, it works perfectly the first time. Then if try to create a second one, they get the error.

The dll file is in path, and also added it to the Project: Attached the screen shot of the same.

Appreciate your advice further. Thanks.

Try adding the java library path in the <Eclipse Home Directory>/eclipse.ini

[eclipse.ini]

-startup
plugins/org.eclipse.equinox.launcher_1.3.0.v20130327-1440.jar
--launcher.library
plugins/org.eclipse.equinox.launcher.win32.win32.x86_1.1.200.v20130807-1835
-product
org.eclipse.epp.package.standard.product
--launcher.defaultAction
openFile
--launcher.XXMaxPermSize
256M
-showsplash
org.eclipse.platform
--launcher.XXMaxPermSize
256m
--launcher.defaultAction
openFile
--launcher.appendVmargs
-vmargs
-Dosgi.requiredJavaVersion=1.6
-Xms40m
-Xmx512m
-Djava.library.path=C:\<path>\sqljdbc_<version>\enu\auth\x86

If this still does not work, it's beyond OD. There is not much we can do. The problem is between the JDBC driver and Java. I suggest customer use sqlserver authentication instead since it uses the pure Java jdbc driver instead of having to load the dll.