How to Boost Database Development Productivity on Linux, Docker, and Kubernetes with Microsoft SQL Server 2017
As every developer knows by now, Microsoft has focused renewed attention on security in recent product releases. One of the important concepts in this effort is surface area. Roughly speaking, a piece of software has a smaller surface area if there are fewer ways to attack it: fewer open ports, fewer APIs, fewer protocols, and so on. OSQL Server 2005 takes this concept to the next level by letting you explicitly manage the software's surface area. In this article, I'll show you the tools that SQL Server offers for this management, using the current (February 2005) Community Technical Preview build of SQL Server Express as an example. Some changes are inevitable between now and the final product, but the concepts should remain relatively stable.
The SQL Server Surface Area Configuration Tool
After you install SQL Server 2005, you can select Microsoft SQL Server 2005 COnfiguration Tool, SQL Server 2005 Surface Area Configuration to launch the SQL Server Surface Area Configuration Tools. The initial screen, shown in Figure 1, provides a brief explanation of the surface area concept and offers hyperlinks that let you start the individual surface area configuration tools. You'll almost certainly want to run this tool when you finish installing SQL Server 2005. That's because SQL Server 2005 defaults to installing with most features disabled in the interest of security. It's up to you to enable the ones that you really need.
Configuring Services and Protocols
Your first stop in configuration should be the Surface Area Configuration for Services and Protocols tool. This is where you can turn on (or off) the broadest swathes of SQL Server 2005 functionality. Figure 2 shows this tool in action.
One thing you'll discover right off the bat when you install SQL Server 2005 is that you can't talk to it from across the network. While this does enhance security by preventing remote attacks, it may not be the most useful configuration for a shared database server! The protocols node of this tool lets you enable TCP/IP or named pipes connections so that other machines on your network can access the new server. Stick to TCP/IP unless you've got a known requirement for named pipes because TCP/IP doesn't require opening as many ports in your firewall.
The other node in this tool lets you selectively enable or disable the various services that collectively make up SQL Server 2005. Depending on which edition of SQL Server you installed, and which installation options you selected, you can enable or disable any of these services here:
- Analysis Services
- Database Engine
- Full-Text Search Service
- Integration Services Service
- MSSQLServerADHelper Service
- Notification Services Service
- Reporting Services Service
- SQL Server Agent Service
- SQL Server Browser Service
- SQL Server Writer Service
After you've decided which services to enable, you can proceed to finer-grained configuration by turning individual features on or off. As with many other things in the software world, SQL Server offers tradeoffs between power and danger. For example, the xp_cmdshell extended stored procedure offers a tremendous amount of power for system administrators by allowing them to execute operating system commands from within T-SQL batches. But, by the same token, it means that anyone who gains access to an administrative account can wreak havoc on the entire server. If you don't require xp_cmdshell for some particular purpose, it's safer to disable it entirely.
The Surface Area Configuration for Features tool, shown in Figure 3, lets you enable and disable individual features. Depending on which services you have installed, you'll see different selections in this tool. Here's a summary of the features that you can manage with this tool.
Analysis Services Features
- Ad-hoc Data Mining Queries allow Analysis Services to use external data sources via OPENROWSET.
- Anonymous Connections allow unauthenticated users to connect to Analysis Services.
- Linked Objects enables linking dimensions and measures between instances of Analysis Services.
- User-Defined Functions allows loading user-defined functions from COM objects.
Database Engine Features
- Ad-hoc Remote Queries allows using OPENROWSET and OPENDATASOURCE.
- CLR Integration allows using stored procedures and other code written using the .NET Common Language Runtime.
- Database Mail lets you use the new Database Mail system to send e-mail from SQL Server.
- HTTP Access enables HTTP endpoints to allow SQL Server to accept HTTP connections.
- OLE Automation enables the OLE automation extended stored procedures.
- Service Brokerenables Service Broker endpoints.
- SMO and DMO turns on Server Management Objects and Distributed Management Objects.
- SQL Mail lets you use the older SQL Mail syntax for sending e-mail from SQL Server.
- Web Assistant enables the Web Assistant for automatic output to Web pages.
- xp_cmdshell turns on the xp_cmdshell extended stored procedure.
Reporting Services Features
- HTTP and Web Service Requests allows Reporting Services to deliver reports via HTTP.
- Scheduled Events and Report Delivery enables "push" delivery of reports.
As you can see, there is a fairly wide variety of features that you can turn on or off in the features configuration tool. In new SQL Server 2005 installations, you'll find that the bulk of these features are disabled by default. This is a radical change from SQL Server 2000, where just about everything was enabled right out of the box.
Be Smart About Your Surface Area
If you've been administering SQL Server installations for a few years, your first reaction to this new approach to things may be to simply turn everything back on. After all, one of the great things about SQL Server has always been the sheer number of powerful features that it manages to pack into a reasonably-priced package. Why not take advantage of all of those features as long as they're still in the box?
The answer is that there's nothing wrong with taking advantage of powerful features—when you need them. But, when you don't need them, it's safer to turn these features off so that they can't be misused. If you own a chainsaw, you keep it in its case in the garage when you're not using it, rather than on the couch in the living room (at least, I hope you do!). Treat the powerful but dangerous features of SQL Server with the same care, and you'll be better protected just in case anything does go wrong in the future, whether from a malicious user or an as-yet-undiscovered security hole.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his latest books, Coder to Developer (from which this article was partially adapted) and Developer to Designer, both from Sybex. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.