This document is provided without warranty, always vet out what works best for you and your organization.

SQL Standard Security Standards

Scope

This standard applies to all Corporate equipment and data, including Corporate customer data, whether located at a Corporate facility or a third party facility, and whether handled by Corporate employees, or Corporate contractors, vendors, third party service providers, or their staff or agents. This standard also applies to all wholly owned and partially owned subsidiaries.

The guidance in this standard shall be considered the minimum acceptable requirements for the use of SQL server. This standard sets forth expectations across the entire organization. Additional guidance and control measures may apply to certain areas of Corporate. This standard shall not be construed to limit application of more stringent requirements where justified by business needs or assessed risks.

Secure SQL Standard

Corporate’s business functions rely upon the integrity, confidentiality, and availability of its computer systems and the information assets stored within them. Responsibilities and procedures for the management, operation and security of all information processing facilities must be established. This standard supports the stated objectives.

Roles & Responsibilities

The End User (including systems owners and administrators) is responsible for providing justifications to their activities detected through any monitoring.

The IT Custodian is responsible for defining and implementing security measures and controls to ensure the system(s)/application(s) are managed and operated in a secure and effective manner.

The Chief Information Security Officer has overall responsibility for security standard, and in conjunction with the Information Security Department will be responsible for defining, implementing, managing, monitoring and reviewing compliance with the Electronic Messaging Standard.

The Information Security Department will assist End Users and IT Custodians in assessing, defining, implementing, managing and monitoring appropriate controls and security measures.

The Information Security Department will audit and review the adequacy of controls and security measures in place to measure and enforce conformance to this standard.

Requirements and Implementations

