17 Replies Latest reply on Oct 12, 2017 7:32 AM by ramoas

    Error Setting DBSecurity

    vint.maggs@srs.gov

      All,

      I am cleaning up the database security for my system. While editing the permissions on the PIModules table, I got this error when trying to delete the Identity: "Error setting DBSecurity for PIModules on server x. RPC Invoke failed [-16034]. At least one PI Identity on the PI Module has zero mapped Windows Principals."

       

       

      How to I proceed?

       

      Thanks
      Vint

        • Re: Error Setting DBSecurity
          mskallet

          Hi Vint,

           

          I attempted to test the same situation and I got the same error you mentioned:

          when trying to set an identity on this table when there was no windows account mapped to it. Then I went to Security > Mappings and Trusts and mapped any windows account to this identity (I just used a local TEST account) and I was able to add the identity to the PIModules table.

           

          I was able to successfully remove the same identity after I had deleted the mapping, so I was not able to re create your same situation. Perhaps try mapping any domain or local account to the identity you are trying to delete, then delete it, then you can delete the mapping you used.

           

          Hopefully it will let you delete the identity if a mapping exists to it exists

           

          Best,

          Mark

            • Re: Error Setting DBSecurity
              vint.maggs@srs.gov

              I don't think that is my problem, Mark. The account still exists and was mapped to an identity when I was doing this. I had no problems removing the identity from the other tables, just this table. I have since deleted the identity (not the account, yet) entirely. When I did that, the identity changed to a number 15 (database key perhaps?) on the PIModules table.

               

              After further researching, I decided to click the AF Link "Check for security conflicts" button. The Security Report says "The following Asset elements use a Windows account that does not have an explicit mapping in PI."

                   Element  \\<AF-HOSTNAME>\MDB to AF Sync - <DA-HOSTNAME>\ModuleDB 

               

              I don't know how that got removed or when and how I set it in the first place.

               

              So now my question is , when I click the "Create missing mapping" button, do I map it to a local or domain group, user, or computer account and to which PI Identity?

               

              I am getting ready to go to production, which is why I am "cleaning up" the security.

               

              Thanks,

              Vint

                • Re: Error Setting DBSecurity
                  ramoas

                  See my first reply, Mark.  None of the other DBSecurity tables are mapped to PI AF -- just PIModules DBSecurity.  Thus, that AF element is created and managed/synchronized automatically by PI AF Link.  The security got set and removed when you modified PIModules DBSecurity.

                   

                  Can you be more specific about "account"?  Do you mean Windows account (user or group)?  In the ACL for PIModules DBSecurity, you are not directly adding Windows accounts -- you're adding PI Identities / PI Users / PI Groups (this is true of all ACLs in the PI Data Archive).  PI AF Link is checking to ensure that everything specified in PIModules DBSecurity has at least one PI Identity Mapping pointing to it.  This is what you need to correct as outlined before.

                   

                  In the PI Data Archive, the PI Identity Mapping is the only thing that references a Windows account (besides the creator / modifier of a point or module).  Deleting a Windows account specified in a PI Identity Mapping (or creator / modifier of a point or module), will cause that Windows account to be rendered/displayed as its Windows SID.

                   

                  Every PI Identity / PI User / PI Group has a unique numerical integral identifier.  Once it gets deleted, all references to it refer to this numerical identifier because its current name can no longer be determined.  What actually gets stored in the ACL is the numerical identifier -- it just gets looked up for display purposes.  Thus, '15' is the corresponding numerical identifier for the PI Identity that you deleted.  When experimenting/testing, keep in mind that you can actually disable a PI Identity / PI User / PI Group instead of deleting it; disabling it renders it unusable, but preserves the record of its existence.

                    • Re: Error Setting DBSecurity
                      vint.maggs@srs.gov

                      Omar,

                         The "account" was a local account on my data archive server. It had the same name and password as the account on the non-domain joined interface node. At the time it was created, I thought it was required for this scenario. I have since learned otherwise. As I said earlier, I am now running the interface as Local System on the non-domain joined interface node and have created the appropriate trusts. It is all working fine.

                       

                         I went and looked at the security settings for the AF databases, and this account is not defined there. As I said, it existed for the interface so that I could configure Trusts.

                       

                         I deleted the identity, but the local account remains on the data archive server. The account has been disabled for some time. I am not going at this willy nilly. I am being thorough and methodical, testing along the way and checking for impacts and errors.

                       

                         I would like to reiterate that I got this error before deleting the identity, while it was mapped to the local account which was disabled. So my original question still stands... How do I fix this error? I do not believe that recreating the Identity and mapping it to the local account is the answer as that Identity was mapped at the time the problem originally occurred.

                       

                         This brings me back to my reply to Mark regarding the Security Report...This may very well be the missing mapping that it is fussing about, I just don't know how to respond to the prompts.

                       

                      Thanks,

                      Vint

                        • Re: Error Setting DBSecurity
                          ramoas

                          The security of the PIModules DBSecurity gets synchronized with an AF element, not the PIFD databases.  Specifically, it gets synchronized with the AF element: \\<AF-HOSTNAME>\MDB to AF Sync - <DA-HOSTNAME>\ModuleDB.  That is exactly what I was trying to say in my last two replies.  Modifying that element in PI AF affects PIModules DBSecurity in the PI Data Archive and vice versa.  Therefore, there are supposed to be restrictions on what edits are permitted to PIModules DBSecurity: the ACL is only supposed to contain PI Identities that have a PI Identity Mapping to some Windows account.

                           

                          When you got the error, did you make any other changes to PIModules DBSecurity ACL besides attempting to delete this one PI Identity?  Perhaps the error was not caused by that PI Identity or that Windows account.

                           

                          Let's answer these questions:

                          (1) What PI Identities exist now in PIModules DBSecurity?

                          (2) Do all of the PI Identities in (1) appear in a PI Identity Mappings?

                          (3) What Windows accounts exist on the security of that AF Element \\<AF-HOSTNAME>\MDB to AF Sync - <DA-HOSTNAME>\ModuleDB?

                          (4) Do all of these Windows accounts in (3) appear in a PI Identity Mapping?

                           

                          In order to sync any edits on that module / element from either the MDB or PI AF, all the PI Identities and all the Windows accounts must appear in a PI Identity Mapping.

                           

                          Does your system have any root PI Modules outside %OSI?  I'm assuming you care about PIModules DBSecurity because you're actually using PIModules.  Yes, MDB to AF migration/two-way synchronization was originally intended to allow customers (especially PI ACE customers) to get the benefits of both MDB and AF.  Most applications use either MDB or PI AF, not both, but there were some use cases for having the MDB information reflected in PI AF and vice versa.  Starting with PI Data Archive 2015, MDB to AF migration/synchronization also became a pre-requisite for PI Batch to PI EF migration.

                           

                          If this is a brand new system that will never use PI Modules (or PI Units), then you likely do not need PI AF Link.  Unfortunately, with PI Data Archive 2017, I am not current on whether PI AF Link can be disabled after the fact or what's the currently best supported method for disabling it.  If it's not documented in the manual or a KB article, then you'll need to contact OSIsoft Technical Support to get the procedure, if any.

                           

                          You're confusing some ideas together.  A PI Identity can be used with a PI Trust, and a PI User or PI Group can be used with Windows authentication.  With the exception of PI User / password authentication, the method of authentication is independent from the resulting set of PI Identities / PI Users / PI Groups.  Furthermore, with Windows authentication, as much as possible, the recommendation is to use Windows groups in PI Identity Mappings, not specific Windows users.  That way, once you set up your mappings in the PI Data Archive, you manage access through Active Directory or Windows user/group management on the local computer.  If you have a bunch of individual Windows users in your PI Identity Mappings, you might want to revisit the administrative strategy and the unique categories of access required.

                           

                          I would strongly recommend revisiting your decision to use PI Trust authentication for your interface computer.  PI Trust authentication is incredibly poor/weak and does not get the benefit of transparent transport security (= integrity + confidentiality) on data going over the network to/from the PI Data Archive.  I am not familiar with the GSE D3 DBA interface or its requirements.  If it's not a new PI Connector using PI AF SDK and/or PI SDK, then, hopefully, it works with PI API 2016, which supports Windows authentication.  Regardless of what you do for the interface-specific connection, I hope you at least configure the PI Buffer Subsystem on that computer to use Windows authentication, not a PI Trust.

                           

                          If the interface node is not in a trusted domain with the PI Data Archive, then your options for Windows authentication are to either create a local Windows user on the interface node and the PI Data Archive with identical username and password like you had before or to use Windows Credential Manager on the interface computer to specify the desired Windows account you want to use on the PI Data Archive computer.  The latter option is possible even if you want to use Local System (if possible, Local System should be avoided from a least-privilege perspective), but it's a little more annoying to configure Windows Credential Manager for Local System.  For more on how to use Windows Credential Manager, see this KB article:  https://techsupport.osisoft.com/Troubleshooting/KB/KB01457.

                            • Re: Error Setting DBSecurity
                              vint.maggs@srs.gov

                              Good Morning Omar, I hope you enjoyed your weekend. In response to the questions above:

                               

                              1. Using PI SMT, the only PI Identities defined are PIEngineers, PIOperators, PISupervisors, PIWorld, Coresight, and PIInterfaces. The Coresight Identity is mapped to a domain account created for this purpose and used for PIVision access as prescribed by the documentation. The PIInterfaces Identity is not mapped to any account and is used to set up Trusts for the Interface Node. And FYI, the GSE D3 DBA Interface is an OSISoft product.

                               

                              2.  I am not exactly sure what you are asking but I am going to say YES with the exception of the PIInterfaces. There are no individuals (domain user accounts) mapped to any PI Identities in PI SMT. I am diligently following the Security Best Practices Guide and managing access through domain group memberships in Active Directory.

                               

                              3. No windows accounts exist on the MDB-AFMigrationData element. Only the default: piadmin: A(r,w) | piadmins: A(r,w) | PIWorld: A(r).

                               

                              4. Not applicable.

                               

                              Also, the only Module at the root of Modules is %OSI.

                               

                              At this point I would like to add something I just discovered while composing this reply: When I double-clicked the MDB-AFMigrationData Module in PI SMT, the AFGroupSID property contains the SID of the account I am trying to remove. Could this be the issue?

                               

                              Thanks,

                              Vint

                                • Re: Error Setting DBSecurity
                                  ramoas

                                  Unfortunately, I failed to communicate my questions clearly, so let me try again, starting with just the first two questions:

                                   

                                  (1) This question is about the current setting for PIModules DBSecurity, not about the list of all the defined PI Identities.  In PI SMT, go to Security > Database Security, and find PIModules.  How is it currently configured?  Please take a screenshot and/or transcribe it to text.  For example, on a new installation of more recent PI Data Archive versions, the default ACL for PIModules DBSecurity is "piadmin: A(r,w) | piadmins: A(r,w) | PIWorld: A(r)."

                                   

                                  (2) To answer this question, you need the information from question (1) and the list of PI Identity Mappings from PI SMT.  For example, if piadmins appears in the ACL for PIModules DBSecurity determined in (1), is there at least one PI Identity Mapping to piadmins?  Repeat this question for every PI Identity / PI User / PI Group found in (1).

                                   

                                  Please send a screenshot of the module %OSI\MDB-AFMigrationData showing the contents of all its properties (this should match the settings for MDB to AF in PI SMT > AF Link).  This information will help me clarify my instructions for question (3).

                                   

                                  Given what has been shared so far, It would not make sense for the AFGroupSID to be the issue.  Are you sure the SID is identical and not just very similar?  With local accounts on a given computer, at least the very last component of a SID should be different.  Furthermore, two different computers should have different SIDs; typically, only well-known accounts (e.g., LocalSystem, service SIDs, etc.) should have an identical SID across computers.

                                    • Re: Error Setting DBSecurity
                                      vint.maggs@srs.gov

                                      For the PIModules table, I have the default ACLs as you describe plus Coresight: A(r), PIInterfaces: A(r), and 15: A(r,w). There is no Identity mapped to piadmin, just a trust for the loopback address. The piadmins identity is mapped to a domain group of which I am a member.  PIWorld is not mapped to an identity. The Coresight Identity is mapped to a domain account for use with PIVision. PIInterfaces is not mapped to an identity but contains the trusts for the GSE D3 DBA interface. You will recall from earlier that 15 was the Identity I was originally trying to remove when this mess started. At the time that identity was mapped to a local account.

                                       

                                      I am unable to provide a screen shot for security reasons. I have double checked the AFGroupSID property for the MDB-AFMigrationData Module. This SID corresponds to the SID of the identically named local user account on the data archive server. I am running AF and PIVision on one box, and the data archive on another. So if Server A is the AF server and Server B the data archive server, the SID would correspond to \\ServerB\localusername

                                       

                                      Thanks,

                                      Vint

                                        • Re: Error Setting DBSecurity
                                          ramoas

                                          "PIInterfaces is not mapped to an identity but contains the trusts for the GSE D3 DBA interface."

                                           

                                          PIInterfaces is your problem.  Once MDB to AF is enabled, in order to edit PIModules DBSecurity, *ALL* (except for piadmin and PIWorld) PI Identities / PI Users / PI Groups that appear in the ACL must have a PI Identity Mapping.

                                           

                                          Did you disable PIWorld?  If not, then why have you added Coresight and PIInterfaces to the ACL with only read permissions?  By default, the ACL grants read permission because of PIWorld: A(r), so as long as you have not disabled PIWorld, all authenticated users (regardless of authentication method) should have read permissions to PIModules DBSecurity.

                                           

                                          You have two options to fix:

                                          - (A) if PIWorld is not disabled and provides read permissions, simply remove PIInterfaces as well as 15 from the ACL for DBSecurity when editing it

                                          - (B) if PIWorld is disabled, first add a PI Identity Mapping to PIInterfaces; if you do not want to allow this PI Identity Mapping to be used, once it's created, simply disable it (most granular: you can edit just this specific mapping to disable it; most broad: edit the PIInterfaces PI Identity to disable its use in all mappings); PI AF Link ignores whether the mapping is enabled; once this is completed, simply remove 15 from the ACL for DBSecurity when editing it

                                           

                                          Please note that PI Identities do not "contain" PI Trusts.  Just like PI Identity Mappings, PI Trusts can be configured to point to any PI Identity / PI User / PI Group.

                                           

                                          I was not asking for the properties of MDB-AFMigrationData because of the AFGroupSID property -- I want to know what AF element you configured for synchronization in PI AF so that you could examine its security.  I wanted to confirm the assumption I was making based off the Security Conflicts Report shared previously.  We'll come back to both the AFGroupSID (there's still some confusion here that needs to be clarified) and this AF element once you have resolved the error when editing the ACL for PIModules DBSecurity.

                                           

                                          BTW, it seems like you actually first encountered this error back in February 2017 when setting up that interface: https://pisquare.osisoft.com/message/94017-re-pi-interface-for-gse-d3-dba-4317-firewall-port-requirements#comment-94017.

                            • Re: Error Setting DBSecurity
                              vint.maggs@srs.gov

                              I just looked at the Security and System Attributes using PI Builder. The account in question is listed as the creator for many of these points.

                               

                              Not sure if this is relevant or not, just throwing it out there.

                               

                              Vint

                                • Re: Error Setting DBSecurity
                                  ramoas

                                  As I mentioned in my previous reply, that is expected and is not relevant to the error.  Please see the 4 questions in my reply from 10:57 AM.  The focus should be on reviewing the PIModules DB Security, the security of the corresponding PI AF element(\\<AF-HOSTNAME>\MDB to AF Sync - <DA-HOSTNAME>\ModuleDB), and the PI Identity Mappings.  Which Windows account(s) does the Security Conflict Report identify as missing a mapping?

                              • Re: Error Setting DBSecurity
                                ramoas

                                What is prompting such a cleanup?  Is this a production system or a test system?  Before making any more changes, I would recommend taking a recent backup if you have not already done so that you can quickly revert if necessary, especially on a production system.  The actual security settings required by some applications and usage scenarios is rarely obvious until after security permissions have been removed which may cause significant disruption to users and applications in a production system and any layered business applications.  I would also not make too many different types of security changes all at once, and I would recommend waiting several days in between types of changes to allow access issues to manifest themselves and to be correctly and easily understood.

                                 

                                What version of the PI Data Archive do you have?  Have you enabled PI Module DB to PI AF migration/synchronization with PI AF Link?

                                 

                                I'll assume you have at least PI Data Archive 2010 (3.4.385.59) or later and have enabled MDB to AF migration/synchronization.  If not, then something is wrong and you should contact OSIsoft Tech Support to help diagnose your issue and get to root cause.

                                 

                                In you have such a PI Data Archive version and you are in such a configuration, editing the security on the PIModules DB Security also affects the security of the corresponding root AF element in PI AF under which all modules are migrated/synchronized.  While the PI Data Archive supports multiple authentication methods (Windows authentication, PI Trust authentication, PI User/password authentication), PI AF only supports Windows authentication.  Therefore, when editing PIModules DB Security in the PI Data Archive, all specified PI Identities must have a PI Identity Mapping for Windows authentication to allow PI AF Link to make a corresponding edit to the AF element; otherwise, the edit will be rejected with the given error message.

                                 

                                To fix, you have two straight forward options:  (1) if they're not actually needed, remove any PI Identity in your desired PIModules DBSecurity that does not have a PI Identity Mapping; (2) if they're actually needed, add a meaningful PI Identity Mapping to any such PI Identity in your desired PIModules DBSecurity.

                                  • Re: Error Setting DBSecurity
                                    vint.maggs@srs.gov

                                    Omar,

                                         This is a fairly new system. It started out as Data Archive 2016, since upgraded to 2017 SP1 with the Hotfix and W2K12R2 and SQL Server 2014 Standard Edition.

                                     

                                        I don't fully understand the purpose of the MBD and the MDB - AF Syncronization. I thought this was built for older versions to have a migration path and I don't think I ever used the MDB, suspect I don't need it, and would like to eliminate it entirely if that is an option. I have enabled PI Module DB to PI AF migration/synchronization with PI AF Link, and everything appears to be in sync.

                                     

                                        I know this identity is not needed. It was originally created because I had a non-domain joined interface node running the GSE D3 DBA software under a specific account. I later reconfigured the interface to run using the Local System account and set up Trusts which enable it to connect without using an identity.

                                     

                                    Thanks,

                                    Vint