SQL Server 2012 Security Overview _mssql

In keeping your servers and data secure from the current complex attacks, SQL Server has everything you need. But before you can effectively use these security features, you need to understand the threats you face and some basic security concepts. This article provides the basics so you can make the most of the security features in SQL Server without wasting time in the face of specific threats and the ability to protect your data.

Using connections from dazzling clients, relational databases are widely used in a wide variety of applications, through networks that are distributed everywhere, especially the Internet. This makes the data accessible to anyone, anywhere. Databases can preserve a large part of human knowledge, including highly sensitive personal information and key data for international business work.

These features make the database an attractive target for people who want to steal data or harm data by tampering with data. Ensuring your data security is an important part of the program that SQL Server configures and uses to save data. This series explores the basics of SQL Server 2012 security, so you can protect your data and server resources, and protect your data according to the level of security you need to avoid the impact of these threats on your data. Most of the information applies to earlier versions of SQL Server, and it's OK to go back to SQL Server 2005, because that's when Microsoft thoroughly checks security in the product. But I'll also talk about features that are only available in SQL Server 2012 and later.

In keeping your servers and data secure from the current complex attacks, SQL Server has everything you need. But before you can effectively use these security features, you need to understand the threats you face and some basic security concepts. This article provides the basics so you can make the most of the security features in SQL Server without wasting time in the face of specific threats and the ability to protect your data.

Threat

Recognizing the threat of a particular dataset and its servers is an important first step in understanding how to configure and use SQL Server to protect your data. The list of equipment you have created to manage your primary school football team is likely to require no stringent security measures. It is likely that you will want to provide minimal access control so that football team members cannot randomly change the record of which football box he owns. Even if the data were stolen or tampered with, it would not be the end of the world.

On the other hand, if your database is about personal information about your soccer team's children, such as your home address and contact number, you may want to tighten your security (perhaps legally). You may be able to protect the privacy of your data by isolating access, so that almost any database can be accessed to modify device data, but only some people can access personal data. If the data includes a parent's credit card number, you will need to protect that data with extreme care.

Your data is susceptible to a lot of threats, and the following list is a common number. There are a lot of resources on the web that can help you analyze your specific situation. This list just wants to help you start thinking about these threats, how to use SQL Server capabilities to deal with them, or at least reduce the exposure data to them.

1. Data theft: Data theft includes a variety of unauthorized access to your data, from outsiders who hacked into your network, or from insiders who were illegally scanned by important people. It involves reading the excitement of prohibiting information, or being driven by the interest of a credit card number that is stolen through sales.
2. Data destruction: Gain access to your data can modify it, it will bring a series of problems, including the closure of all your business to make you embarrassed in public (when all of your customer records are deleted when it happens).
3. Data tampering: One of the biggest benefits of storing data in relational databases is that the database itself can help protect the integrity of the data. Data integrity includes customers for which each order is associated, and dates stored in the date column represent dates on the calendar, and the percent field contains only those constraints, such as values between 0 and 100. When you think about security, data integrity is not the first thought, but it is an important part of your data protection.
4. Illegal storage: In the past, during the business process, the data you collected is only your personal matter. But in the United States, throughout the EU and can control the various personal information you store, how you store it, how you protect it in other countries have a corresponding legal presence. The penalties for breaking the law are strict-including fines and loss of public image of your company.

This series of articles about SQL Server 2012 features can help you mitigate these types of threats and other aspects. You have to understand the threats to your data before you know how to protect them. Do not waste time on measures that do not prevent you from specific data threats. You can never take into account all the assumptions, but the worst case scenario is to make sure that your database server is completely unavailable to its client-facing clients. Security is always a compromise, a balance between the risk of time and the necessary money to achieve it.

Safety design Concept

As early as 2012, Bill Gates released his infamous computer-trusted memo that Microsoft has been able to consider and achieve security in its products, proving a turning point. According to Microsoft's official website, a credible computer policy has been launched. "Based on good business practices, focus on creating and bringing security, privacy and a reliable computing experience." Our goal is a safer, more credible internet. "

In other words, Microsoft has become very concerned about security more than 10 years ago. The first version of this modification was SQL Server 2005, and Microsoft began to consider full security in the product. Since then, each version of SQL Server retains a new security architecture, strengthening it, and adding new functionality to address emerging threats while increasing security.

