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?