SQL Server 2000 Security – Part 1 (Net Libraries)

One of the most interesting phenomena in the computing arena within the last
few years is the growing concern for security, which, at this point, is being
treated on a par with efficiency and cost (as a matter of fact, security became
one of the primary factors when evaluating any technology-based solution) . The
main driving factors behind this trend are the increasing number of computer
worms and viruses (combined with ubiquity of the Internet), the financial
impact of their destructive nature, as well as mounting international political
tensions. Microsoft Secure Computing initiative, launched in early 2002,
triggered major programming efforts geared towards securing the Windows 2003
operating system code; however, signs of this tendency were present in a number
of other, previously released products. In this series of articles, we will
focus on security features in SQL Server 2000, starting with the most basic
configuration settings and their security implications. In particular, we will
look into authentication related issues, affected by the choice of Net
Libraries, authentication mode and security context of the SQL Server service
account.

Net Libraries

From the OSI application layer perspective, commands between SQL Server 2000
and its clients are interchanged in the form of Tabular Data Streams (TDS)
packets. TDS packets, which are created and read by SQL Server OLE DB provider,
ODBC driver, or DB-Library DLL, are than handed over to Net Library protocol
DLLs. Net Libraries facilitate interprocess communication (IPC) by providing a
mechanism to exchange TDS packets between SQL Server and its clients, so they
require (in most cases – with the exception of such IPC methods as shared
memory or Local Procedure Calls) a network protocol to carry this communication
over a network. This is the reason why the majority of Net-Libraries are named
after underlying network protocols, such as TCP/IP, IPX/SPX (or rather its
Microsoft implementation called NWLink), AppleTalk (for communication with
Macintosh systems), or Banyan Vines (used in legacy Banyan Vines environments).
There are, however, a few exceptions, such as Named Pipes and Multiprotocol Net
Libraries, which can operate using any of the most popular network/transport
protocols offered by Microsoft (such as TCP/IP, NWLink, or NetBEUI) or via
shared memory (for local server access). In addition, SQL Server 2000
introduces new SuperSocket Net Library, unique in its characteristics, because it
functions strictly as a helper to other Net Libraries, rather than operating on
its own, (its sole purpose is to encrypt data using Secure Sockets Layer
mechanism). We will discuss its features in more details later in this article.

As you can expect, a server and its clients need to be set up with at least
one matching Net Library (with equivalent parameters) in order to be able to
communicate. Server Net Library configuration is done with SQL Server Network
Utility; its client-based counterpart is called SQL Server Client Network
Utility. They both contain "General" and "Network
Libraries" tabs, where the "General" tab allows enabling (or
disabling) individual Net Library protocols and modification of their
parameters, and "Network Libraries" tab lists details of DLL files
implementing each of the Net Libraries. Client Network Utility has two
additional tabs – "Alias" which allows for the creating of aliases
for connecting to different servers with distinct connection settings, and
"DB-Library Options" listing DB-Library DLL information and providing
limited access to its options.

Even though both client and server can have multiple Net Libraries enabled
(making it possible for differently configured clients and servers to
communicate), it is beneficial from security point of view to limit the total
number of different Net Libraries used in your environment (since each one
constitutes a potential vulnerability). Limiting the number of Net Libraries on
a client to only those that are used by its server also speeds up initial
connection, since it allows the first attempt to succeed.

