Pi OledB Security Settings and access to datasources

Discussion created by IPCOSRumaila on Jul 4, 2014
Latest reply on Feb 6, 2015 by Roger Palmen



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?