AD Group Authentication to SQL 2008

Question

Post reply

AD Group Authentication to SQL 2008

cstewart-1126229

SSC Journeyman

Points: 79
More actions
July 9, 2009 at 6:42 am

#208040

Hi All!
I'm a regular reader here but first time poster 🙂
We recently moved to SQL 2008 Ent and are attemping to setup AD Group Authentication to SQL on the sever level. I say attempting because for some reason all our domain windows accounts setup in SQL are autenticating correctly but our domain distribution groups and/or security groups will not allow a user to authenticate that belongs to that group. Instead, that user must be explicitly defined in SQL with their individual domain account. Is there any trick to get group AD authentication to work out-of-the-box? The error we receive simply states it cannot connect. Again, individual domain accounts work but none of our groups are allowing authentication. We're very very new to SQL in general so I'm sure I might just be overlooking something here.
Thanks for any help, guys!

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

David Sorauer SSCrazy Points: 2874 More actions · Answer 1

My SQL 2008 servers are fine with AD groups...I assume you are running SP1?

cstewart-1126229 SSC Journeyman Points: 79 More actions · Answer 2

Yeah, we are. It would be easier to work with if our normal domain accounts couldn't authenticate but they can. When we add a security group it just won't recognize the members when they try to connect with SSMS but SQL allows us to add groups at the server-level. And sorry about the replicate posts, the board kept erroring on me. Not my day haha.

lfernandesfotos Old Hand Points: 317 More actions · Answer 3

Did u had your issue solved?? I have the same problem. xD

TroyG SSCrazy Points: 2633 More actions · Answer 4

I too have been battling this today; have come back from holiday and my PC has been rebuilt. So I reinstalled SQL2008 (SP2) but cannot connect. My domain username is added into the Local machine "administrators" group, and the group is added as sysadmin. The local administrator can connect fine, but my username will not.

I have setup a seperate local group (and made is a sysadmin) and added myself to this group; no dice.

I have added a domain group (that I am a member of) and added this as sysadmin; still nothing.

I add my individual username and whamo, all is good in the world.

Why are the groups not working? I am at a loss, after trawling the web all day and uninstalling and reinstalling all to no avail.

Any ideas are much appreciated!

Cheers

Troy

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 5

Do you mean in those tests that you cannot log in at all, or that you are not a sysadmin?

I assume this machine is a member of the domain, correct?

TroyG SSCrazy Points: 2633 More actions · Answer 6

Thanks for your interest/help 🙂

Yes this is a domain member

When the logon is not listed individually (ie the logon is only present within a local/ad group), the connection to the SQL server is refused, with the message.

TITLE: Connect to Server

------------------------------

Cannot connect to (local).

------------------------------

ADDITIONAL INFORMATION:

Login failed for user 'DOMAIN\UserId'. (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

Marimuthu-129612 Hall of Fame Points: 3318 More actions · Answer 7

check below url

http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx

TroyG SSCrazy Points: 2633 More actions · Answer 8

Thanks for your suggestion.

I have gone and had a look through the ring buffer and this is what it has come back with:

<Record id="36" type="RING_BUFFER_CONNECTIVITY" time="74539790">

<ConnectivityTraceRecord>

<RecordType>Error</RecordType>

<RecordSource>Tds</RecordSource>

<Spid>53</Spid>

<SniConnectionId>8A3DDCEE-1EFF-4B2E-8672-1D92499D8B27</SniConnectionId>

<OSError>0</OSError>

<SniConsumerError>18456</SniConsumerError>

<SniProvider>4</SniProvider>

<State>11</State>

<RemoteHost><local machine></RemoteHost>

<RemotePort>0</RemotePort>

<LocalHost />

<LocalPort>0</LocalPort>

<RecordTime>8/17/2010 20:54:37.253</RecordTime>

<TdsBuffersInformation>

<TdsInputBufferError>0</TdsInputBufferError>

<TdsOutputBufferError>0</TdsOutputBufferError>

<TdsInputBufferBytes>96</TdsInputBufferBytes>

</TdsBuffersInformation>

<TdsDisconnectFlags>

<PhysicalConnectionIsKilled>0</PhysicalConnectionIsKilled>

<DisconnectDueToReadError>0</DisconnectDueToReadError>

<NetworkErrorFoundInInputStream>0</NetworkErrorFoundInInputStream>

<ErrorFoundBeforeLogin>0</ErrorFoundBeforeLogin>

<SessionIsKilled>0</SessionIsKilled>

<NormalDisconnect>0</NormalDisconnect>

</TdsDisconnectFlags>

</ConnectivityTraceRecord>

<Stack>

<frame id="0">0X00000000026933E3</frame>

<frame id="1">0X0000000002693819</frame>

<frame id="2">0X00000000032038FC</frame>

<frame id="3">0X0000000001EB0604</frame>

<frame id="4">0X0000000001212752</frame>

<frame id="5">0X00000000011E25C7</frame>

<frame id="6">0X0000000000E2B0FB</frame>

<frame id="7">0X0000000000E2A61A</frame>

<frame id="8">0X0000000000E2B249</frame>

<frame id="9">0X00000000013BA670</frame>

<frame id="10">0X00000000013BA550</frame>

<frame id="11">0X00000000013B7EA0</frame>

<frame id="12">0X00000000013BA43F</frame>

<frame id="13">0X0000000074A337D7</frame>

<frame id="14">0X0000000074A33894</frame>

<frame id="15">0X0000000076BCF56D</frame>

</Stack>

</Record>

From what I can tell this just appears to be erroring on the idea that there is no security defined for my user? Although I am 100% sure that the group has been setup and includes my userid. I have even removed my userid and readded it (logged off and back on), but all to no avail. The only way I can get connectivity is by adding individual logins and not groups.

I am lost.

TroyG SSCrazy Points: 2633 More actions · Answer 9

Incidentally, this behavior appears to be consistent across all 3 of the computers that have recently been rebuilt. All are windows 7 64bit with SQL 2008. All have Visual Studio installed too.

*shrug*

TroyG SSCrazy Points: 2633 More actions · Answer 10

Also; have noticed that in the ERRORLOG for Sql the following entry is made:

2010-08-18 12:40:25.65 Logon Error: 18456, Severity: 14, State: 11.

2010-08-18 12:40:25.65 Logon Login failed for user 'DOMAIN\UserID'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

Not sure what it means, but hoping that it might shed some light.

TroyG SSCrazy Points: 2633 More actions · Answer 11

Righto; well the problem was the User Account Control Settings. I have pulled this back and set it to never notify, and now things are all sorted. Unbelievable. 😀

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 12

Thanks for the update. That's a good thing to check early on with the newer OSes.

TroyG SSCrazy Points: 2633 More actions · Answer 13

So so true; this has been my first exposure to Windows 7 in a true sense so hopefully won't stub my toe again 😉

Rick.Cornell.SME SSCrazy Eights Points: 8597 More actions · Answer 14

Rick.Cornell.SME

SSCrazy Eights

Points: 8597

August 18, 2010 at 12:51 pm

#1208709

OUCH!!! A little pain never hurts from time to time.