Second SQL Server security verification

This article is the second in the SQL Server Security series, please refer to the original text for more information.

Validation is the process of examining the subject. The principal requires a unique identity, so that SQL Server can determine what permissions the principal has. Proper authentication is the first necessary step to provide secure access to database objects.
SQL Server supports two types of authentication: Windows authentication and SQL Server authentication. The way you use it depends on the network environment, the types of applications that will access the database, and the user types of those applications.
Windows Authentication: This form of authentication relies on Windows to authenticate. Permissions to access the SQL Server object are assigned to the Windows login name. This type of validation applies only to SQL Server running on Windows NT or Kerberos authentication-capable versions (Windows 2000 and after).
SQL Server Authentication: SQL Server can rely entirely on itself for validation. In this case, you can create unique user names (SQL Server is called logins) and passwords. The user or application connects to SQL Server and accesses the credentials provided. Permissions directly to the login or through role assignments.
The SQL Server Authentication configuration is not a simple or either choice. You can configure any one of the authentication:
Mixed Authentication Mode: The server supports SQL Server and Windows authentication.
Windows Authentication Mode: The server only supports Windows authentication.
Microsoft strongly recommends using Windows authentication. Windows has powerful authentication options, including password policies, but in real-world applications, Windows validation is not always feasible. SQL Server Authentication can reference some Windows authentication features, but it is not secure.
Windows Authentication
If you configure your SQL Server operation in Windows Authentication mode, SQL Server assumes the trust relationship with Windows Server. When a user logs on to Windows, Windows verifies the user's identity. SQL Server checks the user account (any Windows group, and the user of any SQL Server role) to decide whether to allow users to manipulate SQL Server objects.
Windows authentication has several advantages over SQL Server authentication, including:
-User Single Sign-on, so it doesn't have to be logged into SQL Server separately
Audit features
Simplified Login Management
Password Policy (in Windows Server 2003 and later versions)
Another big advantage of Windows authentication is that any changes you make to Windows users and groups are automatically reflected in SQL Server, so you don't have to manage them separately. However, if you change a Windows user that is already connected to SQL Server, these changes will not take effect until the next time the user connects to SQL Server.
Configure security settings for SQL Server
When you install SQL Server, you can select the server instance that will allow authentication mode. You can also change the settings in the Server Properties dialog box in SSMs. These settings apply to all databases and other objects in an instance of SQL Server. So if you need to use SQL Server authentication for any database, you need to set up mixed mode for the server.
Figure 2.1 Shows the Server Properties dialog box Security page. To open this dialog box, right-click the server instance name in Object Explorer and choose Properties from the pop-up menu, then go to the Security page. You can change the authentication mode by clicking the appropriate button, and then click OK to commit the changes.
Figure 2.1 Configuring the authentication mode for an instance of SQL Server
adding Windows Logins
In order to use Windows authentication, you first need a valid Windows logon account. You can then grant permissions to a Windows group to connect to SQL Server, or you can grant permissions to a single Windows user if you do not want to give collective permissions.
One of the benefits of managing security with Management Studio is that you can set up logins while providing database access. In order for Windows to log on to access SQL Server and AdventureWorks2012 databases, use the following steps, assuming the clearfile login is already defined on the local machine.
1. Open SSMs to ensure that the Object Explorer window is visible and that you have connected to a SQL Server instance
2. Expand the server object, and then expand Security. You will see several sub-nodes, as shown in 2.2

Figure 2.2 Security under Object Explorer
3, right-click the login honour point, choose New Login from the pop-up menu, open Login-New dialog
4, make sure Windows Authentication
5 is selected, there are two ways to select Windows logins. The first way is to enter the domain or computer name directly, and then a backslash and Windows logon name. The second option is to click the Search button to open the Select Users or Groups dialog box. Type the user name, and then click the Check Names button to find the exact name. If the user is found, it will appear in the box, as shown in 2.3. Click OK to select the user.
Figure 2.3 SQL Server find Windows Logins
6, return to Login-New dialog box, set the ADVENTUREWORKS2012 database as the default database. This is the database that is used when the user connects to the server and does not specify a database. It does not restrict users from accessing only this database. Figure 2.4 shows the Windows logins that I set up locally.

