August 30, 2006 at 12:35 pm
I thought I'd share how to connect to to a remote SQL 2005 via Server Management Studio.
On SQL Server 2005 be sure remote access is enabled. This is done in SQL Server Surface Area Configuration, remote connections. Check 'local and remote connections'.
Go to SQL Server Configuration Managers, SQL Server 2005 Network Configuration, select your server, tcp/ip. Set Listen All to no. Under IP tab for IP1, set active yes, enabled yes, IP Address (ip address of server), dynamic ports blank, tcp port 1433 (or port of your choosing).
Restart SQL server, suggest a reboot, but technically restarting should work.
On firewall, setup a port forward for 1433 (or port of your choosing) to your SQL server IP address.
On remote computer running Server Management Studio: When connecting to your server enter ip address\server, port. Eg: 67.88.72.5\devserver,1433. Enter username and password. You can use a DNS entry assuming it's pointing to your router. Eg: http://www.mysqlserver.com\devserver,1433. Where http://www.mysqlserver.com points to your networks router. And your rerouter is forwarding 1433 to your sql server.
What got me was in SQL 7 and SQL 2000 you didn't need the server name, just the IP address and port. And you used to use : between server and port.
There may be other ways to make this work, and I'd love to here them. Hope this saves somebody else a day of struggling. And be sure you have strong password on your SA user.
September 4, 2006 at 8:00 am
This was removed by the editor as SPAM
September 6, 2006 at 8:45 am
Here is a KB for configuring remote connections: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Cheers,
Dan
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply