locked
AD user removed RRS feed

  • Question

  • I have wierd situation for an failed login 

    The login name was associated with AD group which has an access to the databases , few days back this login was removed from the AD group and then we are alerted with this below error. 

    Error: 18456, Severity: 14, State: 11. Login failed for user 'WG\Loginname'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 192.168.1.100]  

    I need to get clarrify on below items. 

    1.  Is Deleting the user from AD group removes the login from SQL Instance ?

    2. On db level the user was shown with login name (AD group name) but missing in DB engine-Security-Logins ?

    3. Is this the orphaned user ?

    4. How to know the user belongs to which AD group ?

    5. is Windows level permissions matters for this login to access on the instance ?

    Appreciate if anyone help me in clarrifying the above items. 


    SANTHOSH KUMAR


    Friday, January 13, 2017 6:05 AM

Answers

  • Hi Santosh,

    As you may be knowing, there are two type of authentication in SQL Server. 
    A. SQL Authentication. It uses Login name and Password
    B. Windows or Integrated Authentication. It uses Windows Login to connect to SQL Server. On SQL Server a Windows Login can be Added explicitly or a AD Group can be added into SQL server and all AD users in that AD group will get the permission.

    In pertaining to point B, if a Windows Login has been removed from AD Group, and when he tries login it will give an error.

    Now answer to your specific questions:

    1. NO, AD group Login still be there as login on SQL Server. You have explicitly remove it if you want to.

    2. May be someone has deleted only the login and not user

    3. In context of point 2, yes it orphan login

    4. use can use xp_logininfo. This will give you permission path. If a login is part of multiple AD groups and those AD groups are also added as Login and further users in database you are checking, xp_logininfo will return you lest permission path.

    xp_logininfo 'DomainName\LoginName'

    Let me know if any questions.

    Thanks,


    Kindly mark the reply as answer if they help

    • Marked as answer by SANTHOSH_DBA Friday, January 13, 2017 1:20 PM
    Friday, January 13, 2017 6:21 AM
  • 1. Not necessarily. There could still be an individual user account of 'WG\Loginname" in Logins that *also* needs to be explicitly dropped, and as you point out in #2, there could  also still be individual accounts left behind as 'orphan' in db level (ie: no longer associated with an instance-level login.)

    2. This suggests to me that there *may* be (or may have been at some point in the past) an individual Windows domain account as Login at the instance-level in addition to 'WG\Domain_Group' that the account previously was a member of.

    3. Yes, if there is not a corresponding WG\Loginname or WG\Doman Group (that the user account belonged to) then this is an 'orphaned' db user.

    4. If you have access to 'Active Directory Users and Computers' in Administrative Tools on server, you can lookup Groups to find out user accounts that are member of given group.

    5. The fixed server role memberships and databases privileges that have been granted to the user account on the SQL instance determine the accounts access level on the SQL instance. Not sure what you mean by 'Windows level permission'.

    [Please mark as answer if this post helped you]

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, January 13, 2017 12:25 PM
    • Marked as answer by SANTHOSH_DBA Friday, January 13, 2017 1:20 PM
    Friday, January 13, 2017 12:22 PM

All replies

  • Hi Santosh,

    As you may be knowing, there are two type of authentication in SQL Server. 
    A. SQL Authentication. It uses Login name and Password
    B. Windows or Integrated Authentication. It uses Windows Login to connect to SQL Server. On SQL Server a Windows Login can be Added explicitly or a AD Group can be added into SQL server and all AD users in that AD group will get the permission.

    In pertaining to point B, if a Windows Login has been removed from AD Group, and when he tries login it will give an error.

    Now answer to your specific questions:

    1. NO, AD group Login still be there as login on SQL Server. You have explicitly remove it if you want to.

    2. May be someone has deleted only the login and not user

    3. In context of point 2, yes it orphan login

    4. use can use xp_logininfo. This will give you permission path. If a login is part of multiple AD groups and those AD groups are also added as Login and further users in database you are checking, xp_logininfo will return you lest permission path.

    xp_logininfo 'DomainName\LoginName'

    Let me know if any questions.

    Thanks,


    Kindly mark the reply as answer if they help

    • Marked as answer by SANTHOSH_DBA Friday, January 13, 2017 1:20 PM
    Friday, January 13, 2017 6:21 AM
  • Sunil, Thanks for your  reply.  

    I dont think  it was deleted explicitly, but after User was removed from AD group the server was restarted whether this could affect here ?

    Executing Xp_logininfo 'Domainname\Loginname' is not returning any results , this i tried with login name and group name also. 

    But one thing i observed here is when i use this login with Execute as Login  then it gets executed successfully , how this works ?


    SANTHOSH KUMAR

    Friday, January 13, 2017 6:33 AM
  • EXECUTE AS CLAUSE should not work until you have corresponding login for the database user. You can check system view sys.users and syslogins into this database and check where SID or user is mapped with.

    Thanks,


    Kindly mark the reply as answer if they help

    Friday, January 13, 2017 11:20 AM
  • Sunil,

    i can find the user in sysusers but i cant find in syslogins .  Even checked with SID from sysusers  to syslogins.


    SANTHOSH KUMAR

    Friday, January 13, 2017 11:59 AM
  • The newly created account in the AD will have a different SID than the one in SQL Server. You will need to drop this login and recreate it. The problem with be that the previous login will have database user accounts and permissions associated with it and its sid. You will need to script out all permissions in the databases before dropping and re-adding this login.
    Friday, January 13, 2017 12:07 PM
  • 1. Not necessarily. There could still be an individual user account of 'WG\Loginname" in Logins that *also* needs to be explicitly dropped, and as you point out in #2, there could  also still be individual accounts left behind as 'orphan' in db level (ie: no longer associated with an instance-level login.)

    2. This suggests to me that there *may* be (or may have been at some point in the past) an individual Windows domain account as Login at the instance-level in addition to 'WG\Domain_Group' that the account previously was a member of.

    3. Yes, if there is not a corresponding WG\Loginname or WG\Doman Group (that the user account belonged to) then this is an 'orphaned' db user.

    4. If you have access to 'Active Directory Users and Computers' in Administrative Tools on server, you can lookup Groups to find out user accounts that are member of given group.

    5. The fixed server role memberships and databases privileges that have been granted to the user account on the SQL instance determine the accounts access level on the SQL instance. Not sure what you mean by 'Windows level permission'.

    [Please mark as answer if this post helped you]

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, January 13, 2017 12:25 PM
    • Marked as answer by SANTHOSH_DBA Friday, January 13, 2017 1:20 PM
    Friday, January 13, 2017 12:22 PM