SALES: 1-800-867-1380

Connect to SQL Server in a BizTalk Services Project

Updated: February 2, 2015

There are three overall steps to connect to the SQL Server database from a BizTalk Services project.

  • Create an LOB Target for SQL Server.

    ImportantImportant
    1. To create an LOB Target for SQL Server, you must be a member of the local Administrators group and have the System Administrator right on the on-premises SQL Server.

    2. Visual Studio must be opened with Administrative privileges to use BizTalk Adapter Service.

  • Use the LOB Target

  • Generate schema for the operation to be performed on the SQL Server application.

ImportantImportant
These steps assume you have a Service Bus namespace. Install Azure BizTalk Services SDK lists the requirements.

  1. In Server Explorer, expand BizTalk Adapter Service, expand the Management URL, and then expand LOB Types.

  2. Right-click SQL and select Add SQL Target. The Add a Target wizard opens:

    1. In the Welcome window, select Next.

    2. In Connection Parameters, enter the following:

      • Server: The server name or IP address of the SQL Server and optionally, the port number. If the port number is not entered, port 1433 is used. To use a different port, enter ComputerName:PortNumber.

      • Instance: The name of the SQL Server instance. If no value is entered, the Default instance is used.

      • Catalog: The name of the database.

      • Advanced: Select this button to configure additional Uri Properties and any Binding Properties:

         

        Failover Partner

        Optional. The server name or IP address of the SQL failover server. This server is used if the primary server goes down.

        InboundId

        Optional. An identifier used to make the URI unique.

        ImportantImportant
        Required when using the TypedPolling operation.

        InitialCatalog

        Required. The name of the on-premises database.

        InstanceName

        Required. The name of the SQL Server instance. If no value is entered, the Default instance is used.

        Server

        Required. The server name or IP address of the SQL Server and optionally, a port number. If the port is not entered, port 1433 is used. To use a different port, enter MyServerName:PortNumber.

        In the Binding Properties tab, refer to Working with BizTalk Adapter for SQL Server Binding Properties to configure these properties.

        The SQL Server Connection URI provides additional information on the SQL adapter.

      • Specify the credentials: Enter the credentials to authenticate to the on-premises SQL Server. Options include:

         

        Use Windows Credentials

        The logged on user credentials are used to connect to the SQL Server.

        Use the following UserName and Password

        Enter a User name and Password that can connect to the SQL Server.

      Select Next.

    3. In Operations, expand the node, select the database operation, and select the right arrow:

      Configure SQL Server URI

      Browsing, Searching, and Retrieving Metadata for SQL Server Operations provides additional details on selecting an operation.

      To see the operation’s generated WSDL, select the operation, and select Properties.

      Select Next.

    4. In Runtime Security, enter the security type. This security type determines how the client message is authenticated with the LOB Target. Options include:

       

      Fixed Username

      Select this option if you are using a username and password created locally on the LOB system.

      Fixed Windows credential

      Select this option to use a Windows domain account.

      Custom SOAP Header

      Select this option if you create a custom SOAP header to include the username and password.

      Message Credential

      Select this option if you are including the logon credentials in the WS-Security header of the message.

      Select Next.

    5. In Deployment, choose an existing LOB Relay or create a new LOB Relay.

      TipTip
      A single LOB Relay can be used with multiple LOB Targets. There are restrictions based on the security model. As a best practice, group the same security method in one LOB Relay. For example, use the same LOB Relay to host the LOB Targets that use Message Credential or Fixed Windows security type.

      To create a new LOB Relay:

       

      Namespace

      Required. Enter your Service Bus namespace. The namespace name is available in the Azure Management Portal.

      Issuer Name

      Required. A valid Service Bus Issuer Name is required.

      Issuer Secret

      Required. A valid Service Bus Issuer Secret key is required.

      Relay Path

      Required. Enter the desired name of the relay path. For example, if you use chose the Fixed windows credential option for Runtime Security; you can enter something like WindowsAuthRelay.

      Target Sub-path

      Required. Enter a sub-path to make this target unique. For example, you can enter GetOrder.

      Target runtime URL

      This is automatically populated with the namespace name, relay path and target sub-path entered. If using the examples above, it is populated with something like:

      https://MyNamespace.servicebus.windows.net/WindowsAuthRelay/GetOrder

      Select Next.

    6. Summary shows your configured values. Select Create.

    7. When complete, select Finish. The following activities occur in the background:

      • The LOB Target is created in Server Explorer. It can be disabled, started, and deleted. Its configuration can also be exported.

      • The LOB Target is created as an application in IIS. This application uses the Runtime for this specific LOB Target. Runtime Components: BizTalk Adapter Service describes the IIS components.

  1. Right-click anywhere on the BizTalk Service project design area, select Properties, and update the BizTalk Service URL property to include your BizTalk Services name. This is the name that you entered in Azure Management Portal when creating the BizTalk Services.

  2. Set the security property for the relay endpoint:

    1. Right-click the relay endpoint in Server Explorer and select Properties.

    2. In the Properties grid, select the ellipsis (…) next to the Runtime Security property.

    3. In the Edit Security dialog box, select the security method you want to use, and enter their values.

    4. Select OK.

  3. Drag and drop the LOB Target onto the design area. Note the Entity Name property of the LOB Target. The default value of the property is Relay-Path_target-sub-path.

  4. Open the .config file for the LOB target, which typically has the RelayPath_target-sub-path.config naming convention. Enter the Service Bus issuer name and issuer secret, as shown:

    <tokenProvider>
      <sharedSecret issuerName="owner" issuerSecret="issuer_secret" />
    </tokenProvider>
    

    Save changes to the config file.

Once a LOB Target is configured and added to the design area, add a XML One-Way Bridge or a XML Request-Reply Bridge to be the source. Use Connector in the Toolbox to connect the bridge to the LOB Target, similar to the following:

Bridge Connection

Create an XML Bridge provides more specific information on the XML bridge and any additional properties that must be configured.

TipTip
The bridge uses the Relative Address property of the LOB Target to send messages to the on-premises LOB system.

  1. In the BizTalk Service project, in the Server Explorer, right click the LOB Target you created, and then select Add schemas to <project_name>. The Schema Generation dialog opens.

  2. Enter a file name prefix. This value is prefixed with all the schema files that are generated. You can also enter the folder name under which the schema is added in the Visual Studio Solution Explorer. The default value for the folder is LOB Schemas.

  3. Select a credential type to generate the schema, provided appropriate values for authentication, and then select OK.

    The schemas are added to the project under the folder name.

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2015 Microsoft