Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 13, 2004

SQL Server 2000 Security - Part 1 (Net Libraries)

By Marcin Policht

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_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib 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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM