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: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

——————————
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

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

3. Type In Login Name as ComputerName\UserName

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

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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

107 thoughts on “SQL SERVER 2005 – FIX Error: 18456 : VISTA Windows Authentication

  1. Usually during the install on Vista – once completed, you get the “User Provisioning Tool” prompt at the end of the install – this is where you can assign admin privileges to user accounts so that you (and others) can log in after install.
    That way you don’t have to know the sa password to create logins.
    Makes life much easier when not in Mixed Mode.

  2. Sorry, should have mentioned that you will need to install SP2 in order to get full functionality on Vista. And I’m pretty sure the User Provisioning Tool was introduced in SP2 – but I can’t remember for sure.

  3. I have installed SQL Server 2005 Developer edition on a new Vista machine, I have Service pack 1 installed but not 2, had not known of it yet

    I have gotten the same erorr message

    I am very new to this, I am trying to do an Online course, but cannot get past this install to even try the program

    I do not have a network, so how would I be able to set up a SA account and passwerod to go through this fix?

    I find this unreal that during setup I was not setup as default admin

    Any help or leads would be gratefully used

    Hank D

  4. I just right clicked server manager studio and “run as administrator” and it logged in with windows authentication.

    Hope it helps someone

  5. I have installed SQL Server 2005 Express Edition with Management Studio Express on my new laptop running Vista Home Premium (ouch) and I am getting authentication errors (18456 ) every time. I tried run as admin with no luck. Both accounts I created have admin rights and both wont work.
    I tried reinstalling SQL twice as well. I disabled all security features on Vista thinking that might be the problem. I have this setup running on 2 XP Pro machines and have never seen this error. Anyone have any ideas on how to solve this?

  6. Note: I am just trying to connect to the database engine using Windows authentication, not mixed mode. When trying to setup an account using SQL authentication I receive the same error when testing.

  7. 3rd time installing was the charm. I had a currupt CAB file that I think was causing this error connecting to the local machine/DB. Glad that’s gone. I was starting to think that Vista was the problem.

  8. Hi Pinal,
    I have selected only Windows Authentication mode and now I am on Vista and not able to login. Is there any way to overcome this probelm or Do I need to re-install SQL Server?? Let me know Thanks

  9. Julian

    I just right clicked server manager studio and “run as administrator” and it logged in with windows authentication.

    Hope it helps someone

    —–
    Julian,
    It has helped me. THANKS MUCH.
    I re-installed 2005 Standard edition and after 2 hrs of breaking my head, I came across your message.
    Thanks a ton

  10. if you right click the exe and select properties, then the compatibility tab, at the bottom is a checkbox “Run as Administrator” check that and click apply. Now it wil run as admin every time. No need to right click it again.

  11. I also right clicked server manager studio and “run as administrator” and then logged in with windows authentication.
    I use vista business with sp1.

  12. “you cannot login using SA username and password and go to Security” because in vista ultimate you set up Sql Server 2005 with Windows Authentication.
    So if you set up your sql server 2005 with Windows Authentication then you cannot use SQL Server Authentication.
    For removing error no 18456 you have to use method listed below.

    Use “SQL Server 2005 Surface Area Configuration” to add yourself as an administrator on the SQL Server Instance. In Vista, a Windows admin is not a SQL admin by default – they need to be added.”

    I also find method to configure Report Server in Vista Ultimate for SQL Server 2005. If anybody needs it then mail me on “hardikmehta75@yahoo.co.in”

  13. Julian,

    thank you so much…was pulling my hair out until you saved the day!

    Other posts suggest you go into SQL Server 2005 Surface Area Configuration and select “Add New Administrator” however, when I click “Add New Administrator” nothing happens.

    I was able to logon as Julian suggested by right clicking, select “Run as Administrator”.

  14. I have detach the one database from my server using droping all connection to remove the log files (because it was filling up all my database space). Now I cant login to SQL Server from my server using Administrator Account. It gives me “ERROR 18456″. what are my options?

  15. This error is easy to solve:
    Right click on SQL Server Management Studio and click On “Run As Administrator” which is usually the third option

  16. Pingback: SQL SERVER - Create Database Error in Windows Vista Journey to SQL Authority with Pinal Dave

  17. Thanks for the article PINAL, it solved my problem! I just had the same problem trying to connect to SQL Server 2005 instance using Windows Athentication. Cheers!

  18. Wow varma helped me after 2 days of nothing..Right click server management studio and run as admin. Then you can connect honly cow..LOL

  19. Hey, who ever thought to try Run AS Administrator…may have remembered something from Microsoft Academy. Thanks for reminding me.

    I too am completely new to Management Studion and have no idea how/or where, I would type the SA solution (though I have seen it posted elsewhere also).

    Any ideas?

  20. Hi Pinal,

    It’s fine that after selecting ‘Run as Administrator’ or by creating an user with Admin rights, we can get rid of this error (18456 and can connect to the SQL Server 2K5 Mgmt Studio.

    But there’s another problem which is external to mgmt studio. When you try to connect to the same sql 2k5 db instance from an external source (lets say an SSIS package), then we dont get any databases populated in the db drop down box, in the Connections dialog box in SSIS. Hence, we cannot select any db and set up a connection.

    Can you help me solve this problem?

    Is it that I need to install a patch or something?

    I’m running my SQL Server 2K5 on Windows Vista.

    Regards,
    Bhushan

  21. I got this problem resolved.

    FYI, when we run SQL Server 2k5 on vista, by default, the administrators are not considered as users with admin privileges for the sql db instance. You need to specify yourself as an administrator somewhere within the sql server known area. This is pretty simple (but actually I had to hunt a lot to get this point :-( ).

    Click on Start –> Programs –> Microsoft SQL Server 2005 –> Configuration Tools –> SQL Server Surface Area Configuration. After this on the newly opened dialog box, click on the link ‘Add an Administrator’. This tool is termed as an User Provisioning Tool. From here, you can drag the desired user (which should have adminstrative rights) from left list box to right list box.

    Now, try connecting to the db instance, without right-clicking and selecting ‘Run as Administrator’. It will get connected as a charm ;-)

    Thanks!
    Bhushan

  22. Great Work..pinaldave…I was stuck with same thing in Windows 7.
    Now all the problem is solved..
    Thank you very much.

  23. hi, well I have a problem whit SQL SEVER MANAGEMENT in Windows vista Home premium, ERROR 18456, I try everyting,
    example: sa and password, windows authentication mode, sql authentication, nothing works I realy need help…

    and 1 question: how can i know what’s instances u have installed in my pc ?

    tnks any way :-D

  24. hey i ve installed visual studio 2005 with sql express 2005 on vista ;but it dont give me permission to do any thing when i connect using windows auth…

    i want to change the auth mode to both and i want to enable the sa login
    or create a new login

    but it says no permission to do such thing…

    plz reply me on my email ….
    thnx

  25. What about connecting to SQL Server 2000 with Vista? We are having problems with that. Connections work from XP to the SQL Server 2000 Server, but not from Vista.

  26. Alternate Work Around:

    If you used an account to run the SQL service other then “local service” or “network service” (such as SomeDomain\SQLAccount, SomeComputer\SQLAccount)

    1). Log on to the computer using the service account you selected or created

    3.) Security–>Logins–>Right Click “New Login…”

    2.) Add ‘administrator’ or any other account you may be trying to access the database with.

    This should help for those who did not used mixed authentication. Once you’re done creating the SQL Admin account for ‘Administrator,’ you can then enable ‘Deny Local Logon’ for the SQL server and your special service account.

  27. Thanks for the information – but my problem is regular users that shouldn’t have to have SYSADMIN rights or Administrator rights to access SQL Server 2000 (remotely) from Vista. I don’t want all my users to have SYSADMIN access to SQL Server. Is there really no other way to give regular users (could be hundreds or thousands) access to SQL Server 2000 (again, remotely) from their own Vista workstations than making every one of them a SYSADMIN user or an Administrator?

    Can the users be given access to SQL Server from Vista via a Service Account or a Group with limited rights?

    These are users who are connecting to a Sharepoint Portal database on SQL Server 2000 from their Vista workstations. We don’t want them doing ‘run as administrator’ simply to use applications.

    Thanks!

  28. 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.

  29. 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

  30. 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.

  31. 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.

  32. 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..

  33. 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

  34. 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

  35. 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

  36. User “Company\admin” exists in the Windows Administrator group and also available in the SQL server logins but I am still getting the 18456 error, Login failed for user ‘Company\admin’ [CLIENT: ].

    Any help would be appreciated.

  37. Had the error 18456 problem. This forum got me the idea that it was indeed a security issue that didnt allow me to log on to the database server (analysis was working fine). So closed it, and restarted with a right click -> run as administrator. Now works like a charm :) easy fix

  38. Heloo

    i am using windows7 with sql server 2008.

    My problem is i couldn’t login by sql server authentication

    but i can login by using windows authentication.

    If i try to login by sql server authentication login error occurred. The details are given below…. I hope help from here..

    Server Name: PRAJITH-PC\SQLEXPRESS
    Error Number: 18456
    Severity: 14
    State: 1
    Line Number: 65536
    ———————————

    Thanks in advance…

    Prajith

  39. i found this solution very helpful. I have resolved my problem of login as ‘windows authentication’ by viewing the given demo. thanks alot.

  40. i have sucessfully connected to the the server but when it comes to database attatchment there is a error as

    “cannot attatch a database with the same name as an existing database”

  41. Ossssom thnxxxxxxxxxxx a lot sir it really worked i hav been facing this problm quit long time ago but was never able to figureout what exactly is going wrong and how to handle it but now itz jus a matter of fun………….
    thnxxxxxxxxx.

  42. Please help…

    same error for two SQL server 2008 R2 , on same computer (virtual PC), i can’t connect two server’s always same error.
    Maybe is problem in “name path” server MACHINENAME\SERVERNAME\USER (login)

    please help!

    THX

      • Because i think that the best way to connect and learn about connection (two or more DT servers) is if i had two equally system (OS and server)…
        Now am stuck here, regardless of reading and doing stuff from luts of tutorial.

        thx for replay…

        i still can’t connect two server’s

  43. OK, thanks anyway..

    now i try to create a new login (SQL express 2008R2 & SQL express 2005) , with the tips from the Pinal (above)

    now….i get a new error ;

    Create failed for Login ‘IGZYY-PC\SQLEXPRESS05\igorSQL’.

    Windows NT user or group ‘IGZYY-PC\SQLEXPRESS05\igorSQL’ not found. Check the name again. (Microsoft SQL Server, Error: 15401)
    ———————————————————————————————-

    > WTF!!!!

    same thing with the creation of login (“sa” login , witch is ok) , i just can’t create a new login with the windows authority.

    Please help

  44. THX all..

    It was problem with virtual VMware , remember all internet propertis on virtual machine set to “default” and NAT bridget onnection…

    Check with “ping” and shutdown firewall

    that’s it…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s