Guidelines for Resolving SQL Server Permissions Problems
BizTalk Server 2006 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 2006.
Use domain users and groups for a multicomputer installation of BizTalk Server
You must use domain user groups and accounts when configuring BizTalk Server 2006 to run in a multicomputer scenario, for example, where BizTalk Server 2006 and SQL Server are installed on separate computers. Do not attempt to configure or run BizTalk Server 2006 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 2006 to fail in other scenarios and is not a supported configuration.
For more information about installing and configuring BizTalk Server 2006 in a multicomputer configuration download the BizTalk Server 2006 Installation Guide - Multiserver.doc Word document from BizTalk Server 2006 Installation and Upgrade Guides.
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 2006.
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 jobs that are installed with BizTalk Server 2006 fail to execute
The SQL jobs that are installed with BizTalk Server 2006 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: 2006-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 2006 databases and the Master database.
The following Microsoft Knowledge Base articles describe other known issues with BizTalk Server 2006 related to SQL Server permissions:
You receive a "TDDS failed to read from source database." error message in the application log of a computer that is running BizTalk Server
SQL Server log on fails when you run a BizTalk Server Orchestration that contains rules
In BizTalk Server 2006, you may receive an error message that the BizTalk Host name is not valid when you try to configure the BizTalk Server runtime