In order to determine which Net Library you should use, consider the
following criteria:

  • performance: TCP/IP and IPX/SPX Net Libraries are slightly
    faster than their counterparts due to their implementation (they bypass Net
    Library Router internal core component, utilized by other types of Net
    Libraries).

  • ability to support named instances of SQL Server (you can
    install multiple instances of SQL Server 2000 on the same physical computer):
    not provided in the Multiprotocol, AppleTalk, and Banyan Vines Net Libraries.

  • authentication:

    • dependency on secure channel between client and server
      computers
      : Named Pipes and Multiprotocol Net Libraries require that a
      Windows-authenticated connection already exists between client and server
      computers, before authentication can be attempted on the SQL Server level. This
      means that while these two Net Libraries will work without any problems in a
      single- or trusted multi-domain environments (assuming a user connecting to a
      SQL Server is logged on with appropriate domain credentials), they will fail in
      a situation where no trusted connection exists between the two systems. Note
      that on one hand, this complicates connecting to a SQL Server in a distributed environment,
      on the other, it provides an additional level of security, by preventing access
      from rogue computers.

    • support for delegation: available only with TCP/IP Net
      Library (due to the fact that delegation is based on having a Service Principal
      Name assigned to SQL Service Account linked to a specific IP address and port
      combination). Delegation is necessary in order for a local server to
      impersonate logged on users when they run distributed queries against linked
      servers (we will describe delegation and its impact on linked server
      functionality in more details in our next article).
  • support for encryption: while Multiprotocol Net Library
    still can be used to encrypt data transmitted between SQL Server 2000 and its
    clients (just as in previous versions of SQL Server), it is no longer the only
    option providing this capability. In SQL Server 2000, you can also encrypt data
    by configuring Net Libraries with Secure Sockets Layer (SSL), which takes
    advantage of the mentioned earlier SuperSocket Net Library.

    Multiprotocol Net Library utilizes Windows
    encryption API and can be turned on by simply selecting the "Enable
    encryption" checkbox in the Multiprotocol Net Library Properties dialog
    box, once you enable the Multiprotocol Net Library using the SQL Server Network
    Utility (obviously your clients need to have Multiprotocol Net Library enabled
    as well). The Secure Sockets Layer option offers several advantages, such as
    stronger encryption, message integrity and server authentication, but it also
    is more complex to configure. More specifically, it requires installation of a
    certificate issued by a Certificate Authority (CA) on the Windows system
    hosting SQL Server 2000 and ensuring that clients are configured to trust the
    issuing CA. To implement SSL based encryption for communication between SQL
    Server 2000 and its clients, you need to follow these general steps (for more
    detailed instructions, refer to Microsoft
    Knowledge Base Article Q276553
    and Q316898,
    which describe two different implementation methods):

    • decide which type of CA is appropriate for your environment. You
      can chose between Microsoft CA Server, available at no additional cost on
      Windows 2000 and 2003 platform, or use commercial, third party certificates
      issued by CA companies such as VeriSign or Thawte. The first option is ideal if
      your clients are members of the same or trusted domain, since this greatly
      simplifies certificate deployment (and clients can be configured to
      automatically trust the CA that issued the certificate). The latter case is
      suitable for situations where client population includes systems outside of
      your management scope (since it is likely they will already trust the third
      party, commercial CA). Its main drawback is the fact that you need to purchase
      certificates (which are free with Microsoft-based CA).

    • obtain certificate for server authentication purposes (server
      name on the certificate needs to match fully qualified DNS name of your SQL
      server) and install it on the Windows server hosting SQL Server 2000. When
      using Windows based CA, you can simplify this step by requesting the
      certificate after logging to the SQL Server with MSSQLServer service account
      and connecting to your CA server from Internet Explorer (using http://CAServerName/certsrv
      URL path, where CAServerName is the name of your CA server)

    • restart SQL Server Service (the certificate needs to be read
      during every service startup in order to become available)

    • configure the issuing CA as the Trusted Root Authority on each
      client. This can be done by exporting from the SQL Server computer the Trusted
      Root Certificate Authority of the newly installed server certificate, and then,
      importing it to client computers.

    • in case the Windows system hosting the SQL Server 2000 has
      multiple certificates installed, you will need to specify which one is to be
      used by SQL Server. This can be done with the SETCERT.EXE utility from SQL
      Server 2000 Resource Kit. Alternatively, you can create the following registry
      key (of REG_BINARY data type): HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServerSuperSocketNetLib
      and populate it with the Thumbprint value of the certificate.

    • select the "Force protocol encryption" check box in the
      Server Network Utility if you want to encrypt all incoming server connections
      or use the same checkbox in the Client Network Utility on your client
      computers, in case you want to ensure that encryption is used only for the
      connections, which they initiated.

    Note that, in addition to encryption, you can
    further secure packets sent between SQL Server and its clients with IP Security
    (IPSec), which obfuscates communication down to the network layer (SSL
    encryption operates on the session layer of the OSI model, so it does not
    secure network information portion of the packet) and provides protection
    against certain types of exploits that SSL does not eliminate (such as
    man-in-the-middle attacks). On the other hand, keep in mind that every level of
    encryption has negative impact on performance, since each requires extra
    processing power.

In general, you should try to use TCP/IP Net Library whenever possible, due
to its optimum speed and security characteristics. With TCP/IP Net Library, you
can also change the default port (from TCP 1433) and choose the option to
"Hide server," which switches the port to TCP 2433). Note that this
also requires SQL clients to switch to the same port using Client Network
Utility (e.g. by creating an alias). Unfortunately, using the "Hide
server" option has also serious drawbacks (check the Microsoft Knowledge
Base articles 308091
and 814064
for details) and is not recommended by Microsoft. Another consideration
applicable to TCP/IP Net Library is blocking UDP port 1434 (the one exploited
by the notorious Slammer in February 2003) on your company’s network perimeter.

In this article, we presented the Net Library protocols available in SQL
Server 2000 from a security perspective. We will follow with discussion on the
authentication options and their security implications.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles