SQLTeam.com | Weblogs | Forums

Group versus User Windows Authentication


#1

Hello All

I have read a number of post related to the issue I am having but wanted to field my question to make sure I have a understanding..

I have three users added to a AD group, named DEV
I added the group to the security node on the server with sysAdmin rights.
Two users are able to authenticate no issues
One user is not able to authenticate, rather the error states they are trying to authenticate to the local box, BUT if I add the windows user individually they can authenticate with windows.

I understand the admin group needs to be added BUILTIN\Administrators, but I was not able to find the group in 2012 which I understand is by design?

Why are two of the users in the group go-t0-go with windows authentication but one is not?

I checked with my AD team and they state the users are all a 1to1..... so what is it about this one user account?

I read running ssms from a elevated position allows windows credits to be passed, but was not able to test with the end user and two of the users within the group do not have to do this.

Can someone please provide insight.


#2

Do not add BUILTIN\Administrators to SQL Server, that allows anyone in the organization to be added as a local administrator on the server to then have access to SQL Server. And especially do not grant that account sysadmin...

Verify that the one AD account that is having problems does not have the 'Change password at next logon' checked. If that option is checked the user will not be able to authenticate to any windows resources until that has been completed.

The next item to check is whether or not the user who is failing is actually logged into the domain. If the user is not logged into the domain then their AD credentials are not being sent.

I have to wonder why you are granting end users sysadmin rights. That gives them the ability to completely control SQL Server - is that what you want them to be able to do?


#3

I did go that route, but with that said adding the user allows them to authenticate it is only when the login is vetted by the group it is a no go.. So if the checkbox was flagged wouldn't the user have the same experience when authenticating as a user?

Okay, so this seems like the same scenario, if they where not on the domain shouldn't they fail using the group or a individual user?

Yup, good point however no requirements other-than get it working now... but yes I would love to curtail this, but it has not been a priority for the organization.


#4

Is there a particular error message they get? If it's 18456 can you also post the state?


#5

maybe the 2 other users that are able to login do not like person #3 so they wrote a powershell command that disables that user's account. easy cause they are sysadmin.

all jokes aside

  1. is this user on site or logging in via vpn or something?
  2. If on site have them log in on another computer and see what happens when they try to login using SSMS
  3. What tools are they using to access the sql server? have you seen what actually happens when they try to login?

#6

Reading the post I want to first say THANK YOU EVERYONE. I think the root cause is the end user, and I believe they are not inside the domain but trying to use SSMS to connect so it trys to authenticate local and not with windows.


#7

If that user is able to login when you add their login to SQL Server - then they should not have any issues logging in when a member of the group. Unless they are not actually a member of the group or they have a different account that is a member of the group.

If they are a member of the group - then it could be that they have not logged off their workstation and logged back in to pick up the membership in the group. In that case, as far as SQL Server is concerned they are not authorized because they do not have that group listed in their credentials. Yes - this sounds backwards but it is how it works...SQL Server does not look to AD to determine membership - it expects the user logging in to the system to have that group already listed in their group membership.

It is also possible that the individual is part of another security group that has been denied CONNECT to SQL Server which would override their other group membership.


#8

remember that if this is production and you have them as sysadmin, once they delete a very important table, they most probably will come back to you and say "we have to let you go" Why you may ask. Because you did not speak up at least warn them of the danger of giving them sysadmin permissions!