WikiDBA

I think, for SQL Server Security, the Defense-in-Depth is the best strategy. Defense-in-Depth is an information assurance (IA) concept in which multiple layers of security controls /defense are placed throughout an information technology system. Its intent is to provide redundancy in the event a security control fails or a vulnerability is exploited that can cover aspects of personnel, procedural, technical and physical for the duration of the system’s life cycle.

SQL Server provides a security architecture that is designed to allow database administrators and developers to create secure database applications and counter threats. Each version of SQL Server has improved on previous versions of SQL Server with the introduction of new features and functionality. However, security does not ship in the box. Each application is unique in its security requirements. Developers/DBA need to understand which combination of features and functionality are most appropriate to counter known threats, and to anticipate threats that may arise in the future.

A SQL Server instance contains a hierarchical collection of entities, starting with the server. Each server contains multiple databases, and each database contains a collection of securable objects. Every SQL Server securable has associated permissions that can be granted to a principal, which is an individual, group or process granted access to SQL Server. The SQL Server security framework manages access to securable entities through authentication and authorization.

• Authentication is the process of logging on to SQL Server by which a principal requests access by submitting credentials that the server evaluates. Authentication establishes the identity of the user or process being authenticated.
  
• Authorization is the process of determining which securable resources a principal can access, and which operations are allowed for those resources.
  

SQL Server can be configured to work in either the Windows Authentication Mode or the SQL Server and Windows Authentication Mode, which is also called Mixed Mode.

Windows Authentication Mode

In Windows Authentication Mode only logins for valid Windows users are allowed to connect to SQL Server. In this authentication mode, SQL Server trusts the Windows, Windows Domain, or Active Directory security subsystem to have validated the account credentials. No SQL Server accounts are allowed to connect. They can be created, but they cannot be used for login access.

SQL Server and Windows Authentication Mode (Mixed Mode)

In SQL Server Mode and Windows Authentication Mode or Mixed Mode, valid Windows accounts and standard SQL Server logins are permitted to connect to the server. SQL Server logins are validated by supplying a username and password. Windows accounts are still trusted by SQL Server. The chief advantage of Mixed Mode is the ability of non-Windows accounts (such as UNIX) or Internet clients to connect to SQL Server.

Here, Some Best Practices on Security are as,

As per the best practices, always use security templates from PCI, HIPAA or FERPA which are security standard for most companies.

• Ensure the physical security of each SQL Server, preventing any unauthorized users to physically accessing your servers.
  
• Change SQL Server default port 1433 to any other port .
  
• During installation, only install required network libraries and network protocols on SQL Server instances.
  
• Minimize or avoid the number of sysadmins allowed to access SQL Server.
  
• As a DBA, log on with sysadmin privileges only when needed.
  
• Always 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.
  
• Give users the least amount of permissions they need to perform their job.
  
• 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-sysadmin users.
  
• Remove sample databases from all production SQL Server instances.
  
• 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.
  
• 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 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.
  
• 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.
  
• When using distributed queries, use linked servers instead of remote servers.
  
• Do not browse the web from a SQL Server.
  
• Instead of installing virus protection on a SQL Server, perform virus 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.
  
• Only enable C2 auditing or Common Criteria compliance if required.
  
• Consider running a SQL Server security scanner against your SQL servers to identify security holes.
  
• Consider adding a certificate to SQL Server instances and enable SSL or IPSEC for connections to clients.