9 Replies Latest reply on Apr 4, 2013 12:40 PM by Gregor

    pi sdk unable to open a session on the pi server

    anshuman

       

       
      using System;
      using System.Collections.Generic;
      using System.Linq;
      using System.Net;
      using System.Net.Http;
      using System.Web.Http;
      using System.Data.OleDb;
      
      namespace PIServicesWrapper.Controllers
      {
          using PIServicesWrapper.Models;
          using System;
          using System.Collections.Generic;
          using System.Linq;
          using System.Net;
          using System.Net.Http;
          using System.Web.Http;
          using System.Data.OleDb;
      
          public class TagDataController : ApiController
          {
      
              private string connString = "Provider=PIOLEDB; Data Source=simplyfeye; Integrated Security=SSPI; User ID=piadmin; Password=;Log File=C:\\Temp\\Log\\PIOLEDB.log;";
      
      
              private OleDbCommand cmd;
              private OleDbConnection cnn;
      
              public IEnumerable<PIUnitTagOutputData> GetAllTagData(UnitTagParamsInput unitTagParamsInput)
              {
                  if (unitTagParamsInput == null)
                  {
                      UnitTagParamsInput unitTagParamsInput0 = new UnitTagParamsInput { fromTime = "01-Mar-2003 17:12:00", toTime = "*", timeRange = "30", tagStr="('SINUSOID','BA:TEMP.1')" };
                      unitTagParamsInput = unitTagParamsInput0;
                  }
      
                  string mystarttime = unitTagParamsInput.fromTime;
                  string myendtime = unitTagParamsInput.toTime;
                  string tagStr = unitTagParamsInput.tagStr;
                  string timeRange = unitTagParamsInput.timeRange;
                  List<PIUnitTagOutputData> outputItems = new List<PIUnitTagOutputData>();
                  PIUnitTagOutputData tmpItem;
                  string lineSpeedSql = string.Format("SELECT tag, time, value, status FROM piarchive..piinterp2 u WHERE u.tag IN " + tagStr + " AND u.time BETWEEN '" + mystarttime + "' AND '" + myendtime + "' AND u.timestep = '" + timeRange + "' AND u.value !=''");
                  //string lineSpeedSql = string.Format("SELECT tag, time, value, status FROM piarchive..picomp2 u WHERE u.tag IN " + tagStr + " AND u.time BETWEEN '" + mystarttime + "' AND '" + myendtime + "' AND u.value !=''");
      
                  cnn = new OleDbConnection(connString);
                  cmd = new OleDbCommand();
                  cmd.CommandTimeout = 5000;
                  cmd.Connection = cnn;
      
                  try
                  {
                      cnn.Open();
                  }
                  catch (Exception ex)
                  {
                      //could not connect to the pi server
                      outputItems.Add(new PIUnitTagOutputData { tag = ex.Message, time = ex.Data.ToString(), value = ex.ToString(), status = "" });                // outputItems.Add(ex.StackTrace);
      
                  }
      
                  try
                  {
                      DateTime current = DateTime.Now;
                      //get the line status (running or not)
                      cmd.CommandText = lineSpeedSql;
      
                      //cmd.CommandText = lineStatusSql;
                      OleDbDataReader rdr = cmd.ExecuteReader();
                      try
                      {
                          while (rdr.Read())
                          {
                              //tmpItem = rdr.GetString(0).ToString() + "||||" + rdr.GetString(2).ToString() +"^^^^"+ rdr.GetString(3).ToString(); 
                              //Console.WriteLine(rdr.GetInt32(0) + ", " + rdr.GetString(1));
                              string tag = rdr.GetString(0);
                              string time = rdr.GetValue(1).ToString();
                              string value = rdr.GetValue(2).ToString();
                              string status = rdr.GetInt32(3).ToString();
      
                              tmpItem = new PIUnitTagOutputData { tag = tag, time = time, value = value, status = status };
      
                              outputItems.Add(tmpItem);
      
                          }
                      }
                      finally
                      {
                       
                          // always call Close when done reading.
                          rdr.Close();
                          // always call Close when done reading.
                          cnn.Close();
                      }
      
                  }
                  catch (Exception ex)
                  {
                     
                  }
                  return outputItems.ToArray();
              }
          }
      }
      

       The above code works perfectly fine when I start debugging the code on MS Visual studio 2012 for web by clicking on the "firefox" button. This same code fails to connect to the pi server when I release the code by creating the zip archive and in the IIS manager, deploy this application using import mechanism.

       

       

       

      I receive th following error:

       

       

       

      PI SDK] Unable to open a session on a server. The user name and password may be incorrect.

       

       

       

      The url : http://localhost:51820/api/tagData works fine in debug mode. but after depluoyment say on port 8091,

       

      the url http://localhost:8091/api/tagData does not work.

       

       

       

      I am also able to connect to the PI server througn PI OLEDB Snap-In utility with the same username:password pair [padmin:""]

        • Re: pi sdk unable to open a session on the pi server
          anshuman

          actually the code fails at line #53

           

          The stack trash is:

           

          System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)\r\n   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)\r\n   at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)\r\n   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)\r\n   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)\r\n   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)\r\n

          • Re: pi sdk unable to open a session on the pi server

            Hello Anshuman,

             

            You are facing a connection issue, I assume because authentication fails for the Active Directory account selected for the Web Service. You may want to open a ticket at OSIsoft TechSupport and look into the issue together with a Technical Support Engineer in a remote session.

             

            Anshuman Bansal

            "Provider=PIOLEDB; Data Source=simplyfeye; Integrated Security=SSPI; User ID=piadmin; Password=;Log File=C:\\Temp

             

            Looking at the connection string you are using, it isn't clear to me how the PI OLEDB connection is supposed to authenticate, because you are using "Integrated Security=SSPI" (PI trust or WIS) and "User ID=piadmin; Password=" (explicit user login).

             

            If your application is supposed to authenticate via a PI trust, please use "Integrated Security=SSPI" and be sure you have a valid PI trust i.e. limiting by application name and IP address of your Web Service host. For this case, please remove "User ID=piadmin; Password=" from your connection string.

             

            I am a little unsure if Windows Integrated Security (WIS) might work at all, because your Web Service is using PI OLEDB as "middleware" but in case the application pool identity is submitted (check with the PI Server's log), please be sure you have a valid mapping. Authentication through WIS does as well not require to specify username and password ("User ID=piadmin; Password=" should be removed from the connection string).

             

            If the PI OLEDB Connection is supposed using explicit user login, you need to provide a valid PI username and password within your connection string. OSIsoft doesn't recommend using explicit login at all. Please see the this Support Bulletin. Please never use piadmin user with a blank password.

              • Re: pi sdk unable to open a session on the pi server
                anshuman

                Hi Gregor,

                 

                Thank you very much for your reply. Actually I was trying to point out that the same code [with the same username and password combo] works fine in application mode but it fails in the service mode after deployment. I saw another post where someone is facing the same problem [in VB instead of c#]. The link is:-

                 

                www.rjksolutionsltd.co.uk/.../yaf_postst955_PIOLED-connection-fails-when-change-from-aplicationto-a-service.aspx

                 

                My question is : irrespective of how unsecured this method is, why is it working in the testing mode [in MS Visual Studio For Web 2012] but fails when it is deployed as a service in IIS.

                 

                --Anshuman.

                  • Re: pi sdk unable to open a session on the pi server

                    Hello Anshuman,

                     

                    What I was trying to say is that the connection string you are using isn't clear about what authentication method you would like to use. I understand now that you would like to go with explicit login. If so, please remove "Integrated Security=SSPI;" from your connection string. I haven't tested it but I expect with "Integrated Security=SSPI;" Windows Integrated Security / PI Trust will be persevered.

                     

                    When trying to establish a connection via PI SDK, what's happening if you use PI OLEDB (classic) Provider, information will be printed to the local PI Message Log and the PI Message Log on the PI Server (PI Server Log). When looking at messages from source PI Network Manager (pinetmgr) and PI Bases Subsystem (pibasess) in the PI Server Log one gets a pretty good picture of what's going on.

                     

                    Please allow me to repeat my recommendation of opening a ticket at OSIsoft Technical Support and to ask for a remote session. I expect the colleagues will be able to help you solving your connection issue within shortest time.

                      • Re: pi sdk unable to open a session on the pi server
                        anshuman

                        Hi Gregor,

                         

                        Thanks for the reply. Now i am able to connect to the PI server after changing the "Integrated Security" attribute from SSPI to false. Now when I try to connect to the PI server by using windows integrated security, these seems to be some issue. I have followed the instruction specified in  the document "PI Server 2012 Introduction to System Management (English)". It can not connect and gives the same error as before [PI SDK] Unable to open a session on a server. The user name and password may be incorrect.].

                         

                        I also tried to connect to the server using PI OLEDB MMC Snap In client  and was successfully able to connect t the server.

                         

                        It seems as if the application first tries to connect using windows integrated authentication method, then through trusts and finally through explicit login.

                         

                         

                         

                        Is there some incompatibility issue with OLEDB classi provider and windows integrated aauthentication?

                         

                         

                         

                        The pinetmgr log is:--

                         

                         

                         

                         

                         
                                                                                             
                                                                                             
                                                                                             
                                                                     
                        [Server] Localhost [ID] 7138 [Time] 4/3/2013 4:52:58 PM [Program] pinetmgr [Priority] 10 [ProcessOSUser] SYSTEM [ProcessID] 2732 [Severity] Information 
                        Unsuccessful login  ID: 29. Address: fe80::f059:17cd:e306:34d9%10. Name: w3wp.exe(20096):remote. Credentials used: NT AUTHORITY\ANONYMOUS LOGON. Method: Windows Login (SSPI,NTLM). Error: [-10433] No identity mapping for this request
                        
                        [Server] Localhost [ID] 7138 [Time] 4/3/2013 4:53:01 PM [Program] p
                        
                        
                        [Server] Localhost [ID] 7138 [Time] 4/3/2013 4:53:01 PM [Program] pinetmgr [Priority] 10 [ProcessOSUser] SYSTEM [ProcessID] 2732 [Severity] Information 
                        Unsuccessful login  ID: 29. Address: fe80::f059:17cd:e306:34d9%10. Name: w3wp.exe(20096):remote. Credentials used: IIS APPPOOL\ASP.NET v4.0. Method: Trust. Error: [-10413] No trust relation for this request
                        
                        
                        
                        
                        inetmgr [Priority] 10 [ProcessOSUser] SYSTEM [ProcessID] 2732 [Severity] Information 
                        Unsuccessful login  ID: 29. Address: fe80::f059:17cd:e306:34d9%10. Name: w3wp.exe(20096):remote. Credentials used: administrator. Method: Explicit Login. Error: [-12001] Name Not Found in PInt
                        
                        
                        
                        
                        [Server] Localhost [ID] 7082 [Time] 4/3/2013 4:54:27 PM [Program] pinetmgr [Priority] 10 [ProcessOSUser] SYSTEM [ProcessID] 2732 [Severity] Debug 
                        Successful login  ID: 30. Address: fe80::f059:17cd:e306:34d9%10. Name: iisexpress.exe(19204):remote. Identity List: ppidentity | PIWorld. Environment Username : SIMPLYFEYE\Lokesh. Method: Windows Login (SSPI,NTLM)