12 Replies Latest reply on Jan 30, 2019 3:22 AM by SwatiAggarwal

    PI AF link table connection issue


      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

          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

              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,


              • Re: PI AF link table connection issue

                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

                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.

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

                    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

                      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.

                        • Re: PI AF link table connection issue

                          Hi Philippe,


                          Did you purge your tickets after making the changes? (klist purge)


                          Are all three machines (Client, AF Server and SQL Server) in the same domain? If they are, can you check for duplicate SPNs (setspn -x)?


                          Are you using unconstrained delegation on the service account running the PI AF Application service? If you are not, are you delegating to MSSQL?


                          Hope this helps,


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

                        Hi Philippe,
                        I had the same issue and it was resolved when I added a new SQL User account and provided its credentials.

                        Provider=SQLOLEDB;Password=<PASSWORD>;Persist Security Info=True;User ID=[NEW SQL USER ID];Initial Catalog=XXXX;Data Source=XXXX;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=XXXX;Use Encryption for Data=False;Tag with column collation when possible=False


                        And instead of selecting "Impersonate Client", we select "Supply Password" and enter the password for the SQL user account being used.


                        Thank you.