connect sql server 2005/2008 over internet

问题:

How do I connect to SQL Server 2005/2008 using Management Studio or other desktop application over the internet?

回答1:

Check out WCF Data Services:

http://msdn.microsoft.com/en-us/data/bb931106.aspx

That way, you don't have to totally expose your database server out to the internet, but you get fine grained control over what gets exposed and who (which type of user) can see or modify what.

Marc

回答2:

I finally solved this by:

Changing default SQL Port to 8080 from 1433 (our ISP was blocking)

Turning off Windows Firewall on the server. I know this is not a long term solution, but at least I was able to pinpoint the problem. I had set two inbound firewall rules to allow port 8080 and SSMS program, on the server. I created an outbound firewall rule on the client for SSMS (but don't think this mad a difference).

I had to change all apps to use the new port instead of the default port. I had to configure our router to handle the new port 8080 and forward to the database server.

I will modify the Windows Firewall settings to see what was blocking SSMS.

DB Server: Windows 7 Pro 64bit 24 GB RAM

回答3:

If you put 'your' SQL server (any brand, I'm not bashing) on the internet ... it won't be yours for long, unless you add some rather effective security measures ...

I suggest you look into VPN.

回答4:

To be able to access your database over the internet, you will need to make sure that the server that hosts the database is accessible over the internet and the port that the database instance uses is open on that machine. You will also need to allow remote connections using the SQL Server Configuration Tool.

For Management Studio access I would recommend rather than opening the server to the outside to use a VPN solution that allows you to create a secure connection to the server and from there you can use the server name or IP to connect as if your machine is part of that network.

For the desktop application I would recommend looking into having the data be consumed through a web service or WCF rather than needing to have a direct connection to the database over the internet.

Hope this helps.

回答5:

Firstly, if we put security consideration on the side, you have to configure SQL server (sql surface area configuration) to accept traffic, then you have to open proper ports on you server and allow inbound traffic thru to your router to the SQL server.

回答6:

When you open sql server management studio in connect to server window and at the server name type the IP of your server and enter your username and password.

correct format : IP\InstanceName

you should have a user on target database.