Re: Howto troubleshoot login failure - State 11?

I have now tested the exact same scanrio in two other environments and it does not work there either (I have separate environments for developing, staging and beta tests). Please forget anything I wrote about getting it to work, it must have been during the time I testad different approaches.

The only way I have been getting this to work is adding the computer account to the logins, and granting it the sysadmin role.

The problem is:
Logging in with a normal computer account fails. Logging in with a normal user account succeeds. Both is a member of the Windows group that is granted access to the database. All accounts and groups are in the domain.

1. What are the database access permissions assigned to your Windows group
in your SQL Server?

The standard permissions that BizTalk Server assigns;
The 'public' role, and the different BTS_* application roles added by BizTalk. The service only accesses the BizTalkMgmtDb database, and the application roles granted there are BAM_CONFIG_READER, BTS_ADMIN_USERS and BTS_HOST_USERS (If you need to know the actual grants of those, You have to ask som BizTalk guy, I don't know).

2. What is the result if you explicitly assign the fixed server role
sysadmin to your Windows group?

It makes no difference.

3. Could you please mail me (changliw_at_microsoft_dot_com) your SQL error
logs which are located in the folder %ProgramFiles%\Microsoft SQL
Server\MSSQL(.x)\MSSQL\LOG?

Sent!

Regards;
/jb

.