locked
SOS: Cannot connect to SQL Server 2005 locally by Windows Authentication DOMAIN \ myname. But network connection is successful ! RRS feed

  • Question

  • Cannot connect to SQL Server 2005 locally by Windows Authentication DOMAIN \ myname.

    But network connection is successful.

     

    But SQL Server Logins contain   BUILTIN \ Administrators, and

                                                       BUILTIN \  Administrators contain DOMAIN \ Domain Admins, and

                                                       DOMAIN \ Domain Admins contain DOMAIN \ myname.

     

    Creating SQL Server Login with DOMAIN \ myname entail successful local connection.

     

    Removing SQL Server Login with DOMAIN \ myname and creating SQL Server Login with DOMAIN \ Domain Admins

    do NOT entail successful connection. Cannot connect!

    System:   Windows Server 2008.

    Error 18456

    Friday, September 17, 2010 8:30 PM

Answers

  • Your symptoms sound like you can connect as yourself when you have a login, but you cannot connect using your admin credentials if you don't have a login. This is the way it is supposed to work. Windows Server 2008 enforces User Access Control (UAC) like Windows Vista and Windows 7. That is, your administrator credentials are not used, unless you select Run as Administrator when you start the program (Management Studio). You have already found the best solution. Add your domain credentials as a SQL Server login.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by dmfr Monday, September 20, 2010 5:35 AM
    Friday, September 17, 2010 10:32 PM

All replies

  • Your symptoms sound like you can connect as yourself when you have a login, but you cannot connect using your admin credentials if you don't have a login. This is the way it is supposed to work. Windows Server 2008 enforces User Access Control (UAC) like Windows Vista and Windows 7. That is, your administrator credentials are not used, unless you select Run as Administrator when you start the program (Management Studio). You have already found the best solution. Add your domain credentials as a SQL Server login.
    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    • Marked as answer by dmfr Monday, September 20, 2010 5:35 AM
    Friday, September 17, 2010 10:32 PM
  • Thanks for your feedback!

    But we just wanted to have that "best solution" would work in general use  DOMAIN \ Domain Admins because we do not want every time to add:

    DOMAIN \ name1,

    DOMAIN \ name2,

     ... ,

    DOMAIN \ name250

    to SQL Server Logins!

    Why can not we use only once DOMAIN \ Domain Admins   ?

     

    Saturday, September 18, 2010 9:54 AM
  • Did you add

    DOMAIN \ name1,

    DOMAIN \ name2,

     ... ,

    DOMAIN \ name250

    to the administrator groups on that machine? You can create a group with all those logins and then create login for that group in SQL Server


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, September 19, 2010 6:48 AM
  • Yes of course !

    Administrator group on that machine is BUILTIN\Administrators and this group contains DOMAIN \ Domain Admins  and this last group contains 

    DOMAIN \ name1,

    DOMAIN \ name2,

     ... ,

    DOMAIN \ name250

    and BUILTIN \ Administrators have SQL Server Login as sysadmin.

    But still can not connect locally with DOMAIN \ nameK (K=1,...,250) !

    But despite this network connection is successful !

     

    Sunday, September 19, 2010 7:26 AM
  • Ok,what is exact error you are getting?

    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Sunday, September 19, 2010 12:20 PM
  • Thanks a lot, Rick!

    Helped me your answer : "... unless you select Run as Administrator when you start the program (Management Studio)".

    I can now connect by selection "Run as Administrator".

    User Access Control (UAC) is the very real reason.

     

    Monday, September 20, 2010 5:34 AM