Figure 2.4 New Windows Authentication login
Note: never set the default database to master. I am told by painful experience that it is too easy to connect to a server and forget to change the database. If you run a script that creates hundreds of database objects in the master database, manually deleting these objects to clean up the master database will be a tedious task.
7. Next, let the user access the database. Select User mappings from the list on the left side of the dialog box. Grant the user access to the ADVENTUREWORKS2012 database by mapping next to the database. SQL Server automatically creates and maps a user with the same name under the database. You can see the third column in the table, and of course you can change the user name. Assign sales as the default schema for the user by typing or clicking the ellipsis (...) button to select from the list. The dialog box should look like Figure 2.5.
Figure 2.5 AdventureWorks2012 access to a database for Windows logins
Note: There is a difference between setting the default database for logins and authorizing access to the database. The default database simply means that when a user logs on without specifying a database, SQL Server attempts to change the context to the default database. But 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 assign a default database, but the user cannot access it. For the user to do anything useful when accessing the database, you need to explicitly grant the user permissions.
8, by default, a new Windows logon name can access the server. However, if you want to explicitly deny login access to the server, select the status on the left side of the Login-New dialog box and choose the Deny button. You can also temporarily disable the login by selecting the Disable button. Figure 2.6 shows these options.

Figure 2.6 Grant/deny connection to the database engine or disable login
9, click OK to create a user
You can also add a Windows group in the same way. In this case, any member of the group can access the database server as long as you grant the group permission to access the database object.
SQL Server Authentication
When you use SQL Server logon authentication, the client application must provide a valid user name and password to connect to the database. These SQL Server logins are saved in SQL Server. When you log on, SQL Server throws an error if no account matches the user name and password, and the user cannot access SQL Server.
Although Windows authentication is more secure, in some cases you can choose to log on using SQL Server. For simple apps that don't require special security requirements, SQL Server authentication is easier to manage, and it lets you avoid being entangled in Windows security. If the client is running on an older version of Windows (earlier than Windows 2000) or a non-Windows operating system, you must be logged on using SQL Server.
in order to create a SQL Server login, use the same dialog box as the Windows logon name-new. Enter a unique login name (no domain name or machine name) and provide the password. For example, figure 2.7 shows how to create a new SQL Server login Casper and set AdventureWorks2012 as the default database.
Figure 2.7 Creating a SQL Server login
The SQL Server login has the same user mapping and status options as the Windows logon name.
Create SQL Server logins with T-SQL statements
You can also use T-SQL code to do the same, code 2.1 creates a SQL named Topaz with Create LOGIN Server Logon name

CREATE  with = ' ybqyzipt8}b]b[{5al0v'; GO

Code 2.1 T-SQL statement to create a log server login
Then give topaz 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  for LOGIN Topaz      with = HumanResources; GO

Code 2.2 Creating a database user for a login
Like Windows logins, you can map logins to database users of different names. Code 2.3 Maps The Topaz login to the Topazd user of the AdventureWorks2012 database

DROP USER Topaz; GO CREATE USER  for  with = HumanResources; GO

Code 2.3 Delete a database user that already exists, and then create a database user with a different name than the login
beware of the sa login name
If you configure your SQL Server to support SQL Server login, you need to beware of the built-in SQL Server login--sa login, you may have noticed the Object Explorer login node. Include SA, or system administrator login is primarily backward compatibility with older versions of SQL Server. The sa login is mapped to the sysadmin fixed server role, and any log in to SQL Server as SA is a full-system administrator with access to the entire SQL Server instance and all databases.
You cannot modify or delete the sa login name. If you choose Mixed Mode authentication when you install an instance of SQL Server, you will be prompted to set a password for the SA user. If no password is set, anyone can log in with SA and play "Let's manage the server". Needless to say, this is the last thing you want your users to do. If other system administrators are unavailable or have forgotten their Windows password, the SA is used as the backdoor login. If this happens, you may need a new administrator!
Never use the sa login in your application to access the database. Doing so may allow hackers to control your database server if the hacker is able to control the application. In the distant past, this is already an easy way to attack the server and is a horrible practice. Conversely, regardless of whether a custom Windows or SQL Server login is set, the application is run with absolute minimum permissions for the login (the principle of least privilege is implemented).
Note: In fact, you should consider disabling the sa login name. This way, the attacker will not be able to use this powerful login to control your server instance, whether you have a strong password or not.
Enforce password Policy
In versions prior to SQL Server 2005, system administrators did not have an easy way to enforce password policies to help the system be more secure. For example, SQL Server has no way to force a user to create a strong password: Minimum length and mixed alphanumeric and other characters. If someone wants to create a login with a letter as the password, you cannot configure SQL Server to block it. Similarly, there is no way to expire passwords regularly, such as every three months. Some people take it for granted that this is the main reason not to use SQL Server login.
The latest version of SQL Server can use the password policy of Windows server 2003,windows Vista (or later). The password is still stored in SQL Server, but SQL Server calls the NetValidatePasswordPolicy () method of the Windows API, which is first launched in Windows Server 2003. This API feature applies to Windows password policy to the SQL Server login and returns a value indicating whether the password is valid. SQL Server calls this function when a user creates, sets, or resets a password.
You can define your Windows password policy through Control Panel > Administrative Tools > Local Security Policy > Password policy. The default settings for the Password Policy section are shown in Figure 2.8. Figure 2.9 shows the account lockout policy, which is locked when the user is unsuccessful in multiple logons. By default, the lockdown policy is disabled in a newly installed Windows system.
Figure 2.8 Password Policy

