This project has moved and is read-only. For the latest updates, please go here.

Old SQL Adapter and WCF-SQL with BTDF

Topics: Bindings File, Settings Management and SSO
Jul 21, 2010 at 1:23 AM
Edited Jul 21, 2010 at 2:24 AM

We have an app that uses a lot of the built-in SQL Adapter.  We are not starting a move to add new SQL functionality using the WCF-SQL LOB Adapter.

Any advice in advance for working with that adapter and BTDF?

In phase 1 of our project, we had made just the SQL server name a parm.  Then yesterday, we decided to create 3 SQL parms in our SettingsFileGenerator.xml file:

1) MyDatabaseSQLServer
2) MyDatabaseDatabase
3) MyDatabaseConnectionString - to allow for integrated or non-integrated security

We have some VM's with their own Active Directory, and some systems on the client's normal AD, all pointing to the same SQL server box, so I have to be able to switch each of the above variables.  When using cleint's AD, we want to use integrated security, but when on our own AD we have to use a SQL user/pass. 

The first two exist to build the URI Address: 

        <!-- ifdef ${_xml_preprocess} -->
        <!--     <Address>SQL://${MyDatabaseSQLServer}/${MyDatabaseDatabase}/</Address> -->
        <!-- else -->
        <Address>SQL://(local)/MyDatabase/</Address>
        <!-- endif -->

I did the ElementTunnel this morning, and substituted as follows:

          <TransportTypeData>
          <CustomProps>
            <AdapterConfig vt="8">
              <Send>
                <connectionString>${MyDatabaseConnectionString}</connectionString>
                <outputRootElementName>MyRootElement</outputRootElementName>
                <documentTargetNamespace>http://MyNameSpace</documentTargetNamespace>
                <uri>SQL://($MyDatabaseSQLServer)/${MyDatabaseDatabase}/</uri>
              </Send>
            </AdapterConfig>
          </CustomProps>
        </TransportTypeData>

Got that working. Is that the way most people do it for the ultimate flexibility?  My last few projects have done so much with SQL as we are doing here.

Now this afternoon, I added a new data functionality using WCF-SQL.  And I'm stuck.

Maybe this is not even a BTDF question, but anyway... In the WCF-SQL adapter, even in BTAdmin Console,
how do you change to point to a different database, and or change from integrated security to using a password?

I tried jumping straight to the binding file, and realized I didn't know what parms to change.  At first, I was afraid ElementTunnel didn't expact the WCF properties, but now I'm not even sure it needs to or not.

[We are BT2006/R2].

Thanks,
Neal Walters

P.S. 

More brainstorming on this...

    I'm thinking about writing a WinForm or something where I can paste in part of the binding files, and have it "adjust".

When I run ElementTunnel, it's great, it expands the "unencoded" bindings.  But I still need the unencoded bindings for the "else" statement in the IF-DEF, correct?

So if we have an application with 10 SQL send ports, it is extremely tedious and error-prone to copy/paste together the correct binding.

