Connect to sql server 2008 using Microsoft Server Management Studio

Question

I have difficulties connecting to my MS SQL Server and I can't find tutorial or answer how to do this, even though I think this should be something pretty standard.

My current setup is this - I have freshly new Installed Visual Studio 2010 and followed Beth Massi's article Step-by-Step: Installing SQL Server Management Studio 2008 Express after Visual Studio 2010 : http://blogs.msdn.com/b/bethmassi/archive/2011/02/18/step-by-step-installing-sql-server-management-studio-2008-express-after-visual-studio-2010.aspx

But I'm not able to connect to a SQL Server. In my Configuration Tools\SQL Server Configuration Manager I see this when I open it :

I set to Enable all TCP\IP and Named Pipes (Just saw that this may be a problem) and in the properties of the SQL Server (SQLEXPRESS) from Services I see this:

I also see in the Log On tab this :

some sort of password (not entered by me) it seems to be something default, but since I'm not sure where the problem is I try to show everything I think may have something to do with solving my problem.

At the end I tried different options to connect to my SQL Server form the Management Studio I ended up with this:

As you see I get an error (included in order to be able to read it). I read the the machine name may be required so my User Account name is Xman and my machine's name is Xman-PC. Eveh though my printscreen image is not from this try I made an attempt to connect to the Server with XMAN-PC\SQLEXPRESS but again - no success.\

Could you point me to a good tutorial on how to connect to a MS SQL Server (I can't believe there's nothing that explains in simple steps what you need to do to connect to a server) or could you tell me how to set-up my configurations so I actually can start to work with databases?

P.S

Forgot to mention in Command Prompyt - sqlcmd command also returns error - Could not open SQL connection.

P.S

Finally I managed to connect using Windows Authentication and (local)\sqlexpress for ServerName. Which at least means that everything is working. But now even more I would like to know how to connect using SQL Server Authentication. It must be something default as it was for Windows Authentication?!

Solution

Looks like you've done the hard work - last step will be turning on SQL Server Authentication.

Since you installed with the default values, only Windows Authentication is allowed; you need to set it to SQL Server and Windows Authentication mode. You can do this through Management Studio through the instance Properties -> Security.

See this link for full details: Change Server Authentication Mode.

Comments

[Leron]

A connection was successfully established with the server but then an error occurred during the login process. (provide : Shared memory provider, error : 0 - No process is on the other end of the pipe. - any idea what may cause this error?

[Leron]

Sorry, my bad, this was cause because I wasn't activate the sa login. But yet I cant connect. It seems that the Server Name is by default not the same for the SQL Server Authentication

[Leron]

No idea why it has to be so difficult but finally I made it. Thanks. Your answer and this - social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread/‌​… made it possible. In case someone else is trying to do the same.