Security with the SQL adapter and BizTalk Server

Article
02/02/2021

When you configure a send port or a receive port (location) by using the BizTalk Server Administration console or use the Consume Adapter Service BizTalk Project Add-in to retrieve message schemas for a BizTalk solution, you must provide credentials for the SQL Server database. It is important to supply these credentials in a secure way to help prevent them from being revealed to potentially malicious actors. This topic discusses how to most securely supply credentials for the Microsoft BizTalk Adapter for SQL Server for BizTalk Server solutions.

A more general discussion of security in the context of BizTalk solutions is an expansive topic and is beyond the scope of this documentation. For information about how you can make your BizTalk solutions more secure, see Secure and protect your BizTalk messages.

How Do I Protect Credentials When I Use the Consume Adapter Service BizTalk Project Add-in?

When you use the Consume Adapter Service Add-in to retrieve message schemas for a BizTalk solution, you must supply the user name and password from the Security tab on the Configure Adapter dialog box. The Consume Adapter Service Add-in will not allow you to set credentials in the Configure a URI field. This improves security by preventing credentials from appearing in clear text. For more information about how to retrieve message schemas by using the Consume Adapter Service Add-in, including how to enter a user name and password for the SQL Server database, see Get metadata for SQL Server operations in Visual Studio using the SQL adapter.

How Do I Protect Credentials When I Configure a Send Port or a Receive Location?

BizTalk solutions use the Microsoft BizTalk WCF-Custom adapter to consume WCF services. The SQL adapter is a WCF custom binding that enables clients to consume the SQL Server database as if it were a WCF service. BizTalk solutions consume the SQL adapter through send ports and receive locations that are configured to use the WCF-Custom adapter. The WCF-Custom adapter is, in turn, configured to use the SQL adapter as its transport. For more information about how to configure send ports and receive ports (receive locations), including how to configure the WCF-Custom adapter, see Manually configure a physical port binding to the SQL adapter.

You configure the SQL Server database credentials from the Credentials tab of the WCF-Custom Transport Properties dialog box for send ports or from the Other tab of the WCF-Custom Transport Properties dialog box for receive locations. Because the WCF-Custom adapter supports Enterprise Single Sign-On (SSO), you can also choose to provide either a user name and password or an SSO affiliate application on either of these tabs. The following topics discuss both options.

User Name Password Credentials

You should only supply a user name and password from the Credentials tab (for send ports) or the Other tab (for receive locations) in the WCF-Custom Transport Properties dialog box. This ensures the following:

Your credentials will not be displayed in the Address (URI) field of the dialog box. This prevents those who have access to your screen (or who have permissions that enable them to view the send port or receive location properties) from seeing your credentials.
Your password will not be written to the binding file if you export the send port or receive port binding. This prevents anyone with access to the file from viewing your password.

Enterprise Single Sign-On and SSO Affiliate Applications

You can configure the WCF-Custom adapter so that it uses Enterprise Single Sign-on (SSO) to get the credentials for the SQL Server database. SSO uses a database and a master secret to encrypt and store user credentials. It also provides services to map Microsoft Windows accounts to secondary credentials that are used to access a back-end system. By using SSO, you can map a Windows account to a user name and password on the SQL Server database.

SSO uses affiliate applications and SSO mappings to map credentials to the back-end system. An affiliate application is a logical entity in SSO that refers to a system or an application that requires secondary credentials. An SSO mapping is associated with an affiliate application. It maps a Windows account to the secondary credentials used by that account to access the affiliate system or application. An SSO mapping can be associated with a Windows user account or with a group.

To use SSO with the SQL adapter, you must do the following.

Create an affiliate application in SSO to hold the user name password credentials for the SQL Server database. This step is often performed by someone with special types of SSO administrative privileges.
Create a user or group mapping for the affiliate application that maps your Windows account to the user name and password that are used to establish a connection with the SQL Server database. Depending on your installation, a user might be able to perform this step, or it might require someone with special types of SSO administrative privileges.

Note

When configured for SSO, the WCF-Custom adapter uses services provided by SSO to get the SQL Server user name and password from the SSO database. It provides these (unencrypted) to the SQL adapter, so that the adapter can open a connection to the SQL Server database. SSO provides no encryption or protection across the connection between the SQL adapter and the SQL Server database.

For information about how to use SSO, including information about how to create affiliate applications and SSO mappings, see Using SSO. For more general information about SSO, see Implementing Enterprise Single Sign-On.

The AcceptCredentialsInUri Binding Property

The SQL adapter does not support AcceptCredentialsInUri binding property. Credentials are never permitted in the connection URI.