Access to SQL Server resources is controlled by two separate mechanisms. The
first one is authentication, which determines the identity of a user attempting
to connect, based on a verifiable identifier. The second one is authorization,
which establishes the level of privileges granted to a login associated with
the logged on user. In this article, we will focus on the authentication
(authorization will be covered later in the series).
In the case of SQL Server, authentication is handled by comparing
credentials provided by a connecting user with set of entries stored in the sysxlogin
table of the SQL server master database (to get familiar with its content, you
might want to use sysloging view, presenting data in a more friendly format).
The way these credentials are provided depends on the authentication mode,
which can be set to one of the following:
-
SQL Server authentication - in this case, SQL Server
requires that a user specifies a name and password at the time when connection
is attempted. The login name and one-way hash of the password need to match one
of entries in the sysxlogins table for the login attempt to be successful.
-
Windows authentication - in this case, SQL Server does not
prompt a user for credentials, but instead it uses an access token assigned at
the time the user logged on using a Windows account. This can be a token
generated by the Windows operating system on which SQL Server was installed (in
case of a local user account) or by a Windows NT 4.0 or Active Directory domain,
(if a user domain account is used). The token contains a security identifier
(SID) which uniquely identifies the user, as well as SIDs of local (or domain)
groups that the user is a member of. SQL compares all of the SIDs stored in the
token against entries in the sysxlogin table and, depending on the outcome, it
grants or denies login privileges. Presence of a matching entry is not
sufficient though for logon to be allowed, since with Windows authentication, it
is possible to not only grant login privileges to a specific SID but also deny
them (denying rights will always take precedence over granting them). This
means that you can, for example, grant login privileges to a Windows group, but
still deny access to SQL server to some of its members.
SQL Server creates two default logins (entries in the sysxlogin table) at
the installation time, granted the highest possible level of administrative
privileges. The first one is the sa login, allowing access using SQL Server
authentication, the second is the BUILTIN\Administrators, representing SID of
the local Administrators Windows group on the SQL Server computer and allowing
every member of this group to access the server via Windows authentication. As
part of the installation procedure, you are prompted to assign a password to
the sa account. Make sure to choose a complex one (Spida worm took advantage of
SQL Servers where sa was assigned a blank password). SQL Server logins
passwords can be up to 128 characters long and contain any type of ASCII
characters, however, you should note that password strength is affected by case
sensitivity settings. Case insensitive installations store hashes of passwords
in sysxlogins table after converting all characters to upper case first, which
makes them more vulnerable to brute force attacks and defeats the purpose of
creating mixed case passwords. As an additional security measure, you might
consider modifying default Windows authentication configuration by removing
BUILTIN\Administrators login, replacing it with another group or user account,
and assigning to it Sysadmin fixed server role. This is recommended in
environments where Windows server and SQL server administration is handled by
separate teams. For more information on this procedure, including possible side
effects and cluster specific caveats, refer to the
Microsoft Knowledge Base article 263712.
At the installation time, you also need to decide on the authentication
mode, although you can change it afterwards from the Security tab of the SQL
Server Properties dialog box of the SQL Enterprise Manager console (the change
requires that SQL Server service be restarted). Two available options are
"SQL Server and Windows" and "Windows only," which means
that you cannot disable Windows authentication, regardless of your choice. In
either case, you can create new logins from the Logins node in Security folder
of SQL Server Enterprise Manager or with system stored procedures (sp_addlogin
for standard SQL logins and sp_grantlogin for Windows-based logins). When
creating a new login, in the New Login dialog box, you can either select an
existing user or group account from any available Windows domain or local
Windows account store on the SQL Server computer (subject to Windows
authentication), or type in a new name and password (subject to SQL Server
Authentication). As mentioned before, when selecting accounts for Windows
authentication, you can not only grant, but also explicitly deny access. In the
same dialog box, you can assign logins to fixed server roles and set individual
database access (using "Server Roles" and "Database Access"
tabs, respectively). We will describe these configuration options when
discussing authorization later in this series.
Windows authentication is inherently more secure than SQL Server
authentication (and therefore recommended by Microsoft). Windows credentials
are delivered to SQL Server without passing the actual password, while SQL
authentication sends the login name and password in unencrypted format, using a
fairly simple obfuscation algorithm, involving conversion to Unicode, bit
swapping, and a couple of XOR operations with a constant value (an algorithm
reversing the obfuscation algorithm, which produces original password can be
downloaded from http://www.sqlsecurity.com/Portals/57ad7180-c5e7-49f5-b282-c6475cdb7ee7/decrypt_ODBC_sql.txt).
This means that anyone who can capture network traffic carrying client
authentication information can easily retrieve it. If you are forced to resort
to using SQL Authentication (when your clients are running legacy operating
systems such as Windows 98 or Me, or are not part of the same or trusted
domain), to protect passwords from being easily captured and exposed, you
should always encrypt communication between SQL server and its clients with the
built-in feature of Multiprotocol Net Library or by implementing SSL (for
details, refer to the first
article of this series).
Windows domain environment provides a number of additional advantages over
SQL Server 2000 authentication mechanism. For both local and domain Windows
accounts, you can implement account policies, enforcing password complexity,
maximum and minimum age, history, as well as lockout settings. This mitigates
the effectiveness of brute force attacks. Note, however, that the highest level
of security is provided by Kerberos authentication, which requires that login
accounts reside in a Windows 2000 or 2003 native mode Active Directory domain
and that client computers run Windows 2000 or later. NTLM authentication
protocol used in legacy operating systems is inherently less secure and can be
exploited using a number of popular hacking utilities (such as L0phtcrack -
currently available as LC4). If you cannot switch to Kerberos, you should
implement NTLM v2 (much more resilient than its predecessor), following
instructions in the
Microsoft Knowledge Base article 239869.
Another benefit of operating in the native Active Directory environment is the
ability to use delegation. In order to understand its functionality, you need
to first get familiar with another, closely related feature called
impersonation, much more prevalent in Windows operating systems. Its primary
purpose is to allow system processes and applications to run tasks on behalf of
a user. Impersonation is employed whenever a user starts a new process or
application, so their tasks execute in the user's security context and their
security boundaries reflect the user's privileges. This way, when a user
launches a Command Prompt or Windows Explorer, capabilities of each program are
limited by what the user account is allowed to do. However, impersonation has
its limitations. While it works fine for processes operating on the same system
on which a user is logged on, remote processes (processes created on systems
remote to the user) can take advantage of impersonation only when accessing
their local resources (resources residing on the same remote computer). In
other words, if a user X logged on to a computer A launches a process
impersonating this user on a computer B, than this process is not capable to
access resources on a computer C in the security context of the same user (i.e.
user X).
In the context of SQL Server operations, this creates a problem with linked
servers (for more information on linked servers, refer to the Books Online)
where local and linked servers are set up with Windows Authentication mode.
Ideally, in the scenario where all SQL Servers authenticate against the same
Active Directory domain (or two Active Directory domains linked by trust
relationships) and a user logged on to the first server wants to run a
distributed query against a linked server, the same user's credentials should
be used to connect to it. Unfortunately, creating such configuration is not
possible with impersonation. In order to accomplish this goal, you need to resort
to delegation.
Several important conditions need to be satisfied in order for delegation to
work properly. First of all, since delegation is the function of Kerberos
authentication protocol, your server and user accounts need to reside in a
native mode Active Directory domain or trusted domains (Windows 2000 or 2003).
User accounts that will be connecting to linked servers need to be trusted for
delegation (this is enabled from the Account tab of the user's account
Properties dialog box in the Active Directory Users and Computers console).
Accounts of SQL servers involved in distributed queries must be trusted for
delegation (this is done by checking the "Trust computer for
delegation" checkbox on the General tab of each computer's account
Properties dialog box in the Active Directory Users and Computers console). You
also need to ensure that there exists a Service Principal Name (SPN) associated
with each SQL Server instance (with a unique port for each instance, if
multiple instances are installed on the same computer). If the SQL Server
service runs in the security context of the Local System account, then a
temporary SPN is created every time the service starts, so no additional
configuration is needed. However, if the service uses a local or domain user
account, you need to create one with the Windows 2000 Resource Kit utility
SETSPN.EXE (downloadable from the
Microsoft Web site) and execute if from the Command Prompt using the
following syntax:
SETSPN -A MSSQLSvc/SQLServerName:SQLPort SQLServiceAccount
The command listed above generates a new SPN for the SQL server SQLServerName
(this has to be in the form of the fully qualified domain name) listening on
the TCP port SQLPort and operating in the security context of the SQLServiceAccount.
In addition, you need to ensure that servers communicate using TCP/IP Net Library,
since this is the only one which supports Kerberos based authentication.
This concludes our coverage of authentication related topics. In our next article,
we will look closer into configuration of SQL Server service accounts and their
impact on server security.
»
See All Articles by Columnist Marcin Policht
» 
Comment and Contribute
