4 Replies Latest reply on Aug 20, 2018 8:36 PM by vkaufmann

    Having issues testing GetSnap/ accessing PI SDK using Java

    hiella

      Hi,

       

      I've been trying to access PI OLEDB using Java. I have the latest PI JDBC and PI DAS installed on local computer (A), and the target DB is in another computer (B) but same network. I basically copied the code from examples when installing the JDBC.

       

      And I get the following error message all the time: "Connection failed. Connection reset."

       

      I am not showing all the code here because I get the same error message when I test using GetSnap.

       

      Do anyone have any thought on this? I have a few assumptions after I did some research:

      1) The PI DAS should be installed in the computer B where the DB locates;

       

      2) According to the PI JDBC Driver 2018 Administrator Guide, this is a "Missing or not suitable SSL certificate" issue (In local computer A, I tried to follow the steps on Using the Keytool to Import a Certificate in the Developing with PI Web API, but I cannot export the certificate in .cer format.) Both computer A and B do not have any internet connection--not sure if this is related.

       

      3) Something wrong with my connection string. Here's my url: "jdbc:pioledb://computerB.companyname.com/Data Source=computerB.companyname.com; Integrated Security=SSPI;"

       

      I am using port no. 5450 since that's the port no. used my PI SDK Utility, and the computerB.companyname.com is from there too. Here's the rest of the properties:

      String enableCertificateValidation = "No";

      String isTrustedConnection = "Yes";

      String useDCA = "No";

      String userName = "";

      String password = "";

      String portNumber = "5450";

      String logLevel = "0";

       

        • Re: Having issues testing GetSnap/ accessing PI SDK using Java
          rschmitz

          Hi Hiella,

           

          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).

           

           

          Cheers,

          Rob

          1 of 1 people found this helpful
            • Re: Having issues testing GetSnap/ accessing PI SDK using Java
              hiella

              Hi Rob,

               

              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.)

               

              And,

              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.

               

              Thank you,

                • Re: Having issues testing GetSnap/ accessing PI SDK using Java
                  rschmitz

                  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

                   

                  --Rob

                    • Re: Having issues testing GetSnap/ accessing PI SDK using Java
                      vkaufmann

                      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).

                       

                      https://livelibrary.osisoft.com/LiveLibrary/content/en/jdbc-v5/GUID-18301C95-8D15-4D23-B283-B2B4E9BDDD5E

                       

                      --Vince

                      3 of 3 people found this helpful