I decided to post the solution to this problem that I believe many people have encountered. After trying for 2 days, I finally found the solution to it and am just very frustrated to how simple it actually was and that it wasn’t explicitly mentioned in the setup of MSSQL.

I am using Windows Vista SP2.

If you’ve tried other methods that are posted online (e.g. Setting Advanced Settings for Windows Firewall, Enabling Pipeline and TCP/IP, etc) and it doesn’t seem to work, perhaps the following method will work:

1) Go to All Programs -> Microsoft SQL Server 2005 -> Configuration Tools -> SQL Server Surface Area Configuration
2) Click on Add New Administrator
3) On the left window, under your (Computer name, e.g. USER-PC) click on Member of SQL Server SysAdmin role on (Computer name)
4) Click on the > button
5) Click on OK

Try connecting to MS SQL Server 2005 using SQL Server Management Studio Express using Windows Authentication and it should work now. 🙂