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.
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,
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.
1 of 1 people found this helpful
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.
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.
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.