In this two-part series, Serdar Yegulalp explains how to remotely administer SQL Server without compromising network security. In part one below, he describes two methods for providing secure administrative access to remote employees. In part two, he'll examine practical problems you may encounter when remotely administering SQL Server and how to work around them.
Just like Windows Server, SQL Server can be administered directly on the computer to which it's installed or from a remote computer. One common remote-administration technique is to use SQL Server Enterprise Manager, which can connect to a remote SQL Server via TCP/IP, named pipes or other network protocols. However, this tool requires that SQL Server be made accessible across a network -- typically a security hazard. If the network is closed (LAN), the danger isn't as great since it's not as easy to mount an attack from the outside. But if users outside the local network need to perform administrative work, things get complicated.
So what are your options for giving administrative access to people outside your local network? The answer will depend on your network topology, your SQL Server needs and how much administrative work is being done.
Web-based administration
One very powerful and highly malleable way to administer SQL Server is through a Web-based client. Rather than connect to the database server directly, the user fires up a Web browser and connects to a Web server, which accesses SQL Server from its own backend, and performs all the needed administrative tasks through a Web interface.
The Web client can be set up to be as open or secure as necessity dictates, and it can be connected through SSL for added security. If you're a reseller, Web-based administration will also enable you to provide SQL Server functionality to your customers without having to expose SQL Server directly. (Many Unix and Linux-based Web hosts with database support use this approach.)
There are a number of Web-based SQL Server administration programs. Microsoft has created one called the SQL Server Web Data Administrator, which runs through IIS and the .NET Framework. It includes most common SQL Server administrative functions: creating and editing databases, running queries, managing users and roles, working with stored procedures, etc. However, it is not supported by Microsoft, so use it at your own risk.
If you'd rather use a supported third-party application, one tool to consider is myLittleAdmin for SQL Server. It was originally written for Web-hosting companies that wanted to provide SQL Server administration functions for their customers; a free evaluation version with many features disabled is available in ASP source-code format. A single-user (i.e., internal use) license is $350. Another, similar product written in ASP.NET, ASP.Net Enterprise Manager, is currently available as an alpha demo, but it is both free and open source, and can be freely modified as needed.
Almost all common administrative functions that need to be carried out can be done through one of these Web interfaces. However, if you need to give your SQL administrator a higher degree of control, you might need to give them full desktop access.
Terminal Services (Remote Desktop)
Administrators should be familiar with Terminal Services --- now known as Remote Desktop -- as a way to administer a whole system, but it can also be used to grant a user access to both SQL Server Enterprise Manager and other commonly used SQL tools like the Query Analyzer.
The Remote Desktop client is a standard component of Windows 2000 and XP, and can even be set up as a Web-page component if needed. It works relatively quickly even on a dial-up connection, so as far as bandwidth is concerned, it's nearly as efficient as using a Web-based manager.
One drawback to this method is that it requires more care to set up and administer than a Web-based interface; the main system administrator needs to make sure the account created for the SQL administrator has all the right permissions assigned to it. SQL Server does not create user groups of its own in Windows (it uses its own internal user schema for the most part), so the administrator will need to create a user group specifically for this. (Keep in mind that if the system administrator is also the SQL Server administrator, this method is probably the best and most direct one to use since it involves fewer things to configure.)
