August 30, 2009 at 6:32 am
Deb Anderson (4/24/2009)
I am also having a very similar issue. Did this ever get resolved?
Go back and do a Run As login with the user that you originally set the SQL Server up with; add your new user and GRANT whatever permissions. Retry...should be gold.
September 16, 2009 at 5:55 pm
Hey all,
Well, I'm having the same problem. The odd thing is though, is that it only happens for certain users (which are part of NT groups). Myself and many others whom are part of NT groups on this server, can connect without problem. Yet others, are having this problem. The only difference is that I have sysadmin, they do not. I have not yet tried a test case with a lower set of permissions but will do so tomorrow.
Curious though to as if this is really the only resolution thus far? I have seen many posts on this matter and I have seen this as a solution (to grant sysadmin) as well as the specific user is part of to many NT groups (not true).
I'm at a loss here. Never seen this. Unfortunately now I have to let some of these users/vendors use SQL security until I can resolve this.
* Just realized this is in SQL7/2000 forum ... I am on SQL2008/Windows2008. My gut is telling me it's something about UAC. Oh well, I'll leave this here for now and will re post where appropriate if no response is made.
Thanks all
November 19, 2009 at 4:48 pm
I had the same problem today, suddenly everyone that was coming trough a particular group to my SQL Server 2008 server (SP1 64 bit on Windows 2003 Sp2) stopped being able to login. If I added them individually it would work, but not something I am willing to do.... Had another group that was overlapping of users, actually more of a subset as this group is a member of the group that is failing, I added that group directly in SQL and they could login.
Now for the strange thing. The security from the group that failed to log them in seems to be applied to them, so only the login part fails. From this I take it I can most likely drop the group that is failing and recreate it, and I hope things will work....
November 27, 2009 at 5:21 am
Hi ppl.
I had the same problem with state 11. I found that the builtin\administrator group was denied access to the server and this caused the login to fail for users that was part of the server admninistrators group (via domain policy or explicitly). I changed the administrators login to have public access and it seems to work fine.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Do not reinvent the wheel.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
January 13, 2010 at 10:08 am
Poster above musing about UAC is right. I was experiencing on Win2008 R2. When rt-clicking the SSMS shortcut and choosing "Run as Administrator", I sailed right in.
June 8, 2010 at 2:10 pm
I was able to resolved this problem by creating a SQL Serve login for that windows user.
Before this it was part of the administrator group. Not sure if this will be a solution for everyone.
Blog
http://saveadba.blogspot.com/
June 28, 2010 at 3:02 am
Just solved an issue like this here.
Our client had a group in AD mapped to a SQL Server login, mapped to a database user. For various reasons, they decided that the group needed to be changed from a Global Security group to a Domain Local Security Group. They achieved this by renaming the group and creating a new one with the original name.
SQL Server seemed to partially cope with this. Users in the group could log in, but would intermittently get a token-based server login error. So, it appears the database user was partially orphaned. SQL Server showed the correct Login name mapped to the user. I didn't want to recreate the database user, because it had custom, database-level permissions.
The solution:
Before executing this script:
Script:
USE [master]
DROP LOGIN [EXAMPLE_DOMAIN\ExampleGroup]
GO
-- At this point SQL Server showed that the user was mapped to the renamed AD group
CREATE LOGIN [EXAMPLE_DOMAIN\ExampleGroup] FROM WINDOWS WITH DEFAULT_DATABASE=[ExampleDb]
GO
USE [ExampleDb]
ALTER USER [ExampleGroup] WITH LOGIN = [EXAMPLE_DOMAIN\ExampleGroup]
GO
-- Correct database username if required
June 28, 2010 at 7:12 am
Can anyone provide any insight on the impact of Security group vs Domain Local Security on cross forest authentication?
July 2, 2010 at 10:03 am
That might be more of a Windows/Active Directory question than SQL Server, but this might help:
http://technet.microsoft.com/en-us/library/cc755692(WS.10).aspx
In our client's case it was to do with being able to grant database access permissions to certain external users without granting much else.
March 9, 2011 at 3:46 am
I had this issue when using a domain local group to provide SQL Server authentication across domains (users and group were in one domain, the SQL box was in another). I changed the group type to Universal and the problem was solved.
April 26, 2011 at 11:22 am
I received this error when connect was revoked from the public role and a login was attempted from a domain account with only public access. Once CONNECT was granted again the error went away.
Script used to revoke CONNECT:
REVOKE VIEW ANY DATABASE FROM public
REVOKE CONNECT ON ENDPOINT::[TSQL Local Machine] FROM public
REVOKE CONNECT ON ENDPOINT::[TSQL Named Pipes] FROM public
REVOKE CONNECT ON ENDPOINT::[TSQL Default TCP] FROM public
REVOKE CONNECT ON ENDPOINT::[TSQL Default VIA] FROM public
Script used to grant CONNECT to login:
GRANT CONNECT TO [DOMAIN\LOGIN]
August 16, 2011 at 10:18 am
[font="Verdana"][font="Verdana"][font="Verdana"] Can you check where are the two users running reports from? I suspect it would be from their desktops.
And user name xxxx\xxxx some times could be local system account like NT Authority\anonymos,. please confirm. You could validate where the connection is coming from using ip in error log [CLIENT: xxx.xxx.xxx.xxx]
I know this is queite old post, but probably,if we get solution would be useful for others.[/font]
September 6, 2011 at 3:09 am
I had the same issue with both Token-based and Login-based authentication.
Ive blogged my solution here:
http://dbamohsin.wordpress.com/2011/09/06/token-based-server-access-validation-failed-with-an-infrastructure-error/[/url]
if you dont want to read that then run this code for the user experiencing issues...
GRANT CONNECT SQL TO [DOMAIN\firstname.lastname]
GRANT CONNECT ON ENDPOINT::"TSQL Default TCP" TO [DOMAIN\firstname.lastname]
October 17, 2011 at 1:37 am
I am also having a very similar issue.
June 11, 2013 at 8:03 am
I think I discovered a solution.
In my case, it was sufficient to start the SQL Server Browser Service on the SQL server.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply