none
SQL Server 2008 -- Can't Connect to Local Named Instance

    Question

  • I created a named instance on installation (MSSQLSERVER). I can connect to PC-NAME and PC-NAME\SQLEXPRESS, but not to MSSQLSERVER or PC-NAME\MSSQLSERVER (I get error 25, SQL Server Error 87: verify instance name is correct and that remote connections are allowed). I've enabled remote connections, TCP/IP and named pipes, and followed the advice from many other sites/threads based on hours of searching. I've tried re-installing SQL Server 2008 and repairing the instance, none of which have worked. Windows Firewall is also disabled, even though this is all happening on one computer.

     

    I've run out of ideas. Help?

    Tuesday, January 27, 2009 6:53 PM

Answers

  • Hi,

    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".

     


    Mangal Pardeshi
    SQL With Mangal
    Wednesday, January 28, 2009 5:03 AM

All replies

  • Did you restart the SQL Server service for the instance after enabling remote connections/protocols?  Also, is SQL Browser running?  Shouldn't be required on the local machine, but it's worth a shot.
    Aaron Alton | thehobt.blogspot.com
    Tuesday, January 27, 2009 11:05 PM
  • Yep. I restarted the SQL Server Service and the SQL Browser is running. Indeed, I restarted my system after restarting the services when that didn't work initially.
    Wednesday, January 28, 2009 12:24 AM
  • Hi,

    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".

     


    Mangal Pardeshi
    SQL With Mangal
    Wednesday, January 28, 2009 5:03 AM
  • Huh, that appears to be exactly the case. I entered in PC-NAME in and it connected without problems. Looks like AdventureWorks is installed on there, confirming that it is indeed the MSSQL Instance (I also checked the properties of the database to confirm this). Thanks!
    • Proposed as answer by mccandch Thursday, November 07, 2013 11:44 PM
    Wednesday, January 28, 2009 12:08 PM
  • Just to be clear, if you're accessing it by specifying the machine name only, it's not called a 'named instance'.  It's called the 'default instance' - you select this option when you install the instance.

    Glad to hear that your problem is sorted.


    Aaron Alton | thehobt.blogspot.com
    Wednesday, January 28, 2009 5:07 PM
  • Basically if we use only machine name as server name than it logins to SQL SERVER 2008 but if we provide \SQLSRVER or \SQLEXPRESS than it logins with compatibility of SQL SERVER 2005

    As date is valid data type 2008 onward.

    Wednesday, January 26, 2011 6:13 AM
  • i still can not connect :S

    when i go to browse for servers i dont find anything :S

    Friday, February 17, 2012 8:08 PM
  • Hus-HP, have you tried '<PC-Name>/<Instance-Name>'

    An easy way to find name of the instance - In the services management console, i see two services named starting with sql server.  One is sql server(sqlexpress), one is sql server(My_instance) and that is name of the instance.

    Wednesday, February 22, 2012 11:46 AM
  • Use PC-NAME\INSTANCE-NAME in the server field.

    So if your PC is called "JOES-PC" and your instance is called "MSSQLSERVER" then when you're in SQL Server Management Studio trying to login, use:

    JOES-PC\MSSQLSERVER

    Saturday, June 09, 2012 8:06 PM
  • Yes, if you have set some instance name during installation, use PC-NAme\InsatnceName, it works
    Monday, August 06, 2012 3:04 PM