June 15, 2009 at 10:00 am
I am unable to connect to a remote SQL Express 2005 server. I believe that the server is properly configured (for the most part anyway…) – SQL Server Configuration Manager and SQL Server Surface Area Configuration were used to enable TCP/IP access. Here is a description of the setup and some data.
Remote Machine: Windows Server 2003 running SQL Express 2005. Remote Machine is accessed by IP address. The hosting company has opened ports 1433 (TCP) and 1434 (UDP) in the firewall. The SQL Server instance is machname\sqlexpress. Access is via SQL Server Authentication.
Local Machine: Windows XP running SQL Server Management Studio 2005
On Local Machine, using SQL Server Management, I entered IP_Address\sqlexpress in the server name field of the Connect to Server window. Looking at the network traffic between Local Machine and Remote Machine, I saw the following:
Local->Remote, to port 1434: <0x04>sqlexpress<0x00>
Remote->Local, from port 1434: <0x05> <0x86> <0x00>ServerName;machname;InstanceName;SQLEXPRESS;IsClustered;No;Version;9.00.4035.00;tcp;1158;np;\\ machname\pipe\MSSQL$SQLEXPRESS\sql\query;;
Local->Remote, to port 1158: no data
Local->Remote, to port 445: no data
Local->Remote, to port 139: no data
The connection never completed.
On Local Machine, using SQL Server Management, I entered IP_Address in the server name field of the Connect to Server window. Looking at the network traffic between Local Machine and Remote Machine, I saw the following:
Local->Remote, to port 1433: no data
Remote->Local, from port 1433: no data
Local->Remote, to port 1433: no data
Remote->Local, from port 1433: no data
Local->Remote, to port 1433: no data
Remote->Local, from port 1433: no data
Local->Remote, to port 445: no data
Local->Remote, to port 139: no data
The connection never completed.
In both cases, the error message returned is:
TITLE: Connect to Server
ADDITIONAL INFORMATION:
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: 53)
What do I need to do differently so that I can connect to the remote server? (It seems like the first attempt is closer to working, but that port 1158 also has to be opened. How many open ports are needed? Can the remote server be forced to reply on port 1433?)
Thank you.
June 15, 2009 at 10:38 am
Error 53 means the instance could not be contacted because your remote configuration is not correct, so check the first link for what you need and the second link for how to enable remote connections in both instances. The reason Express and the developer edition comes with most features disabled by default. You know Microsoft provides database deployment wizard you should look for it.
http://support.microsoft.com/kb/914277
Kind regards,
Gift Peddie
June 15, 2009 at 12:02 pm
Thank you for taking time to reply. Sadly, neither reference was useful.
The answer turns out to be the configuration of the TCP/IP protocol with the SQL Server Configuration Manager. Under 'Protocols for SQLEXPRESS->TCP/IP', the TCP DYnamic Port value had to be set to 1433.
June 15, 2009 at 12:10 pm
I am glad you have fixed your problem but I gave you available solutions for your posted errors, good to know new one.
Kind regards,
Gift Peddie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply