Create the SQL Server connection URI

The Microsoft BizTalk Adapter for SQL Server connection URI contains properties that the adapter uses to establish a connection to the SQL Server database. This topic provides information about the SQL Server connection URI, and provides links to other topics that explain how to specify a URI in different programming scenarios.

The Connection URI for the SQL Adapter

A typical endpoint address URI in WCF is represented as: scheme://hostinfoparams?query_string, where:

• scheme is the scheme name.

• hostinfoparams is information required to establish the connection to the host; for example, a server name.

• query_string is an optional name-value collection of parameters delimited by a question mark (?).

  The SQL adapter connection URI adheres to this basic format and is implemented as follows:

  
mssql://[Server_Name[:Portno]]/[Database_Instance_Name]/[Database_Name]?FailoverPartner=[Partner_Server_Name]&InboundId=[Inbound_ID]  

where, mssql is the scheme for the SQL Server connection URI.

The following table explains the properties contained in the connection URI.

SQL Server Credentials and the Connection URI

The SQL adapter does not support specifying credentials in the connection URI. For more information about specifying credentials in your applications that use the SQL adapter, see Secure your SQL applications.

Using Special Characters in the Connection URI

The SQL adapter does not support specifying a connection URI that has special characters for any of the parameter values. If the connection parameter values contain special characters, make sure you do one of the following:

• If you are specifying the URI in Visual Studio using Add Adapter Service Reference Plug-in or Consume Adapter Service Add-in, you must specify them as-is in the URI Properties tab, that is, without using any escape characters. If you specify the URI directly in the Configure a URI field and the connection parameters contain special characters, you must specify the connection parameters using proper escape characters.

  For example, if the connection URI has a parameter with name sql server, you must specify it as sql%20server.

• If you are specifying the URI while creating a send or receive port in BizTalk Server Administration console, and the connection parameters contain special characters, you must specify the connection parameters using proper escape characters.

Using the Connection URI to Connect to the SQL Server Database

The following is a sample connection URI for the SQL adapter.

mssql://sql_server/sql_server_instance//  

In the preceding example, “sql_server” is the name of the computer on which SQL Server is installed whereas “sql_server_instance” is the name of the database instance to connect to. Because no database name is specified, the adapter will connect to the default database.

The following is an example of a connection URI where the SQL Server database is installed on the same computer as the SQL adapter. In this example, the adapter connects to the database “my_database” for the “sql_server_instance” database instance on the local computer.

mssql://localhost/sql_server_instance/my_database/  

In this example, the adapter connects to the default database for the default instance running on the local computer.

mssql://localhost///  

For information about how to specify a connection to the SQL Server database when you:

• Use the Consume Adapter Service BizTalk Project Add-in or the Add Adapter Service Reference Visual Studio Plug-in, see Connect to SQL Server in Visual Studio using the SQL adapter.

• Configure a send port or receive port (location) in a BizTalk Server solution, see Manually configure a physical port binding to the SQL adapter.

• Use the WCF channel model in a programming solution, see Create a channel using the SQL adapter.

• Use the WCF service model in a programming solution, see Configure a Client Binding for the SQL Adapter.

See Also

Develop your SQL applications