none
unable to login into mssql2012 server with group login.

    Question

  • I have ready many questions here and on the internet reguarding this issue and have been unable to find a solution to my issue.

    I have created a user in AD and placed that user into a group domain\groupname then went to loginto my sql server expanded security -> logins right clicked new login -> search -> object types > checked Group -> selected domain for location -> added domain\groupname ->  set  role to sysadmin and hit ok

    i went to login as user and am greated with login failed for user. event log says "login failed for user 'domain\username' reason: Couldnot find a login matching the name provided. [client: IP]'

    i then went and tested the users login via a new sql query exec XP_logininfo 'domain\username', 'all' and it came back with one result... 

    i am unsure how to continue... i should also say that this machine is using both sql and windows account... it has to be this way.

    Wednesday, March 06, 2013 9:00 AM

Answers

  • Perhaps there is some issue with your AD and the replication between Domain Controllers?

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by dw5304 Tuesday, March 12, 2013 1:16 PM
    Thursday, March 07, 2013 3:04 PM

All replies

  • server properties->security-> check windows authentication or SQL Server authentication,check the screenshot below.

    please remember making changes in the this area requires server restart


    Ramesh Babu Vavilla MCTS,MSBI

    Wednesday, March 06, 2013 9:18 AM
  • the server is already in sql and windows auth mode. 
    Wednesday, March 06, 2013 10:35 AM
  • That error messages sounds like you tried to login as a SQL Server authontication instead of Windows authentication.


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, March 06, 2013 11:54 AM
  • Wednesday, March 06, 2013 12:12 PM
  • Because the login attempt failed, SQL Server does not reply with full information to this unknown user. However SQL Server will place more interesting information in the SQL Server error log so that the SQL Server administrator can troubleshoot the problem. Check the SQL Server error log for error 18456 and check the state of the error. For more information, see http://msdn.microsoft.com/en-us/library/cc645917.aspx


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Wednesday, March 06, 2013 4:40 PM
  • well today after letting it sit for 24 hours it works...

    as i stated earlier the error in the log was:
     "login failed for user 'domain\username' reason: Couldnot find a login matching the name provided. [client: IP]'


    • Edited by dw5304 Thursday, March 07, 2013 2:40 PM
    Thursday, March 07, 2013 2:39 PM
  • Perhaps there is some issue with your AD and the replication between Domain Controllers?

    Tibor Karaszi, SQL Server MVP | web | blog

    • Marked as answer by dw5304 Tuesday, March 12, 2013 1:16 PM
    Thursday, March 07, 2013 3:04 PM
  • any chance that SID is different than AD?


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Thursday, March 07, 2013 3:05 PM
  • Are you trying for the contained database, if so can you please check in the state 5 column under the below link-

    then specify the database name explicitly in the options tab of the connection dialog; do not use the browse feature. when you are connecting(refer-below one)

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Thursday, March 07, 2013 3:11 PM
  • Are you trying for the contained database, if so can you please check in the state 5 column under the below link-

    then specify the database name explicitly in the options tab of the connection dialog; do not use the browse feature. when you are connecting(refer-below one)

    http://sqlblog.com/blogs/aaron_bertrand/archive/2011/01/14/sql-server-v-next-denali-additional-states-for-error-18456.aspx


    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.


    In this case no im thinking it might have been from ad... I will make another change and find out :).
    Thursday, March 07, 2013 3:24 PM
  • State 5 means that the login does not exist. And it's not a SID mismatch; that usually leads to the quirky "infrasrtucture" error. If you have a case-sensitive collation, check that the case matches what's sys.server_principals.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, March 07, 2013 10:41 PM
  • State 5 means that the login does not exist. And it's not a SID mismatch; that usually leads to the quirky "infrasrtucture" error. If you have a case-sensitive collation, check that the case matches what's sys.server_principals.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Oh yeah. State 11. Thanks for correction!

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Thursday, March 07, 2013 11:27 PM