I have built a UCCX 11.5 test system which integrates with MS SQL Server. I have written a script which works fine with my test SQL environment which uses SQL Server based authentication.
I have moved the script to a customers production UCCX system and the script is failing.
The customer has stated that we need to use Windows based authentication for SQL Server rather than SQK Server based authentication.
I have successfully configured a Database Datasource to connect to the customers SQL database using Windows based authentication - the Test Connection button reports success.
Looking at the DB Read step in the script I refresh the schema under the General tab which seems to complete ok. Under the Field Selection tab I do not see the tables I created in the SQL database to which I connect when I click the Show all fields buttons - there are lots of other things listed which seem to be system related.
These tables are visible when I do the same thing on my test system. The picture below is from my test system and shows the Services table listed from the SQL Server - this is what is missing from the output when running on the customer's system.
Can anyone suggest why the tables are missing from the customer system? - I can see them fine using if I connect using SQL Server Management Studio with the same credentials.
Not sure if this applies to your scenario, but I had a situation where I had the script go pull information from a DB and the tables would not appear and the issue was not on the UCCX side ,but on the database side. I had to get our SQL DB administrator to give permission to the tables for my user UCCX user ID that connects to the Database Datasource.
The Test Connection button only says the ID can connect to the SQL Server, it does not indicate it has permission to a table.
Not sure if that helps, but hope so.
Thanks - it is really useful knowing how you solved a similar problem.
The SQL DBA has said that the user account I am using has full permission to the database and I seem to be able to view it fine using those credentials in SQL Server Management Server.
Can you remember which type of authentication you used? Was it SQL or Windows based?
Think it is SQL, I don't think we do any Windows based auth.
Just wondering, if your user ID has 'full permissions' , I would be asking the DBA --> then why is it only showing certain tables that your userID can access. Which is why I brought up the specific table permissions.
btw - the DB side of things is not my area of knowledge. When I had my issue, I went to the DBA admin about the table permissions angle as it seemed to me to be on the datasource side since I was not having an issue connecting to the DB and seeing other tables. I could do the DB Read and DB Get from other tables, but could not see the table for another particular DB Read and DB Get step
That conclusion is as far as my SQL DB knowledge goes :-)
Turns out that this was an issue with the permissions the DBA had assigned to the Windows user we used for the SQL Server connection.
All working ok now and failover works perfectly with SQL Server Availability Groups (sort of HSRP for SQL Server).