UCCX SQL access for third party application

[Shaijala]

We have a requirement from customer to allow read access to UCCX database. This account would be used to query the UCCX database and it will use as a service account for another system.

Please let me know how create DB account on UCCX.

UCCX version:7.1

UCCX database: MSDE

[Gergely Szabo]

Hi,

take a look at the Cisco Unified CCX Administration Guide, Release 7.0(1), chapter Real-Time Snapshot Config Menu Option, there's a complete step-by-step guide. Is that what you need?

G.

[Shaijala]

This is not for Wallboard integration. We have a third party application which need to access UCCX database.

Is there any way to create  DB account on UCCX to query SQL from another application

[Aaron Harrison · Accepted solution]

Hi

All local administrator on the UCCX box have DB rights. So a simple way to do this is to :

1) Create a new user on the UCCX server, and add to the administrators group

2) Create a new user on the server that needs to access the DB, and make sure it has the same exact username and password

3) Ensure that the service/program running on the third party server runs under the new account.

When you set up your DSN, it will be servername\CRSSQL to access the instance rather than just servername.

If you need to authenticate using SQL Auth rather than Windows authentication then you must be on SR5 (I think) and there are steps in the UCCX release notes to switch to SQL Auth mode.

If you want to use SQL Auth or not give full admin rights then you need to use OSQL (a command line SQL admin tool, search microsoft's website for info) or get access to the server using a copy of SQL Ent Manager (much easier).

Aaron

Aaron

[Shaijala]

Hi Aaron,

  Thanks for the info...

We are running UCCX in Mixed mode hence we believe it will support SQL Authentication..

What we need is from a third party application server we need to get access to the SQL DB on UCCX using SQL authentication. Windows authentication is not supported since third party server and UCCX are not in the same domain.

How do I create a SQL authentication account in the UCCX Server ..?

Thanks,

Shaijal

[Anthony Holloway · Accepted solution]

Here you go:

Open the command line on your UCCX server and do the following:

List the available sql instances you can connect to (and their syntax)

osql -L

From that list you should see one that looks like:

SERVERNAME\CRSSQL

Connect to MSDE using your Windows local admin account (has admin rights in SQL)

osql -E -S SERVERNAME\CRSSQL

List the current logins in SQL

exec sp_helplogins
go

Create a user account and grant access to db_cra database:

use db_cra
go
exec sp_addlogin 'username','password'
go
exec sp_grantdbaccess 'username'
go
exec sp_addrolemember 'db_wallboard_read','username'
go

* Sorry, I don't have a 7x system anymore and cannot tell you what the role name is for admin rights.  You could issue the following to get a list of other roles:

exec sp_helprole
go

Enabling Mixed Mode

Stop the Node Manager

Open Regedit and navigate to:

HKLM\Software\Microsoft\Microsoft SQL Server\CRSSQL\MSSQLServer\

Change LoginMode from 1 to 2

Start the Node Manager

*Remember to fix all HRC clients to support mixed mode (this is a manual task).  See here for help on that: https://supportforums.cisco.com/message/3116762

[Aaron Harrison]

Well.. Anthony's response is typically comprehensive so I'll not go on too long...

But Windows authentication is perfectly possible outside of a domain - as long as the Windows account on the UCCX server you create matches the account the third party server runs as (same username/password) it will work whether it's a domain account or another local account on that server.

Good luck

Aaron

[Shaijala]

Thank you so much. Its working now

[Farhan Khan]

Hello All,

Excellent post and lots of useful information.

Can someone please provide the port number to connect to SQL Server 2000 via the thrid party application. Is it port 1433 or some other port?

We are using IPCC 5.0

TIA,

Farhan

[Anthony Holloway]

1433 is the correct port for IPCC 5.0.

Anthony Holloway

Please use the star ratings to help drive great content to the top of searches.

[Farhan Khan]

Thanks Anthony,

Although 1433 is the default port for SQL Server, it turned out out installation was using port 4433.

Had to go to SQL Server Client Network Utility and check TCP\IP properties to verify the correct port.

Thanks

[Anthony Holloway]

Good to know.  Thank you for posting that for us.

Anthony Holloway

Please use the star ratings to help drive great content to the top of searches.