Sql server 2005 Security Best Practices Operational and Administrative Tasks
|
|  Yüklə 274 Kb.
|
Authentication ModeSQL Server has two authentication modes: Windows Authentication and Mixed Mode Authentication. In Windows Authentication mode, specific Windows user and group accounts are trusted to log in to SQL Server. Windows credentials are used in the process; that is, either NTLM or Kerberos credentials. Windows accounts use a series of encrypted messages to authenticate to SQL Server; no passwords are passed across the network during the authentication process. In Mixed Mode Authentication, both Windows accounts and SQL Server-specific accounts (known as SQL logins) are permitted. When SQL logins are used, SQL login passwords are passed across the network for authentication. This makes SQL logins less secure than Windows logins. It is a best practice to use only Windows logins whenever possible. Using Windows logins with SQL Server achieves single sign-on and simplifies login administration. Password management uses the ordinary Windows password policies and password change APIs. Users, groups, and passwords are managed by system administrators; SQL Server database administrators are only concerned with which users and groups are allowed access to SQL Server and with authorization management. SQL logins should be confined to legacy applications, mostly in cases where the application is purchased from a third-party vendor and the authentication cannot be changed. Another use for SQL logins is with cross-platform client-server applications in which the non-Windows clients do not possess Windows logins. Although using SQL logins is discouraged, there are security improvements for SQL logins in SQL Server 2005. These improvements include the ability to have SQL logins use the password policy of the underlying operating system and better encryption when SQL passwords are passed over the network. We'll discuss each of these later in the paper. SQL Server 2005 uses standard DDL statements to create both Windows logins and SQL logins. Using the CREATE LOGIN statement is preferred; the sp_addlogin and sp_grantlogin system stored procedures are supported for backward compatibility only. SQL Server 2005 also provides the ability to disable a login or change a login name by using the ALTER LOGIN DDL statement. For example, if you install SQL Server 2005 in Windows Authentication mode rather than Mixed Mode, the sa login is disabled. Use ALTER LOGIN rather than the procedures sp_denylogin or sp_revokelogin, which are supported for backward compatibility only. If you install SQL Server in Windows Authentication mode, the sa login account is disabled and a random password is generated for it. If you later need to change to Mixed Mode Authentication and re-enable the sa login account, you will not know the password. Change the sa password to a known value after installation if you think you might ever need to use it. Best practices for authentication mode
Network ConnectivityA standard network protocol is required to connect to the SQL Server database. There are no internal connections that bypass the network. SQL Server 2005 introduces an abstraction for managing any connectivity channel—entry points into a SQL Server instance are all represented as endpoints. Endpoints exist for the following network client connectivity protocols:
In addition, endpoints may be defined to permit access to the SQL Server instance for:
Following is an example of creating an endpoint for Service Broker. CREATE ENDPOINT BrokerEndpoint_SQLDEV01 AS TCP
( LISTENER_PORT = 4022 ) FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS )
In keeping with the general policy of "off by default, enable only when needed," no Service Broker, HTTP, or database mirroring endpoints are created when SQL Server 2005 is installed, and the VIA endpoint is disabled by default. In addition, in SQL Server 2005 Express Edition, SQL Server 2005 Developer Edition, and SQL Server 2005 Evaluation Edition, the Named Pipes and TCP/IP protocols are disabled by default. Only Shared Memory is available by default in those editions. The dedicated administrator connection (DAC), new with SQL Server 2005, is available only locally by default, although it can be made available remotely. Note that the DAC is not available in SQL Server Express Edition by default and requires that the server be run with a special trace flag to enable it. Access to database endpoints requires the login principal to have CONNECT permission. By default, no login account has CONNECT permission to Service Broker or HTTP Web Services endpoints. This restricts access paths and blocks some known attack vectors. It is a best practice to enable only those protocols that are needed. For example, if TCP/IP is sufficient, there is no need to enable the Named Pipes protocol. Although endpoint administration can be accomplished via DDL, the administration process is made easier and policy can be made more uniform by using the SQL Server Surface Area Configuration tool and SQL Server Configuration Manager. SQL Server Surface Area Configuration provides a simplified user interface for enabling or disabling client protocols for a SQL Server instance, as shown in Figure 1 and Figure 2. Configuration is described in Knowledge Base article KB914277, How to configure SQL Server 2005 to allow remote connections, as well as in SQL Server 2005 Books Online. A screenshot showing the remote connections configuration dialog box is shown in Figure 1.
Figure 1 Configuring remote connections In the Surface Area Configuration for Services and Connections dialog box, you can see if any HTTP or Service Broker endpoints are defined for the instance. New endpoints must be defined by using DDL statements; SQL Server Surface Area Configuration cannot be used to define these. You can use the Surface Area Configuration for Features tool to enable remote access to the dedicated administrator connection. SQL Server Configuration Manager provides more granular configuration of server protocols. With Configuration Manager, you can:
The dialog for configuring TCP/IP address properties such as port numbers and dynamic port assignment is shown in Figure 2. 
Figure 2 TCP/IP Addresses configuration page in SQL Server Configuration Manager SQL Server 2005 can use an encrypted channel for two reasons: to encrypt credentials for SQL logins, and to provide end-to-end encryption of entire sessions. Using encrypted sessions requires using a client API that supports these. The OLE DB, ODBC, and ADO.NET clients all support encrypted sessions; currently the Microsoft JDBC client does not. The other reason for using SSL is to encrypt credentials during the login process for SQL logins when a password is passed across the network. If an SSL certificate is installed in a SQL Server instance, that certificate is used for credential encryption. If an SSL certificate is not installed, SQL Server 2005 can generate a self-signed certificate and use this certificate instead. Using the self-signed certificate prevents passive man-in-the-middle attacks, in which the man-in-the-middle intercepts network traffic, but does not provide mutual authentication. Using an SSL certificate with a trusted root certificate authority prevents active man-in-the-middle attacks and provides mutual authentication. In SQL Server 2005, you can GRANT, REVOKE, or DENY permission to CONNECT to a specific endpoint on a per-login basis. By default, all logins are GRANTed permission on the Shared Memory, Named Pipes, TCP/IP, and VIA endpoints. You must specifically GRANT users CONNECT permission to other endpoints; no users are GRANTed this privilege by default. An example of granting this permission is: GRANT CONNECT ON MyHTTPEndpoint TO MyDomain\Accounting Best practices for network connectivity
|
