connect sql server 2005/2008 over internet

Question

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

Answer 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

Answer 2

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.

Answer 3

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.

Answer 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.

Answer 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.