In this two-part series, Serdar Yegulalp explains how to remotely administer SQL Server without compromising network security. Part one offered two methods for providing secure administrative access to remote employees. In part two below, he'll examine practical problems you may encounter when remotely administering SQL Server.
In my previous tip I explained how SQL Server could be administered remotely, whether it's through a Microsoft interface or a third-party package. Here I'll discuss some problems that can occur during remote administration and how to work around them securely.
Slow SQL Server connectivity
A slow or sporadic connection (such as a dial-up link) can make administering SQL Server through Remote Desktop difficult.
One option might be to install the SQL Server Enterprise Manager on the machine being used to perform the administration. However, this option often means exposing SQL Server to the outside world -- never a good move from a security perspective.
A better solution in this case would be to use a Web-based administration tool (see part one). Web traffic is stateless and can handle slow or intermittent connections more robustly.
Another option, although it's somewhat less flexible and has proved to be a source of exploits, is the SQL Mail. This service allows you to send commands to the SQL Server in an e-mail and have the results returned as a responding message.
Remote data transfer
Getting data to and from a remote SQL Server machine can be done several ways:
Copy files by hand from the remote computer
Use mapped drives
FTP
The first option is labor-intensive, and mapping a drive across a WAN is almost always a bad idea unless it's done through a tunneled connection. FTP is the most secure of the three options. An FTP site on the remote server can be set up on nearly any port and accept secure connections (if the software allows it). FTP is also easier to set up than mapped drives.
As an added note, when sending and receiving the files themselves always compress them with a tool like the WinRAR compression utility, which can compress structured files quite heavily. The tradeoff is in the compression/decompression time, but if your network bandwidth is limited, it's worth it.
WARNING: If you are trying to mount a database from a backup on another system, SQL Server is finicky about the file locations. If your original file was in C:Datatables and your current SQL Server setup has no such directory, you need to create one.
