Guidelines for Resolving SQL Server Permissions Problems
BizTalk Server makes extensive use of Microsoft SQL Server databases for run-time operations and as such, it is important that the SQL Server permissions are set correctly. This topic provides some general guidelines for minimizing SQL Server permissions problems and steps that you can follow to troubleshoot SQL Server permissions problems that affect BizTalk Server.
Use domain users and groups for a multicomputer installation of BizTalk Server
You must use domain user groups and accounts when configuring BizTalk Server to run in a multicomputer scenario, for example, where BizTalk Server and SQL Server are installed on separate computers. Do not attempt to configure or run BizTalk Server in a pass-through authentication scenario whereby matching pairs of usernames and passwords are created on each computer to avoid using domain groups and accounts. While such a pass-through scenario may appear to function correctly in some scenarios, this will cause BizTalk Server to fail in other scenarios and is not a supported configuration.
For more information about installing and configuring BizTalk Server in a multicomputer configuration, download the Installation Guide at Installation Guides Related to BizTalk Server 2013.
Ensure that the appropriate Windows users and groups are defined in the appropriate SQL Server roles
Verify correct SQL Server role membership as listed in the table in the topic Windows Groups and User Accounts in BizTalk Server.
User SQL Server Profiler to diagnose permissions problems
Set up a SQL Server Profiler trace to monitor the Audit Login Failed Event to gather detailed information about permissions failures. For information about how to use SQL Server Profiler, see the SQL Server documentation.
The SQL Server jobs that are installed with BizTalk Server fail and errors similar to the following are generated in the SQL Server Application log:
Event Type: Warning
Event Source: SQLSERVERAGENT
Event Category: Job Engine
Event ID: 208
Time: 4:45:01 PM
SQL Server Scheduled Job 'Backup BizTalk Server'
(0x4AC7C44A48541443927A56C5C6699ECF) - Status: Failed - Invoked on: 2008-6-29 13:45:01 - Message: The job failed. The Job was invoked by Schedule 305 (MarkAndBackupLogSched). The last step to run was step 1 (BackupFull).
- and -
Event Type: Information
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 17055
Time: 4:45:01 PM
18456: Login failed for user 'NT AUTHORITY\SYSTEM'.
This error can occur if the BUILTIN\Administrators login has been removed from SQL Server. If the BUILTIN\Administrators login is deleted, sqlmaint.exe will be unable to logon to SQL Server which will prevent SQL jobs from running.
To resolve this issue, re-create the BUILTIN\Administrators Login and add it to the db_owner role for the BizTalk Server databases and the Master database.