Problem solve Get help with specific problems with your technologies, process and projects.

Secure data transfers for remote SQL Server administration

In this tip, learn about problems you may encounter when remotely administering SQL Server and how to secure the transfer of your data.

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.

    Dig Deeper on SQL Server Security

    Start the conversation

    Send me notifications when other members comment.

    Please create a username to comment.