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