Thursday, September 17, 2009

Cannot connect to SQL Server 2005 on Vista

If you are reading this article it means you already ran into the problem of connection (login) issue with SQL Server 2005 on Windows Vista. Let me explain the scenario:

I opened up SQL Server Management Studio and tried to log in using Windows Authentication but SQL Server came up with login failed error (Login Failed for user blabla, Error: 18456)
I tried using SQL Server Authentication and typed 'sa' (without quotes obviously) as username and the password. To my surprise i faced another error that the user sa is not a trusted user. Now i was wondering how to log in and change the settings or reset the passwords. The "sa" account was disabled and windows authentication was not working as required. I thought maybe due to my changing the password for my user account it happend, so I changed my windows account password back to the previous one however the attempt to connect to SQL Server was again failed. After a little thought I came to the conclusion that Windows Vista User Account Control is the culprit here.
So what i did was as follows:

1. Right click the shortcut or executable for SQL Server Management Studio and Select Run as Adminsitrator
2. Select the server name for SQL Server installed on the machine
3. Select Windows Authentication and click Connect. Vola! it worked this time.
4. In Object Manager Click Security -> Logins Right click Logins and select New Login
5. Search and add your windows account. e.g: ComputerName\TestUser
6. Select Server Roles on the Left and check sysadmin box on the right at the very bottom of defined roles
7. Click OK. That's it, you are done now.
8. Also remember to enable "sa" account and give it a strong password if you are using Mixed Mode Authentication.

I hope it helps.
There was an error in this gadget