0

I am experiencing problems with a BDC connection to Oracle. I am using SharePoint 2013. The database I am connecting to is moved to a new Oracle 11 server. It was Oracle 10. I use this connection to sync with my user profile service.   I have the following Oracle connection in my tnsnames.ora  

BFTPRD=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=mis-prd-001.intra.nl)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=BFTPRD1)
    )
  )
 

My BDC import file:

    <?xml version="1.0" encoding="utf-8" standalone="yes"?>
<Model xmlns="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://schemas.microsoft.com/windows/2007/BusinessDataCatalog BDCMetadata.xsd" Name="Beaufort">
    <LobSystems>
        <LobSystem Type="Database" Name="Beaufort" DefaultDisplayName="Beaufort">
            <Properties>
                <Property Name="WildcardCharacter" Type="System.String">%</Property>
            </Properties>
            <LobSystemInstances>
                <LobSystemInstance Name="Beaufort connection">
                    <Properties>
                        <Property Name="AuthenticationMode" Type="System.String">RdbCredentials</Property>
                        <Property Name="DatabaseAccessProvider" Type="System.String">Oracle</Property>
                        <Property Name="RdbConnection Data Source" Type="System.String">BFTPRD</Property>
                        <Property Name="SsoApplicationId" Type="System.String">BeaufortSSO</Property>
                        <Property Name="SsoProviderImplementation" Type="System.String">Microsoft.Office.SecureStoreService.Server.SecureStoreProvider,Microsoft.Office.SecureStoreService, Version=15.0.0.0, Culture=neutral,PublicKeyToken=71e9bce111e9429c</Property>
                        <Property Name="ShowInSearchUI" Type="System.String">False</Property>
                    </Properties>
                </LobSystemInstance>
            </LobSystemInstances>
            <Entities>
                <!-- Locatie -->
                <Entity Namespace="http://localhost" Version="1.0.0.0" EstimatedInstanceCount="10000" Name="Beaufort_Locatie" DefaultDisplayName="Beaufort - Locatie">
                    <Properties>
                        <Property Name="Title" Type="System.String">Beaufort - Locatie</Property>
                    </Properties>
                    <Identifiers>
                        <Identifier TypeName="System.String" Name="EmployeeNumber" />
                    </Identifiers>
                    <Methods>
                        <Method IsStatic="false" Name="Beaufort_Locatie_ReadItem">
                            <Properties>
                                <Property Name="RdbCommandText" Type="System.String">
                                    SELECT loc_plaats
                                    FROM mw_gegevens
                                    WHERE pers_nr = :EmployeeNumber
                                </Property>
                                <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property>
                            </Properties>
                            <Parameters>
                                <Parameter Direction="In" Name=":EmployeeNumber">
                                    <TypeDescriptor TypeName="System.String" IdentifierName="EmployeeNumber" Name="EmployeeNumber" />
                                </Parameter>
                                <Parameter Direction="Return" Name="Beaufort_Locatie_ReadItem">
                                    <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="Beaufort_Locatie_ReadItem">
                                        <TypeDescriptors>
                                            <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="Beaufort_Locatie_ReadItemElement">
                                                <TypeDescriptors>
                                                    <TypeDescriptor TypeName="System.String" Name="loc_plaats" />
                                                </TypeDescriptors>
                                            </TypeDescriptor>
                                        </TypeDescriptors>
                                    </TypeDescriptor>
                                </Parameter>
                            </Parameters>
                            <MethodInstances>
                                <MethodInstance Type="SpecificFinder" ReturnParameterName="Beaufort_Locatie_ReadItem" ReturnTypeDescriptorPath="Beaufort_Locatie_ReadItem[0]"
                                    Default="true" Name="Beaufort_Locatie_ReadItem"
                                    DefaultDisplayName="Read Beaufort - Locatie external content type">
                                </MethodInstance>
                            </MethodInstances>
                        </Method>
                    </Methods>
                </Entity>
</Entities>
        </LobSystem>
    </LobSystems>
</Model>

I configured a Secure Store username and password. This worked before in Oracle 10. When I import this file I get no errors. I configured the sync to use a profile property as the paramater value for "EmployeeNumber". When I run the profile sync I get errors in the ULS:

the host name 'BFTPRD' is invalid and it's could not be found. The exception is System.Net.Sockets.SocketException (0x80004005): 
No such host is known     at System.Net.Dns.InternalGetHostByName(String hostName, Boolean includeIPv6)     
at System.Net.Dns.GetHostAddresses(String hostNameOrAddress)     at Microsoft.SharePoint.Utilities.CommonUtility.IsIntranetAddress(String hostName) "                               
Exception occured while logging Sqm data. Exception message: No such host is known  "                               
IsSystemTypeEnabled[Database] : True    "                               
Chose Oracle database access provider   "                               
Initialized DbSystemUtility for LobSystemInstance with Id '775' "                               
Db AuthN Mode: 'RdbCredentials' Db Provider: 'Oracle'   "                               
Checking for current connection to SystemInstance: Beaufort connection  "                               
st: RemoteAddress: 'https://sha-wp-001:32844/9a37f3b1aeb649c4becc4f1adda70028/SecureStoreService.svc/https' Channel: 'Microsoft.Office.SecureStoreService.Server.ISecureStoreServiceApplication' Action: 'http://schemas.microsoft.com/sharepoint/2009/06/securestoreservice/ISecureStoreServiceApplication/GetRestrictedCredentials' MessageId: 'urn:uuid:fc191350-7f5c-4edf-8408-cc7d6ba31124'    "                               
Setting credentials obtained from SSO in Connection string  "                               
Reverting Windows Impersonation:    "                               
Opened connection to Db:    "                               
Restoring Windows Impersonation:    "                               
Executing MethodInstance 'Beaufort_Afdeling_ReadItem' representing command type 'Text'  "                               
SELECT afdeling          FROM mw_gegevens          WHERE pers_nr = :EmployeeNumber  "                               
Parameter Signature : System.String :EmployeeNumber,    "                               
Parameter Values    "                               
    "                               
Parameter ':EmployeeNumber':    "                               
An instance of type 'System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.   "                               
 monitored scope (BandwidthUsageMonitor). Parent systemUtility.ExecuteStatic: Beaufort connection : Beaufort_Afdeling_ReadItem  "                               
Monitored Scope (BandwidthUsageMonitor). Execution Time=1.43616 "                               
um  Finished executing query    "                               
 monitored scope (BandwidthUsageMonitor). Parent No "                               
Monitored Scope (BandwidthUsageMonitor). Execution Time=0.122336    "                               
um  Closed connection to Db:    "                               
Error calling importProperties : System.InvalidOperationException: The given dot notation 'AFDELING' refers to a node in Type Descriptor structure that does 
not exist.     at Microsoft.SharePoint.BusinessData.Runtime.FieldValueHelpers.SplitWithTypeDescriptors(IEnumerable`1 roots, String dotNotation, IDictionary`2 cache, 
Boolean throwWhenAbsent)     at Microsoft.SharePoint.BusinessData.Runtime.FieldValueHelpers.GetTypeDescriptorByDotNotation(IView view, String dotNotation, IDictionary`2 
cache)     at Microsoft.SharePoint.BusinessData.Runtime.FieldValueDictionary.get_Item(String fieldDotNotation)     
at Microsoft.Office.Server.UserProfiles.ProfileImportExportService.importProperties(IEntityInstance profileEntityInstance, ProfileChangeData profileChangeEntry, 
String[] propertyList, Boolean fFirstMatch).    "

Why do I get the unexpected error "the host name 'BFTPRD' is invalid"? When I look at the BCS  connection in Central Admin I see that Host is filled  with the name form TNS names. But that is not a real host.

The database name is empty. When I try to fill that BCS thinks it is a SQl connection because then I get the following error in  my ULS

Error calling FindSpecific : Microsoft.BusinessData.MetadataModel.InvalidMetadataPropertyException: The provided database connection string is malformed: Initial Catalog=BFTPRD1;Pooling=False;Persist Security Info=false;User ID=sharepointuser;Password=******** ---> System.ArgumentException: Keyword not supported: 'initial catalog'. 
at System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) 
at System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useOdbcRules)

I also tried setting the secure store username with passing the Oracle schema in the username like sharepointuser@bftprd then I get this error in ULS

Error calling FindSpecific : Cannot connect to the LobSystem (External System). Reason: 'ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA ' (ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA ) Stack Trace: 
at Microsoft.SharePoint.BusinessData.SystemSpecific.Db.DbConnectionManager.Execute(Object[] args)

The first approach should work but I have no idea what is going wrong here. Could it just be the Oracle 11 server that is blocking this?

Improve this question
6
  • What about the environment variable?Does it points to the correct path where tnsnames.ora is placed?
    – samolpp2
    Dec 17, 2015 at 14:08
  • Which env var do you mean? Nothing changed on the client. And if you look at the first log you see that after the host not found line it says connected to database.
    – Danny
    Dec 17, 2015 at 14:13
  • Did you set up Oracle 11 on another server and change DNS? Also, I'm not familiar with Oracle, but I'd check the change logs and see if there are any hardened security rules with Oracle 11 that may be preventing db access.
    – Mike
    Dec 17, 2015 at 17:29
  • DNS is correct. I can see in the log that SharePoint is connecting to Oracle but then something goes wrong.Problem is that in SharePoint the TNS entry is put in the Host field of the BCS connection. This is not aa valid server name because it is a TNS net service name. It is like SharePoint thinks it is a SQL server connection or something in that order
    – Danny
    Dec 17, 2015 at 20:03
  • How did you set up your BCS connection? If it's not sql, then is it a we service?
    – Mike
    Dec 18, 2015 at 2:06

1 Answer 1

Reset to default
0

It turned out that the problem was in the BDC import model. After setting the log level to verbose we have seen a new error.

Error calling importProperties : System.ArgumentException: Column with Name 'loc_plaats' does not exist.    
 at Microsoft.SharePoint.BusinessData.Infrastructure.GenericAdapterObject.get_Item(String key)    
 at Microsoft.SharePoint.BusinessData.Runtime.FieldValueHelpers.GetValue(Object adapter, ITypeDescriptor root, String dotNotation, IDictionary`2 cache)    
 at Microsoft.SharePoint.BusinessData.Runtime.FieldValueDictionary.get_Item(String fieldDotNotation)    
 at Microsoft.Office.Server.UserProfiles.ProfileImportExportService.importProperties(IEntityInstance profileEntityInstance, ProfileChangeData profileChangeEntry, String[] propertyList, Boolean fFirstMatch).

After changing the query to uppercase:

SELECT LOC_PLAATS FROM MW_GEGEVENS WHERE PERS_NR = :EmployeeNumber

It started working. It seems that the selectfields need to be uppercase.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.