Login failed for user with token-based server access validation error

Question

Post reply

Login failed for user with token-based server access validation error

Drop the login. At this point SQL Server showed that the user was mapped to the renamed AD group
Recreate the login
Apply any relevant server-wide permissions and config to the recreated login
Map the db user to the recreated login
Correct database username if required (for some reason SQL Server prefixed my user with the domain name, which I didn't want)

Before executing this script:

Ensure you have made the appropriate backups prior to execution
Ensure any server specific login config/permissions is recorded prior to execution
Replace EXAMPLE_DOMAIN, ExampleGroup and ExampleDb as applicable

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

ab5sr SSCarpal Tunnel Points: 4056 More actions · Answer 1

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.

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

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

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

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....

OomBoom Hall of Fame Points: 3481 More actions · Answer 4

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.
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

jwinks Valued Member Points: 63 More actions · Answer 5

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.

savethytrees SSCommitted Points: 1968 More actions · Answer 6

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/

chris-fry SSC Enthusiast Points: 135 More actions · Answer 7

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:

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 8

Can anyone provide any insight on the impact of Security group vs Domain Local Security on cross forest authentication?

chris-fry SSC Enthusiast Points: 135 More actions · Answer 9

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.

Chris Houghton SSCrazy Points: 2557 More actions · Answer 10

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.

danw SSCommitted Points: 1842 More actions · Answer 11

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]

SumLasty SSCommitted Points: 1923 More actions · Answer 12

[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]

dbamohsin SSCommitted Points: 1948 More actions · Answer 13

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]

My DBA Ramblings - SQL Server | Oracle | MySQL | MongoDB | Access[/url]

geek12 SSC Enthusiast Points: 150 More actions · Answer 14

geek12

SSC Enthusiast

Points: 150

October 17, 2011 at 1:37 am

#1395158

I am also having a very similar issue.

ossejevigor SSC Rookie Points: 41 More actions · Answer 15

I think I discovered a solution.

In my case, it was sufficient to start the SQL Server Browser Service on the SQL server.

Login failed for user with token-based server access validation error

Cookies on SQLServerCentral