Guidelines for Resolving SQL Server Permissions Problems

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

Problem

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

Date: 6/29/2006

Time: 4:45:01 PM

User: N/A

Computer: SQLServer

Description:

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

Date: 6/29/2006

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 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:

Community Additions

ADD
Show:
© 2016 Microsoft