As Microsoft described in the SQL Server 2005 period, the four pillars of product security are defined to influence the development of SQL Server Trusted computing.

1. Security Design: Microsoft carries out a wide range of threat analysis and code design and interaction security audits to determine where attackers can gain access to servers and data. This allows Microsoft-designed SQL Server to protect the confidentiality, integrity, and availability of the data you store on the server.
2. Default security (Secure by default): SQL Server's creative default installation and activation of critical core database components. This means that the functionality of the database that is not core is not installed or will not be enabled after installation. Features that are not installed will not be targeted as an attack. You must be aware of the non core features installed or enabled. This prevents many attacks that you may not be aware of but install features, and you think you've never used them.
3. Deployment security (Secure in Deploymen): Microsoft provides tools to support the security of installing SQL Server and keeping it secure. The SQL Server Configuration tool helps you configure server security. Most importantly, SQL Server updates are already part of Microsoft's online update, making it easy to get secure updates and patches.
4. Interactive security (secure through communication): Microsoft has built a sophisticated infrastructure to phone its products in a loophole. But that information would be of no use if it remained within Microsoft. So companies are committed to interacting with new vulnerabilities, aggressively patching them up, and regularly posting updates to its online Help system to reflect new security information.

The idea of "It's safe" has affected the entire product. While SQL Server is fairly secure to open, when you create a database and install a service, you must make an informed security choice to keep SQL Server safe. Make and ensure the security of the production database server requires action and vigilance.

Remember that sometimes the best way to protect your data is to not put it in the database-for example, save only the credit card information you must absolutely insist on (and not many people). If you don't have such a demand, you should deal with credit card transactions and save the results instead of the credit card information itself. You will not be able to withstand the security risks you have no data on.

2 Stages of security

SQL Server's security model, like Windows, is a 2-phase process that allows users and other logged on access servers to be protected resources.

1. Authentication (authentication): A user login succeeded, is valid, can access the server. Authentication answers the question, "Who are you?" "Users are required to prove it, often requiring users to provide user names and corresponding passwords, but other forms of evidence become more prevalent."
2. Authorization (Authorization): Users can interact with SQL Server objects-such as databases, tables, and stored procedures-which user has permissions. Authorized to answer the question, "What can you do?" “。

Users can log on to SQL Server, but they cannot do anything unless they have permission to do something, such as accessing a database. So you not only need to provide authorization credentials for your users, you also authorize them to access the data, authorize them once for each database they need to use, and define the account number.

When you think about it, it's more meaningful to have a user account in each database. Otherwise, what prevents users from logging on to SQL Server and can access any database they want to access. This concept has different meanings to implement different scenarios, but this is the basic state of security.

The next article in this series talks about validation and authorization, and you'll learn how to implement a variety of security features in SQL Server to deal with the threats you've identified with your data.

SQL Server Security Terminology

When you are dealing with SQL Server and other products, you will encounter a variety of specific terminology. Here are some of the most common terms, as well as what they mean in the database security context.

1. Verification (authentication): As mentioned above, validation is the process of actively identifying a person by requiring that it be able to log in. It answers the question: Who are you?
   
2. Authorization (Authorization): Once the system verifies that you are a valid user, authorization (as described above) determines the permissions the user has on the server or database. It answers the question, what can you do here now?
   
3. Group: In Windows, a group is the body that is associated with it after it is logged on. Any authorization to the group will authorize the associated login in the group.
   
4. Impersonation (Impersonation): All Windows processes, including various SQL Server processes, run in a specific security context, typically causing the process to start. Impersonation occurs when a process is temporarily in a different security context. It is very powerful to cut the required capacity, but it can cause potential abuse.
   
5. Login: Login is the principal of some access levels for objects in the server instance. Typically, logins are often used to interact with users. However, SQL Server login is an account that is used to access the server from the outside. Sometimes logins include access to server-level objects, such as configuration information, but do not grant any permissions in the database.
6. License (Permission): Permission is the right to access protected resources, such as reading data from a table or creating a new database at the server level. A discipline usually means other licenses, depending on the scope of the object's license.
7. Principal (Principal): A principal is any user or code component that can receive access to protected resources in SQL Server.
8. Permissions (Privilege): Permissions are a broad range of rights and licenses owned by the subject. This term is sometimes used interchangeably with a license, which usually means a specific, narrowly defined right. Right implies a broad set of permits.
9. Role: The SQL Server role is similar to the Windows user group, but is limited to the SQL Server instance scope. For a group, you can assign logins and users to roles, and this is the role that has all the permissions of the login and the user.
   
10. User: A user is a principal of some level of access to an object in a particular database. Users typically map to logins. In simple terms, a login can access the SQL Server instance and the user can access the data.

In this series of articles you will see the use of most of these terms.

SQL Server security management and implementation

As with most of the manipulation and management tasks in SQL Server, there are often many management and implementation methods for security features, including the use of SSMs graphical interfaces, the writing and execution of T-SQL code, and the use of writing PowerShell on the command line to interact with SQL Server. Using PowerShell is not the scope of this series of articles, but in this series you will see many examples of 2 other technologies.

With SSMs security features, you can usually right-click on the appropriate object in the Object Browser, select Properties, and then use the appropriate page in the dialog box to make the changes you want. For example, set security on the ADVENTUREWORKSLT2012 sample database, right-click the database name in the Object Browser, select Properties, and then select the Permissions page, as illustrated in Figure 1.1.

Illustration 1.2: Permissions page to modify the security of the ADVENTUREWORKSLT2012 database

At the same time at the database and server level, the Object Browser includes security nodes that allow you to manage and implement various other security features. Illustration 1.2 shows this node for the ADVENTUREWORKSLT2012 database and server instance level. These 2 highlighted nodes include a variety of child nodes, giving you access to the appropriate security objects within the range of choices.

Illustration 1.2: In the Object Browser, the security object at the server and database level

You'll find a variety of hidden security features in SSMs and each of its forms, so you can get a good start on these security nodes.

Another change in the SQL Server 2005 thorough security check is the way to allow more granular distribution of security. In the following article you will learn that you will assign various licenses to various subjects to achieve the important security principle of "least privilege"-the idea that each subject can only have the permission they need: No more and no less. With this change, Microsoft has enhanced T-SQL and has strong language support to manipulate security objects directly.

Code 1.1 shows a simple example of how you can use T-SQL to create a secure object. The code first creates a server-level login linked to an existing system login. Then in the AdventureWorksLT2012 database, the code creates a user mapping to the login, assigning the default schema to the user. The last bit of code shows you how to delete a user in a database and create another user to map to the same login. It's a show, of course, but it shows what you can do in the code. Or you can do it in the SSMs graphical interface, if you like.

--Add A Windows login to SQL Server
CREATE login [pc201602202041\woodytu] from windows;
Go use

AdventureWorksLT2012;
Go

--Name the user ' same name as login
CREATE user [pc201602202041\woodytu] for login [pc201602202041\woodytu] with
 Default_schema = Production;
Go

--or, rename the user in the database
DROP user [pc201602202041\woodytu];
Go
CREATE USER Woody for LOGIN [Pc201602202041\woodytu];
Go

--Query metadata to show that user is created
SELECT * from sys.database_principals WHERE name = ' PC2016022 02041\woodytu ';
SELECT * from sys.database_principals WHERE name = ' Woody ';

Code 1.1: Create sample code for server logins and database users

If you want to run this code, first you have to make some changes. Unless your computer name is the same as mine, and a woodytu user exists, you need to modify the name in your code. This code creates the database user in the ADVENTUREWORKSLT2012 database and uses its production architecture, so you either need to install the database or modify the code to use another database and the schema that exists inside it. But I suggest you install the AdventureWorks database, because the next article will use it frequently.

Summary

The first article in this series gives you an overview of the basic security concepts in SQL Server 2012. You've learned some common data threats, and you've explored the design concepts behind SQL Server. You've learned 2 phases of security--authentication and authorization, learning some of the security terminology you see throughout a series of articles, and seeing that you can manage and implement security using the SSMs graphical interface and T-SQL code.

In the next article, you'll learn more about how authentication works and authenticates the options available in SQL Server.

Original link:

http://www.sqlservercentral.com/articles/Stairway+Series/109941/