Home / server / Questions / 315266
Accepted
SilverLight
Asked: 2011-09-26 01:59:12 +0800 CST

how remotely connect to sql server 2008-r2 installed on vps(virtual private network)? (firewall and sql server 2008 configs)

  • 772

i have sql server 2008 r2 installed on a virtual private network (vps) with one ip valid ...
my sql server instance info for connect on server(vps) is like below :
server type: database engine
server name : ServerComputerName\InstanceName
Authentication : sql server
login : sa
password : blablabla

================================================

how can i connect to that sql server instance from my pc (remotely server access for backup) ?
*which changes are necessary in windows firewall and node32 firewall on server?
*also which changes are necessary in sql server 2008 r2 for remote access?
*which sotware is the best for remotely connect to that instance?

server type: database engine
server name : ????\????
Authentication : sql server
login : sa
password : blablabla

thanks in advance

firewall remote-access windows-server-2008-r2 sql-server-2008-r2

1 Answers

  1. Best Answer

    Regarding your first question which changes are necessary in windows firewall and node32 firewall on server? you should open port TCP 1433 on both your windows firewall and NOD32 (I guess you mean ESET Smart Security - because NOD32 is antivirus only).

    also which changes are necessary in sql server 2008 r2 for remote access? To enable remote access on SQL Server 2008 you need to enable TCP/IP from the SQL Configuration Manager and restart the SQL Service to apply changes. Screenshot: http://georgetasioulis.com/wp-content/uploads/2011/09/enable-tcpip-sql-server.png

    Also I would suggest you switch to SQL Server and Windows authentication mode. This can be done from the Microsoft SQL Server Management Studio by right clicking on the SQL server instance, selecting "Properties" and the "Security" on the left window. Screenshot: http://georgetasioulis.com/wp-content/uploads/2011/09/sql-authentication-mode.jpg

    Finally regarding your third question which sotware is the best for remotely connect to that instance? I would stick with Microsoft SQL Server Management Studio. No need to go to any 3rd party software solution.

    • 3