0

windows 2008 server r2 installed sql server 2008 express sp1. also installed sql server management studio. how ever am not able to connect to sql server thru the management studio. error message show below :-

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)

2
  • Further to Ola's comments, also post what name you are using to connect in-case you have that value wrong
    – Wayne
    Nov 6, 2009 at 10:49
  • wayne - i was using the name of the server. checked it many times. when i first installed sql server 2008 express , i was warned of incompatibilty with windows 2008 r2. yet i continued. i could not connect. i then downloaded sql server 2008 express sp1. and installed sp1. even then i was not able to connect.
    – Ravi
    Nov 6, 2009 at 12:43

6 Answers 6

2

This stumped me for a while. One thing that's not in the other lists mentioned is the following :

In 'Sql Server Configuration Manager':

  • Expand the 'SQL Server Network Configuration' node
  • right-click no the 'TCP/IP' entry (this should be enabled as per the other links) (the following was the step I was missing)
  • select the 'IP Addresses' tab. You'll have multiple IPs here even if you only have a single NIC.
  • Scroll to the bottom and locate the 'IPAll' entry
  • Enter '1433' in the TCP Port
  • Apply to save your changes and restart the SQL Server service.

In my case, I was now able to connect. Hopefully this'll help someone else.

1

Is the server browser running? have you enabled named pipes? You find both these settings in SQL Server Configuration Manager. Remember to restart SQL Server service after doing changes.

1
  • named pipes was enabled. and the services were restarted. i then tried using tcp/ip but i got the same result. i then checked the tcp/ip settings under sql config manager->network config->protocols-tcp/ip . the port address was 0. i changed it to 1433 under ipall. and i was able to connect.
    – Ravi
    Nov 6, 2009 at 12:39
1

If you are trying to connect remotely make sure that you have followed the steps in Enable Remote Connection on SQL Server 2008 Express.

1

Changing '1433' in the TCP Port worked for me

0

If it's SQL Express then don't forget that the instance name is SQLEXPRESS.

Therefore when connecting you want to put [SERVERNAME]\SQLEXPRESS in the "Server name" field.

If you just put the Server name it wont be able to connect to the SQL Express instance.

1
  • Not entirely true. SQL Express simply recommends using the SQLEXPRESS named instance by default. This can be changed to be the default instance or any other named instance of your choice.
    – Shane
    Feb 21, 2011 at 18:47
0
  1. Ensure TCP/IP is enabled in SQL Server Configuration. (And remember to restart the service if you change anything.)

  2. Ensure that the selected port is added to the Firewall, inbound for your network profile.

  3. If the assigned TCP port isn't 1433, then you need to specify a different port when connecting. (In SSMS use Host\SQLExpress,Port.

You must log in to answer this question.