More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
MS SQL Server DBA Checklist - General - Security
-- Ensure the physical security of each SQL Server, preventing any unauthorized users from physically access your servers.
-- Only install required network libraries and network protocols on your SQL Server instances.
-- Minimize the number of sysadmins allowed to access SQL Server.
-- As a DBA, log on with sysadmin privileges only when needed. Create separate accounts for DBAs to access SQL Server when sysadmin privileges are not needed.
-- Assign the SA account a very obscure password, and never use it to log onto SQL Server. Use a Windows Authentication account to access SQL Server as a sysadmin instead.
-- When assigning permissions to users, only give them the minimum permissions they need to perform their jobs.
-- Use stored procedures or views to allow users to access data instead of letting them directly access tables.
-- When possible, use Windows Authentication logins instead of SQL Server logins.
-- Use strong passwords for all SQL Server login accounts.
-- Don’t grant permissions to the public database role.
-- Remove user login IDs who no longer need access to SQL Server.
-- Remove the guest user account from each user database.
-- Disable cross database ownership chaining if not required.
-- Never grant permission to the xp_cmdshell to non-sysadmins.
-- Use Windows Global Groups, or SQL Server Roles to manage groups of users that need similar permissions.
-- Avoid creating network shares on any SQL Server.
-- Turn on login auditing so you can see who has succeeded, and failed, to login. In SQL Server 2008, you can use SQL Server Audit instead.
-- Don’t use the SA account, or login IDs who are members of the Sysadmin group, as accounts used to access SQL Server from applications.
-- Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
-- Remove the BUILTIN/Administrators group to prevent local server administrators from being able to access SQL Server. Before you do this on a clustered SQL Server, check Books Online for more information.
-- Run each separate SQL Server service under a different Windows domain account.
-- Only give SQL Server service accounts the minimum rights and permissions needed to run the service. In most cases, local administrator rights are not required, and domain administrator rights are never needed. SQL Server setup will automatically configure service accounts with the necessary permissions for them to run correctly, you don’t have to do anything, with one possible exception. You may want to give the SQL Server service account SE_MANAGE_VOLUME_NAME rights so that instant file initialization can be used for SQL Server 2005/2008. This is only required if the SQL Server service account is not a member of the local administrator’s group.
-- When using distributed queries, use linked servers instead of remote servers.
-- Do not browse the web from a SQL Server.
-- Instead of installing virus/antispyware protection on a SQL Server, perform scans from a remote server during a part of the day when user activity is less.
-- Add operating system and SQL Server service packs and hot fixes soon after they are released and tested, as they often include security enhancements.
-- Encrypt all SQL Server backups with a third-party backup tool, such as SQL Backup Pro. If you have SQL Server 2008, Enterprise Edition, you can use Transparent Data Encryption to ensure encrypted backups.
-- Only enable C2 auditing or Common Criteria compliance if required.
-- SQL Server 2008 includes a new built-in auditing tool called SQL Server Audit. It can be used to audit virtually any user activity. Keep the number of activities and objects you audit to a minimum to reduce performance overhead.
-- Consider running a SQL Server security scanner against your SQL servers to identify security holes.
-- If using SQL Server 2005/2008, enable password policy checking.
-- If running SQL Server 2008, Enterprise Edition, consider implementing Transparent Data Encryption to help protect data stored on disk.
(Continued on next question...)
