Connect to CCM database as read/write user

[stephan.steiner]

I'm trying to programmatically connect to the CCM database and perform some maintenance operations (currently I'm looking at deleting a record).

So far I've been using the same means of connection as used on the ccm admin pages:

var oConn = new ActiveXObject("ADODB.Connection");

var systemX = new ActiveXObject("DBLX.SystemX");

var connectionString = systemX.GlassHouseDSN;

oConn.Open(connectionString, dbLogin, dbPassword);

rs = new ActiveXObject("ADODB.Recordset"); // for results recordset

rs = oConn.Execute(sql); // runs the query read-only, forward-only cursor

That works just fine for reading, but when I try deleting something, I get a permission error:

[Microsoft][ODBC SQL Server Driver][SQL Server]DELETE permission denied on object 'LineGroupNumPlanMap', database 'CCM0301', owner 'dbo'.

Is there another way to connect so that the deletion operation will succeed? It works just fine when I execute the same query via SQL Query Analyzer while logged in using the same account as I'm using in my code (the NT admin account) (and the operation does not have any adverse affect on the call manager (I know you're not supposed to mess around the database but I have no choice in the matter.. and after all it's my head if I screw up)) so there must be a way but I've been unable to figure it out.

I've managed to do it if I use SQL Server Authentication but that requires that a parameter on the SQL Server be changed and I feel a bit uneasy about that (though, the CDR documentation mentions SQL Authentication as a means to get to CDR records so I suppose activating SQL Authentiation does not do any harm).

[aaronw.ca]

"[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection."

As you noted, that error indicates that the database is configured for Windows Authentication only and not mixed mode (sql + windows authentication).

CallManager versions prior to 4.0 were configured with mixed-mode authentication on the database, but as of 4.0 and newer the databases are set to windows authentication only. Windows authentication is more secure than sql authentication and is recommended by Microsoft, and now Cisco.

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a00801f9f33.shtml

Just to check, are you going against the most current CCM03xx database? You're probably aware that it increments with each CM upgrade (CCM0301, CCM0302, etc) and saves the previous database for restore purposes, but just checking! (which would be why the query might seem to work but no effect on CM).

In your notes above the error indicates database CCM0301 while your connection string goes after CCM0300.

[stephan.steiner]

aaron: I guess you read my post before my final edit.. I had already figured out where I went wrong with SQL authentication. Interestingly though, even the CDR Documentation for CCM 4.12 still mentions SQL Authentication as one of the two ways to get access to the CDRs.

BTW, when exactly are those upgrades made? That particular CCM was installed as 4.0.1, underwent several upgrades with service releases, and I finally install the 4.0.2 maintenance upgrade. I presume that's the point where the 2nd database was created.

And if you have any idea on the permission error I get using windows authentication please let me know. If at all possible I'd like to use the more secure way.