GregsListAcct


Problem: I upgraded a SQL Server Express instance to SQL Server Enterprise, but I'm unable to connect to the SQL server from any remote machine. Only local connections work -- and I've verified that Remote Connections are allowed in the SQL Surface Confg. utility.

Scenario: Server1 has an SQL Enterprise edition installed as a default installation. SharePoint Server 2007 was installed later, and during that install a SQL Express edition instance was installed which is part of the standard SharePoint install. That Express instance contains all the SharePoint content databases.

So Server1 has SQL instances:
Server1 <default install running Enterprise>
Server1\OFFICESERVER <previously running SQL Express, now upgraded to Enterprise>

After upgrading Server1\OFFICESERVER to Enterprise, everything seemed to work fine (i.e. SharePoint works fine). However when I try to connect remotely to Server1\OFFICESERVER I can't. I've verified through the SQL Surface configuration tool that remote connections are allowed. I've rebooted the server as well. I've tried connecting remotely using various PCs and various applications (e.g. SQL Server Mgmt Studio, Visual Studio, etc.).

Any other ideas to try




Re: unable to connect remotely after upgrading a SQL instance from Express to Enterprise

Jens K. Suessmeyer


Which error message do you get, is SQL Server Browser turned on and running, are you able to connect locally using Shared memory How is the port configuration

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---





Re: unable to connect remotely after upgrading a SQL instance from Express to Enterprise

GregsListAcct

Answers to Jens' questions:

The error is:

Cannot connect to server1\officeservers.

------------------------------
ADDITIONAL INFORMATION:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, 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) (Microsoft SQL Server, Error: -1)

SQL Server Browser is disabled and stopped -- which is the same state on other servers that I can connect to remotely, including the default instance: server1

I can connect locally using Shared Memory as the network protocol, but not TCP/IP or Named Pipes.

Port configuration: the ports are configured for dynamic port assignment.






Re: unable to connect remotely after upgrading a SQL instance from Express to Enterprise

GregsListAcct

OK, so I started the SQL Browser and now I can connect. I thought the other machines I was connecting to remotely indicated that SQL Browser didn't need to be running, but then I noticed the other machines I was comparing to weren't non-default instances.