August 18, 2010 at 10:12 am
We have setup a domain security group that allows access to one of our SQL databases. We have many users logging in and using it without any issues. One particular user it will not work for. She is part of the group, tried multiple PC's, but to no avail.
When I add the user specifically as a user in SQL it works.
the two part error in the log:
"
Date 8/18/2010 11:55:01
Log SQL Server (Current - 8/18/2010 12:00:00)
Source Logon
Message
Error: 18456, Severity: 14, State: 11.
"
and
"
Date 8/18/2010 11:55:01
Log SQL Server (Current - 8/18/2010 12:00:00)
Source Logon
Message
Login failed for user 'domain\user'. [CLIENT: IP Address]
"
Why would it let the user connect directly but not as part of the group?
Donald Mayer
Oswego Health
August 18, 2010 at 11:04 am
State 11 indicates she doesn't have access to her target database/default database. So when you add her manually is her default database different?
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 18, 2010 at 11:13 am
Nope, the default DB is the same, and what is strange, we have about 40 other users who are part of the same group that do not have any problems connecting....
Donald Mayer
Oswego Health
August 18, 2010 at 11:20 am
I have weird errors like that too, are there any SSPI errors logged?
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 18, 2010 at 11:52 am
I don't see any SSPI errors.
Donald Mayer
Oswego Health
August 20, 2010 at 9:49 am
Hmm no SSPI errors, users defaults are correct, hmmm
If user is able to access it directly and not via AD group.. there is one other issue have found. If the AD group is defined at distribution list; then there are some issues with security.
If that doesn't work, right now I am out of options, any kind of errors logged in System Event Viewer when he tries to logon? Under security or application, maybe kerbose authentication issue.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 20, 2010 at 10:10 am
Should the group be defined as a Security group or distribution group? It currently is set to security.
Donald Mayer
Oswego Health
August 20, 2010 at 10:42 am
should be security.
Please check when he attempts a logon to SQL Server in Windows Security logs Login any kerberos issues recorded?
I don't think it can be this issue as no SSPI error recorded but you can maybe check this out? (Link).
Somewhere along the line his security token is not being passed; because it is not even showing his user name in the error log. It cannot verify his credentials back to the AD. This happened for me on cross domain issue with no cross-domain trust, only resolution I found was using SQL Authentication for developers. But in my case it was for all developers not just one.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
August 20, 2010 at 11:28 am
Yea I checked the windows logs on the domain controller and on the SQL server itself and there wasn't any errors registerd at the same time as the logs in SQL. the SQL log does show the domain and username that errors.
I was wondering about the AD token, maybe the user SSID is corrupt or incorrect somehow. The only errors are the ones in the SQL log and the "copy" in the App event log.
Don
Donald Mayer
Oswego Health
August 20, 2010 at 2:36 pm
Only other thing I can suggest is try removing him from group and re-adding it? My last job the ad guy knew how to check if security token were being generated properly; maybe that is another thing you can look at?
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply