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
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.
Thanks.. resolved the issue with your help
Thanks Pinal,
This information is very useful for me.
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
Thanks !!!!
Thanks Pinal,
The above information was useful and now I can run sql server in Windows Authentication Mode.
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.
thanks, simple solution to frustrating problem. It’s a pity MS are not so forthcoming in their literature.
Regards
Derek
Only install sql server 2005 SP3 and run as administrator and you can access without problems with windows authentication.
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?
Thanks Julian your solution is the simplest and the only one that works for me.
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.
Thank you. You saved my day
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..
See earlier posts concerning “Run as Administrator” and others. Very Helpful
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
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
Many thanks
excellent sir,
thanks a lot
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
HI SIR ,
THANK YOU VERY MUCH FOR YOUR SOLUTION IT REALLY HELPED ME A LOT.