SQL Server 2012 Authentication (authentication) _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.

Authentication is the process of authenticating the principal (the user or process that requires access to the SQL Server database) and is claiming to be a person or thing. The principal needs a unique identity so that SQL Server can determine which license the principal has. Proper authentication is a necessary first step in providing secure access to database objects.

SQL Server two ways to support authentication: Windows Integrated authentication and SQL Server authentication. The path you use depends on the network environment, the type of database the application accesses, and the user types of those applications.

1. Windows Authentication: This authentication method relies on Windows for responsibility--authenticating when a user logs on to Windows. Access permissions for SQL Server objects and then assign to Windows logins. This can only be used when SQL Server is running on Windows NT or Kerberos authentication-enabled versions, which has been almost standard since Windows 2000.
2. SQL Server Authentication: SQL Server can completely autonomously authenticate. In this case, you can create a unique username--a login in SQL Server invocation--and a password. Users or applications that are connected to SQL Server provide these credentials for access. Permission is then assigned directly to that login or through the character's eligibility.

Configuring authentication between these 2 types in SQL Server is not an either-or option (can be mixed). You can configure authentication in any of two ways:

1. Mixed authentication mode: The server supports both SQL Server and Windows authentication.
2. Windows Authentication mode: The server only supports Windows authentication.

Microsoft strongly recommends the use of Windows authentication whenever possible. Windows has reliable authentication options, including password policies, but Windows authentication is not always feasible in real applications. SQL Server Authentication can embed some of the features of Windows authentication, but it is less secure.

Windows authentication

If you configure your SQL Server to operate in Windows Authentication, SQL Server believes that it has a trust relationship with the Windows Server. When they log on to Windows, SQL Server thinks that Windows has authenticated the user. SQL Server then checks the user account, any Windows group, and any SQL Server role to see if the user is one of its members to determine whether the user is allowed to deal with individual SQL Server objects.

Windows authentication has many advantages over SQL Server authentication, including:

User can log in at one time, so she doesn't need to log on separately to SQL Server audit features simplified login Management password policy (in Windows Server 2003 and later)

Another big advantage of winows authentication is that any changes you make to Windows users and groups automatically take effect in SQL Server, so you don't need to manage them separately. Then, if you make changes to Windows users that are connected to SQL Server at this point, these changes will not take effect until the next time the user connects to SQL Server.

Configuring SQL Server Security Settings

When you install SQL Server, you can select the authentication mode allowed by the SQL instance. You can modify this setting in the Server Properties dialog box in SSMs after the installation is complete. These settings apply to all databases and other objects in the SQL Server instance. So if you need to use SQL Server authentication for any database, you need to set the server to mixed mode.

Illustration 2.1 Shows the Server Properties dialog box with the Security page selected in SSMs. To open the dialog box, right-click the server instance name in the Object Browser, select Properties from the pop-up menu, and then click the Security page. You can modify the validation mode by clicking on the corresponding radio box and clicking "OK" to submit the change.

Figure 2.1: Configuring the authentication mode for the SQL Server instance

Add a Windows Login

With Windows authentication, your users need to verify the Windows logon account before they can access SQL Server. You can then grant a Windows group to connect to SQL Server, or you can grant permission to a separate Windows user if you do not want to grant a collective license.

One of the benefits of using SSMS to manage security is that you can configure both login and database access. Enable Windows logon to access SQL Server and AdventureWorks2012 databases. Use the following steps and assume that the local machine has defined the WOODYTU user.

1. Open SSMs, make sure the Object Browser form is visible, and you are connected to an instance of SQL Server

2. Expand the tree view of the server object, and then expand the Security node. You will see more than one child node as shown in illustration 2.2.

Illustration 2.2: The Security section of the server Object Browser, where you define the login

3. Right-click on the "Login" node, from the pop-up menu, choose "New Login" to open "Login"-New dialog box

4. Make sure that the Windows Authentication radio box is selected

5. You can choose Windows login in any of the 2 ways. The first way is to enter the domain name or machine name directly, and then a \ and the Windows login used. The second way, usually in a simpler way, is to click the "Search" button to open the "Select User or Group" dialog box. Enter the username and click "Check Name" to find the specific name. If you find a user, the full name appears in the dialog box, as shown in illustration 2.3. Click "OK" to select that user.

Illustration 2.3: Find a window login to add to SQL Server

6. Go back to the Login name-New dialog box and set the AdventureWorks2012 database as the default database for logins. This is the database used by the user when the user connects to the server and does not specify a database. This does not restrict users from accessing only that database. Illustration 2.4 shows the login configuration for the sample database AdventureWorks2012 for the login user woodytu for Windows on the WIN10 machine.

Illustration 2.4: The Login name-New dialog box enables Windows logon to access the SQL Server instance.

