Connect to SQL Server using Windows Authentication with the SQL adapter
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.
Tip
If SQL Server Management Studio is not installed on your SQL Server, you can Download SQL Server Management Studio (SSMS), and install it.
Add the user in SQL Server
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.
In Object Explorer, expand the SQL Server, expand Security, right-click Logins, and then select New Login.
For the Login name, enter the Windows user name in the
domain\usernameformat.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.
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
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.
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.