6 Replies Latest reply on May 15, 2018 8:30 PM by philco

    PI AF link table connection issue

    philco

      This is a question about the security of the PI AF link tables. The source of an existing linked table in AF needs to be moved from SQL server A to SQL server B. The connection string is set to "Integrated Security=SSPI". The linked table on SQL server A is working well but when trying to connect to the new one on SQL server B there's a connection error: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON". Worse, it's working well on the PI AF server but not on other computers. Which security should I look at? PI AF or MSSQL server or ? Thanks for your help.

        • Re: PI AF link table connection issue
          LalBabuShaik

          Hi Phillippe

           

          In PI system explorer, Library--> Table Connections --> Select the connection item and check the connection string. If you are using SSPI check PI AF service account whether it has access to SQL server B. You can also check the same in SQL server logins if AF service account is having at least read permissions.

            • Re: PI AF link table connection issue
              philco

              Hi Lal,

               

              The account used to run the AF service on the AF server has read and write access to the SQL database. This account can connect to the database using SSMS with success.

              What is strange is that the connection is working from the AF server but not from other computers.

              And in the error message it's not the AF service account that is displayed but 'NT AUTHORITY\ANONYMOUS LOGON'. It seems that the AF service account is not used to connect. Note that, in the security section of the link options, "Impersonate" is selected.

              This is the connection string used: Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XXXX;Data Source=XXXX

               

              Thanks for your help,

              Philippe

              • Re: PI AF link table connection issue
                philco

                The connection succeeds when I test it in the data link properties of the tables connections. It's only when the table is linked that the error message appears.

              • Re: PI AF link table connection issue
                lmlcoch

                This is a Kerberos Delegation issue. It sounds like your AF Service account can delegate end user credentials to the original SQL, but not the new one. This could be due to missing or incorrect Service Principal Name (SPN) of the target SQL Server and/or the lack of access to actually perform Kerberos Delegation.

                 

                Check out KB00599 for tips on enabling/checking Kerberos delegation. One note I want to add is that in section Trusting the AF Application Service for delegation, I highly recommend going for Constrained Kerberos delegation.

                1 of 1 people found this helpful
                  • Re: PI AF link table connection issue
                    philco

                    Hi Lubos,

                     

                    Thanks! That seems to be the problem here. I'm trying it and come back with the answer. The link to the KB is nice too.

                    • Re: PI AF link table connection issue
                      philco

                      It's quite clearly a Kerberos delegation issue but I haven't been able to solve it so far. I've followed all the steps detailed in the KB article but the issue is still happening:

                      - non-sensitive client credentials

                      - checked both SPNs associated with the account running the AF service.

                      - enabled Kerberos delegation for this account.

                       

                      I've enabled the Kerberos logging and got the error code: KDC_ERR_BADOPTION. This points to the delegation not being enabled, but it is. I've restarted the AF service but it didn't helped. I'm out of ideas right now.