OpenHistorian - Remote SQL Server

#1

Hello everyone,
i have installed the openHistorian outside of my company’s AD on a windows vm.
I have also been given a username and a password to connect to a SQL Server that my company has as testing. Please note that due the vm being outside and isolated i can’t use a dns resolution so i am using the SQL Server’s IP Address to successfully test the communication via ping.

But when i try to set up and test the SQL Server database in the Configuration Setup Utility i keep getting an error message displayed image

Basicly the information that i have been given by the SQL Admin is as follows:
connection string: string-example-test\TestingLTQ,12345
username
Password

There is a message when selecting which type of database you would like to have set up saying that i need to execute the sql scripts located in the “Database scripts” folder on the SQL Server.

Would that solve the issue or am i missing something else? ( besides ofcourse the DNS not translating the ip address)

#2

Hello Renos,

Assuming that string-example-test is the domain name of your database server with IP address 10.10.10.10, your connection string should look like the following:

Data Source=10.10.10.10\TestingLTQ,12345; Initial Catalog=openHistorian; User Id=username; Password=password

If you fill out the form in the Configuration Setup Utility, the Advanced dialog will show you everything besides the password. Also note that the Configuration Setup Utility is hiding the real error message to display a more “user friendly” message, but it may be masking the underlying issue in this case. If all else fails, I recommend removing Initial Catalog from the connection string and using the following PowerShell script to test your connection.

$connection = New-Object "System.Data.SqlClient.SqlConnection" "Data Source=10.10.10.10\TestingLTQ,12345; User Id=username; Password=password"
$connection.Open()

Thanks,
Stephen

#4

Since yesterday, after browsing through various posts it seems i hadn’t configured the database source on Grafana. I have the following questions i need some help with:

  1. From one of the posts i’ve read it seems for me to read data from the sql server i will have to create a new table in the openHistorian database ( as seen in an example when another user was configuring openPDC (Configuration to get data from database ). Should i do the same to be able to retrieve data from the database or any other query? If so, is there a specific default table where the measurement data is kept from which i should conduct my queries?

  2. The connectivity between the Remote SQL Database and my VM seems to be working but when i try to retrieve data from the openHistorian Web Client (via Trend/Export Data) the following error message is displayed.

    image
    image972×451 13.4 KB

Does that mean that even though i can see that the tables and the database has been created that they are not populated aka there’s nothing written in those tables?

#5

I think it means that ExportDataHandler.ashx does not exist in the openHistorian installation folder under the wwwroot subfolder. Can you check?

#6

Hi,
i just checked and the file does exist in the wwwroot subfolder.

#7

I don’t know what else would cause a 404, after a quick review of the code. Does it do the same thing in other web browsers?

#8

I just installed Chrome v80 and tried to export data from there.
The error appears as a failed download from ExportDataHandler.ashx as displayed here.

image
image702×176 8.67 KB

In which case could this be a bad installation of openHistorian?

#9

Looks like there may be an error trying to download, can you try installing the nightly build?

http://www.gridprotectionalliance.org/nightlybuilds/openHistorian/Beta/openHistorian.Installs.zip

#10

Hi,
i have deleted the created database from the SQL Server, uninstalled the previous version of the OpenHistorian and installed the suggested build. The Real Time data on the web application is working perfectly but it seems to display the same error message on exporting data.
I don’t know if it helps but to lessen the volume of data stored i had a unselected a lot of signals from the openHistorian and kept 6 in total. ( 4 Voltage Magnitudes, 1 Frequency measument and 1 Df/Dt measurement); this is the same configuration i did on a different vm when the openHistorian is storing data locally.

#11

I went ahead and installed the latest nightly build and the sample data set on my dev system, and I was not able to reproduce this issue. My best suggestion at this point would be for you to use Developer Tools in a web browser on the local system to try to collect more information about the error.

  1. Log on to the openHistorian system.
  2. Open a web browser and navigate to http://localhost:8180.
  3. Attempt to export data from the historian.
  4. When it fails, capture the URL of the failed request.
  5. Open a new tab and open up Developer Tools.
  6. In Developer Tools, switch to the Network tab and make sure it’s capturing.
  7. Enter the URL of the failed request into the address bar of the browser tab and then attempt to navigate to that URL.
  8. When the request fails again, inspect the response data in Developer Tools to see if you can find a status code and/or error message.

Thanks,
Stephen