Get password into Oracle Adapter

Topics: Bindings File, Settings Management and SSO
Nov 12, 2014 at 10:17 AM
BTDF is superb but I've started to use the Oracle adapter for the first time (BTS 2013 project) and I've hit a problem.

An extract from my PortBindingsMaster.xml:

<PrimaryTransport>
            <!-- ifdef ${_xml_preprocess} -->
            <!--<Address>${OracleDWAddress}</Address>-->
            <!-- endif -->
            <TransportType Name="WCF-Custom" Capabilities="907" ConfigurationClsid="af081f69-38ca-4d5b-87df-f0344b12557a" />
            <TransportTypeData>
                <CustomProps>
                    <BindingType vt="8">oracleDBBinding</BindingType>
                    <BindingConfiguration vt="8">
                        <binding name="oracleDBBinding" useAmbientTransaction="false" enableBizTalkCompatibilityMode="true" />
                    </BindingConfiguration>
                    <InboundBodyPathExpression vt="8" />
                    <OutboundBodyLocation vt="8">UseBodyElement</OutboundBodyLocation>
                    <AffiliateApplicationName vt="8" />
                    <StaticAction vt="8">
                        <BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
                            <!-- ifdef ${_xml_preprocess} -->
                            <!-- <Operation Name="${OracleSelectOperationName}" Action="${OracleDWFootfallSelectAction}" /> -->
                            <!-- endif -->
                        </BtsActionMapping>
                    </StaticAction>
                    <ProxyUserName vt="8" />
                    <ProxyAddress vt="8" />
                    <!-- ifdef ${_xml_preprocess} -->
                    <!--<UserName>${OracleDWUsername}</UserName>-->
                    <!--<Password>${OracleDWPassword}</Password>-->
                    <!-- endif -->
                    <InboundBodyLocation vt="8">UseBodyElement</InboundBodyLocation>
                    <EndpointBehaviorConfiguration vt="8">
                        <behavior name="EndpointBehavior" />
                    </EndpointBehaviorConfiguration>
                    <OutboundXmlTemplate vt="8">
                        <bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="xml" />
                    </OutboundXmlTemplate>
                    <PropagateFaultMessage vt="11">-1</PropagateFaultMessage>
                    <InboundNodeEncoding vt="8">Xml</InboundNodeEncoding>
                    <Password vt="1" />
                    <IsolationLevel vt="8">Serializable</IsolationLevel>
                    <UseSSO vt="11">0</UseSSO>
                    <EnableTransaction vt="11">-1</EnableTransaction>
                </CustomProps>
            </TransportTypeData>
What appears in ProtBindings.xml:

<PrimaryTransport>
    <Address>oracledb://test_godw_lincoln/</Address>
    <TransportType Name="WCF-Custom" Capabilities="907" ConfigurationClsid="af081f69-38ca-4d5b-87df-f0344b12557a" />
    <TransportTypeData>&lt;CustomProps&gt;
        &lt;BindingType vt="8"&gt;oracleDBBinding&lt;/BindingType&gt;
        &lt;BindingConfiguration vt="8"&gt;&amp;lt;binding name="oracleDBBinding" useAmbientTransaction="false" enableBizTalkCompatibilityMode="true" /&amp;gt;&lt;/BindingConfiguration&gt;
        &lt;InboundBodyPathExpression vt="8" /&gt;
        &lt;OutboundBodyLocation vt="8"&gt;UseBodyElement&lt;/OutboundBodyLocation&gt;
        &lt;AffiliateApplicationName vt="8" /&gt;
        &lt;StaticAction vt="8"&gt;&amp;lt;BtsActionMapping xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&amp;gt;
            &amp;lt;Operation Name="Select" Action="http://Microsoft.LobServices.OracleDB/2007/03/USER_DWDEV/Table/DW_FOOTFALL/Select" /&amp;gt;
          &amp;lt;/BtsActionMapping&amp;gt;&lt;/StaticAction&gt;
        &lt;ProxyUserName vt="8" /&gt;
        &lt;ProxyAddress vt="8" /&gt;
        &lt;!-- ifdef ${_xml_preprocess} --&gt;
        &lt;!--&lt;UserName&gt;${OracleDWUsername}&lt;/UserName&gt;--&gt;
        &lt;!--&lt;Password&gt;${OracleDWPassword}&lt;/Password&gt;--&gt;
        &lt;!-- endif --&gt;
        &lt;InboundBodyLocation vt="8"&gt;UseBodyElement&lt;/InboundBodyLocation&gt;
        &lt;EndpointBehaviorConfiguration vt="8"&gt;&amp;lt;behavior name="EndpointBehavior" /&amp;gt;&lt;/EndpointBehaviorConfiguration&gt;
        &lt;OutboundXmlTemplate vt="8"&gt;&amp;lt;bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="xml" /&amp;gt;&lt;/OutboundXmlTemplate&gt;
        &lt;PropagateFaultMessage vt="11"&gt;-1&lt;/PropagateFaultMessage&gt;
        &lt;InboundNodeEncoding vt="8"&gt;Xml&lt;/InboundNodeEncoding&gt;
        &lt;Password vt="1" /&gt;
        &lt;IsolationLevel vt="8"&gt;Serializable&lt;/IsolationLevel&gt;
        &lt;UseSSO vt="11"&gt;0&lt;/UseSSO&gt;
        &lt;EnableTransaction vt="11"&gt;-1&lt;/EnableTransaction&gt;
      &lt;/CustomProps&gt;</TransportTypeData>
As you can see, the BTDF has correctly substituted the <Address> element but hasn't attempted substitution within the <StaticAction> or <UserName> or <Password> element.

Maybe someone else has managed to get this to work with the Oracle WCF adapter?

Thanks,

Rob.
Coordinator
Nov 13, 2014 at 4:09 AM
Hi Rob,

Do you have the RequireXmlPreprocessDirectives property in your .btdfproj? It's set to True by default in new projects, which eliminates all of the ifdef/endif comments. Did you get any errors in the output about replacement values not being found?

Thanks,
Tom
Nov 13, 2014 at 12:50 PM
Edited Nov 13, 2014 at 12:54 PM
Hi Tom,

Thanks for replying so soon.

I wan't aware of the RequireXmlPreprocessDirectives, previously I'd always included the ifdef comments. I do like the ability to turn this off since it makes the btdfproj easier to read / maintain, so I have now set the value of this property to false.

I have found that when I give the PortBindingsMaster.xml TransportTypeData\CustomProps elements a <Password vt="8" >${OracleDWPassword}</Password> element then the resulting bindings fail to import with, with BTSTask raising the error "Invalid or malformed XML data". However, if I change <Password vt="8" >${OracleDWPassword}</Password> to <random vt="8" >${OracleDWPassword}</random> then the bindings file is imported ok but obviously my password doesn't make its way in.

Here's the section from my PortBindingsMaster:
<UserName vt="8" >${OracleDWUsername}</UserName>
<Password vt="8" >${OracleDWPassword}</Password>
Here's the resulting content from PortBindings.xml:
&lt;UserName vt="8"&gt;USER_DWDEV&lt;/UserName&gt;
&lt;Password vt="8"&gt;dwdev&lt;/Password&gt;
Here's an extract from the output window:
Checking syntax of XML file 'c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\PortBindingsMaster.xml'...
  Syntax of XML file 'c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\PortBindingsMaster.xml' appears to be valid.
  Clearing file attributes for 'PortBindings.xml'.
  "C:\Program Files (x86)\Deployment Framework for BizTalk 5.5\Framework\DeployTools\xmlpreprocess.exe" /v /c /noDirectives /i:"c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\PortBindingsMaster.xml" /o:"c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\PortBindings.xml" /d:CurDir="c:\Workspaces\BTS.GO.FactFeeds" /s:"c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\EnvironmentSettings\Exported_LocalSettings.xml"
  XmlPreprocess v2.0.18.0
  Copyright (c) 2004-2013 Loren M Halvorson
  XML File Preprocessor
  
  Settings XML file: "c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\EnvironmentSettings\Exported_LocalSettings.xml"
  Preprocessing "c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\PortBindingsMaster.xml" to "c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\PortBindings.xml"...
  PortBindings.xml : Set the value 'GO.FactFeeds' of 5 node(s) matching XPath '//ApplicationName'.
  "C:\Program Files (x86)\Deployment Framework for BizTalk 5.5\Framework\DeployTools\ElementTunnel.exe" /i:"c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\PortBindings.xml" /o:"c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\PortBindings.xml" /x:"C:\Program Files (x86)\Deployment Framework for BizTalk 5.5\Framework\DeployTools\adapterXPaths.txt" /encode+
  Nested XML Encoder/Decoder v5.5.100.0
  Copyright (C) 2005-2014 Scott Colestock, Tim Rayburn, Thomas F. Abraham
  
  XML encodes/decodes content of element(s) by XPath. Applies escaping rules such
  as &gt; for '<'. Namespace decls, PIs, etc. of nested XML are not preserved.
  
  XPaths                        : 18
  Transformed Nodes             : 23
  Empty Nodes (skipped)         : 11
  No Transform Req'd (skipped)  : 0
  Total Matched Nodes           : 34
  
  Complete - output file has been saved.
  Checking syntax of XML file 'c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\PortBindings.xml'...
  Syntax of XML file 'c:\Workspaces\BTS.GO.FactFeeds\BTS.GO.FactFeeds.Deployment\PortBindings.xml' appears to be valid.
Coordinator
Nov 14, 2014 at 5:39 AM
When you have a bindings file issue like this, the only reasonable way to figure it out is an A-B comparison. Get everything manually configured and working in BizTalk Admin, then export the bindings. Run the BTDF preprocessing to produce PortBindings.xml. Then compare the two files in the affected area using a tool like WinMerge. In this case, they will not be identical since the bindings export likely leaves out the password. Hopefully that narrows it down. Even an extra line break or space can sometimes make or break the import.

Thanks,
Tom
Nov 14, 2014 at 5:05 PM
Edited Nov 14, 2014 at 5:10 PM
I don't know if this is related or can help, but...

In addition to my BizTalk work I have web app development responsibilities. I have an ASP website that accesses an Oracle database. The password is mixed case. We upgraded the version of Oracle and somehow the interaction of the new version of Oracle and the Oracle.DataAccess libraries overrode the casing of the password that was passed in versus what I specified in the web.config file. I was able to get around this by quoting the password value in the config file (using the HTML entity) and Oracle respected that.

This failed:
<add name="xxx" connectionString="Data Source=yyy; User Id=zzz; Password=MyPassword;"/>
This worked:
<add name="xxx" connectionString="Data Source=yyy; User Id=zzz; Password=&quot;MyPassword&quot;;"/>
for reference: https://community.oracle.com/message/3335876
Coordinator
Nov 14, 2014 at 5:08 PM
When you uncomment Password, aren't you ending up with TWO Password elements? In your example, the Password element appears twice. Once above InboundBodyLocation, and again above IsolationLevel.
Marked as answer by BizTalkers on 11/14/2014 at 12:23 PM
Nov 14, 2014 at 7:24 PM
Doh - how dumb am I! Thanks a million Tom you've made my weekend :) I'd totally missed the one above IsolationLevel
Coordinator
Nov 16, 2014 at 4:11 AM
No problem, second set of eyes usually helps!