Tips:
Never keep the default database as the master database. This is a painful lesson: Connecting to the server is too easy to forget to modify the database. Then if you run the script to create hundreds of database objects on the master database, you will spend a lot of effort to manually delete these objects and clean up the master database.

7. Next, access a database to the user. Select the User Mappings page from the left list in the dialog box. Grant the user access to the AdventureWorks2012 database by selecting the selection box next to the database name. SQL Server automatically maps users to users in the database with the same user name, as you can see in the 3rd column of the table, and if you want, modify the username. Assign sales as the default schema for users in the database, you can enter them in the default schema column, or click the "..." button to select from the list. The dialog box should look like Figure 2.5.

Illustration 2.5: Granting Windows login access to the AdventureWorks2012 database

Tips:
There is a difference between setting the default database for logins and granting access to the database. When a user logs on without specifying a database, the default database refers to SQL Server attempting to modify the context to that database. This does not grant any permission to do anything in the database, or even allow access to the database. This means that it is possible to allocate a database that is completely inaccessible to users. Once the database has been accessed, you will need to authorize the user for some action.

8. By default, new Windows logins can access the server. But if you want to disable login access to the server, select "Status" from the list on the left of "login-new" and check the "Deny" radio box. You can also temporarily disable login by selecting the "No" button. Illustration 2.6 shows these options.

Illustration 2.6: Grant and deny connections to the database and temporarily disable the logon account option Click OK to create the user.

You can also add Windows groups to SQL Server in the same way, and any member of the group can access the database server, including any objects in your database to the group.

SQL Server Authentication

When you use SQL Server login for authentication, the client application needs to provide a valid username and password to connect to the database. These SQL Server logins are saved in SQL Server, regardless of windows. When you log on, if there is no matching username and password, SQL Server throws an error and the user cannot access the database.

Although Windows authentication is more secure, in some cases you may have to choose SQL Server logins instead. SQL Server authentication is easier to manage for applications that simply do not have broad security requirements, and it allows you to avoid the complexities of Windows security. And if the client is running on an older version of Windows (older than Windows 2000) or a non-Windows operating system, you must log on using SQL Server.

Create a SQL Server login with the same login-New dialog box as Windows logon. Instead of selecting Windows logon, enter a username with no domain name or machine name and provide a password. For example, figure 2.7 shows how to create a new SQL Server login user and use AdventureWorks2012 as his default database.

Illustration 2.7: Creating a SQL Server login

SQL Server logins and Windows logins are the same for user mappings and all other options for the state.

Log on through SQL Server for T-SQL

You can also use T-SQL code to do the same. The Create login code in code 2.1 creates a SQL Server login Tudou with strong passwords.

CREATE LOGIN Tudou with PASSWORD = ' ybqyzipt8}b]b[{5al0v ';
Go

Code 2.1: Code to create a new SQL Server login using T-SQL

Then, grant Tudou access to the AdventureWorks2012 database, use the Create USER statement, and assign the default schema, as shown in code 2.2.

Use AdventureWorks2012;
 Go
 
 CREATE USER Tudou for LOGIN Tudou with
   default_schema = HumanResources;
 Go

Code 2.2: Code to create a database user with a SQL Server login association

Tips:

As in the first article, if you want to run them in a local SQL Server instance, you probably need to make some changes to your code. In code 2.2, assume that you have installed the AdventureWorks2012 database.

Like Windows login, you can map the server login Tudou to some other names in the database. Code 2.3 Maps Tudouz to Tudou users in the AdventureWorks2012 database.

 DROP USER Tudou;
 Go
 CREATE USER Tudouz for LOGIN Tudou with default_schema = HumanResources;
 Go

Code 2.3: Delete the code for an existing user to increase the database username with a different login name

Beware of SA login

If you configure your SQL Server to support SQL Server logins, a SQL Server login built into SQL Server needs to be aware of the--sa login-the "Security" node in the Object Browser, which is visible in the login name. SA or system administrator, logging in for backward compatibility with earlier versions of SQL Server. The sa login is mapped to the sysadmin server role, and any person who logs on to SQL Server with SA has full system administrator privileges and has irrevocable rights throughout the SQL Server instance and all the databases inside. This is indeed a powerful login.

You cannot modify or delete the sa login. When you install SQL Server, if you choose a mixed authentication mode, you will be prompted to enter the password for the SA user. Without the password, anyone can login directly with SA without losing the password, playing "I'll manage the server". Needless to say, this is the last thing you want your users to do. If there are no other system administrators or have forgotten their Windows password, using SA login is just a backdoor. If that happens, you need a new administrator!

