locked
SQL Server 2008 - Windows Authentication fails for an Administrator on Vista but not XP RRS feed

  • Question

  • Hello all, I just installed SQL Server 2008 on Vista Business SP1.  When installing it I added the entire BUILTIN\Administrators group as SQL Server Administrators. Now post-installation I'm logged onto Vista as an Administrator, the same account I installed with however when I try to logon to the Management Studio I receive "Login Failed" error 18456. This makes no sense to me.  Does anyone have an idea?  When installing SQL Server 2008 on XP using the identical settings I do not have this issue.

    The only difference I can think of with Vista vs. XP is that with XP I'm the windows Administrator when I'm logging in to SQL Server not just part of the Administrators group. Under Vista I have left the true Administrator disabled as it is by default.

    Thanks for any help,

    Mike




     

    Friday, January 16, 2009 6:10 PM

Answers

  • I found a partial answer in a blog, apparently it's something to do with UAC weirdness under Vista (what else right?).  If I right-click on the Management Studio icon and choose to Run as Administrator then I can logon with the current Administrator without errors. I don't understand why this should be necessary given that I've explicitly defined Administrators during the installation of the app.... but hey here's looking forward to Windows7.    

    Friday, January 16, 2009 7:36 PM

All replies

  • I found a partial answer in a blog, apparently it's something to do with UAC weirdness under Vista (what else right?).  If I right-click on the Management Studio icon and choose to Run as Administrator then I can logon with the current Administrator without errors. I don't understand why this should be necessary given that I've explicitly defined Administrators during the installation of the app.... but hey here's looking forward to Windows7.    

    Friday, January 16, 2009 7:36 PM
  • It seems that you did not run the Vista provisioning tool, right after the setup. This eleveates the priviledges of the user to make sure the user can access the server without any problems. It´s sqlprov.exe.

    -Jens K. Suessmeyer
    Saturday, January 17, 2009 4:09 PM
  • Thanks Jens,  however my experience is sqlprov.exe will not execute on a SQL Server 2008 machine due to certain dependencies which aren't in place (unless SQL Server 2005 is present) -- in fact sqlprov.exe is not even part of the SQL Server 2008 installation.  I personally think MS should've kept it around for deployment scenarios if nothing else.  The current situation is windows authentication (specifically on Vista with UAC enabled) fails when logging into the 08Management Studio -- this occurs even when the entire Administrators group has been provisioned during the installation and you are part of the Administrators group. The only workaround I'm aware of is to set advanced properties on the start menu shortcut to 'run as administrator'.  I'm interested to see how this plays out for SQL Server 2008 installations on Windows Server 2008 since it's closely related to Vista.    
    Monday, January 19, 2009 4:14 PM
  • In Windows Server 2008 as well you need to right click > run as administrator to login into SQL server. But once you do it the 1st time, you can add your windows/sql login explicitly after which you don't need to use run as admin option. Use the below command to do it,

    Use master
    Go
    sp_addsrvrolemember 
    'login name''sysadmin'


    - Deepak

    Deepak | Mark the answers if it helps to solve your problem |
    Tuesday, January 20, 2009 3:26 AM