Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Login failed for user with token-based server access validation error Expand / Collapse
Author
Message
Posted Tuesday, June 8, 2010 2:09 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, September 21, 2013 8:43 PM
Points: 148, Visits: 566
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/
Post #934236
Posted Monday, June 28, 2010 3:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 5:03 AM
Points: 17, Visits: 41
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:
  • 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

    Post #943745
    Posted Monday, June 28, 2010 7:12 AM
    Ten Centuries

    Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

    Group: General Forum Members
    Last Login: Wednesday, January 29, 2014 2:58 PM
    Points: 1,141, Visits: 944
    Can anyone provide any insight on the impact of Security group vs Domain Local Security on cross forest authentication?
    Post #943870
    Posted Friday, July 2, 2010 10:03 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Wednesday, April 27, 2011 5:03 AM
    Points: 17, Visits: 41
    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.
    Post #946965
    Posted Wednesday, March 9, 2011 3:46 AM
    Mr or Mrs. 500

    Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

    Group: General Forum Members
    Last Login: Wednesday, December 17, 2014 8:17 AM
    Points: 558, Visits: 1,495
    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.
    Post #1075419
    Posted Tuesday, April 26, 2011 11:22 AM
    SSC-Enthusiastic

    SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

    Group: General Forum Members
    Last Login: Thursday, September 27, 2012 10:43 PM
    Points: 126, Visits: 56
    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]



    Post #1098843
    Posted Tuesday, August 16, 2011 10:18 AM
    SSChasing Mays

    SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

    Group: General Forum Members
    Last Login: Monday, December 22, 2014 2:39 PM
    Points: 637, Visits: 567
    [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.[size="7"][/size]
    Post #1160653
    Posted Tuesday, September 6, 2011 3:09 AM
    SSC Journeyman

    SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

    Group: General Forum Members
    Last Login: Friday, April 11, 2014 4:21 AM
    Points: 97, Visits: 392
    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/

    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
    Post #1170202
    Posted Monday, October 17, 2011 1:37 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Thursday, July 12, 2012 4:24 AM
    Points: 8, Visits: 10

    I am also having a very similar issue.
    Post #1191144
    Posted Tuesday, June 11, 2013 8:03 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Tuesday, June 11, 2013 8:25 AM
    Points: 1, Visits: 1
    I think I discovered a solution.
    In my case, it was sufficient to start the SQL Server Browser Service on the SQL server.
    Post #1462143
    « Prev Topic | Next Topic »

    Add to briefcase «««1234»»

    Permissions Expand / Collapse