SQL 2005 Connection error

• sanra001

  Mr or Mrs. 500

  Points: 527

  More actions

  December 5, 2007 at 12:48 pm

  #179750

  Hi

  I keep getting the following error trying to connec to a sql 2005 server. I am able to connect to all other sql 2005 servers except for one:

  An error has occured while establishing connection to the server. This failure may be caused by the fact that under the default settings SQL server does not allow remote connections. Provider: SQL Network Interfaces,error 26- error locating server/instance specified)

  Any help will be appreciated.

  We have the use TCP port enabled under the surface area config .

  Should the named pipes be also enabled?

  Thanks

  KR

• roger-555516

  Newbie

  Points: 4

  More actions

  December 6, 2007 at 8:04 am

  #757680

  2 things I'd check:

  1. Make sure the SQL Server Browser service is running especially if you're using a different port other than 1494.

  2. In the IP Address tab of the TCP/IP Properties, make sure it's not assigned to an "unanticipated" IP Address.

  Hope it helps.

• Key DBA

  SSCertifiable

  Points: 6029

  More actions

  December 7, 2007 at 7:11 am

  #758062

  Remote connections in SQL Server 2005 are disabled by default.

  You will need to change or verify your settings in the "Surface Area Configuration for Services and Connections" application.

  Start >>

  All Programs >>

  Microsoft SQL Server 2005 >>

  Configuration Tools >>

  SQL Server Surface Area Configuration >>

  Surface Area Configuration for Services and Connections >>

  Expand the entry for the Instance Name >>

  Expand the entry for Database Engine >>

  Click on "Remote Connections".

  Choose the option "Local and Remote Connections"

  AND one of the following:

  "Using TCP/IP"

  OR

  "Using both TCP/IP and named pipes"

  You will get a warning that the SQL Instance must be restarted for the changes to take effect.

  Personally I have had to reboot the server to get the changes to take effect, but you should be able to connect remotely afterward.

  Happy SQLing!

  "Key"
  MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

• pg53

  Ten Centuries

  Points: 1303

  More actions

  December 7, 2007 at 4:53 pm

  #758247

  Another thing to check - Windows firewall , Norton Internet security, etc - these will all prevent communication from workstation to server.

  pg