Cant connect to SQL 2005 Express with SQL Server Management Studio Express

I've seen the following error posted when people try to connect to their server via their applications but didn't see anyone having problems when trying to connect with SQL Server Management Studio Express. Am I missing something here? This is the error:

<<
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 2)
>
The server is not remote so I don't see why I need to enable remote connection. I am also using the default connection settings.

Are U using 'Windows Authentication' or SQL Server Auth?

While trying to login as 'sa' on my SQLServ2005 Express, I'm getting a bunch of errors like...

Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

No idea why !!!

I thought 'sa' was the ultimate super-user login. If 'sa' is not a trusted connection, then who is... sounds like a big joke !!

|||

I figured out the solution to my problem. By default, the Server Managaement Studio tried to connect to my computer name. It turns out when you install SQL 2000 Express, the default instance name is SQLEXPRESS. So when connecting, I just changed the server name to <computer name>\SQLEXPRESS and it worked!

As to your problem BugSlayer, I have seen that error before. It was due to the fact that the server was configured to allow only windows authentication. It has to be set to "mixed authentication" or "both windows and sql authentication" (I forget what the exact words were). I tried to look for the setting but couldn't find it. I set mine explicitly during installation. Hope that helps.

|||

Thanks NthPixel.

Actually my installation of SQL 2005 Express was an accident waiting to happen. I had installed on top of an existing MSDE (from VS 7.0) and for reasons unknown the 'sa' account was locked out PLUS built-in/Administrators did not have any grant rights. A classic catch-22 situation !!

Had to uninstall both SQLExpr & MSDE and reinstall SQLExpr with mixed auth mode this time, and now I'm able to use both types of authentications. Although our symptons match, the problem on my machine basically arised from upgrading MSDE to SQLServer 2005 Express.

If any of U have such a setup, please remove and simplify it before it blows up in to a massive disaster at some future critical juncture.