For Administrators

  • Setting up the environment prior to installation
  • Ensure the physical security of your server.
  • Put a firewall between your server and the Internet.
  • Always block TCP port 1433 and UDP port 1434 on your perimeter firewall. If named instances are listening on additional ports, block those too.
  • In a multi-tier environment, use multiple firewalls to create screened subnets.
  • Isolate services to reduce the risk that a compromised service could be used to compromise others.
  • Never install SQL Server on a domain controller.
  • Recommend running separate SQL Server services under separate Windows accounts.
  • In a multi-tier environment, do not co-habitate SQL Server and other tiers in the same server.
  • Create Windows accounts with the lowest possible privileges for running SQL Server services. To avoid causing problematic object ownership issues on applications.
  • Use NTFS filesystem.
  • Use RAID for critical data files.
  • Installation
  • Always install the latest service packs and Microsoft patches marked as ‘critical’. Recommend all service packs and patches be tested against SQL servers and associated applications prior to implementation in production environment. Security patches marked as ‘critical’ by Microsoft can be applied without testing but should be applied with backups properly done on the SQL servers and associated application or web servers.
  • Run SQL Server services with the lowest possible privileges. To avoid causing problematic object ownership issues on applications, the service account used to launch the SQL server and agent services are recommended to have local administrator previledges.
  • Use Enterprise Manager to associate services with Windows accounts.
  • Require Windows Authentication for connections to SQL Server.
  • Always assign a strong password to the sa account, even when using Windows Authentication.
  • Always use strong passwords for all SQL Server accounts.
  • Data file and log file should be created on separate drive.
  • Configuration Options and Settings After Installation
  • If necessary, delete or archive the following files after installation: sqlstp.log, sqlsp.log, and setup.iss in the <systemdrive>:\Program Files\Microsoft SQL Server\MSSQL\Install folder for a default installation, and the <systemdrive>:\Program Files\Microsoft SQL Server\ MSSQL$<Instance Name>\Install folder for named instances. Use this step at your discretion and should avoid mistakes when deleting wrong files.
  • If the current system is an upgrade from SQL Server 7.0, delete the following files: setup.iss in the %Windir% folder, and sqlsp.log in the Windows Temp folder. Use this step at your discretion and should avoid mistakes when deleting wrong files.
  • Assign static ports to named instances of SQL Server.
  • Set login auditing level to failure.
  • Enable security auditing of Sysadmin actions, fixed role membership changes, all login related activity, and password changes for SQL 2005 and later – via default trace.
  • After selecting appropriate auditing options, you should script the audit, wrap it in a stored procedure,and mark that stored procedure for AutoStart.
  • Assign a strong password to the sa account, even on servers that are configured to require Windows Authentication.
  • Remove sample databases from production servers.
  • Secure Operation
  • Learn to work with the SQL Server security model.
  • Back up all data regularly and store copies in a secure off-site location.
  • Test your disaster recovery system.
  • Reduce the attack surface area of your system by running only those services and features needed in your environment.
  • Restrict membership of the sysadmin fixed server role to a known limited number of trusted individuals. A separate SQL admin group should be created for database administrators. The default server admin group should have only server administrators. This would help to establish a layer of segregation of duties between the server administrator role and the database administrator role.
  • Perform a Quarterly Audit on ALL system level accounts.
  • Ensure that you use complex passwords for all SQL Server accounts; enforce domain-level complexity for SQL 2005 and later.
  • Disable cross database ownership chaining if your system does not use it.
  • By default, only members of the sysadmin role can execute xp_cmdshell. You should not change this default.
  • Do not grant execute permission on xp_cmdshell to users who are not members of the sysadmin role.
  • Install a certificate to enable SSL connections.
  • Certificates should use the fully-qualified DNS name of the server.
  • Create a new dedicated SQL Server service account to encrypt database files with EFS.
  • If your application requires data encryption, consider using the products of such vendors as Protegrity and Application Security Inc.
  • Collect users into SQL Server roles or Windows groups to simplify permissions administration.
  • Never grant permissions to the public database role.
  • When setting up SQL Server in an environment that supports distributed queries, use linked servers rather than remote servers.
  • Allow linked server access only to those logins that need it.
  • Disable ad hoc data access on all providers except SQL OLE DB, for all users except members of the sysadmin fixed server role - except when such access is explicitely requested, this should be provided on an expiring basis.
  • Allow ad hoc data access only on trusted providers.
  • Do not enable the guest account.
  • If you need to change the account associated with a SQL Server service, use SQL Server native tools for non-clusrers; use Cluster Services for clusters.
  • If you change multiple services, you must apply the changes to each service separately using SQL Server native tools.
  • Recommended Periodic Administrative Procedures
  • Add MBSA to your weekly maintenance schedule, and follow up on any security recommendations that it makes.
  • Periodically scan for accounts with NULL passwords and remove them or assign them strong passwords.
  • Disable and/or Delete unused accounts.
  • Periodically scan fixed server and database roles to ensure that membership is only granted to trusted individuals.
  • Verify the safety of stored procedures that have been marked for AutoStart
  • Ensure that the mapping between database users and logins at the server level is correct.
  • Run sp_change_users_login with the report option regularly to ensure that the mapping is as expected.
  • Do not allow direct catalog updates.
  • Use sp_dboption to enumerate and validate databases for which cross database ownership chaining has been enabled.
  • Best Practices for Patching Instances
  • Keep an inventory of all versions, editions, and languages of SQL Server for which you are responsible.
  • Include instances of MSDE in your inventory.
  • Use SQL Scan and SQL Check, available from the Microsoft Web site, to scan for instances of SQL Server within your domain.
  • Subscribe to Microsoft security bulletins.
  • Maintain test systems that match the configuration of you production systems, and are readily available for testing new patches.
  • Test patches carefully before applying them to production systems.
  • Consider patching development systems with relatively little testing.

For Developers

  • General
  • Use ownership chaining within a single database to simplify permissions management.
  • Avoid using cross database ownership chaining when possible.
  • If you must use cross database ownership chaining, ensure that the two databases are always deployed as a single administrative unit.
  • Assign permissions to roles rather than directly to users.
  • Objects may be owned by roles, rather than directly by users, if you want to avoid application changes when the owning user is dropped.
  • Enable encrypted connections to your server, and consider allowing only encrypted connections.
  • When allowing SQL Server Authentication, you are strongly urged to encrypt either the network layer with IPSec or the session with SSL.
  • Your application should not return SQL Server errors to the end user. Log them instead, or transmit them to the system administrator.
  • Defend against SQL injection by validating all user input before transmitting it to the server.
  • Limit the scope of possible damage by permitting only minimally privileged accounts to send user input to the server.
  • Run SQL Server itself with the least necessary privileges.
  • Multi-tier Options
  • If the application server and the database server are within the same domain, or within trusted domains, you should use Windows Authentication and configure for "full provisioning" in which all client contexts are tunneled to SQL Server. This makes it possible to audit all users who access SQL Server, enables Windows security policy enforcement, and makes it unnecessary to store credentials in the middle tier. In this scenario, the client connects to the application server, which in turn impersonates the client and connects to SQL Server.
  • Every user on the application server must have a valid Windows login on the database server and delegation must be enabled.
  • All systems interacting in this scenario, including the Domain Controller, must run Windows 2000 or higher.
  • The account the application is running under must be trusted for delegation (that is, the Active Directory option Account is trusted for delegation must be turned on for this account).
  • The client account must be able to be delegated (ensure that the Active Directory user account option Account is trusted and cannot be delegated is unchecked).
  • The application service must have a valid Service Principal Name (SPN).
  • Note: Full provisioning is not recommended in cross-enterprise or Internet-scale installations, when your security plan calls for minimizing user access to the database server, or in enterprises with policies prohibiting delegation.
  • When the Internet-facing tier does not have an individual Windows domain account for every possible user, the recommended scenario is to divide authentication into stages. The outer tier (which authenticates users) should use SSL to encrypt at least credentials, if not the entire session. It should connect to the database server using Windows Authentication, forwarding transaction information under a separate security context that is low privileged, with only the permissions necessary to perform its function. This effectively uses the middle tier as an additional layer of defense between your server and the Internet.
  • Note: Using SQL Server Authentication between the middle tier and SQL Server is not recommended, because of the need to store credentials. If you must use SQL Server Authentication between the middle tier and SQL Server, you should create several accounts, with different levels of privileges corresponding to different classes of users. This requires that you add logic to the middle tier to allocate connections according to the desired privilege level.
  • When Windows Authentication between tiers is not possible, you should require SSL encryption of the login sequence. Encrypting the entire session is preferable.
  • You should also use DPAPI to encrypt credentials that must be stored.
  • You should store encrypted credentials in a registry key protected with an ACL.

For Software Vendor

  • Security Processes
  • Ensure that members of your development team understand major security issues: current threats, security trends, changing security environments, and attack scenarios.
  • Require relevant security training for all developers and testers.
  • Increase the awareness of issues like cross-site scripting, buffer overflows, SQL injection, and dangerous APIs.
  • Identify specific categories of threats that apply to your product — for example, denial of service, escalation of privileges, spoofing, data tampering, information disclosure and repudiation.
  • Analyze security threats to your product, component-by-component.
  • Create a security threat checklist based on your product.
  • Add security reviews to all stages (from design to testing) of your product development cycle.
  • If you distribute MSDE with your application, the following additional guidance applies
  • Install MSDE using "Windows security mode" as the default.
  • Never install a blank sa password.
  • When distributing MSDE to your customers, you should use the Microsoft-supplied installer rather than merge modules.
  • When installing an instance of MSDE that will operate only as a local data store, you should disable the Server Net-Libraries.
  • If your product includes MSDE, you should make this known to your customers. In the future, they may need to install or accept MSDE-specific software updates.
  • MSDE installs SQL Server Agent by default, but leaves the Service startup type to "Manual." If your application does not use SQL Server Agent, you should change this to "Disabled." Include security best practice information in your product documentation.

Exceptions under this policy must be detailed in a Risk Acceptance form approved by the System/Application Business Owner, a Executive Lines of Business representative and the IT Custodian and the Information Security Compliance Department.

Page 1 of 1