The WinForm might be an interim solution to help reduce the human errors.  The ultimate solution would be to have ElementTunnel perhaps insert the IF-DEFs (that's one reason I was looking at its code today). But each person will have different substitutions, and ElementTunnel is not psychic.  However, if there were best practices for each adapter, maybe it could work.  For example, if everybody agreed that there were a minimum of three parms on SQL adapter, then perhaps it could be standardized. People could still customize other items like the retries, but those are relatively easy. 

Or another idea would be a BindingFileAuditor - that would check that things like the targetnamespace and root element are still in sync after manual editing.  I've been bit numerous times by taking copy/paste shortcuts, and then copying too much of the wrong stuff.

 

 

 

 

 

 

 

Jul 23, 2010 at 2:19 AM

I'm getting some answers to the WCF-SQL here: http://social.msdn.microsoft.com/Forums/en/biztalkr2adapters/thread/90cd0614-81c4-4c00-b10d-4d9260a763a7

But question about regular SQL adapter.  I use integrated security on dev and prod, but ned to use a SQL user/pass on "shared dev" and "QA".

I made my entire ConnectionString a parm like this:

                <connectionString>${MyDatabaseConnectionString}</connectionString>

and set the value in thespreadsheet like this:

                <connectionString>${EagleConnectConnectionString}</connectionString>

Provider=SQLOLEDB.1;Integrated Security=False;Persist Security Info=True;User Id=MyUser; Password=MyPass;Initial Catalog=MyDBNAME;Data Source=MyServer,MyPort 

But after a deploy on "shared dev", the radio button in the SendPort "configure" shows "use Windows Integrated Security". It's easy to switch, but how do I make it "stick" in the binding file?

Thanks,

Neal

 

 

 

Jul 23, 2010 at 5:06 AM

Maybe leave out Integrated Security=False altogether?  If you fully configure it the way you want in BizTalk Admin and export the binding file, does it come out with a different connection string?

Tom

Jul 23, 2010 at 5:22 AM

On your WCF-SQL adapter question, you'll want to use the Add Generated Items/Consume Adapter Service tool.  You'll do all the configuration there for what you want to execute in SQL Server, and it will generate schemas and a binding file fragment.

http://msdn.microsoft.com/en-US/library/dd788445(v=BTS.10).aspx

http://msdn.microsoft.com/en-US/library/dd788504(v=BTS.10).aspx

 

Jul 23, 2010 at 4:53 PM
tfabraham wrote:

Maybe leave out Integrated Security=False altogether?  If you fully configure it the way you want in BizTalk Admin and export the binding file, does it come out with a different connection string?

Tom

Yep, that did it, here is the compare (which make it easy in normal editor without wrapping turned on) to compare the two lines, one above the other:

First line below is export bindings from target system after correction, second line is my binding (that I was using yesterday before the above suggestion):
        <TransportTypeData>&lt;CustomProps&gt;&lt;AdapterConfig vt="8"&gt;&amp;lt;Send xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&amp;gt;&amp;lt;connectionString&amp;gt;Provider=SQLOLEDB.1;Password=***************;Persist Security Info=True;                          User ID=MyUser;Initial Catalog=MyDatabase.SharedDev;Data Source=MyServer,12345  &amp;lt;/connectionString&amp;gt;&amp;lt;outputRootElementName&amp;gt;SQLInsertCommonErrorResp&amp;lt;/outputRootElementName&amp;gt;&amp;lt;documentTargetNamespace&amp;gt;http://ABC.CommonBizTalk.Artifacts/SQLInsertCommonError&amp;lt;/documentTargetNamespace&amp;gt;&amp;lt;uri&amp;gt;SQL://MyServer,12345/MyDatabase.SharedDev/&amp;lt;/uri&amp;gt;&amp;lt;/Send&amp;gt;&lt;/AdapterConfig&gt;&lt;/CustomProps&gt;</TransportTypeData>
        <TransportTypeData>&lt;CustomProps&gt;&lt;AdapterConfig vt="8"&gt;&amp;lt;Send                                                                                                   &amp;gt;&amp;lt;connectionString&amp;gt;Provider=SQLOLEDB.1;Password=MyPassword;     Persist Security Info=True;Integrated Security=False;User Id=MyUser;Initial Catalog=MyDatabase.SharedDev;Data Source=(MyServer,12345)&amp;lt;/connectionString&amp;gt;&amp;lt;outputRootElementName&amp;gt;SQLInsertCommonErrorResp&amp;lt;/outputRootElementName&amp;gt;&amp;lt;documentTargetNamespace&amp;gt;http://ABC.CommonBizTalk.Artifacts/SQLInsertCommonError&amp;lt;/documentTargetNamespace&amp;gt;&amp;lt;uri&amp;gt;SQL://MyServer,12345/MyDatabase.SharedDev/&amp;lt;/uri&amp;gt;&amp;lt;/Send&amp;gt;&lt;/AdapterConfig&gt;&lt;/CustomProps&gt;</TransportTypeData>

 

 

Jul 23, 2010 at 4:56 PM
tfabraham wrote:

On your WCF-SQL adapter question, you'll want to use the Add Generated Items/Consume Adapter Service tool.  You'll do all the configuration there for what you want to execute in SQL Server, and it will generate schemas and a binding file fragment.

http://msdn.microsoft.com/en-US/library/dd788445(v=BTS.10).aspx

http://msdn.microsoft.com/en-US/library/dd788504(v=BTS.10).aspx

 

Yea, got it working on dev.  But there is no normal connection string. Instead you have the mssql: format of URI, and the credentials tab.  Even using BTAdmin Console, still haven't got the right combination to work yet with my default host instance and port.  More discussion on that here: http://social.msdn.microsoft.com/Forums/en/biztalkr2adapters/thread/90cd0614-81c4-4c00-b10d-4d9260a763a7.

 

 

Jul 23, 2010 at 5:16 PM

I use WCF-SQL receive polling quite often.  I just put the entire <Address/> value into the settings spreadsheet, same as for FILE adapter, etc.  It looks like this: mssql://ServerName//DatabaseName?InboundId=SomeNameForTheOperation.  Notice the double slash (//) in the middle.  If you have a SQL instance on the server, its name goes inside the slashes.

Tom

Aug 6, 2010 at 11:30 PM
Edited Aug 10, 2010 at 7:09 PM

This is a sample of the syntax (from the Microsoft forum above, and for testing), this example is for default instance with a non-standard port number: 

mssql://myServer/,12345/DatabaseName

This becomes the value of ${MyDatabaseWCFSQL} below.  I didn't put any operation name (but in the orchestration, made the operation name on the BizTalk logical port match that of one of the operations listed in the "Action:" section (as generated by the WCFCustomBinding.xml... file).

Then my other issue was that on "SharedDev" environment (because it was a virtual machine with its own active directory domain), we had to use a SQL user/pass instead of integrated security (where as on most other environments we wanted to use integrated).

My quick fix for this was to create a variable called MyDatabaseWCFSqlUserPassXML as follows:

      <PrimaryTransport>
        <Address>${MyDatabaseWCFSQL}</Address>
        <TransportType Name="WCF-Custom" Capabilities="907" ConfigurationClsid="af081f69-38ca-4d5b-87df-f0344b12557a" />
        <TransportTypeData>
          <CustomProps>
            <PropagateFaultMessage vt="11">-1</PropagateFaultMessage>
            <OutboundBodyLocation vt="8">UseBodyElement</OutboundBodyLocation>
            <StaticAction vt="8">
              <BtsActionMapping>
                <Operation Name="BizTalkInsertComonError" Action="TypedProcedure/dbo/BizTalkInsertComonError" />
              </BtsActionMapping>
            </StaticAction>
            <BindingConfiguration vt="8">
              <binding name="SqlAdapterBinding" closeTimeout="00:01:00" openTimeout="00:01:00" receiveTimeout="00:10:00" sendTimeout="00:01:00" maxConnectionPoolSize="100" encrypt="false" workstationId="" useAmbientTransaction="true" batchSize="20" polledDataAvailableStatement="" pollingStatement="" pollingIntervalInSeconds="30" pollWhileDataFound="false" notificationStatement="" notifyOnListenerStart="true" enableBizTalkCompatibilityMode="true" chunkSize="4194304" inboundOperationType="Polling" useDatabaseNameInXsdNamespace="false" allowIdentityInsert="false" enablePerformanceCounters="false" xmlStoredProcedureRootNodeName="" xmlStoredProcedureRootNodeNamespace="" />
            </BindingConfiguration>
            <InboundNodeEncoding vt="8">Xml</InboundNodeEncoding>
            <UseSSO vt="11">0</UseSSO>
            ${MyDatabaseWCFSQLUserPassXML}
            <BindingType vt="8">sqlBinding</BindingType>
            <InboundBodyLocation vt="8">UseBodyElement</InboundBodyLocation>
            <OutboundXmlTemplate vt="8">&lt;bts-msg-body xmlns="http://www.microsoft.com/schemas/bts2007" encoding="xml"/&gt;</OutboundXmlTemplate>
            <Identity vt="8" />
          </CustomProps>
        </TransportTypeData>

 

Then in the spreadsheet, we specified the value of the above variable to:

<Password vt="8">mypass</Password> <UserName vt="8">myuser</UserName>

Fortunately, the order of these xml elements did not seem to be critical.  [By the way, when you export, it shows the password as vt="1", so I changed it back to 8].
So for the machines that don't need user/pass, we just set this variable to an empty-string (yet to be tested).

Neal