How the WCF-SQL Adapter builds the ADO.NET connection string

The WCF-SQL Adapter that is part of the BizTalk Server Adapter Pack 2010 takes advantage of ADO.NET in the .NET Framework to connect to SQL Server, specifically the classes in the System.Data.SqlClient namespace. Internally the adapter uses a combination of the following URI parts and WCF-SQL binding properties to make up the final ADO.NET connection string to SQL Server. This can be useful if you are already familiar with ADO.NET connection strings and how its parts change the connection behaviour so you can configure the adapter properly.

See here for the SQL Server Connection URI explanation. From the URI the following parts are parsed as follows:

• Server name (Server_Name), port ([:Portno]) and instance name ([Database_Instance_Name]) are combined to become the connection string Data Source value
• Database name ([Database_Name]) is used for the connection string Initial Catalog value
• The partner server name ([Partner_Server_Name]) is used for the connection string Failover Partner value

And the following make up the rest of the connection string:

• If username and password are passed in on the credentials tab then the Username is used for the connection string User ID value; Password is used for the connection string Password value; and the connection string Integrated Security value is set to false. If no username is set then the connection string Integrated Security value is set to true.
• The connection string Max Pool Size value is extracted from the WCF-SQL MaxConnectionPoolSize binding property
• The connection string Encrypt value is extracted from the WCF-SQL Encrypt binding property
• The connection string Workstation ID value is extracted from the WCF-SQL WorkstationId binding property
• The connection string Enlist value is extracted from the WCF-SQL adapter UseAmbientTransaction binding property
• The connection string Connect Timeout value extracted from the WCF-SQL adapter OpenTimeout binding property.
• The connection string Asynchronous Processing value is set to true

Note that the WCF-SQL Adapter, unlike other adapters in the BizTalk Adapter Pack 2010 like the WCF-Oracle adapter, does not accept credentials in the URI, and does not expose the AcceptCredentialsInUri binding setting in the binding property form. So Username and Password cannot be set in the URI, at least  not in this version of the adapter. Credentials should be set on the Other tab of the WCF-Custom adapter or the Credentials tab of the WCF-SQL Adapter properties.