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
anil,
change the default database for that login and try again,
may be helpful
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
THANKS JULIAN!!! U’RE SO HELPFUL!!!
Thank you sooooo much
Jolian
right click and run as Admin solve my problem
sql error 18456
thaaaaaaaaaaaaanks alot
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!
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
Julian,
Thanks a lot! You made my day. ^_^
thank you for info.
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?
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
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
Great Work..pinaldave…I was stuck with same thing in Windows 7.
Now all the problem is solved..
Thank you very much.
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
Thanks a lot, It was very helpful.
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
Thanks for the post!!!
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.
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.
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!