SQL Server - Unable to connect if member of many groups

Asked By JanAhlbec

06-Mar-08 10:00 AM

Hi,

I have the following setup:

1 Windows 2000 Server as Domain Controller (sp3)
2 Windows 2003 Server x64 (sp2) with 2005 x64 SQL Server Enterprise (sp2)
100+ Windows 2000 and XP clients

I have a setup where Acitive Domain users are member of many groups. Clients
can connect to the SQL Server without any problems, as long as the user is
not member of more than AD 268 groups!! If user is member of more than 268
groups, the user cannot connect to the SQL Server
([DBNETLIB][ConnectionRead(recv()).] General network error.....). If member
of more than 269 groups I get the "Cannot generate SSPI context" error.

If I enable trusted account delegation for the account that runs the sql
server service on both sql servers, it reduces the number of groups from 268
to 119 groups!

I would expect that this limit would be the same as the Access Token
Limitation which is 1024, see:
http://www.microsoft.com/downloads/details.aspx?FamilyID=22DD9251-0781-42E6-9346-89D577A3E74A&displaylang=en

Why this limit? Any explanation to this issue? Is it possible to raise the
limit? Fixes?

Thanks
Jan Ahlbeck

06-Mar-08 03:34 PM

Do you also receive error 17832. I just posted this early in the week:
http://www.microsoft.com/technet/support/ee/transform.aspx?ProdName=SQL%20Server&ProdVer=10.0&EvtID=17832&EvtSrc=MSSQLServer&LCID=1033
(I know the links in that topic aren't formatting correctly. Working on
that.)
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.

07-Mar-08 07:48 AM

Hi Rick,

Thanks for your reply.  No I do not see error 17832, only "Cannot generate
SSPI context".

I have tried to increase the MaxTokenSize (to ffff) on all servers and a
client, and it raises the limit from 119 groups to at least 512 groups which
is enough for my setup. I will try to find the optimal value.

Can you tell anything about what type of "operations" that will be affected
(in performance) by this increased value? Is it a serious drop in
performance, or just a "minor" drop?

Thanks
Jan Ahlbeck, MCP

07-Mar-08 11:44 AM

Glad that helped.
I think the only concern about performance for the largest possible value of
the MaxTokenSize is the consumption of Windows resources. Allocating more
space for every connection could eat up memory in an environment with lots
of connections. Since you are on a 64 bit server, I'm guessing you have lots
of memory, so it probably isn't a bit concern for you. But if you have
memory constraints, there's no use in wasting it on an oversized
MaxTokenSize.
http://technet2.microsoft.com/windowsserver2008/en/library/3d0fb26b-339e-4415-a0b1-c9f4bd9058501033.mspx?mfr=true
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.

07-Mar-08 02:06 PM

OK,

We have at least 32Gb memory on sql server and 200-300 client connections,
so it should be OK.

Thanks for you fast and very helpfull response :-)
Jan Ahlbeck