SQL Server 2008 R2 can't connect to local data

Question

I am using SQL Server 2008 R2 Express.

I first installed SQL Server 2008 R2 Express Management Studio and then I installed SQL Server 2008 R2 Express. I have the instance SQLEXPRESS running and it is set to automatic.

I am trying to connect to it locally using Windows authentication - server name is set to local and the username is grayed out and set to my profile username.

When I try to connect I get the following error:

Have I installed the wrong SQL Server Management Studio?

Answer 1

If your instance is called SQLEXPRESS, then you need to use .\SQLEXPRESS or (local)\SQLEXPRESS or yourMachineName\SQLEXPRESS as your server name - if you have a named instance, you need to specify that name of the instance in your server name.

Answer 2

Okay so there might be various reasons behind Sql Server Management Studio's(SSMS) above behaviour:

1.It seems that if our SSMS hasn't been opened for quite some while, the OS puts it to sleep.The solution is to manually activate our SQL server as shown below:

• Go to Computer Management-->Services and Applications-->Services. As you see that the status of this service is currently blank which means that it has stopped.
• Double click the SQL Server option and a wizard box will popup as shown below.Set the startup type to "Automatic" and click on the start button which will start our SQL service.
• Now check the status of your SQL Server. It will display as "Running".
• Also you need to check that other associated services which are also required by our SQL Server to fully function are also up and running such as SQL Server Browser,SQL Server Agent,etc.

2.The second reason could be due to incorrect credentials entered.So enter in the correct credentials.

3.If you happen to forget your credentials then follow the below steps:

• First what you could do is sign in using "Windows Authentication" instead of "SQL Server Authentication".This will work only if you are logged in as administrator.
• Second case what if you forget your local server name? No issues simply use "." instead of your server name and it should work.

NOTE: This will only work for local server and not for remote server.To connect to a remote server you need to have an I.P. address of your remote server.

Answer 3

Lots of the above helped for me, plus the accepted answer, but since I was on an EC2 instance, I had no idea what my instance name was. Finally, I opened SQLServer Configuration Manager and in the Name column, use whatever is there as your connection server, so in my case, .\EC2SQLEXPRESS and worked great!