1 of 1 people found this helpful
I did my best to hit all of the specific questions you had.
- The SQL DAS Service doesn't need to be installed on the same server as the Data Archive you're attempting to connect to, though it makes life a little easier in my opinion. A couple of example architectures, neither of which has the SQL DAS service on the Database machine:
- For connecting to a PI Server with PI OLEDB you need to use the form "jdbc:pioledb//<SQL DAS machine>/Data Source=<PI SERVER NAME>". From your description it looks like you use the same name for both the SQL DAS name and the Data Archive name, though they're on different machines. So change the "Data Source=computerB" to "Data Source=computerA"
- You're right that connections to the PI Data Archive are done over port 5450; however what you're specifying in the code is the port to connect the PI JDBC Driver to the SQL DAS service. This port should be either 5461 or 5462, in either case you shouldn't need to explicitly specify this in the code. I suspect you're getting the SSL error because you're attempting to connect to a port that has no SSL certificate bound to it (namely 5450).
Thank you for your detailed response! After I made 3 changes to the connection string I'm able to retrieve some data from the target DB:
1) I changed the port no. to 5461.
2) I changed the <SQL DAS machine> to "localhost".
3) I changed "isTrustedConnection " to "No" and added my credentials.
This is great! Thank you so much! But I do have a few more questions:
When I'm using PI SDK and PI DataLink on local computer, no credentials are ever needed. All I need was Data Source string and port no. (which is 5450). Now:
1) Why does accessing using JDBC needs credentials? (In this case if I keep "isTrustedConnection " to be Yes it does not allow me to access it.)
2) Why it needs a different port no.?
3) Why 5462 does not work? In other words, how do I know if it's https or net.tcp? According to your link, net.tcp is for 5462.
You either need to manually add the credentials or use the isTrustedConnection=true parameter. The isTrustedConnection parameters allows your Windows credentials to be sent along to the requesting service automatically for verification, without it you'll need to supply them. What error message are you getting if you try to use the trusted connection? Is is the same "Connection failed. Connection reset" message or something else?
This is more a general networking related topic, but in any case SQL DAS uses a different port number because only one service can listen on a particular port. In other words both the Data Archive and SQL DAS can't be listening on the same port at the same time. So in order to prevent issues if they were to be installed on the same machine, SQL DAS listens on a different port.
You'll notice in the article I sent you, it mentioned that explicit login (i.e. when you provide credentials and set isTrustedConnect=false) uses port 5461, whereas trusted connections use 5462. In essence your authentication method defines which protocols we're allowed to use and thus which port
3 of 3 people found this helpful
Just a quick note: trusted connections can be used using HTTP (5461) or Net.Tcp (5462). If you do set the trusted connections flag, then you have to be aware of the additional configurations for Kerberos authentication that exist in the PI JDBC driver 2018 release. Because this release of JDBC is a full Java implementation (no additional C++ dependency), the Windows functions that normally perform some of these operations under-the-hood are not accessible. I would suggest taking a look at the Kerberos Configuration section of the documentation if you wish to use GetSnap (or any other custom Java application and TrustedConnections).