02-26-2015 08:07 AM
Having managed to get clearpass filters working talking to a MySQL database, I now need to do the same things for an MSSQL database.
First issue was not being able to specify an MSSQL instance. I was just about to raise an enhancement request when I discovered that someone had beaten me to it .... round about last July :-(
Anyway, our systems team set up an alias pointing to the db instance I needed to access. Next problem was testing an sql SELECT statement. Thought we'd keep it simple and just return a count(*) integer.
From an MSSQL dev application the following statement returned an integer value.
select count(*) from mmsuite.mmcentral.mm_devices
so I thought this would be a good place to start. From clearpass, I get
Session failed for Host=<fqdn name of server>, Reason=[Error executing "select count(*) AS deviceCount from mmsuite.mmcentral.mm_devices": [FreeTDS][SQL Server]Invalid object name 'mmsuite.mmcentral.mm_devices'. SQLState=42S02 ErrorCode=208]
I'm accessing the db from a read only user just to make sure.
I do have to say that while other parts of clearpass are simple to use, creating filters are a major pain. The only examples are those for the internal postgresql databases. It took a support call to find out that CPPM doesn't support MySQL stored procedures. Would be good if some of the doc'n actually told you what you can and can't do SQL wise!
Anyone out there using CPPM to access an MSSQL db? Got any examples?
Solved! Go to Solution.
02-26-2015 08:32 AM
.... and sorted!. Unlike all our other databases apparently this one is configured to be case sensitive, then again this one is not an internal one but one provided by a 3rd party. Got to say thanks to the 2 people in our systems team who sorted this out.
'course now that the connectivity is working, I'm going to pester them about how to actually get real data out of the db :-))