Before we dive into the concepts of authentication and
authorization, it is important to discuss a feature added in SQL Server
2005 called endpoints. In
earlier versions of SQL Server, clients could connect via Transport
Control Protocol (TCP), named pipes, shared memory, and Virtual
Interface Architecture (VIA). As long as one of these protocols was
enabled on the server and the user had a valid login, the connection
was accepted. SQL Server 2005 introduced a separation of this behavior
via endpoints.
Endpoints
can be considered a point of entry into SQL Server. Administrators can
create an endpoint not only for TCP, named pipes, shared memory, and
VIA, but also for Hypertext Transfer Protocol (HTTP). Once an endpoint
is created, you can restrict access so that users can connect only via
a certain endpoint type. For example, you might create a login named Login1 and grant access to the HTTP endpoint while denying access to all other endpoints. In this case, Login1
can access SQL Server only via the HTTP endpoint; it cannot directly
connect to SQL Server via TCP or any of the other protocols. To see how
this endpoint verification affects authentication, let’s examine the
process of making a client connection.
1. How Clients Establish a Connection
If
a TCP client wants to make a connection to SQL Server, it must first
know which port to connect to. Prior to SQL Server 2005, there was
always a thread waiting on User Datagram Protocol (UDP)
port 1434 whose purpose was to return details on all of the running
instances of SQL Server, as well as the port numbers of those
instances. All a client had to do was make a UDP
connection to port 1434 and then determine which port it wanted to
connect to, given a specific instance of SQL Server. This process
generally worked until hackers found a way to launch a Denial of
Service (DoS) attack on SQL Server by continuously sending packets to
this port requesting the enumeration. Because the enumeration process
was part of the SQL Server service, the “SQL Slammer” worm virus
created serious problems for SQL Server installations. As of SQL Server
2005, this functionality has been pulled out into a separate service
named the SQL Server Browser service that can be turned on and off
without touching the SQL Server service itself.
Note
Of
course, this means that the Browser service is now vulnerable to DoS
attacks. You can mitigate this concern if you block port 1434 on your
firewall. If you are concerned about intranet attacks from inside the
firewall, you might want to consider not running the Browser service at
all and explicitly pass port numbers on the connections strings instead.
After the network connection
request and pre-login handshake have been made, SQL Server must
authenticate the user to make sure that he or she has access to begin
with. Figure 1 depicts the authentication process.
At this point, the service accepts the login credentials
supplied by the user and attempts to validate them. If successful, the
login is authorized against the endpoint corresponding to the type of
connection made to SQL Server. In this example, the login is checked to
see whether it has been granted CONNECT
permissions to the TCP endpoint. If this is true, the authentication
process proceeds; otherwise, the connection fails at this point.
Note
By default, new logins are automatically granted CONNECT permissions to the TCP endpoint.
Once
the login has passed the endpoint check, SQL Server switches to a
database context (the default database specified for the login or
specified in the connection string). It then attempts to authenticate
the login as a user of that database. If the login can be
authenticated, the connection succeeds; otherwise, it fails. When a
database context has been established and the login has been
authenticated, the user can perform work against the database on the
server.
In the Windows world, administrators can set login expirations and enforce password
policies (for example, requiring passwords to be a certain length and
contain a mixture of special characters). Traditionally in SQL Server,
SQL logins never respected these global policy settings. Since SQL
Server 2005, both Windows-authenticated and SQL logins obey the group
policy settings defined in the Windows domain.
Note
Password policy enforcement is available only in SQL Server running on Microsoft Windows Server 2003 and later.
To help illustrate password policies, let’s define a minimum password length using the Local Security Policy applet located in the Administrative Tools folder of Control Panel. This tool is shown in Figure 2.
Using
this tool, you can change a variety of parameters. For example, set the
minimum password length to eight characters, as shown in Figure 2, and then attempt to create a login as shown in Example 1.
Example 1. Attempting to create a login that does not satisfy the Windows password policy.
CREATE LOGIN SomeUser WITH PASSWORD = '2short',
CHECK_POLICY=ON,
CHECK_EXPIRATION=ON
This
statement fails because the password has only seven characters and the
Windows password policy requires a minimum of eight characters:
Msg 15116, Level 16, State 1, Line 1
Password validation failed. The password does not meet Windows policy requirements because
it is
too short.
The system view sys.sql_logins returns information about logins, such as whether the policies
and expirations are set. If you want additional information regarding a
particular login, such as how many bad passwords were attempted, you
can use the LOGINPROPERTY built-in function, as demonstrated in Example 2.
Example 2. Querying the LOGINPROPERTY function.
SELECT 'IsLocked' AS Property, LOGINPROPERTY('sa', 'IsLocked') AS Value
UNION ALL SELECT 'IsExpired', LOGINPROPERTY('sa', 'IsExpired')
UNION ALL SELECT 'IsMustChange', LOGINPROPERTY('sa', 'IsMustChange')
UNION ALL SELECT 'BadPasswordCount', LOGINPROPERTY('sa', 'BadPasswordCount')
UNION ALL SELECT 'PasswordLastSetTime', LOGINPROPERTY('sa', 'PasswordLastSetTime')
UNION ALL SELECT 'BadPasswordTime', LOGINPROPERTY('sa', 'BadPasswordTime')
UNION ALL SELECT 'LockoutTime', LOGINPROPERTY('sa', 'LockoutTime')The query results show various properties of the sa login:
Property Value
------------------- ----------------------------------
IsLocked 0
IsExpired 0
IsMustChange 0
BadPasswordCount 0
PasswordLastSetTime 2012-01-14 00:19:57.200
BadPasswordTime 1900-01-01 00:00:00.000
LockoutTime 1900-01-01 00:00:00.000
