12 Replies Latest reply on Feb 6, 2015 7:54 AM by Roger Palmen

    Pi OledB Security Settings and access to datasources

    IPCOSRumaila

      Hi,

       

      We have the following setup: 

       

      (1) AF Server also running the SQL Server with the AF Database. This SQL instance is also hosting a number of other databases which are datasources for attributes in the AFTree

       

      (2) A second SQL Server (2008R2) (on a different machine) having the a PI OledB Enterprise configured as a linked server pointing at (1)

       

      (3) An application server that queries server (2) through Stored Procedures that will access the Pi OleDB Enterprise linked server

       

      The linked server is configured to use a specific (domain) service account (Linked server -> Properties -> Security ( bullet "Be made using this security context" ) ). This way we avoid the problem with the "double hop" and NTLM authentication.  Using kerberos authentication is not an option in the context we are working in,

       

      For accessing the AF-Server this works fine.  But whenever accessing the data references (via snapshot table)  then this service account configured in the security-option of the linked server is not used!

       

      When thinking of it, this is probably normal because these settings are used only for speaking to the AF Server and not to the data-sources referred to from the attributes as these are accessed directly from the OleDB process.    

       

      These "data-sources" are PI and SQL Databases accessed from Custom Data References.  The PI access will have to be configured with a trust, so this should be OK. But how about the SQL databases? Currently these Custom Data References use a connection string which  is common to all users and specifies "Integrated Security = sspi;" and is actually stored in an attribute in the AF Configuration database.  This works fine for all other AF SDK clients, including webservices using the impersonation to a service account. But how to this within this OleDB? I cannot see a way to switch user (to a service account) for these  data references before it uses the connection string with the sspi.   Any suggestions that can help us out?

       

       

       

      Regards,

       

       

       

      Denis

        • Re: Pi OledB Security Settings and access to datasources

          Hello Denis,

           

          Denis Gauder

          But how to this within this OleDB? I cannot see a way to switch user (to a service account) for these  data references before it uses the connection string with the sspi.   Any suggestions that can help us out?

           

          You can set up your linked server connection through PI OLEDB Enterprise for a specific user account. Within SQL Server Management Studio, open the properties for your linked server connection and select Security. Option "Be made using this security context:" allows you to specify a username and password for the connection. Please make sure to remove "Integrated Security=SSPI" from the connection string.

           

          To configure security for AF objects you can use PI System Explorer.

           

           

            • Re: Pi OledB Security Settings and access to datasources
              IPCOSRumaila

              Hi Gregor,

               

              this is exactly the way we are working. This works fine for all access to the AF-Server. But it turns out that for access to the data references this user-context switch is not applied. All access to the data references happens with the original account that is speaking to the OleDB linked server. This connection string I was referring to is not the connection string from the linked server, but the internal connection string in the Custom Data Reference, which should be the same for all clients. At that point is is too late to switch to a different user.

               

              Something I will try is to set up an extra linked server from the SQL instance to itself using the same service account.. Using this extra redirection should switch to correct account on (2) also for the data references. Not sure it will work.  

               

              Regards,

               

              Denis

                • Re: Pi OledB Security Settings and access to datasources

                  Hello Denis,

                   

                  I understand that you like to use data pulled from AF through PI OLEDB Enterprise in a custom data reference in AF. This appears to be some kind of detour - at least at first glance. Are you aggregating / joining information with your queries (stored procedures)? This is currently the only reason that I can think of that would justify the detour.

                   

                  Have you checked other approaches for data treatment like e.g. Asset Based Analytics?

                   

                  To my understanding, when you create a linked server, you do this for a specific database. When you need access to linked server objects when creating custom data references in AF, you would need to set permissions to the SQL Database instance accordingly.

                    • Re: Pi OledB Security Settings and access to datasources
                      IPCOSRumaila

                      Hi Gregor,

                       

                      Not sure if I made myself clear. Maybe and example will help.  Given LINKED_SERVER_TO_PIAF the linked server on the SQL server  referring to the PIAF Server. This linked server is set up with a specific user "Service Account B" .  The queries are part of stored procedures triggered on the application server under account "End User A".

                       

                      When executing:

                       

                      select * from openquery(LINKED_SERVER_TO_PIAF ,'SELECT ehchild.Name ChildName, ea.Name  

                       

                       FROM AFdemo.Asset.ElementHierarchy eh

                       

                       INNER JOIN  AFdemo.Asset.ElementHierarchy ehchild ON ehchild.ParentElementID = eh.ElementID

                       

                       INNER JOIN AFdemo.Asset.ElementAttribute ea ON ea.ElementID = ehchild.ElementID

                       

                       WHERE

                       

                            eh.Path = N''\South\Assets\''

                       

                            and eh.Name = ''Nr1''

                       

                              and ehchild.Name = ''Status''

                       

                             and ea.Name like ''Type String''')

                       

                      This works fine.    AF Server is accessed using the account "Service Account B"

                       

                      But when also using the snapshot table:

                       

                      select * from openquery(LINKED_SERVER_TO_PIAF ,'

                       

                           SELECT ehchild.Name ChildName, ea.Name, s.*

                       

                       FROM AFdemo.Asset.ElementHierarchy eh

                       

                       INNER JOIN  AFdemo.Asset.ElementHierarchy ehchild ON ehchild.ParentElementID = eh.ElementID

                       

                       INNER JOIN AFdemo.Asset.ElementAttribute ea ON ea.ElementID = ehchild.ElementID

                       

                       INNER JOIN AFdemo.Data.Snapshot s ON s.ElementAttributeID = ea.ID

                       

                       WHERE

                       

                            eh.Path = N''\South\Assets\''

                       

                            and eh.Name = ''Nr1''

                       

                        and ehchild.Name = ''Status''

                       

                        and ea.Name like ''Type String''')

                       

                      Then the data-source behind the Attribute is accessed with the account "End User A"  given that we start the stored procedure from the SQL server.

                       

                      We could provide access to "End User A" , but in case  the query starts from the Application Server, through the SQL Server to the source of the attribute, the Windows NTLM gives us an ANONYMOUS LOGON  after the second authentication hop. And then we are stuck, ... .

                       

                      Regards,

                       

                      Denis

                        • Re: Pi OledB Security Settings and access to datasources

                          Hi Denis,

                           

                          Sorry but it feels like sitting in the jam pot once more.

                           

                          Is my understanding correct that the Attribute is a table lookup reference to a "foreign" SQL Server database. If so, do you see the data within PI System Explorer but not with the linked server through PI OLEDB Enterprise?

                           

                          Does this summarize your issue?

                            • Re: Pi OledB Security Settings and access to datasources
                              IPCOSRumaila

                              Hi Gregor,

                               

                              we are not using the "Table Lookup" Data References but similar Custom Data References.  For the sake of clearing this out I just created an attribute using "Table Lookup" with a linked table, and this actually works! Looking at the PI AF  User Manual it says that it the content of the linked table is fetched though the "AF Server". So that explains why that works, in that case the "specific user account" entered in the OleDB Enterprise linked server is doing the job.

                               

                              We have two types of Custom Data References that fetch data from SQL Databases.

                               

                              One of them is a mere replacement of the "Table Lookup" with linked tables. In case of larger tables we experienced significant performance issues. We did experiments where we saw  that the time for fetching the attribute doubled when the size of the table doubled,   So we kind of assumed that the these "Table Lookup" Data References with linked tables are linear in the size of the tables.  Replacing these Table lookup with a Custom Date Reference that fetches the data directly from the database with proper indexes on the columns drastically improved the performance.  Maybe we overlooked some feature that could increase the performance of these "Table Lookup" Data references?

                               

                              These Custom Data References use a connection string equal to the connection string in the definition of the linked table, containing "Integrated Security=SSPI". But these queries do not go through AF-Server and I believe that is the reason why the "specific user"-setting  in the OleDB Enterprise security setting is ignored for these Custom Data References.  

                               

                              We have also a similar Custom Data Reference which queries a table in SQL which implements  a sparse matrix with 3 dimensions: asset, attribute, time.  Something we could also do with PI Points, but it concerns mainly static  data or data changing with low frequency and other reason why this cannot be written to PI for now.  The table has >10.000.000 records, I believe "Lookup Tables" are not an option here.  

                               

                              Hope this sheds some light on what we are doing. And yes, when using PI System Explorer we can see the data.

                               

                              ,

                               

                              Regards,

                               

                              Denis

                                • Re: Pi OledB Security Settings and access to datasources

                                  Hello Denis,

                                   

                                  Denis Gauder

                                  These Custom Data References use a connection string equal to the connection string in the definition of the linked table, containing "Integrated Security=SSPI".

                                   

                                  If you see the data when using PI System Explorer but not through PI OLEDB Enterprise, your issue is likely a classic double-hop problem. Can you try using a specific user account within your Custom Data Reference instead of Windows Integrated Security?

                                   

                                  Denis Gauder

                                   The table has >10.000.000 records, I believe "Lookup Tables" are not an option here.

                                   

                                  This is about 1,000 times of the maximum size we suggest with lookup tables and hence definitely too much. Also suggesting to distribute the data among several lookup tables, if possible, doesn't appear to make sense.

                                    • Re: Pi OledB Security Settings and access to datasources
                                      IPCOSRumaila

                                      Hi Gregor,

                                       

                                      We are in sync now! Indeed this a double hop problem with NTLM authentication. The question is, are there any suggestions to avoid that?

                                       

                                      Using a specific account in the connection string is something we will definitely try, just to verify that this works, and may also be part of a temporary solution to get things going, but I would like to have a better solution.

                                       

                                      We want to have different users for the different applications accessing the data. Different service accounts for OleDB and webservices and the users itself for AF Client application. This way we can have tailored access rights and access logging.  Also the current Connection string is perfect for storing in a public place in the AF Configuration tree,

                                       

                                      Up to now using the "Integated Security=SSPI" this was working fine. In  Client application the user itself is used (in a single hop), and in case of a web-services the impersonation using a specific account is working as expected, also for the custom data reference.  But not with the PI OleDB Enterprise, ... .  

                                       

                                      I see no easy way to pass something to the Custom Data Reference on how to connect to datasources depending on the way it is used.

                                       

                                      We could check on the name of the user and switch to a different account in case of an "OleDB" user, but this is more a hack than a solution?

                                       

                                      Also where will we store the account/password, I'm not fond of compiling it in :-( .

                                       

                                      Regards,

                                       

                                      Denis

                                        • Re: Pi OledB Security Settings and access to datasources
                                          skwan

                                          Denis:

                                           

                                          Please be aware that in AF 2014, enhancements were made to the Table LookUp Data Reference to support parameters for the queries.  I would suggest you take a look at this and see if it can help your use case.  With parameters, you no longer have to load the entire external table into memory, which is where the performance limitation typically comes in.

                                           

                                          In addition, in AF 2014, the external table connections are separate from the tables themselves so if you have a lot of external tables, it's much easier to manage.

                                            • Re: Pi OledB Security Settings and access to datasources
                                              Roger Palmen

                                              Is it really important to access the 'lookup' database with the end user credentials? Typically, the application layers (in this case AF &  OLEDB) are trusted enough to restrict access to only that what is applicable for the end user. You already have a multiple hop scenario, so the risk of penetration is already low and including passwords in your application would only make that worse.

                                               

                                              In similar cases i always used implicit credentials to connect to the backend databases. AFserver can also be setup to do that when using the table lookup DataReference.

                                                • Re: Pi OledB Security Settings and access to datasources
                                                  IPCOSRumaila

                                                  Hi everybody,

                                                   

                                                  Sorry for the silence from my side. Holidays, .... .

                                                   

                                                  We are currently using a hack where the "SSPI" authenthication is replaces by a specific SQL account/password in case of  an OleDB situation. This works, but needs a more solid solution.

                                                   

                                                  I will have a look a these new version of AF, Maybe the "table lookup data reference"  becomes a an option again. Thanks for the tip Steve.

                                                   

                                                  Concerning the need for using credentials. Using service accounts is more than enough security. By configuring this service account in the linked server for the OLE DB Enterprise, it turns out the code in the Custom Data References does not use this service account, and we are stuck with the end-user account.  But maybe there is some way to set up some trust within the SQL-servers, like PI Trust, that deals with this. Currently my knowledge does not go that far, but something to investigate!  Thanks Roger.

                                                   

                                                  Regards,

                                                   

                                                  Denis