SQL adapter FAQs
The following are some frequently asked questions (FAQs) related to Microsoft BizTalk Adapter for SQL Server and the BizTalk Adapter Pack in general.
How can I use the SQL adapter to communicate with the SQL Server database?
You can use the SQL adapter to communicate with the SQL Server database either by developing BizTalk applications, using the WCF service model or using the WCF channel model. For more information, see Overview of BizTalk Adapter for SQL Server.
What interfaces are supported by the SQL adapter for retrieving metadata?
The SQL adapter supports two interfaces for retrieving metadata:
MetadataExchange provided by WCF. WCF provides a metadata-exchange endpoint for all WCF bindings, which enables clients to get metadata from the SQL Server database.
IMetadataRetrievalContract provided by the WCF LOB Adapter SDK, which supports the metadata browsing and searching capabilities of the adapter.
How does the SQL adapter support high availability of data?
While specifying the Create the SQL Server connection URI to connect to a SQL Server database, the SQL adapter allows you to specify the name of a failover SQL Server database to connect to if the primary SQL Server database is not available. The failover SQL Server database is specified using an optional parameter, FailoverPartner, in the connection URI.
Can I migrate BizTalk projects created using the previous version of the SQL adapter to use the WCF-based SQL adapter? How?
Yes. To know the steps for migrating BizTalk projects created using the previous version of the SQL adapter to use the WCF-based SQL adapter, see SQL Adapter Tutorials.
Does the SQL adapter provide a secure way of communicating with the SQL Server database? Are there any best practices to ensure security of data?
The SQL adapter supports Enterprise Single Sign-On (SSO) and Integrated Security for authentication on the connections that it establishes with the SQL Server database. With SSO, the credentials are encrypted and stored in the registry. The system uses these credentials to determine access instead of requiring the user to enter them where they might be seen by unauthorized actors. Integrated Security uses the credentials of the logged on user to access the SQL server. This also eliminates the need for users to enter credentials. The database administrator must configure SQL to accept users’ credentials for Integrated Security to work correctly.
The SQL adapter also does not allow you to enter the user credentials in the connection URI for the SQL Server database while working with the Add Adapter Service Reference Visual Studio Plug-in and Consume Adapter Service BizTalk Project Add-in to prevent credentials from appearing in clear text. Moreover, the password is not written to the configuration file (generated by the Add Adapter Service Reference Visual Studio Plug-in) and the binding file (generated by the Consume Adapter Service BizTalk Project Add-in).
For more information about:
Data security in the SQL adapter, see Secure your SQL applications.
Best practices to ensure data security in the SQL adapter, see Best practices to secure the SQL adapter.
Is there a GUI provided by the SQL adapter to view and perform operations on the artifacts in my underlying SQL Server database?
The Consume Adapter Service BizTalk Project Add-in and the Add Adapter Service Reference Visual Studio Plug-in provide a dialog box where you can view and perform operations on the artifacts in the underlying SQL Server database. For more information about the GUI provided by the SQL adapter, see Browse, search, and get metadata for SQL operations using the SQL adapter.
What are binding properties in the SQL adapter? Where can I find information about all the binding properties in SQL adapter?
Adapter clients can use binding properties in the SQL adapter to configure and control the adapter’s behavior. For information about all the binding properties surfaced in the SQL adapter, see Read about the BizTalk Adapter for SQL Server adapter binding properties.
What is MSDTC? Do I need to bother about it before using SQL adapter?
MSDTC stands for Microsoft Distributed Transaction Coordinator. MSDTC coordinates various transactions among multiple resource managers such as databases, file systems, and message queues. To use the SQL adapter with BizTalk Server, you must enable MSDTC. For detailed information about configuring MSDTC, see Configure MSDTC on SQL Server and adapter client.
Where can I find information about the SQL Server data types that are supported in the SQL adapter?
To know about the SQL Server data types that are supported in the SQL adapter, see Basic SQL Server Data Types.
Which approach (BizTalk Server, WCF service model or WCF channel model) can I use to perform various operations using the SQL adapter?
To know about the approach that you can use to perform various operations using the SQL adapter, see Develop your SQL applications.