Figure 2.9 Account lockout policy
Table 2.1 lists some of the password policies, the default values, and the corresponding descriptions

Category	Strategy	Default value	Description
Password Policy	Enforce password history	0 passwords to Remember	This policy enables administrators to enhance security by ensuring that old passwords are not continuously reused
	Minimum password length	0 characters	This security setting determines the minimum number of characters that a user account password contains
	Passwords must meet complexity requirements	Disable	This security setting determines whether passwords must meet complexity requirements
Password expiration	Maximum Password usage period	42 days	This security setting determines the period in which the password can be used before the system requires the user to change a password
	Minimum password usage period	0 days	This security setting determines that the password must be used for a period of time before a user can change a password
Account Lockout Policy	Account lockout time	No	This security setting determines the number of minutes that the locked account remains locked before it is automatically unlocked
	Account lockout threshold value	0 Invalid Logins	This security setting determines the number of failed logon attempts that caused the user account to be locked out
	Reset account Lockout counters	No	This security setting determines the time that is required to reset the logon attempt failure counter to 0 error logon attempts after a failed logon attempt

Table 2.1 Windows password policy settings
When you create a login, you can enable or disable password policy enforcement. When you create a SQL Server login, the Login-New dialog box has an option below the login name, as shown in 2.10
Figure 2.10 Enforcing password policy for new logins
Password policies are also applied when you create logins using T-SQL. For example, if you run SQL Server in Windows 2003 Server (or later), and the password policy is turned on, code 2.4 will fail

 Use Master; GO CREATE  with = ' simplepwd ' ; GO

Code 2.4 Attempting to create a database login against a password policy
The reason is the password cannot be the same as the user name
You can control the policy when you create or modify the login name. Code 2.5 turns off the check expiration and enforcement policy option.

ALTER  with = ' ybqyzipt8}b]b[{5al0v',    =off=off;

Code 2.5 Modify login Disable password policy
The check_expiration option controls whether SQL Server checks for expired passwords, and the Check_policy option applies to other policies. The Must_change option forces the user to change the password at the next logon.
If the user has multiple unsuccessful logons that exceed the number of account lockout policies, the administrator can reset the account using the Unlock option, as shown in code 2.6.

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

Code 2.6 Unlock Login
Before Windows Server 2003, you can turn on SQL Server by using the Enforce password policy. However, SQL Server uses the default setting of a minimum length of 6 characters, checking that the password does not match all or any part of the login name, and is a mixture of uppercase letters, lowercase letters, numbers, and other characters. You cannot change these default values. But I hope you're not. Running SQL Server on an older version of Windows.
Summarize
In this article, you have learned some of the authentication options in SQL Server. Windows Integrated authentication is the safest but not always feasible, and Microsoft has made SQL Server authentication better and more secure over the years. However, if you use Mixed Mode authentication, do not forget to log a very strong password to SA, even better, disable it! You can use the GUI interface or T-SQL code to create and modify most securable objects. If you are running SQL Server in a modern version of Windows, you can reference the password policy in the local security policy.

Second SQL Server security verification