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
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 (https://blog.sqlauthority.com)
106 Comments. Leave new
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.
Good Solution for Windows Authentication Problem
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
I just right clicked server manager studio and “run as administrator” and it logged in with windows authentication.
Hope it helps someone
Thanx…
U really helped me….
Thanks Julian, super easy fix.
Hi Julian,
Thanks a lot it worked for me. I hanged up almost half day with this connection problem.
wow nice, thx a lot
THank you…
I don’t know why your solution is not the first solutin.
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
tnx a ton
you are incredable
it’s too easy
a lot of thanks for you
really i interested with this smart solve which i didn’t find at microsoft site
It’s works with me , thanks
thanks..
Thanks. it has really helped me
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?
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.
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
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
Thank you very much. Your suggestion worked in my case.
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.
Great, thanks!
Thanks,
It was very useful…
Hi Julian,
What a simple solution to my frustrating problem .
Thanx a lot
Hi Jessie,
Nice solution it has helped me a lot
I also right clicked server manager studio and “run as administrator” and then logged in with windows authentication.
I use vista business with sp1.
“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”
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”.
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?
Additional Information
I am using Window Server 2008 and SQL Server 2008 so you know