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

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.

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.

it works. Julina Man you are awesome, you made everybody look like fool. I did the same (running as adminisrator ) for lot of other programs, but forgot to try with SQL and wasted Half a day. by the way i am using windows-7

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?

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.

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

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.

“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”

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?

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.

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 ;-)

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.

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.

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

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?

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

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

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

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

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.

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.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.