‫ SQL Server Security- 1st Section- An Overview

Date: 2014-01-07
  1. Introduction
SQL Server has many features that support creating secure database applications. Common security considerations, such as data theft or vandalism, apply regardless of the version of SQL Server you are using. Data integrity should also be considered as a security issue. If data is not protected, it is possible that it could become worthless if ad hoc data manipulation is permitted and the data is inadvertently or maliciously modified with incorrect values or deleted entirely. In addition, there are often legal requirements that must be adhered to, such as the correct storage of confidential information. Storing some kinds of personal data is proscribed entirely, depending on the laws that apply in a particular jurisdiction.
Each version of SQL Server has different security features, as does each version of Windows, with later versions having enhanced functionality over earlier ones. It is important to understand that security features alone cannot guarantee a secure database application. Each database application is unique in its requirements, execution environment, deployment model, physical location, and user population. Some applications that are local in scope may need only minimal security whereas other local applications or applications deployed over the Internet may require stringent security measures and ongoing monitoring and evaluation.
The security requirements of a SQL Server database application should be considered at design time, not as an afterthought. Evaluating threats early in the development cycle gives you the opportunity to mitigate potential damage wherever a vulnerability is detected.
Even if the initial design of an application is sound, new threats may emerge as the system evolves. By creating multiple lines of defense around your database, you can minimize the damage inflicted by a security breach. Your first line of defense is to reduce the attack surface area by never to granting more permissions than are absolutely necessary.
The topics in this section briefly describe the security features in SQL Server that are relevant for developers, with links to relevant topics in SQL Server Books Online and other resources that provide more detailed coverage.
  1. Overview of SQL Server Security
A defense-in-depth strategy, with overlapping layers of security, is the best way to counter security threats. 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 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.
The topics in this section cover SQL Server security fundamentals, providing links to the complete documentation in the relevant version of SQL Server Books Online.
2.1          Authentication in SQL Server
SQL Server supports two authentication modes, Windows authentication mode and mixed mode.
  • Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials.
  • Mixed mode supports authentication both by Windows and by SQL Server. User name and password pairs are maintained within SQL Server.
Security Note
We recommend using Windows authentication wherever possible. Windows authentication uses a series of encrypted messages to authenticate users in SQL Server. When SQL Server logins are used, SQL Server login names and passwords are passed across the network, which makes them less secure.
With Windows authentication, users are already logged onto Windows and do not have to log on separately to SQL Server. The following SqlConnection.ConnectionString specifies Windows authentication without requiring the a user name or password.
"Server=MSSQL1;Database=AdventureWorks;Integrated Security=true;
Logins are distinct from database users. You must map logins or Windows groups to database users or roles in a separate operation. You then grant permissions to users or roles to access database objects.
2.1.1      Authentication Scenarios

Windows authentication is usually the best choice in the following situations:
  • There is a domain controller.
  • The application and the database are on the same computer.
  • You are using an instance of SQL Server Express or LocalDB.
SQL Server logins are often used in the following situations:
  • If you have a workgroup.
  • Users connect from different, non-trusted domains.
  • Internet applications, such as ASP.NET.
Specifying Windows authentication does not disable SQL Server logins. Use the ALTER LOGIN DISABLE Transact-SQL statement to disable highly-privileged SQL Server logins.
2.1.2      Login Types

SQL Server supports three types of logins:
  • A local Windows user account or trusted domain account. SQL Server relies on Windows to authenticate the Windows user accounts.
  • Windows group. Granting access to a Windows group grants access to all Windows user logins that are members of the group.
  • SQL Server login. SQL Server stores both the username and a hash of the password in the master database, by using internal authentication methods to verify login attempts.
SQL Server provides logins created from certificates or asymmetric keys that are used only for code signing. They cannot be used to connect to SQL Server.

If you must use mixed mode authentication, you must create SQL Server logins, which are stored in SQL Server. You then have to supply the SQL Server user name and password at run time.
Security Note
SQL Server installs with a SQL Server login named sa (an abbreviation of "system administrator"). Assign a strong password to the sa login and do not use the sa login in your application. The sa login maps to the sysadmin fixed server role, which has irrevocable administrative credentials on the whole server. There are no limits to the potential damage if an attacker gains access as a system administrator. All members of the Windows BUILTIN\Administrators group (the local administrator's group) are members of the sysadmin role by default, but can be removed from that role.
SQL Server provides Windows password policy mechanisms for SQL Server logins when it is running on Windows Server 2003 or later versions. Password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. SQL Server can apply the same complexity and expiration policies used in Windows Server 2003 to passwords used inside SQL Server.
Security Note
Concatenating connection strings from user input can leave you vulnerable to a connection string injection attack. Use the SqlConnectionStringBuilder to create syntactically valid connection strings at run time.



بدون نظر
شما برای نظر دادن باید وارد شوید


تاریخ ایجاد: 18 مرداد 1393



امتیاز شما
تعداد امتیازها:0