SQL Server 2005 Part 1 – Security (Authentication)

In this installment of our series covering new and improved functionality of
SQL Server 2005 Beta 2, we will focus on the topic of security, which has been
becoming increasingly prominent among the issues on every database and system
administrator’s agenda. A new approach to software development started with the
Trustworthy Computing initiative launched in early 2002, necessitated by the growing
number of exploits directed at the Microsoft operating system and applications,
resulted in a "secure by default" product with highly customizable
security features further increasing the degree of protection. We will start
with the features related to authentication (the process of identifying logins
connecting to the SQL Server and users accessing databases), and continue with
authorization (determining the level of permissions granted once the initial
connection is established) and encryption in the future articles. In
particular, we will cover here, password policy implementation and management
as well as endpoint-based authentication (in the context of native support for
HTTTP SOAP requests).

It is worth pointing out that the new version of SQL Server has been
designed with the "secure by default" principle in mind, resulting in
a system with optimum, from the security standpoint, settings. The typical
setup avoids installing or activating non-essential components and features
which can expose the server and its data to potential attacks. This applies,
for example, to SQL Server Agent, Full-Text Search, and Data Transformation
Services (all set to manual startup), Analysis, Reporting, and Notification
Services, SQL Browser, Service Broker Network Connectivity, Database Mirroring,
SQLMail, or SQL Debugging.

Just like its predecessors, SQL Server 2005 still supports Windows and Mixed
authentication modes. In the Windows Authentication Mode, access is granted
based on a security token assigned during successful domain (or local server)
logon by a Windows account, which subsequently requests access to SQL Server
resources. In this case, such accounts must belong to the same Windows
environment as the computer hosting SQL Server (this environment can consist of
any combination of trusted domains or forests). In the Active Directory domain
environment, an additional level of protection is provided by Kerberos
protocol, which governs the behavior of the Windows authentication mechanism
(on Windows 2003 Server based-clusters, Kerberos authentication can be used
against SQL Server 2005 virtual servers). The Mixed Authentication Mode also
allows SQL Server Authentication (in addition to Windows Authentication), which
relies on the verification of credentials stored and maintained by the SQL
Server. Due to increased security and no need for a separate set of accounts
(since existing Windows users and groups are leveraged), Windows Authentication
is the preferred option; however, security of SQL Server based logins has
improved through its encryption with SQL generated certificates, as long as
communication involves the new MDAC client software, based on .NET provider.

New in SQL Server 2005 is the ability to manage SQL Server account password
and lockout properties (in addition to Windows accounts used with Windows
Authentication) with local and domain-based group policies (remember though
that this functionality is available only on Windows 2003 Server systems). This
allows you to enforce such restrictions on password complexity, password expiration,
and account lockout. Password complexity is defined by a number of the
following characteristics:

  • length of the password must be at least 6 characters (in general,
    SQL Server passwords can have between 1 and 128 characters),

  • password must contain at least three out of four types of
    characters such as uppercase letters, lowercase letters, numbers, and
    non-alphanumeric characters,

  • password can not match any of the values: "Admin",
    "Administrator", "Password", "sa", "sysadmin",
    name of the compute hosting SQL Server installation, and all or part (three or
    more consecutive alpha-numeric characters delimited on both ends by space, tab,
    comma, period, underscore, dash, or hash sign) of the name of currently logged
    on Windows account.

Note that regardless of authentication mode and policy enforcement, SQL
Server 2005 Setup Wizard does not permit non-blank passwords for sa account
during the installation. Password expiration is determined by the value of
"Maximum password age" group policy setting, while account lockout
behavior depends on values assigned to "Account lockout duration",
"Account lockout threshold", and "Reset account lockout counter
after" entries.

You can use CHECK_EXPIRATION and CHECK_POLICY clauses when creating new
logins with the CREATE LOGIN T-SQL statement to enable or disable compliance
with respective group policy settings (by setting them to ON or OFF,
respectively). CHECK_EXPIRATION controls password expiration, while
CHECK_POLICY determines both the state of password complexity and account
lockout settings (which limits to some extent the flexibility in configuring
these options – further restricted by the fact that a combination of
CHECK_POLICY set to OFF and CHECK_EXPIRATION set to ON is not supported).
Including the MUST_CHANGE clause forces the user to change the password when
logging in for the first time. For example, the following statement creates a
new login with password complexity, password expiration, and account lockout
enabled, and assigns a new password that will need to be changed at the first
login.


CREATE LOGIN drevil
WITH
PASSWORD = ‘Ch4ngeMe’ MUST_CHANGE,
CHECK_EXPIRATION = ON, CHECK_POLICY = ON

sys.sql_logins catalog view produces output indicating whether password
policy and password expiration have been enforced for existing SQL logins
(within is_policy_checked and is_expiration_checked fields). The same
information is available for individual accounts through the graphical
interface of SQL Server Management Studio. ALTER LOGIN T-SQL statement supports
the UNLOCK clause, intended for unlocking SQL Server logins that have been
locked by mistyping the password more times than the value assigned via group
policy to account lockout threshold setting.

Endpoint-based authentication is used to provide secure communication in
scenarios where SQL Server 2005, running on a Windows 2003 Server, functions
natively as a Web Service, listening and responding to HTTP SOAP requests.
(Windows 2003 Server implements the HTTP.SYS listener process outside of World
Wide Web service, which eliminates the need for Internet Information Server
functioning as an intermediary). This makes it possible to execute remotely
stored procedures, scalar-valued user-defined functions, and T-SQL batches. However,
endpoints, which are, in essence, points of entry to a SQL Server 2005
instance, are more versatile, allowing combining different transport (such as
Named Pipes, Shared Memory, TCP/IP, or HTTP) and payload protocols (such as
SOAP, Service Broker, Tabular Data Stream, or Database Mirroring), listening
ports, authentication modes, and permissions (although the choice of a
transport protocol has implications on other characteristics, such as, default
permissions). Both SQL Server login authentication modes are supported – the
preferred one is designated by the LOGIN_TYPE option (which can take on WINDOWS
or MIXED values, corresponding, of course, to Windows and Mixed authentication)
when creating or modifying HTTP endpoints using CREATE ENDPOINT and ALTER
ENDPOINT statements. WINDOWS is the default and recommended choice, since,
besides added security, it is also easier to configure. Otherwise, with MIXED
LOGIN_TYPE, SQL Server must be configured with Mixed Authentication, endpoints
are required to operate over a Secure Socket Layer channel (which is set by
assigning an SSL value to the PORTS option of the endpoint), and login
credentials need to be specified in the Web Services Security headers preceding
the SOAP requests, formed by a client application. For more details on this
subject, refer to the "SQL Authentication over SOAP" topic in the SQL
Server Books Online.

In the case of SOAP-based communication, CREATE ENDPOINT and ALTER ENDPOINT
statements also support the AUTHENTICATION clause, which controls the transport
protocol (HTTP) authentication mechanism and which can be assigned one of three
values:

  • INTEGRATED – applies Windows-based Kerberos or NTLM
    authentication protocol when establishing HTTP communication between the client
    and server. The choice of the authentication protocol depends on the
    capabilities of the client operating system. Older ones (such as Windows 9x and
    Windows NT 4.0 are limited to NTLM only), while the newer ones will attempt
    Kerberos first (responding to negotiate challenge from the server). However, in
    order for mutual Kerberos authentication to work, the SQL Server service
    account must be associated with a Service Principal Name (SPN). This can be
    accomplished by running sp_register_kerberos_spn_for_http stored procedure,
    which creates two SPNs, for NetBIOS and fully qualified DNS names,
    respectively. If the SQL Server runs in the security context of the Local
    System account, the SPN is associated with the computer account of the server
    hosting the SQL Server installation.

  • DIGEST – based on MD5 one-way hashing algorithm, which is applied
    to user’s Windows credentials on the client side and compared with the result
    of the same algorithm being applied on the server side.

  • BASIC – compares BASE64-encoded Windows credentials on the client
    and server side. Note that encoding is easily reversible and therefore must be
    used in combination with Secure Sockets Layer encryption of HTTP connection,
    enforced by PORTS=(SSL) option on the endpoints.

In the next article, we will look into authorization improvements in SQL
Server 2005 Beta 2.

»


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