UCCX 8.x JDBC URL

[lharris310]

Has anyone been able to successfully configure a Datasource with a SQL 2005 server. My 'Test Connection' fails everytime and I can't figure out why. I opened a Cisco TAC case and the TAC engineer told me that the UCCX server needs to have a domain configured as well as DNS. Is this correct? There is very little documentation detailing the steps required to configure the JDBC URL and I am having a very hard time. Here's the current URL  I have configured (screenshot attached also):

jdbc:jtds:sqlserver://172.16.200.88:1443/UCCX_IVR;domain=mydomain.org

Any help on this matter will be greatly appreciated.

[shalushar]

Hi ,

I hope you have followed the steps mentioned below to add a new jdbc driver:

Procedure

Step 1  From the Unified CCX Administration menu bar, choose Subsystems > Database > Drivers.

The Driver List web page opens up displaying a list of uploaded driver class file names along with a Delete icon.

Step 2  Click Add New icon that displays in the tool bar in the upper,left corner of the window or the Add New button that displays at the bottom of the window to add a new driver class name. The Driver Management web page opens.

Step 3  Specify a valid JDBC driver jar file in the Driver File field or click Browse to locate the driver file. The driver file is validated before uploading.

Step 4  Choose the supported class name for the new driver from the Driver Class Name drop-down list box.

Step 5 Click Upload to save the new driver to the database.

The URL format for jTDS is:

If the server is not in the domain:

jdbc:jtds:sqlserver://10.1.0.15:1433;DatabaseName=

If the server is in the domain:

jdbc:jtds:sqlserver://10.1.0.15:1433;DatabaseName=;domain=

You can also use DBVisualizer (http://www.dbvis.com) to build and test the database URL.

Note:- Make sure that UCCX user has the rights for connection and SELECT rights on the TABLES also.

Hope this helps.

Tx,

Shalu

[lharris310]

Hello Shalu,

I installed the Driver as the Admin Guide described. I tried configuring the JDBC URL as you recommended;however, the connection still failed.

[shalushar]

Hi,

Hope you have the latest driver and check  http://tools.cisco.com/Support/BugToolKit/search/getBugDetails.do?method=fetchBugDetails&bugId=CSCto12196 as it mentions that Current admin guide documentation states the MIcrosoft SQL Server Native Client 10.0 Driver is the correct supported driver . The correct driver should be the JDTS 1.2.5 driver.Could you please tell me what comes when you run dbvisualiser  and are you able to telnet port 1433?Please check with SQL server admin about which TCP/IP port is open for DB connections.Could you find out if the server which SQL 2005 is installed is running Windows Authentication or mixed mode authentication?

Go through this link also for common mistakes

http://jtds.sourceforge.net/faq.html#urlFormat

Hope this helps.

Tx,

Shalu

[Clifford McGlamry]

Based on your original string, you're attempting to connect to SQL server on port 1443.  The correct port is 1433.  It may be a typo, but I think you may have the string wrong in your config.

You shouldn't need the domain setting.  I've done a bunch of these, and never needed that. 

On the SQL server, check the security setup and make sure that mixed mode is enabled.  By default on the newer versions, it wants to used trusted connections (i.e. named pipes).  You can't use that.  They have to have TCP/IP and mixed mode enabled. 

HTH

Cliff

[lharris310]

Hi Cliff,

Thanks for the reply. The 1443 was a typo, the port is actually 1433. I recently setup the SQL server to use mixed mode authentication; however, I selected TCP/IP and names not TCP/IP only. Is this okay?

[Clifford McGlamry]

That should be okay.  I always put TCP/IP at the top of the binding stack for the SQL server, and find it works better that way. 

Did you install the default instance, or did you create a named instance in SQL server? 

Cliff

[lharris310]

Default Instance.

[Clifford McGlamry]

Have you confirmed that you can log in with the user name and password you're providing to the system to use in another tool?  Like maybe setting up and ODBC connection to SQL server on another box, and testing with the same credentials?

Cliff

[lharris310]

I ran DBVisualizer and it states that the login failed for the user id I provided. Am I missing something with my user configuration?

[Clifford McGlamry]

Ok.   So the issue is probably a permissions problem within SQL server.  Is the user a windows user, or a user created in SQL server?

[lharris310]

Both, the user is configured as a local user (local Admin group) as well as a SQL user.

[lharris310]

Ok I think I figured it out. My JDBC URL needed to reference the database the the System DSN is mapped to. For example, I changed my JDBC URL to the following and it successfully:

Original JDBC URL:

jdbc:jtds:sqlserver://172.16.200.88:1443/UCCX

New JDBC URL:

jdbc:jtds:sqlserver://172.16.200.88:1443/Master

[Clifford McGlamry]

Correct....the value following 1433:/ (not 1443!) is the name of the database you want as the default.  I would not use the master database, and would actually limit the SQL user for the UCCX system to only the specific database it needs. 

It's not a good idea to use an admin user for database access with a production system for security reasons.  And the user UCCX uses does not need windows rights, so you're better off just creating a SQL user and granting it rights on the database it's going to use.

Cliff

[lharris310]

Thanks for all your help Clifford. Your recommendations and questions helped me figure this thing out. The Master database was only used for testing in my lab environment and I will be sure to follow your recommendations when I roll out to production. Again thanks for all of your help!!!