5

I'm not a database administrator; 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?

6 Answers 6

3

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?

1
  • connecting with (local) seems to have worked. Thanks. Aug 6, 2009 at 21:53
3

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

1
  • Some heroes wear capes, others post about steps on how to resolve connection issues through sql server configuration manager on serverfault which help people 14 years later.. BTW, you may need to restart the sql server local instance service in services. Thanks for your help!
    – Jwags
    Feb 22 at 15:15
2

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

  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 doesn't work, try the named-instance ".\MSSQLSERVER"
  2. If you're trying to login as "sa" user, enable the user because it's disabled by default.
  3. Also, enable "SQL Authentication" because it's not enabled by default either.
  4. A previous posters suggestion of making sure that SQL Server Browser service is running is important.
  5. Some people also like to enable the TCP port 1433 because it's disabled by default also.
1

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

0

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

4
  • The SQL Server Browser service is needed for named instances. It should be needed for a default instance. Aug 6, 2009 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. Aug 6, 2009 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, 2009 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. Aug 6, 2009 at 21:49
0

I had the same issue and suspect that this may have been caused by some recent Microsoft updates.

After trying all the suggestions unsuccessfully 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 Server using Microsoft SQL Server Management Studio.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .