Connect to SQL Server using Windows Authentication with the SQL adapter - BizTalk Server (2024)

The Microsoft BizTalk Adapter for SQL Server enables adapter clients to use Windows Authentication to establish a connection with SQL Server. To use Windows Authentication, adapter clients must enter an empty user name and password.

To connect to SQL Server using Windows Authentication within Visual Studio, see Connect to SQL Server in Visual Studio using the Consume Adapter Service Add-in.

To enable adapter clients to use Windows Authentication to connect to SQL Server, enable Windows Authentication for the user on the computer running SQL Server.

Add the user in SQL Server

1. Open SQL Server Management Studio. In Connect to Server, select Database Engine, enter your SQL Server name, and enter administrator credentials to connect to the server.

   Select Connect.

2. In Object Explorer, expand the SQL Server, expand Security, right-click Logins, and then select New Login.

3. For the Login name, enter the Windows user name in the domain\username format.

   See Also

   Authentication in SQL Server - ADO.NET Provider for SQL Server

   Note

   • When using this adapter with BizTalk, then the login name you enter, is the identity of the host instance account.

   • When using this adapter in .NET code, then the login name you enter, is the identity for that process.

4. Select User Mapping (left pane). Select a database to associate with the user. A typical BizTalk user should be associated with the following databases:

• BizTalkDTADb
• BizTalkMgmtDb
• BizTalkMsgBoxDb
• BTAHL7
• SSODB

5. In the Database role membership for box, select db_owner for all the BizTalk databases.

   Note

   Server and Database Roles in SQL Server provides good info on the roles.

6. Select OK to save your changes.

   After you have added the user, the user can connect and authenticate to SQL Server using the SQL adapter, logging in with a blank username and password.

See Also

Create a connection to SQL Server