Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm not a DBA, I'm a developer - and I'm having trouble with SQL Server Management studio. I installed SQL Server 2008 Standard on Windows 2008 Server R2, and according to Sql Server Configuration Manager, I've got two instances: OFFICESERVERS (for sharepoint) and MSSQLSERVER. When I open SQL Server Management Studio I can only discover OFFICESERVERS. I've checked the protocol configuration for both instances and didn't see anything that indicates to me why this would be.

Any hints?

share|improve this question
add comment

6 Answers

up vote 2 down vote accepted

So when you connect to (local) or just the name of the machine (since MSSQLSERVER refers to the default instance), you are unable to connect? Is the SQL Server (MSSQLSERVER) service running?

share|improve this answer
    
connecting with (local) seems to have worked. Thanks. –  Ben Collins Aug 6 '09 at 21:53
add comment

Well, a few things since you didn't give us enough info :

  1. First of all, MSSQLSERVER may be a "named instance" . first I would try connecting to the non-named server instance called "(local)" and if that doesnt work try the named-instance ".\MSSQLSERVER"
  2. If your trying to login as "sa" user, enable the user because its disabled by default.
  3. Also, enable "SQL Authentication" because its not enabled by default either.
  4. a previous posters suggesiton of making sure that SQL Server Browser service is running is important.
  5. some people also like to enable the TCP port 1433 because its disabled by default also.
share|improve this answer
add comment

I had this same problem when I installed SQL Server 2008 R2 developer on a machine that already had an earlier instance of SQL Server Express installed for a specific application.

I was able to work around this by using SQL Server Configuration Manager to give each of the instances of SQL Server (other than the dedicated one for the specific application) a specific TCP/IP port (in Configuration Manager, look for Network Configuration, Protocols for the specific instance, and double-click on TCP/IP under protocol name - be sure to both enable the TCP/IP and select a non-used port, like 14330 or 14331)

Then, once I had assigned each instance its own TCP/IP port, use SQL Server Management Studio to connect to, e.g., .\MSSQLSERVER,14330 (be sure to use a COMMA, not a COLON, to separate the instance name and the port!), and you should be able to connect.

I did not need the SQL Server Browser service, probably because I was setting my own port numbers. In fact, SQL Server Browser did not help at all.

James Nachbar www.plastic.org

share|improve this answer
add comment

Although James Nachba's solution helped, I solved this a little differently. I didn't need a workaround using different, non-standard ports. I had the exact same problem, and the exact situation James described (installed SQL express edition first, removed that and replaced it with SQL 2008 R2).

In the SQL server configuration manager, there is an item called SQL server network configuration, under protocols, I selected the properties of the TCP/IP protocol. There is a tab "IP Addresses" there, and at the bottom of the list is an entry called IPAll. In my case the TCP port was empty. I entered the SQL standard port 1433 in there and I was able to connect.

HTH, Erik Oosterwaal

share|improve this answer
add comment

Make sure the SQL Server Browser service is started - that is what determines which instance you are connecting to.

share|improve this answer
    
The SQL Server Browser service is needed for named instances. It should be needed for a default instance. –  K. Brian Kelley Aug 6 '09 at 21:11
1  
Also just verified that for a locally running instance, SQL Server Browser service is not needed for a Named Instance, either. –  K. Brian Kelley Aug 6 '09 at 21:12
    
The SQL Browser is used by remote users to find the TCP port that the instance on the server is using (both named and default as long as they aren't listening on port 1433). Local connections don't use TCP ports to connect. –  mrdenny Aug 6 '09 at 21:36
    
Well, it's not started, but I can't get it to start. I found other forums that suggest that all SQL Server services be stopped first and then to start the browser service, but that doesn't work for me. –  Ben Collins Aug 6 '09 at 21:49
add comment

I had the same issue and suspect that this may have been caused by some recent Microsoft Updates. After trying all the suggestions unsuccesfuly I did a little more digging around and found that all the protocols in SQL Server had been switched off. The solution was to open SQL Server Configuration Manager, open SQL Server Network Configuration and click on Protocols for SQLEXPRESS2008 (this is my instance name). I discovered all protocols were disabled so I enabled TCP/IP, Named Pipes and Shared Memory and was then able to connect to SQL using Microsoft SQl Server Management Studio.

Hope this helps.

Ivan

share|improve this answer
add comment

protected by Jeff Atwood Jan 6 '11 at 10:08

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.