Cannot get AD groups to work in SQL 2k5

I'm trying to get Active Directory groups to work in SQL Server 2k5 so my
users can log in with windows authentication. So I've set up the group in
AD, added the domain users to it, added the group to MS SQL security users,
and assigned a default database to the group. For some odd reason my users
are not able to log in though; I can quickly check this by trying to set up
a
ODBC connection System DSN.
The odd part about this is that if I add a domain user to MS SQL, they have
no problem logging into the database. Understandably I want to avoid adding
each and every user to my database, therefore the use of AD groups that is i
f
they would work.I would expect users to be able to connect via group membership. Do you see
the group members listed when you execute xp_logininfo? Does the group show
as the permission path for a specific user?
EXEC xp_logininfo
@.acctname = 'MyDomain\MyGroup',
@.option = 'members'
EXEC xp_logininfo
@.acctname = 'MyDomain\MyGroupMember'
Hope this helps.
Dan Guzman
SQL Server MVP
"Chandler Chao" <ChandlerChao@.discussions.microsoft.com> wrote in message
news:A2720F69-543C-4DF4-8079-56ACA8776D51@.microsoft.com...
> I'm trying to get Active Directory groups to work in SQL Server 2k5 so my
> users can log in with windows authentication. So I've set up the group in
> AD, added the domain users to it, added the group to MS SQL security
> users,
> and assigned a default database to the group. For some odd reason my
> users
> are not able to log in though; I can quickly check this by trying to set
> up a
> ODBC connection System DSN.
> The odd part about this is that if I add a domain user to MS SQL, they
> have
> no problem logging into the database. Understandably I want to avoid
> adding
> each and every user to my database, therefore the use of AD groups that is
> if
> they would work.|||I was just able to get this working with a some help from this NG.
My SQL Server is configured for Mixed authentication.
I set up my AD groups and added users.
I added logins for each AD group in SQL Server.
I added database users for each AD group and assigned permissions to
objects.
Hope this helps..
Michael
"Chandler Chao" <ChandlerChao@.discussions.microsoft.com> wrote in message
news:A2720F69-543C-4DF4-8079-56ACA8776D51@.microsoft.com...
> I'm trying to get Active Directory groups to work in SQL Server 2k5 so my
> users can log in with windows authentication. So I've set up the group in
> AD, added the domain users to it, added the group to MS SQL security
> users,
> and assigned a default database to the group. For some odd reason my
> users
> are not able to log in though; I can quickly check this by trying to set
> up a
> ODBC connection System DSN.
> The odd part about this is that if I add a domain user to MS SQL, they
> have
> no problem logging into the database. Understandably I want to avoid
> adding
> each and every user to my database, therefore the use of AD groups that is
> if
> they would work.

Cannot Generate SSPI Context.

we installed SQL Server 2000 and a new test database, 2 groups were created:
BobAdmin
BobUser
in Active Directory Users & Computers
and users added to each group and everything was fine,we are now getting an
error "Cannot Generate SSPI Context. " when we try and open the database app
on the workstations.
The only change is we have changed the Main Administrator Password, would
this affect the server or the log-on's to SQL Server and has anyone fixed
this problem that could help.
Many Thanks
Jimleake@.hotmail.com
Does SQL Server know about these groups?
http://www.aspfaq.com/
(Reverse address to reply.)
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>
|||I had a similar problem and it turned out to be a DNS issue.
Have you reviewed this article?
http://support.microsoft.com/default...b;en-us;811889
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>
|||Large time differences between systems can also cause SSPI context failures.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>
|||Also check the step-by-step guide how to troubleshoot this error a
http://support.microsoft.com/default...889#appliesto.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>

Cannot Generate SSPI Context.

we installed SQL Server 2000 and a new test database, 2 groups were created:
BobAdmin
BobUser
in Active Directory Users & Computers
and users added to each group and everything was fine,we are now getting an
error "Cannot Generate SSPI Context. " when we try and open the database app
on the workstations.
The only change is we have changed the Main Administrator Password, would
this affect the server or the log-on's to SQL Server and has anyone fixed
this problem that could help.
Many Thanks
Jimleake@.hotmail.comDoes SQL Server know about these groups?
http://www.aspfaq.com/
(Reverse address to reply.)
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>|||I had a similar problem and it turned out to be a DNS issue.
Have you reviewed this article?
http://support.microsoft.com/defaul...kb;en-us;811889
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>|||Large time differences between systems can also cause SSPI context failures.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>|||Also check the step-by-step guide how to troubleshoot this error a
http://support.microsoft.com/defaul...1889#appliesto.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"jim" <jim@.discussions.microsoft.com> wrote in message
news:91D673DA-73CA-4EF1-BB77-016AC467138A@.microsoft.com...
> we installed SQL Server 2000 and a new test database, 2 groups were
created:
> BobAdmin
> BobUser
> in Active Directory Users & Computers
> and users added to each group and everything was fine,we are now getting
an
> error "Cannot Generate SSPI Context. " when we try and open the database
app
> on the workstations.
> The only change is we have changed the Main Administrator Password, would
> this affect the server or the log-on's to SQL Server and has anyone fixed
> this problem that could help.
>
> Many Thanks
> Jimleake@.hotmail.com
>