none
BDC / Oracle Data - TNS issue when connecting from client machine RRS feed

  • Question

  • I am getting a "Cannot connect to MyInstance" error when I use a Business Data List web part to connect to an Oracle database via BDC/SSO.    I am able to connect and pull data successfully when browsing from the MOSS server, but this error occurs intermittently on the client machines.  When it does happen on the client, it also causes the same error when browsing from the MOSS server.  I have to do an IISRESET to be able to connect from both environments again.


    Here is the area in the SharePoint logs where the error occurs.
    08/14/2009 00:38:16.40  SSOSRV.EXE (0x0798)                      0x10F0 SharePoint Portal Server       SSO                            0 Medium   CSsoResourceManager succeeded in getting the connection string 
    08/14/2009 00:38:16.40  SSOSRV.EXE (0x0798)                      0x10F0 SharePoint Portal Server       SSO                            0 Medium   Provider=SQLOLEDB;Extended Properties='Server=MOSSDBServer;Database=SSO;Trusted_Connection=yes;App=Windows SharePoint Services;Timeout=15'; 
    08/14/2009 00:38:16.40  w3wp.exe (0x10D0)                        0x063C SharePoint Portal Server       Business Data                  71tj High     Setting credentials obtained from SSO in Connection string 
    08/14/2009 00:38:23.24  w3wp.exe (0x10D0)                        0x10CC SharePoint Portal Server       Business Data                  79bv High     Initiating BDC Cache Invalidation Check in AppDomain '/LM/W3SVC/1275250665/Root-1-128946976730065399'  
    08/14/2009 00:38:23.24  w3wp.exe (0x10D0)                        0x10CC SharePoint Portal Server       Business Data                  79bx High     Completed BDC Cache Invalidation Check in AppDomain '/LM/W3SVC/1275250665/Root-1-128946976730065399' 
    08/14/2009 00:38:25.45  w3wp.exe (0x10D0)                        0x063C SharePoint Portal Server       Business Data                  0 Critical Could not open connection using 'data source=db_oracle;pooling=true;persist security info=false;user id=my_login;password=****' in App Domain '/LM/W3SVC/1275250665/Root-1-128946976730065399'. The full exception text is: ORA-12154: TNS:could not resolve the connect identifier specified  
    08/14/2009 00:38:25.45  w3wp.exe (0x10D0)                        0x063C SharePoint Portal Server       Business Data                  8u8a High     System.EventArgs 
    08/14/2009 00:38:25.45  w3wp.exe (0x10D0)                        0x063C                                1154                           9exz Medium   Audit Log Event: Source='BusinessDataCatalog', ResourceId='00000000-0000-0000-0000-00000000017c', SubResourceId='', UserName='Doe, John', UserMachineName='xx.xx.xx.xx', UserIp='xx.xx.xx.xx', Operation='Execute', Result='None', RequestUrl='http://mossserver/sso_bdc_demo/Shared%20Documents/bdc_demo.aspx' 
    08/14/2009 00:38:25.45  w3wp.exe (0x10D0)                        0x063C SharePoint Portal Server       Business Data                  79ct High     Chose Oracle database access provider 
    08/14/2009 00:38:25.45  w3wp.exe (0x10D0)                        0x063C SharePoint Portal Server       Business Data                  79cu High     Chose RdbCredentials auth mode 
    08/14/2009 00:38:25.45  w3wp.exe (0x10D0)                        0x063C SharePoint Portal Server       Business Data                  71tw High     Initialized DbSystemUtility for LobSystemInstance with Id '367' 
    08/14/2009 00:38:25.45  w3wp.exe (0x10D0)                        0x063C SharePoint Portal Server       Business Data                  71tx High     Db AuthN Mode: RdbCredentials 
    08/14/2009 00:38:25.45  w3wp.exe (0x10D0)                        0x063C SharePoint Portal Server       Business Data                  71ty High     Db Provider: Oracle 
    08/14/2009 00:38:25.45  w3wp.exe (0x10D0)                        0x063C SharePoint Portal Server       Business Data                  71q0 High     Checking for current connection to SystemInstance: ARTSInstance

    Here are my SSO settings in the Operations tab of the Central Administration:
    -Manage Account Information for an Enterprise Application Definition
    -mydomain\domain users
    Here are my settings under ShareServices/Business Data Catalog:
    -mydomain\domain users has Edit, Execute, Selectable in clients, Set Permissions rights for the Application down to its Entities
    Here is the connection information in my Application Definition File:
    <LobSystemInstances>
        <LobSystemInstance Name="MyInstance">
          <Properties>
     <Property Name="AuthenticationMode" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAuthenticationMode">RdbCredentials</Property>
     <Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbAccessProvider">Oracle</Property>
     <Property Name="RdbConnection Data Source" Type="System.String">db_oracle</Property>
     <Property Name="RdbConnection Pooling" Type="System.String">true</Property>
     <Property Name="SsoApplicationId" Type="System.String">MySSO</Property>
     <Property Name="WildcardCharacter" Type="System.String">%</Property>
          </Properties>
        </LobSystemInstance>
      </LobSystemInstances>
     
     
    Both server and client machines have Oracle Client 10.2 installed.  I have also written a webpart using SSO that can pull the data successfully when browsing from either the server or the client.  Additionally, I have also written a Console EXE that connects successfully from both machines.
    Any insight on why the BDC won't work from the client machine would be greatly appreciated.  Thanks in advance.
    Tuesday, August 18, 2009 2:57 PM

Answers

  • It seems the problem was that we have a master TNS file on centrally located on another server.  That TNS file is referenced by all other machines needing access to the Oracle database.  We copied the TNS file to the MOSS server.  And now, it is working consistently when the Business Data List web part is called on a client machine.

    • Marked as answer by leongek Wednesday, October 21, 2009 4:58 PM
    Wednesday, October 21, 2009 4:58 PM

All replies

  • When the error occurs on the client and then it occurs on the server, does it also not work through the web part and console app?  It sounds like there's something wrong with the OracleProvider instance.
    Friday, October 2, 2009 3:03 PM
  • Wow! 

    It's hard to tell but just try something crazy.  Set connection Pooling to false and re-import your adf.  I have the same setup you have and I have zero problem.  Of course, I am not in a production environment with Oracle. But I have hit and small tables with no apparent problems.
    the rest of the configuration is just as I have it which is basically what came from SDK examples.

    Regards


    Friday, October 2, 2009 9:21 PM
  • It seems the problem was that we have a master TNS file on centrally located on another server.  That TNS file is referenced by all other machines needing access to the Oracle database.  We copied the TNS file to the MOSS server.  And now, it is working consistently when the Business Data List web part is called on a client machine.

    • Marked as answer by leongek Wednesday, October 21, 2009 4:58 PM
    Wednesday, October 21, 2009 4:58 PM