Never use the sa login in your application to access the database. If the hacker gets control of the application, this will give the hacker the right to administer the database server. In the early days, this was the easiest way to hack into a server, and it was a scary example. Instead, set up a custom Windows or SQL Server login for your application to use, and give this login the absolute minimum of permission to run the program (implementing the least privilege principle).

Tips:

In fact, you should consider using the status page of the Login Properties dialog box that you just saw to completely disable the sa login. In that case, the attacker would not be able to use this universal login to control your server instance, whether or not you set up a strong sa password.

Password Policy and execution

Prior to SQL Server 2005, there was no easy way to enforce password policies for system administrators that would make the system more secure. For example, SQL Server meter has the means to force users to create strong passwords with a combination of minimum length, numbers, and other characters. If someone wants to create a login password with a single letter, you cannot configure SQL Server to block it. Similarly, passwords do not have the means to set it to expire periodically, for example every three months. Some people just saw the main reason, not using SQL Server logins.

The most recent version of SQL Server can be embedded in Windows Server 2003 and subsequent versions of the password policy. The password is still stored in SQL Server, but SQL Server invokes the NetValidatePasswordPolicy () Windows API method, which was first introduced in Windows Server 2003. This API function applies Windows password policy to the server login, and returns a value indicating whether the password is valid. SQL Server calls this function when the user creates, sets, or resets the password.

You can define Windows password policies by using the local password policy in the Windows Control Panel Administration tool. The default Password Policy section is shown in illustration 2.8. This applet has a separate account lockout policy, as illustrated in Figure 2.9, which takes effect when a user tries too many failed logins. By default, the newly installed Windows Lockdown policy is disabled.

Illustration 2.8:windows The Local Security policy applet to display the default password policy.

Illustration 2.9:windows The Local Security policy applet to display the default account lockout policy.

The following table lists the password policies for the default values and instructions for how they work.

Class Policy name default value description

Password policy Enforce password history 0 remembered password blocking usefulness reusing old passwords, for example, modifying between 2 passwords

Minimum password length 0 characters Use this requirement password length to make them hard to crack

Password must meet complexity requirement has been disabled for at least 6 letters or numbers and other characters, not including user names

Maximum password expiration 42 days before user modifies password

Minimum password age of 0 days before allowing users to modify passwords

Account lockout policy account lockout time does not apply if the lockout threshold is enabled, lock

Account lockout threshold 0 unsuccessful logon account lockout logon count

Reset account lockout counter does not use reset failed login number;

Enabled when lockout thresholds are enabled

Table 2.1:windows Password Policy settings

When you create a login, you can enable or disable the Execute password policy. Login name-New dialog box under login, there is an enabling section when you create SQL Server login, as illustrated in Figure 2.10.

Illustration 2.10: Password Policy for new logins

When you use T-SQL to create a login, you can also apply a password policy. For example, if you run SQL Server on a later version of Windows 2003 Server and have password policies enabled, code 2.4 will fail.

 Use master;
 Go
 CREATE LOGIN simplepwd with PASSWORD = ' simplepwd ';
 Go

Code 2.4: Attempt to create a login that violates the password policy

This code failed because the password cannot be the same as the user name.

When you create or modify a login, you can control the policy. Code 2.5 closes the expiration check and policy.

 ALTER LOGIN Tudou with PASSWORD = ' ybqyzipt8}b]b[{5al0v ',
   check_expiration = off, check_policy = off;

Code 2.5: Code that disables password policy only for modifying logins (for this login only)

CHECK_EXPIRATIONoption controls the age of SQL Server's password-checking policies and CHECK_POLICY applies to other policies. MUST_CHANGEoption to execute the user must modify the password the next time they log on.

If the user has too many failed logons, exceeding the number lockout policy setting, the administrator can use UNLOCK to reset, as shown in code 2.6.

Copy Code code as follows:

ALTER LOGIN Tudou with PASSWORD = ' ybqyzipt8}b]b[{5al0v ' UNLOCK

Code 2.6: Lock the login for too many failed logins and unlock the code.

When you run SQL Server on a version prior to Windows Server 2003, you can enable the Enforce password policy. However, SQL Server uses a password of at least 6 characters by default, which cannot contain your username, and is a mixture of uppercase and lowercase letters, numbers, and other characters. You cannot modify these default settings. But I hope you don't run SQL Server on this old version, because there has been a big security improvement since then.

Summary

In this SQL Server security article, you learned multiple validation options in SQL Server. Windows Integrated authentication is the safest, but not all feasible, Microsoft has made SQL Server validation more secure over the years. But if you use a hybrid authentication model, don't forget to give the SA strong enough password to even deactivate it. As with most objects, you can create or modify them using the graphical interface or T-SQL in SSMS. If you run SQL Server on the current version of Windows, you can embed the local security policy into the password policy.

Thank you for your attention!

Original link:

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