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?
This question is related to
sql-server
sql-server-2008-r2
I know, this problem can be faced by so many people and many of them have uninstalled and re-installed the sql server for resolving this issue. In my observation the problem of not connecting the database service locally is just because of your network connection you are using, in most of the cases these problems will come when you are using wi-fi network.
Solution is, if you are using wi-fi then just right click on status of the network and get the ip details and enter the same ip in sql server name, it will work. Regards Vishwajeet
Follow these steps to connect with SQL Server 2008 r2 (windows authentication)
Step 1: Goto Control Panel --> Administrator Tools --> Services select SQL SERVER (MSSQLSERVER) and double click on it
Step 2: Click on start Service
Step 3: Now login to SQL server with Windows authentication and use user name : (local)
Enjoy ...
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!
I have the same error but with different case. Let me quote the solution from here:
Luckly I also have the same set up on my desktop. I have installed first default instance and then Sql Express. Everything is fine for me for several days. Then I tried connecting the way you trying, i.e with MachineName\MsSqlServer to default instance and I got exctaly the same error.
So the solution is when you trying to connect to default instance you don't need to provide instance name.(well this is something puzzled me, why it is failing when we are giving instance name when it is a default instance? Is it some bug, don't know)
Just try with - PC-NAME and everything will be fine. PC-NAME is the MSSQLServer instance.
Edit : Well after reading your question again I realized that you are not aware of the fact that MSSQLSERVER is the default instance of Sql Server. And for connecting to default instance (MSSQLSERVER) you don't need to provide the instance name in connection string. The "MachineName" is itself means "MachineName\MSSQLSERVER".
Your "SQL Server Browser" service has to be started too.
Browse to Computer Management > Services.
Find find "SQL Server Browser"
Hope it helps.
I also received this error when the service stopped. Here's another path to start your service...
Note: As Kenan stated, if your services Startup Type is not set to Automatic, then you probably want to double click on the service and set it to Automatic.
I had this problem. My solution is: change same password of other in windowns. Restart Service (check logon in tab Service SQL).
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:
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:
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.
Source: Stackoverflow.com