none
Could not find the login matching the name provided. Error: 18456 Severity: 14 State: 5

    Question

  • The problem is in topic.

    Circumstances:

    1. Stand-alone instance which work under virtual server account! (new feature or maybe already not)

    2. The user SQL\Administrator  is member of AD Group  SQLAdmins which has sysadmin permissions on the instance.

    3. This user also domain administrator in this domain SQL.

    I am trying to logon to this instance with this account SQL\Administrator through AD Group SQLAdmins. In this case I get the error in the topic "Could not find ..."

    4. I can add this login explicity and this works.

    5. I can change service account to domain  user instead of virtual account. In this case it also works.

    6. I can run EXEC XP_LOGININFO 'sql\sqladmins','members' under virtual account and I get list of all users in this group.

    How can I get access to the server through the AD group without explicit creation of the login?

    It is very important question for large production environment. Very hope guys you can help me :) !

    Update:Checked from local computer - it is work, but from domain controller ssms gets the same error
    • Edited by AleshaDBA Saturday, November 30, 2013 6:07 PM New unput info
    Saturday, November 30, 2013 5:08 PM

All replies

  • When you are logged in as SQL\Administrator (after having added this login explicitly), what does

    SELECT is_member('SQL\SQLAdmins')

    report?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, November 30, 2013 6:02 PM
  • Ha-ha

    0 - remotely

    1- local


    • Edited by AleshaDBA Saturday, November 30, 2013 6:13 PM new result
    Saturday, November 30, 2013 6:11 PM
  • So you are saying that you get different results depending on whether you connected locally or not?

    What about any difference when you don't have added the login explicitly? Are you able to login locally or remotely in this case?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, November 30, 2013 8:46 PM
  • 1. Yes. It is different.

    2. Without explicit login I can logon locally, but I get an error remotely.

    Sunday, December 01, 2013 7:50 AM
  • Hi,

    this article explains very well what happened: http://stackoverflow.com/questions/13821188/sql-server-fixing-error-error-18456-severity-14-state-11

    I hope it helps.

    Janos


    There are 10 type of people. Those who understand binary and those who do not.

    My Blog | Hire Me

    Sunday, December 01, 2013 8:15 AM
  • I am trying to logon to this instance with this account SQL\Administrator through AD Group SQLAdmins. In this case I get the error in the topic "Could not find ..."


    Is SQL the domain name (I guess not) or is SQL the machine name and that's a local account, not an AD account? When you work on an other machine, are you using a AD account or a local one?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Sunday, December 01, 2013 8:39 AM
  • make run as Administrator on remote machine - it doesn't help.

    workaround - explicit login is not good when AD group are used for granting permissions

    Sunday, December 01, 2013 9:22 AM
  • SQL - domain name

    SQL\Administrator - domain administrator

    SQL\SQLAdmins - domain group for SQL admins and local administrator for domain host

    Server FQDN: Node1.sql

     Instance uses virtual account to run services! (I feel the cause in this :)

    The problem arises only for remote logons. 

    No local accounts are used,  only domain
    • Edited by AleshaDBA Sunday, December 01, 2013 9:27 AM need to specify details
    Sunday, December 01, 2013 9:26 AM
  • THE PROBLEM ARISED ONLY FROM DOMAIN CONTROLLER WHEN THE INSTANCE RUN UNDER VIRTUAL ACCOUNT!!!

    I can logon remotely from other domain hosts. 

    • Edited by AleshaDBA Sunday, December 01, 2013 9:40 AM
    Sunday, December 01, 2013 9:37 AM
  • What is puzzling here is that your subject line says "State: 5". According to
    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx
    this means that the attempt was from the local machine.

    As I understand, the problem occurs only if you connect from a particular remote machine, to wit the domain controller.

    You make a noise of the fact that SQL Server runs under a virtual account? So if SQL Server runs under a domain account, the problem does not occur? It could be an issue of lack of permissions in the AD when SQL Server is trying to retieve group information.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, December 01, 2013 10:26 AM
  • As I understand, the problem occurs only if you connect from a particular remote machine, to wit the domain controller.

    YES

    You make a noise of the fact that SQL Server runs under a virtual account? So if SQL Server runs under a domain account, the problem does not occur? 

    YES, YES

    It could be an issue of lack of permissions in the AD when SQL Server is trying to retieve group information.

    Probably, but how to get the root cause?



    • Edited by AleshaDBA Sunday, December 01, 2013 9:30 PM
    Sunday, December 01, 2013 9:30 PM