May 8, 2006 at 8:37 am
How would I connect to remote SQL 2005 database using Management Studio?
The only difference from local DB is that I type IP address instead of server name, right?
I'm getting error:
Testing the registered server failed. Verify the server name, login cridentials, and then click Test again.
Additional information:
An error has occured 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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 53)
I did everything from article "How to configure SQL Server 2005 to allow remote connections"(http://support.microsoft.com/?kbid=914277&SD=tech). SQL Browser enabled. Both TCP/IP and Named Pipes are enabled. Exceptions in firewall for sqlsrvr.exe and sqlbrowser.exe added.
Did not help.
What else could I check?
May 9, 2006 at 6:06 am
If you have done all those steps from the MS site, then the only other thing I have had a problem with before that doesn't appear to be on the list is Dynamic Port Allocation. I believe Dynamic Port Allocation is anabled by default. With it, when the SQL server is installed (and maybe on reboot or service restart - have to check into that though) and with the TCP/IP protocol enabled, a port is selected for the TCP/IP communication and the sqlserv.exe process. The selected port would be something other than the default port 1433. I think the SQL browser service is supposed to take care of that so if you allowed that to communicate through your firewall then theoretically you should be ok. But I have seen where I had to make an explicit TCP or UDP allow/exception for the firewall for the specific dynamic port that was selected by the server. When I allowed the traffic through the specific port then I could communicate remotely.
You can see the port from SQL Server Configuration manager, Network Configuration, Protocols for your instance, right-click TCP/IP (if enabled), choose Properties, IP Addresses tab. You want to look at the TCP Dynamic Ports is enabled or not (1 or 0) and if enabled what TCP Port was selected. I Believe I was able to focus on the IP All section.
And then the other thing I have done is turn dynamic port allocation off by setting all the values to zero- then it used the default port 1433 and everything was ok once I did the stuff in the MS artcle you referenced.
I like the idea of dynamic port allocation since 1433 is so well known by hackers out there - but with properly configured firewall I figure I can't hope for much more. Even a dymanic one would be found if they really wanted to port scan me anyhow.
So of course I am not positive this is what you've run into- but I've seen this kind of behavior (I was using SQL Express at the time).
Good luck.
June 14, 2006 at 8:39 am
The problem was because port 1433 was closed. Now it's open but I get different error:
Cannot connect to xxx.xxx.xxx.xxx
Additional information:
A connection was successfully established with the server, but then an error occured during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (Microsoft SQL Server)
Authentication - SQL Server. Login - SA.
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply