bderemer

Cannot connect to AF server using username/password, can only connect with Windows Integrated security

Discussion created by bderemer on Jun 6, 2012
Latest reply on Jun 6, 2012 by Ahmad Fattahi

How can I get the PIOLEDBENT provider to support non-integrated security?  The error I am getting is:

 

 [OSIsoft.AFSDK] Cannot connect to server 'localhost'.] 

 

when trying to execute any query.

 

From the PI SQL Commander tool, I can only connect to the AF server using integrated security.  I get the same error when I try to use any username or password.  I edited the AFServices.exe.config file to NOT use integrated security, and then the PI SQL Commander can no longer connect to the AF server using integrated or non-integrated security. The underlying SQLServer is configured to use both integrated and standard security.

 

The error from the login log created during the failure from PI SQL Commander is:

 

06-Jun-2012 10:57:54.456     PROVIDER_INIT     PI SQL Query Compendium     4228     6     4156     Data Source     IDBInitialize     Initialize     User ID = "piadmin"; Initial Catalog = "Configuration"; Data Source = "WVM-OSI-SERVER"; Window Handle = "0"; Prompt = "1"; Extended Properties = ""; Connect Timeout = "60"; Integrated Security = ""; Command Timeout = "-1"; Log Level = "3"; Log File = "c:\temp\seclog.txt"; Shorten Primary Keys = "0"; Always Return Rowset = "0"; Defer Execution = "0"; Disable Server Selection = "0"; Time as Double = "0"; Optimization Log Limit = "100"; Keep Default Ordering = "0"; Function Errors as NULL = "0"; Cancel On Low Resources = "-1"; Show Hidden Metadata = "0"; Allow Expensive = "0"; Use Agent = "-1"; 

 

06-Jun-2012 10:57:54.956     ERROR_RECORD     PI SQL Query Compendium     4228     6     4156     Data Source     IDBInitialize     Initialize     E_FAIL - [OSIsoft.AFSDK] Cannot connect to server 'WVM-OSI-SERVER'.

 

06-Jun-2012 10:57:54.956     INTERFACE_ERROR     PI SQL Query Compendium     4228     6     4156     Data Source     IDBInitialize     Initialize     E_FAIL

 

06-Jun-2012 10:57:54.956     INTERFACE_CALL     PI SQL Query Compendium     4228     6     4156     Data Source     IDBProperties     GetProperties     

 

06-Jun-2012 10:57:54.956     INTERFACE_INPUT     PI SQL Query Compendium     4228     6     4156     Data Source     IDBProperties     GetProperties     cPropertySets = 2; i = 0; rgPropertySets.guidPropertySet = DBPROPSET_DBINIT; rgPropertySets.cPropertyIDs = 11; j = 0; ulPropertyId = DBPROP_AUTH_PASSWORD; j = 1; ulPropertyId = DBPROP_AUTH_USERID; j = 2; ulPropertyId = DBPROP_INIT_CATALOG; j = 3; ulPropertyId = DBPROP_INIT_DATASOURCE; j = 4; ulPropertyId = DBPROP_INIT_HWND; j = 5; ulPropertyId = DBPROP_INIT_PROMPT; j = 6; ulPropertyId = DBPROP_INIT_PROVIDERSTRING; j = 7; ulPropertyId = DBPROP_INIT_TIMEOUT; j = 8; ulPropertyId = DBPROP_AUTH_INTEGRATED; j = 9; ulPropertyId = DBPROP_INIT_OLEDBSERVICES; j = 10; ulPropertyId = DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO; i = 1; rgPropertySets.guidPropertySet = {2990C808-33A7-45CA-B05F-605393116275}; rgPropertySets.cPropertyIDs = 15; j = 0; ulPropertyId = 1; j = 1; ulPropertyId = 2; j = 2; ulPropertyId = 3; j = 3; ulPropertyId = 9; j = 4; ulPropertyId = 7; j = 5; ulPropertyId = 10; j = 6; ulPropertyId = 6; j = 7; ulPropertyId = 8; j = 8; ulPropertyId = 14; j = 9; ulPropertyId = 11; j = 10; ulPropertyId = 17; j = 11; ulPropertyId = 18; j = 12; ulPropertyId = 12; j = 13; ulPropertyId = 20; j = 14; ulPropertyId = 21; 

 

06-Jun-2012 10:57:54.972     INTERFACE_OUTPUT     PI SQL Query Compendium     4228     6     4156     Data Source     IDBProperties     GetProperties     *pcProperties = 2; i = 0; (*prgProperties).guidPropertySet = DBPROPSET_DBINIT; (*prgProperties).cProperties = 11; j = 9; ulPropertyId = DBPROP_INIT_OLEDBSERVICES; ulStatus = DBPROPSTATUS_NOTSUPPORTED; j = 10; ulPropertyId = DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO; ulStatus = DBPROPSTATUS_NOTSUPPORTED; 

 

06-Jun-2012 10:57:54.972     INTERFACE_WARNING     PI SQL Query Compendium     4228     6     4156     Data Source     IDBProperties     GetProperties     DB_S_ERRORSOCCURRED

 
This is PI AF 2010 and PI OLEDB Enterprise 2010

Outcomes