Guidelines for Resolving SQL Server Permissions Problems
Article
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.
General Guidelines
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.
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.
Known Issues
The SQL Server jobs that are installed with BizTalk Server fail to execute
Problem
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
Date: 6/29/2008
Time: 4:45:01 PM
User: N/A
Computer: SQLServer
Description:
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
Date: 6/29/2008
Time: 4:45:01 PM
User: N/A
Computer: SQLServer
Description:
18456: Login failed for user 'NT AUTHORITY\SYSTEM'.
Cause
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.
Resolution
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.
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.