SQL Server developers and administrators need to be in the know about available features once they install SQL Server 2005. This latest SQL Server version embraces an improved security model allowing finer grain control over the database server, and the database objects therein. In this article we'll focus on two new tools: Surface Area Configuration tool and the SQL Browser Service. We'll also discuss the SQL Server Configuration Manager, though it is unchanged from SQL Server 2000.
The Surface Area Configuration (SAC) tool provides feature-level control of installed components. It also provides the capability to turn certain services on and off. Capabilities of the SAC tool are broad and worth understanding. For instance, I noticed I could turn remote access to a server instance on and off (that is, use TCP/IP or named pipes), but I couldn't configure the IP address or pipe. The real purpose of the SAC tool is to provide an easy means for reducing the hacking surface area for local instances of SQL Server. SAC does not work across the network.
The SAC tool has some interesting limitations. For instance, SQL Server 2005 no longer automatically listens on port 1434. In fact, it doesn't listen at all. You need to turn on the SQL Browser Service, which acts as a middleman for resolving client connection requests to the server. SQL Browser Service only provides name/port resolution. Looking back to SQL Server 2000, it was the automatic listen that gave the SQL Slammer worm its life. Now, SQL Server is deaf until you allow it to hear.
But there's more to consider. You've turned on SQL Browser, and you've turned on TCP/IP. But your company doesn't allow random IP usage (you've been assigned an IP address), so how do you supply that information to SQL Server? By using the new SQL Server Security Configuration Manager tool! A number of ports are used by SQL Server and its other components by default.
Table 1 lists reported ports for SQL Server 2005. You can
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
check out http://www.iana.org/assignments/port-numbers to learn about what applications open ports. By default, these ports are closed and are unavailable until you explicitly enable them.
Table 1
Ports Used by SQL Server
[TABLE]
If the TCP/IP protocol is enabled when an instance of SQL Server starts, the server is assigned a TCP/IP port. You can change that port. If the named pipes protocol is enabled, SQL Server listens on a specific named pipe. This port, or pipe, is used by that specific instance to exchange data with client applications. During installation, TCP port 1433 and pipe \sql\query are assigned to the default instance, but those can be changed later by the server administrator using SQL Server Configuration Manager. Since only one instance of SQL Server can use a port or pipe, different port numbers and pipe names are assigned for named instances. By design, when named instances are configured to use dynamic ports, an available port is assigned when SQL Server starts. You can assign a specific port to a SQL Server instance if you wish.
At start time, SQL Browser starts and claims UDP port 1434. SQL Browser reads the registry, identifies all SQL Server instances on the computer and notes which ports and named pipes they use. When a server has two or more network cards, SQL Browser returns the first enabled port it encounters for SQL Server. SQL Server 2005 and SQL Browser support IPv6 and IPv4. When SQL Server 2005 clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.
SQL Server Configuration Manager
The SQL Server Configuration Manager tool provides detailed control over the services and network protocols used by SQL Server 2005. It does not cover SQL Server 2000, except on upgrade, when SQL Server 2005 simply moves over the old settings. By design, an upgraded system has the same network listening capability as before the application upgrade. The SQL Server Configuration Manager is an MMC snap-in application. It consolidates the SQL Server 2000 Network Service utility and services utility into one application. Within the SQL Server Configuration Manager, you can explicitly change ports and IP addresses, create and destroy aliases for servers, enable and disable protocols and more. From the services perspective, the SQL Server Configuration Manager provides access to start and stop services, to change logon credentials, and to peer into the registry settings for the services.
About the author: Eric Brown is a senior consultant in the Business Intelligence national practice for Quilogy Inc. He works on emerging technologies and of course SQL Server 2005 and is the author of SQL Server 2005 Distilled (Microsoft Windows Server). Brown's professional computing career began in earnest in 1996 when he started at Multiple Zones International as a product manager. While there he realized the next big wave would be e-commerce and he raced to get a job at a dot-com. After working for three such companies, his acumen for databases and passion for technology landed him on the SQL Server Product Team at Microsoft, for which he ran Yukon readiness during his three-year stint. Eric eventually left to take a sabbatical and pursue other interests, like getting back to his e-commerce roots and writing a book about SQL Server 2005, due out in March 2006. He has presented at Microsoft TechEd and he has written several papers as well as a column in SQL Server Magazine.
More on SearchSQLServer.com
Tip: How to discover vulnerable SQL Server systems
Tip: Introducing the SQL Server 2005 Surface Area Configuration tool
Guide: Learning Guide: SQL Server tools
