Windows authentication group denying access to SQL 2K?

Question

Post reply

Windows authentication group denying access to SQL 2K?

Viewing 9 posts - 16 through 23 (of 23 total)

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

RLee SSC Veteran Points: 241 More actions · Answer 1

Ok... TestUser created... Made him a "Small Business User", and logged onto Win2K3 server, no problem...

Made him a part of "TestGroup"...TestGroup was made part of the Group Scope "Global" first..

I went into ODBC Connections, and started to create the DSN for server2's SQL ...

I went to sign on, and viola, no problem! I was even able to test the connection via "TEST DATA SOURCE".

I then deleted the group, and recreated the group with Group Scope "Domain Local", and look at that... Test failed! (After logging off/on to reset user info).

Odd considering on server1 box, it is all ok and works fine the way it is.

To me, this is the same idea as adding the group to "Domain Admins", or is it?

Thanks.

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Take a look at

http://technet2.microsoft.com/WindowsServer/en/Library/79d93e46-ecab-4165-8001-7adc3c9f804e1033.mspx

I haven't admined any Domains in a while so my knowledge is very rusty. But I believe you have to identify the SQL Server as a Domain resource in ADS in order to use Domain Local groups. You probably did that somwhere along th line for the old server. Again I am shooting without a concise knowledge and I could have my concept wrong right now. Hopefully someone else will chime in or I just hit the right nail by chance.

RLee SSC Veteran Points: 241 More actions · Answer 3

I looked at the link, and based on the info in the page, i can tell you that i'm running our domain in Windows 2K mixed mode...

"When the domain functional level is set to Windows 2000 mixed, members of domain local groups can include accounts and global groups from any domain."

But reading further down, "Groups on client computers and stand-alone servers".

As the Windows 2k3 computer is a stand-alone server, but part of the domain.. hmm..

My thought is to convert the 2k3 computer into a domain controller.. i had considered it when i first got it, but wasn't sure of the implications...

If it was a domain controller, my guess is that it will work... Either that, or i can change the group to Global group (as it spans 2 different servers)...

Lol, i'm not sure if this is right...what are your thoughts on this? (I know this isn't a windows server forum though)... But hopefully if this is all it is, then i know we may have a solution...

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Unfortunately I am not a good source. Personally I would say go with a Global Group (which shouldn't cause you any overhead or open any unwanted security) and avoid the whole changing to a Domain Controller until you can find a more definitive answer. Hopefully someone here has seen this before and can chime in. If not I would consider posting a request under Administration topic as that is what this is with a name like "SQL admin issues in ADS environment, question on configuration" and post back to this.

RLee SSC Veteran Points: 241 More actions · Answer 5

Thanks... i will do that... hopefully someone can help me out with this on the best option.

Basically, options are.

1. Convert Windows 2003 Server to a domain controller (i'm unsure if this will work).

2. Convert the SQLGroup to "Global" Type group.

Unsure of any others at this time.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 6

I assign database rights based on domain groups, but I rarely see SQL Server admit that anyone new belongs to the domain group. I think the SQL server is using a cached version of the domain group definitions, and I don't know any way short of a reboot to force it to refresh.

Whenever we have a new hire, I manually add their user login to the SQL Server(s) they need to use (but no database rights) and they immediatly aquire all their group-based permissions in all databases on that server. At some later date I can drop their login (assuming they own no objects) with no ill effects, and they are recognized by other servers with no manual intervention.

If there is a better answer, I'd love to hear it. Another factor is our ancient broken-down domain controller that the network admin has been trying to replace for years, but all the grand poobahs can still login to get their email so they don't see the need.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 7

Domain local groups should be seen by the SQL Server if it's in the same domain. The mode (native vs. mixed) doesn't have an impact on the domain local group... here's the main difference between domain global and domain local:

domain global: can be seen by other domains but can only contain users and domain global groups for the same domain

domain local: cannot be seen by other domains. Can contain users and domain global groups from other domains. Can contain domain global and domain local groups and domain users from the same domain.

It does sound like there is a security issue between the SQL Server and the DC. Promoting the SQL Server to a DC is typically not a good idea... different roles, contention of resources, etc. With SBS you have to have the first DC up and SQL Server usually installs on it, but I wouldn't make the 2nd computer a DC unless you don't have a second DC.

Are there any relevant errors on the first computer's security event log, the domain controller, especially with respect to Kerberos errors, etc.? Are there any synchronization or "can't find DC errors" in the system event log for the 2nd computer?

K. Brian Kelley
@kbriankelley

RLee SSC Veteran Points: 241 More actions · Answer 8

Hi there... sorry for the long response, but was away for a few days.

Anyway, to answer your question, there are no errors whatsoever in the first computer's event log.

There are no problems with synchronization or "can't find DC errors" in the log for the second computer. In fact, i see the login for the user in the security log just fine.

I checked the SQL Server logs itself, and all i get is "Login failed for user 'DOMAIN\orderentry'."

Here's the security log info.

Event Type: Success Audit

Event Source: Security

Event Category: Logon/Logoff

Event ID: 540

Date: 19/04/2006

Time: 10:10:06 AM

User: DOMAIN\orderentry

Computer: COMPUTER2

Description:

Successful Network Logon:

User Name: orderentry

Domain: DOMAIN

Logon ID: (0x0,0xA957DA)

Logon Type: 3

Logon Process: Kerberos

Authentication Package: Kerberos

Workstation Name:

Logon GUID: {e1ba0a9f-d430-40ca-166a-00a6512d36fb}

Caller User Name: -

Caller Domain: -

Caller Logon ID: -

Caller Process ID: -

Transited Services: -

Source Network Address: -

Source Port: -

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

RLee SSC Veteran Points: 241 More actions · Answer 9

Thanks to everyone who tried to help me figure out this problem... although this isn't the best solution, i decided to go with a new Global Group, with the same permissions as the original group... which gives me what i need.