Interview Preparation: SQL Server 2000 Security "Best Practices".

The article in the series provides you with a list of SQL Server 2000 Security "Best Practices".
--------------------------------------------------------------------------------
Change the sysadmin password!
This is an absolutely necessary. Use a strong password. Keep the password secure.
--------------------------------------------------------------------------------
Install the latest security patches and service packs
With severe virus breakouts now commonplace, it is crucial to keep your SQL Server Instance patched with the latest service packs and security patches.
--------------------------------------------------------------------------------
Sign up for the Microsoft Security Notification Service
To be notified by Microsoft when new security patches are released, sign up for the notification service here.
--------------------------------------------------------------------------------
When possible, use integrated security
This is a tough policy for most businesses to adopt. 3rd party applications continue to use of SQL Logins in their products. When possible, however, do use a integrated security (Windows Authentication Logins/Groups). It is significantly more secure than using SQL Logins (which can be used by anonymous sources).
--------------------------------------------------------------------------------
When possible, use Windows Groups over individual Windows users
In addition to using Windows Authentication, consider using Windows Groups to assign SQL Server permissions instead of individual Windows users. Assigning permissions to Windows Groups allows you to consolidate management of permissions and ensure consistent security access.
--------------------------------------------------------------------------------
Use the rule of "least permissions"
Set up logins with only the permissions they need. Avoid adding logins to server or database roles that provide more access than they need.
--------------------------------------------------------------------------------
Grant permissions at the database role level
Avoid granting individual object permissions to specific database users. Instead, assign them to a database or user-defined role, and then make the database user a member of that role.
--------------------------------------------------------------------------------
Do not allow dynamic SQL!
Minimize potential SQL Injection attacks and performance issues by restricting dynamic SQL within your Transact-SQL code. Rewrite calls to EXEC or sp_executesql with static SQL instead.
--------------------------------------------------------------------------------
Use stored procedures
Don't embed Transact-SQL DML or DDL statements within your web pages. Use stored procedure calls instead. When calling stored procedures, make sure your application is validating (for SQL Injection) all parameters passed.
--------------------------------------------------------------------------------
Evaluate firewall configurations
Keep the SQL Server machine behind a firewall. Based on your network architecture, evaluate whether or not specific TCP/IP ports should be blocked (TCP port 1433, UDP port 1434, or other ports for named instances). Use the Server Network Utility to determine the ports being used.
--------------------------------------------------------------------------------
Disable cross-database ownership chaining
Cross-database ownership chaining (occurs when a source object depends on objects in other databases), when enabled, can create security access for unintended database users. Beginning with SP3, you now have the ability to turn this behavior off both at the server and the database level.
--------------------------------------------------------------------------------
Restrict EXEC access to xp_cmdshell
Xp_cmdshell access in the wrong hands can cause tremendous harm to your server. If SQL Server is running under a Domain Windows Account, other servers could be impacted as well.
--------------------------------------------------------------------------------
Minimize the SQL Server and SQL Server Agent Service Account permissions
When possible, use the system account option for the SQL Server and SQL Server Agent Service Accounts. If that is not possible, choose an account with the least amount of necessary permissions.
--------------------------------------------------------------------------------
Minimize the permissions of the non-sysadmin proxy account
If you must configure this proxy account, remember that it's permissions are now opened up to non-sysadmin users. Minimize the permissions of this account.
--------------------------------------------------------------------------------
Schedule clean-up time, Remove inactive users
Remove logins, database users, and linked server connections which are no longer in-use. Remove logins for "departed" employees or clients immediately.
--------------------------------------------------------------------------------
Monitor your server role permission
Security has a way of "changing" over time, particularly if you have multiple employees with sysadmin or administrator permissions to your SQL Server Instance. At least weekly, check membership to server roles. You can do this by running the following Transact-SQL script:

SELECT 'ServerRole' = A.name,
'MemberName' = B.name
FROM master.dbo.spt_values A
INNER JOIN master.dbo.sysxlogins B ON
A.number & B.xstatus = A.number
WHERE A.low = 0 AND
A.type = 'SRV'
AND B.srvid IS NULL

--------------------------------------------------------------------------------
Check the error log for login failures
You can check the SQL Log in Enterprise Manager, or via Transact-SQL for all login failures to your database:

-- Script that populates errors into
-- a temporary table.

CREATE TABLE #errors
(errorlog varchar(255), continuationrow int)

INSERT #errors
(errorlog, continuationrow)
EXEC xp_readerrorlog

SELECT errorlog
FROM #errors WHERE errorlog LIKE '%Login failed%'

DROP TABLE #errors

Login failure logging must be turned on. To do so, in Enterprise Manager, right click the server you wish to enable and select Properties. On the Security tab, select 'Failure'. You must reboot for this new setting to take effect.
--------------------------------------------------------------------------------
Lock down file directory permissions and physical access to the SQL Server machine
Evaluate the security of file/administrative shares and directories. Remove innappropriate permissions. In addition to NTFS security, make sure the actual SQL Server machine is kept in a secure area. The screen should be locked (requiring a valid Windows login) at the terminal. Terminal Services access should also be restricted to the necessary staff.

Remove any sensitive file data from the SQL Server machine, including log files generated from the SQL Server 2000 installation (sqlstp.log, sqlsp.log, and setup.iss under :\Program Files\Microsoft SQL Server\MSSQL\Install) and :\Program Files\Microsoft SQL Server\ MSSQL$\Install folder for named instances.
--------------------------------------------------------------------------------
Download and use Microsoft Baseline Security Analyzer V1.2
A free tool that looks for issues on your SQL Server machine. This tool reviews security concerns outside of SQL Server too. You can download it here.
--------------------------------------------------------------------------------
Download and use Microsoft's Best Practices Analyzer Tool
This free tool isn't just limited to security concerns, but also evaluates server settings, database settings, and database object Transact-SQL. Download it here.

More on Josepsh Sack's book on SQL Server 2000 Fast Answers.