Cannot access Database from Datasource

Simon_Zeinhofer

Hello guys,

we added a custom datasource to the portal, which connects to our MEMCM Database server, so our MEMCM guys can create reports.

Yesterday we tried the connection string with the Apppool and Cachebuilder account and we saw that both had no access to it. We assigned them read rights to the full memcm db and via powershell I could successfully test the connection.

But still when I create a dashboard which uses the MEMCM datasource, no data is shown/loaded. I restarted the cireson server, made a gpupdate recycled the appool, resterted the website - but nothing helped. Other datasources work fine.

Any ideas how to solve that?

Simon_Zeinhofer

@Brett_Moffett we found the issue.

It was a problem with the count widget. Seems like in the current portal version, it is necessary to write the count like "Select count(Displayname) as Display". The count alone does not return anything in the portal.

So the issue is resolved for us :)

Brett_Moffett

Hi @Simon_Zeinhofer

It certainly does sound like the account that is attempting to connect to the data source does not have the correct rights to do so.

I believe it is the AppPool account that makes the query but I may be wrong on that and it be the Platform account.

The easiest way to test if those accounts can access the data you are after is to open SQL Management tools as the service account and try to run a query. Confirm this and if still not working, report back and we can dig a little deeper.

Hope this helps.

Brett

Simon_Zeinhofer

@Brett_Moffett , thanks for your answer.

We already tried that and we can reach the database via the Management Studio.

We have a call with our Infrastructure Team next week, as we think it might be an issue with the Portal server not getting access to the MEMCM Database server.

Brett_Moffett

If the accounts can get to the data via the Management Studio then I would certainly suggest there is something else preventing this at the network management level.

Firewall rules would stop that port from being opened for sure.

Called the SQL connection from PowerShell from the SCSM Management server would also confirm if it is an account or machine issue as you could run that same PS on the DB and the Management Server and see if you get different results.

Good luck

Simon_Zeinhofer

@Brett_Moffett

We also tried connecting via powershell, and that worked as well.

We think it might be an SPN or firewall issue. I can give you feedback as soon as we found the issue ;)

Simon_Zeinhofer

@Brett_Moffett we found the issue.

It was a problem with the count widget. Seems like in the current portal version, it is necessary to write the count like "Select count(Displayname) as Display". The count alone does not return anything in the portal.

So the issue is resolved for us :)