Domain Users Cannot Use SQL Agent

[jtb]

A pilot team has moved into Company XYZ's Active Directory. Over the weekend they moved a dev SQL server into AD. Groups used to permission SQL are in AD. User accounts are still in Company XYZ's NT Account domain. Users are able to perform all SQL tasks, except those within SQL Agent.

Anyone with assistance to offer, please contact me ASAP, as this is a show stopper for that team.

Thanks!

JTB
Senior Infrastructure Specialist
MCSE-NT4, MCP+I, MCP-W2K, CCNA, CCDA,
CTE, MCIWD, i-Net+, Network+
(MCSE-W2K in progress)

 

[SQLBill]

What do MSSQLSERVER and SQLSERVERAGENT services use as LOGIN? (Check in Administrator Tools > Services). They should both have a domain login w/admin privileges. (ie: domainname/adminprivaccount)

-SQLbill

 

[jtb]

SQLbill,

thanks!! I'll ask and try to get back to you quickly!!

JTB
Senior Infrastructure Specialist
MCSE-NT4, MCP+I, MCP-W2K, CCNA, CCDA,
CTE, MCIWD, i-Net+, Network+
(MCSE-W2K in progress)

 

[jtb]

I want to thank SQLBill, and to fill you in in case you were curious. The support team spent about two solid days on the phone with Microsoft, I guess, trying to figure this one out, and last I was upated, the issue was left with MS to try to come up with a resolution. Here's a summary from Microsoft of the suspected issue to keep in your back pocket in case you ever run into a similar situation.

1. Since the users still login to the NT 4 domain, their group membership is being checked by the NT 4 DC, even though they are authenticated to SQL by group membership in the Windows 2000 domain. The call made to NetUserGetGroups is in fact being sent to th NT 4 DC. Since none of the SIDs for these groups match the SID in syslogins, the user is denied access to the server. We only check the Domain Controller that authenticated the user for the groups that they belong to.

2. Our call to NetUserGetGroups does not return information about Domain Local groups. So, even if the user accounts were moved into the Windows 2000 domain and authenticated by the Windows 2000 domain, if the security is based upon a Domain Local Group, this call would still fail.
We have requested input from the SQL Server Development group and are awaiting their feedback.

Well, waddya stink about dat?

JTB
Senior Infrastructure Specialist
MCSE-NT4, MCP+I, MCP-W2K, CCNA, CCDA,
CTE, MCIWD, i-Net+, Network+
(MCSE-W2K in progress)