locked
Domain Admin users cannot login RRS feed

  • Question

  • Is SQL Server sensitive to Domain group name? Like "Domain Admin"?

    I have user that belong to "myDomain\Domain Admin" group. Group is in SQL as sysadmin but user cannot login using domain credentials. When I move that user to a different domain group which that group is in SQL again as sysadmin my user is able to login. 

    Environment: SQL 2008 Standard Edition. 


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    Friday, June 12, 2015 7:41 PM

Answers

  • I think you are seeing the normal behavior of Windows. When you login as a member of an admin group, and attempt to pass your credentials, Windows passes all of your group memberships except the administrator credentials. This is a security measure. We you login using the "Run as administrator" option, you tell Windows that you want to pass the administrator credentials, too. By policy, most organizations want to discouraged this as it can lead to accidently running malicious software with administrative credentials, which would be very bad.

    You can work around this, either by using the "Run as administrator" option (not recommended) or by adding the necessary permissions through a non-administrator account. Either add the user to SQL Server as a login, or add the user to a non-administrator group, and add that group to SQL Server as a login. Then the user can connect to SQL Server without using the Administrator credentials; the access is still available, but it's safer.


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

    Tuesday, June 16, 2015 3:40 PM

All replies

  • Hello,

    Does this domain group belongs to another domain (a trusted domain)?

    Please share with use the “record” column of the login failed attempts that you will find on the ring buffer. Run the following query.

    select * from sys.dm_os_ring_buffers where ring_buffer_type = 'RING_BUFFER_SECURITY_ERROR'



    Copy the content of the “record” column and paste it on Notepad, then share it with us.



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Friday, June 12, 2015 8:41 PM
  • I doubt that SQL Server as a product is sensitive to certain names. But maybe the login for at that group is disabled?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, June 12, 2015 8:43 PM
  • Hi Alberto, 

    This domain group does not belong to any other domain at all. 

    The table had already some information in it but when I tried to login again multiple times did NOT add any new records to the table. So, my result set is nothing now. 


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Friday, June 12, 2015 11:06 PM
  • I doubt that SQL Server as a product is sensitive to certain names. But maybe the login for at that group is disabled?


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

    Erland, 

    I create it multiple times and drop the group in SQL and had same issue. 


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Friday, June 12, 2015 11:09 PM
  • Are you using "Run as administrator" so that Windows will pass admin credentials?


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

    Monday, June 15, 2015 3:40 PM
  • No, I am NOT running that as Administrator. The user which belong to "Domain Admin" group, login normal and try to open SSMS and getting permission denied to connect to the instance. 

    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    Monday, June 15, 2015 3:42 PM
  • Here is the stack trace of error while try to login:

    ===================================
    
    Cannot connect to myServer.
    
    ===================================
    
    Login failed for user 'myDomain\testDomainAdminUser'. (.Net SqlClient Data Provider)
    
    ------------------------------
    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476
    
    ------------------------------
    Server Name: myServer
    Error Number: 18456
    Severity: 14
    State: 1
    Line Number: 65536
    
    
    ------------------------------
    Program Location:
    
       at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
       at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
       at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
       at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject, Boolean withFailover)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
       at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.


    Monday, June 15, 2015 5:49 PM
  • Are you using "Run as administrator" so that Windows will pass admin credentials?


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

    Rick, 

    If I ran SSMS as Administrator is goes fine. Why should I ran SSMS as administrator to get in? 

    In nutshell, here is again the case:

    1- "Domain Admin" group is under login as sysAdmin

    2- A specific user is part of "Domain Admin" group in Active Directory (Enable)

    3- That specific user credentials is NOT in SQL Server but the group is as mentioned in #1

    4- If we add the user to different group and add the group to SQL as sysAdmin, user is able to login without creating that user in SQL!!!!

    5- Environment is:

    Microsoft SQL Server 2008 (SP4) - 10.0.6000.29 (X64) 
    Sep  3 2014 04:11:34 
    Copyright (c) 1988-2008 Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (VM)


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Monday, June 15, 2015 6:08 PM
  • If I ran SSMS as Administrator is goes fine. Why should I ran SSMS as administrator to get in? 

    Because else Windows will not add the Domain Admin token to your process?

    Install Process Explorer, if you don't already have it. Start SSMS normally. Find SSMS in Process Explorer. Right-click, select Properties. Go to the Security tab. Look through the list of tokens. Do you see Domain Admin? What is the flag?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, June 15, 2015 9:23 PM
  • Erland, 

    I downloaded Process Explorer and Domain Admin is NOT showing up under security tab.


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Monday, June 15, 2015 9:50 PM
  • I think you are seeing the normal behavior of Windows. When you login as a member of an admin group, and attempt to pass your credentials, Windows passes all of your group memberships except the administrator credentials. This is a security measure. We you login using the "Run as administrator" option, you tell Windows that you want to pass the administrator credentials, too. By policy, most organizations want to discouraged this as it can lead to accidently running malicious software with administrative credentials, which would be very bad.

    You can work around this, either by using the "Run as administrator" option (not recommended) or by adding the necessary permissions through a non-administrator account. Either add the user to SQL Server as a login, or add the user to a non-administrator group, and add that group to SQL Server as a login. Then the user can connect to SQL Server without using the Administrator credentials; the access is still available, but it's safer.


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

    Tuesday, June 16, 2015 3:40 PM
  • I downloaded Process Explorer and Domain Admin is NOT showing up under security tab.

    No wonder then that SQL Server does not permit you to login.

    That is, it is not SQL Server have any special rules tied to Domain Admin, but Windows. I think Rick's explanation is the best you can get in an SQL Server forum. (A Windows information may give you more information.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, June 16, 2015 9:07 PM