SQL SERVER 2005 – FIX Error: 18456 : VISTA Windows Authentication

In previous post I have mentioned about SQL SERVER 2005 – Vista Ultimate and SQL Server 2005 DEV Edition. There was one simple issue with the installation. I was not able to login using windows authentication method. I was able to successful login using sa username and password.

I kept on receiving following error.

TITLE: Connect to Server
——————————
Cannot connect to SQLAUTHORITY.
——————————
ADDITIONAL INFORMATION:

Login failed for user ‘SQLAUTHORITY\Pinal’. (Microsoft SQL Server, Error: 18456)

For help, click:

——————————
BUTTONS:
OK
——————————

After a while I realize that this may be due to one needs Administrator rights to do any task in SQL Server. I added the my current login as administrator in SQL Server and next time I was able to login successfully using Windows Authentication.

Following diagrams explains the fix.

1. Error while using Windows Authentication

SQL SERVER 2005 - FIX Error: 18456 : VISTA Windows Authentication vistaauth

2. Login using SA username and password and go to Security >> New Login

SQL SERVER 2005 - FIX Error: 18456 : VISTA Windows Authentication vistaauth1

3. Type In Login Name as ComputerName\UserName

SQL SERVER 2005 - FIX Error: 18456 : VISTA Windows Authentication vistaauth2

4. Click Server Roles and check sysadmin server role, after that click OK.

SQL SERVER 2005 - FIX Error: 18456 : VISTA Windows Authentication vistaauth3

5. Now login using Windows Authentication and it should work fine.

SQL SERVER 2005 - FIX Error: 18456 : VISTA Windows Authentication vistaauth4

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Error Messages, SQL Server Security, Vista
Previous Post
SQL SERVER – Find Table in Every Database of SQL Server – Part 2 Extension
Next Post
SQL SERVER – Create Database Error in Windows Vista

Related Posts

106 Comments. Leave new

  • Damion Sandidge
    July 29, 2009 6:47 am

    Thank you this was helpful

    I had the same issue with connecting from a windows 2003 server (non domain) to a SQL 2005 server on Windows 2008 server.

    Reply
  • Thanks.. resolved the issue with your help

    Reply
  • Thanks Pinal,

    This information is very useful for me.

    Reply
  • saravana kumar
    August 7, 2009 8:38 pm

    good evening sir,
    i installed sql server 2005,but cant create database by windows authentication and also i dont have username and password for sql server authenticatin,
    it doesnt asked me the user name and password while it was installing what should i do please help me sir

    Reply
  • Thanks !!!!

    Reply
  • Thanks Pinal,

    The above information was useful and now I can run sql server in Windows Authentication Mode.

    Reply
  • Julian and Bhushan:
    Thank you for your solutions! “Run as Admin” got me in and the User Provisioning Tool did the trick for the long term. Glad I found your posts.

    Reply
  • thanks, simple solution to frustrating problem. It’s a pity MS are not so forthcoming in their literature.

    Regards
    Derek

    Reply
  • Only install sql server 2005 SP3 and run as administrator and you can access without problems with windows authentication.

    Reply
  • I can’t login even with SA username and password, neither with “run as Admin”. Should i unninstall Sql server 2005 and install it again?

    Reply
  • Thanks Julian your solution is the simplest and the only one that works for me.

    Reply
  • Hi All,

    I also decided to move to Vista with VS 2005 and SQL Management Studio Express and faced troubles accessing the databases through ASP.net or attaching/detaching it in Management Studio. I was logged in as my developer login (with Administrative rights) on Vista. I browsed through the net for various problems i faced but ultimately found the resolution to most of them. And i would like to share it with you, so that to save much time of most of you.

    – That is Open the Management Studio by Right Clicking its shortcut and then Run As Administrator
    – Similarly Open the Visual Studio 2005 Studio by Right Clicking its shortcut and then Run As Administrator

    And i would recommend to use this key at first instance when you are having some security problem on Vista.

    Further, i would like to know from any of you to tell how can we set the Run As Administrator option once for all so that we dont have to do this again and again for each application on Vista?

    Regards.

    Reply
  • Thank you. You saved my day

    Reply
  • hello sir….
    I have installed sql server 2005 in windows 7.I made only windows authetication mode.I cant connect using this.error:18456. but I can connect with user/sqlexpress mode
    now I want to creat sqlserver authentication .wat should I hav 2 do..pls help..

    Reply
  • I have installed SQL server 2008 on windows server 2008 R2. windows authentication mode is working but SQL Server authentication mode is not working giving errors login failures error code 18456, I have not any SQL user I don’t know the password of user “sa” I created user in SQL Management studio, user created but could not login as sql server authentication. Please help

    Reply
  • Hello Gautam,

    Verify that:
    1. Server’s authentication mode is : SQL server and Windows Authentication mode.
    2. guest user is enabled in the default database of new SQL user.

    Regards,
    Pinal Dave

    Reply
  • Many thanks

    Reply
  • excellent sir,
    thanks a lot

    Reply
  • Thank you! This worked perfectly on Windows 7.
    1. Right Click on Sql Server Mgmt Studio app and choose login as administrator
    2. Then the studio opens, login under Windows Authentication as is
    3. Then you can follow the directions at the top by pinaldave

    Reply
  • HI SIR ,
    THANK YOU VERY MUCH FOR YOUR SOLUTION IT REALLY HELPED ME A LOT.

    Reply

Leave a Reply