SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
savethytrees
savethytrees
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 680
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
chris-fry
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
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


    SA-1
    SA-1
    SSCommitted
    SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

    Group: General Forum Members
    Points: 1521 Visits: 944
    Can anyone provide any insight on the impact of Security group vs Domain Local Security on cross forest authentication?
    chris-fry
    chris-fry
    Grasshopper
    Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

    Group: General Forum Members
    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.
    Chris Houghton
    Chris Houghton
    Say Hey Kid
    Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)Say Hey Kid (692 reputation)

    Group: General Forum Members
    Points: 692 Visits: 1695
    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
    danw
    SSC-Enthusiastic
    SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

    Group: General Forum Members
    Points: 176 Visits: 59
    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]



    suneel kamavaram
    suneel kamavaram
    Say Hey Kid
    Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)Say Hey Kid (697 reputation)

    Group: General Forum Members
    Points: 697 Visits: 614
    [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]

    dbamohsin
    dbamohsin
    SSC Veteran
    SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

    Group: General Forum Members
    Points: 200 Visits: 447
    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
    geek12
    geek12
    Grasshopper
    Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

    Group: General Forum Members
    Points: 16 Visits: 10
    I am also having a very similar issue.
    ossejevigor
    ossejevigor
    Forum Newbie
    Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

    Group: General Forum Members
    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.
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search