Guidelines for Resolving SQL Server Permissions Problems

Article
02/02/2021

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.

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.

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.