Stairs for SQL Server security Level 2: Authentication

by Don Kiely, 2014/06/18

The series
This article is part of the stair series: a staircase to SQL Server security.

SQL Server has everything you need to protect your servers and data from today's sophisticated attacks. But before you can effectively use these security features, you need to understand the threats and some basic security concepts you face. This first ladder level provides a foundation so that you can take advantage of security features in SQL Server without wasting time dealing with features that do not have a specific threat to your data.

The authentication process verifies that the PRINCIPAL-A user or process needs access to the SQL Server database who is or what it claims to be. The principal requires a unique identity, so that SQL Server can determine what permissions the principal has, if any. Proper authentication is the first necessary step in providing secure access to database objects.

SQL Server supports two authentication paths: Windows Integrated authentication and SQL Server authentication. The path you use 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 perform a re-authentication when a user logs on to Windows. Access to the SQL Server object is then assigned to Windows logon permissions. This type of authentication is available only when SQL Server is running on a version of Windows that supports Windows NT or Kerberos authentication, which is the standard since Windows 2000.
Server Authentication: SQL Server can handle authentication completely independently. In this case, you can create a unique user name for SQL Server and password logon. The user or application connects to SQL Server and provides these credentials for access. The permissions are then assigned directly to the login, or permissions are assigned through members in the role.
Configuring Authentication in SQL Server is not a simple choice between these two types. You can configure authentication in any of two ways:

Mixed Mode authentication: The server supports SQL Server and Windows authentication.
Windows Unique mode: The server only supports Windows authentication.
Microsoft strongly recommends using Windows authentication whenever possible. Windows has powerful authentication options, including password policies, but Windows authentication is not always practical in real-world applications. SQL Server Authentication can hook up some Windows authentication features, but it is not so secure.

Windows authentication
If you configure SQL Server to run in Windows Authentication mode, SQL Server assumes a trust relationship with the Windows servers. It assumes that Windows authenticates users when they log on to Windows. SQL Server then checks the user account, any Windows group, and the SQL Server role that the user may be a member of, to determine whether the user is allowed to work with various SQL Server objects.

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

The user simply logs in once, so she doesn't have to log in to SQL Server alone.
Audit features
Simplified Login Management
Password policies (in Windows Server 2003 and later)
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 make changes to the Windows user when you connect to SQL Server, the changes will not take effect until the next time the user connects to the server.

Configure SQL Server security settings
When you install SQL Server, you can select the authentication mode allowed by the server instance. Later, you can change the settings in the Server Properties dialog box available in SQL Server Management Studio. These settings apply to all databases and other objects in an instance of SQL Server. Therefore, if you need to use SQL Server authentication for any database, you must set up mixed mode for the server.

Figure 2.1 Shows the Server Properties dialog box in the Security Management page, where the Security page is selected. To open this dialog box, right-click the server instance name in Object Explorer, select Properties from the pop-up menu, and go to the Security page. You can change the authentication mode by clicking the corresponding radio button and clicking OK to commit the changes.

Figure 2.1. Configures the authentication mode for the SQL Server instance.

Adding Windows Logins
To use Windows authentication, your users need a valid Windows logon account before they can access SQL Server. You can then grant Windows groups permission to connect to SQL Server, or you can grant individual Windows users permissions if you do not want to grant collective permissions.

A good thing about using Management Studio to manage security, you can set up logins while providing database access. For Windows logins to access the SQL Server and adventureworks2012 databases, use the following steps, assuming there is already a Joestairway login definition on the local machine.

Open SQL Server Management Studio, and make sure that the Object Explorer window is visible and connected to the SQL Server instance.
Expand the tree view of the server object, and then expand the Security section. You will see several child nodes, as shown in 2.2.

Figure 2.2. A security section in the Object Explorer of a server, where you can define logins.

Right-click the login node, select New login from the pop-up menu, open Login – New dialog box.
Make sure that the Windows Authentication radio button is selected.
There are two ways you can choose Windows logins. The first method is to enter the domain name or computer name directly, followed by a backslash and the user's Windows logon name. Second, generally easy, by clicking the "Search" button to open the "Select User or Group" dialog box. Type the user name and click the Check Names button to find the exact name. If the user is found, the full name is displayed in the box, as shown in 2.3. Click OK to select the user.

Figure 2.3. Locate the Windows logon that you want to add to SQL Server.

In the Login – New dialog box, set the default database for the ADVENTUREWORKS2012 database login. 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 to access to the database only. Figure 2.4 shows the results in the machine named Marathon Windows Joestairway User login, with the default database set to the sample adventureworks2012 database.

Figure 2.4. Logon-A New dialog box enables Windows logins to access the SQL Server instance.

Tips:
Never leave the default database settings to the primary database. I said from a painful experience: connecting to a server is too easy, forgetting to change the database. If you run a script that creates hundreds of database objects in the primary database, it would be tedious to manually delete the objects to clean up the primary database.

Next, let the user access the database. Select the User Mappings page from the list on the left side of the dialog box. Grant the user access to the database by adventureworks2012 the database name next to the check box. SQL Server automatically maps the user to a user with the same name in the database, as you can see in the third column in the table, although you can change the user name if you wish. Assign sales as the default mode for the user in the database, or type it in the default mode column, or click the ellipsis (...). ) button to select it from the list. The dialog box should be shown in 2.5.

Figure 2.5. Grant Windows logon access to the adventureworks2012 of the database.

Tips:
Set the default database for logins and grant access to the database differently. The default database simply means that SQL Server attempts to change the context to that database while the user is logged on, without specifying a database. But this does not grant any permissions to do anything in the database, or even allow access to the database. This means that you can assign a default database that a user cannot access. To let users do anything useful after accessing the database, you need to explicitly grant user permissions.

By default, the new Windows logon Access server. However, if you want to explicitly deny logon access to the server, select New dialog from the list of pages to the left of the login, and then select the Reject radio button. You can also temporarily disable logins by selecting the Disable button. Figure 2.6 shows these options.

Figure 2.6. The option to grant or deny access to the server and temporarily disable the logon account.

Click OK to create the user.
You can also add a Windows group to Windows Server in the same way. In this case, any member of the group can access the database server, regardless of whether you use that group's access rights to objects in the database.

Use user name and password
When you use SQL Server login 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 and are not mentioned in Windows. 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, you can choose to log in under certain circumstances instead of using SQL Server. For simple applications that do not have broad security requirements, SQL Server authentication is easier to manage, and it lets you avoid confusion with Windows security. If the client is running on an older version of Windows (basically, more than Windows 2000) or a non-Windows operating system, you must be logged on using SQL Server.

Create a SQL Server login that uses the same logon – new Windows logon dialog box. However, do not select Windows logon, type a unique login name, no domain or machine name, and provide a password. For example, figure 2.7 shows how to create a new SQL Server login casper,adventureworks2012 his default database.

Figure 2.7. Create a SQL Server login.

All other options for user positioning and status are the same as SQL Server login for their Windows logins.

SQL Server logins through Transact-SQL
You can also use Transact-SQL code to perform the same operation. The Create login code in Listing 2.1 creates a SQL Server login topaz with a fairly strong password:

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

Listing 2.1. Create a new T-SQL Server login code.
Then, give topaz access to the ADVENTUREWORKS2012 database, use the Create user declaration and assign a default mode,

Use AdventureWorks2012; Gocreate USER Topaz for LOGIN Topaz with    default_schema = HumanResources; GO

Listing 2.2. Create the code for the database user associated with the SQL Server login.
Tips:
As with a staircase of level 1, if you want to run these code samples in a local instance of the SQL Server, you may need to make some changes to the code example. The code in Listing 2.2 assumes that you have a adventureworks2012 database installation. The following code example assumes that your machine is running code that is named Marathon in Windows with Joestairway users. You can name your machine marathon or create a user with that name, or change the code as appropriate.

Like Windows login, you can map the server's login topaz to some other name database. The code in Listing 2.3 maps topaz in the adventureworks2012 Topazd user database:

DROP USER Topaz; Gocreate USER Topazd for LOGIN Topaz with default_schema = HumanResources; GO

Listing 2.3. Delete the existing user's code, and then add a database user with a different name than the login name.

Beware of SA logins
If you configure your SQL Server to support SQL Server logins, there is a built-in SQL Server login and you need to beware of the SA logins that you may have noticed hanging in the Object Explorer login node. The SA or system administrator login primarily includes backward compatibility with older versions of SQL Server. The sa login is mapped to the sysadmin fixed server role, who logs on to SQL Server as the SA is a complete system administrator, with an irrevocable entitlement to all databases in the entire SQL Server instance. This is a powerful login, indeed.

You cannot modify or delete an SA login. If you select Mixed Mode authentication when you install SQL Server, you are prompted to provide a password for the SA user. No password, anyone can log in with an SA without a password, play "Let us manage the server", needless to say, this is the last thing you want your users to do. If other system administrators are unavailable or forget the Windows password, use the sa login as the backdoor login. If this happens, you may need a new administrator!

Never use the sa login to access the database in the application. If the hacker is able to gain control of the application, doing so allows the hacker management level to control your database server. In the distant past, this is an easy way to attack a server and is a horrible practice. Instead, either set a custom Windows or SQL Server login for the application, and then give the login application the absolute minimum permissions required to run the principle of least privilege.

Tips:
In fact, you should consider completely disabling the sa login using the status page of the Login Properties dialog box you just saw. This allows an attacker to use this powerful login to gain control of the server instance, whether or not you have a strong sa password.

Password Policy and enforcement
In SQL Server versions prior to 2005, system administrators did not have an easy way to enforce password policies, thereby helping to make the system more secure. For example, SQL Server does not have the means to force users to create a mixture of the shortest length of strong passwords and alphanumeric and other characters. If someone wants to create a login with a single letter for the password, then SQL Server cannot be configured to block it. Similarly, there is no way to expire passwords on a regular basis, such as once every three months. Some people take it for granted that this is the main reason not to use SQL Server login.

An updated version of SQL Server can connect to Windows Server 2003, Windows Vista, or a later version of the password policy. The password is still stored in SQL Server, but SQL Server calls to the NetValidatePasswordPolicy () Windows API method, which is the first rollout of Windows Server 2003. This API feature applies to Windows password Policy for SQL Server logins and returns a value that indicates whether the password is valid. SQL Server calls this function when a user creates, sets, or resets a password.

You can define Windows password policies through the Local Security settings applet in the Administrative Tools in Windows Control Panel. The password policy is shown in section 2.8 and has the default settings. The applet has a separate Account lockout policy section, as shown in 2.9, which takes effect when the user makes too many unsuccessful login attempts. By default, the lockdown policy is disabled in a new Windows installation.

Figure 2.8. Windows Local Security Policy applet, which displays the default password policy.

Figure 2.9. Windows Local Security Policy applet, which displays the default account lockout policy.

Table 2.1 lists the password policies as well as the default values and some comments about how they work.

Category	Policy Name	Default	Notes
Password Policy	Enforce password history	0 passwords remembered	Prevents users from reusing old passwords, such as alternating between, passwords.
	Minimum Password Length	0 characters	Use the Require longer passwords to make them harder to break.
	Password must meet complexity requirements	Disabled	Minimum mix of alphanumeric and other characters, and does not contain the user name.
Password Expiration	Maximum Password Age	Days	Number of days before a user was prompted to the change their password.
	Minimum Password Age	0 days	Number of days before a user was allowed to change a password.
Account lockout Policy	Account lockout duration	Not applicable	Time in minutes, the account was locked out if the lockout threshold is enabled.
	Account lockout threshold	0 Invalid login attempts	Maximum number of unsuccessful login attempts before the account was locked out.
	Reset Account lockout counter after	Not applicable	Time in minutes after which the counter of unsuccessful attempts is reset; Enabled when the lockout threshold is enabled.

Table 2.1. Windows password policy settings.

When you create a login, you can enable or disable password policy execution. Sign In – A new dialog box is enabled when you create a SQL Server login section, 2-10.

Figure 2-10. Execute the password policy for the new login.

Password policies apply when you use Transact-SQL to create logins as well. For example, if you run SQL Server in Windows 2003 server or later and have password policy enabled, the code in Listing 2.4 will fail.

Use master; Gocreate LOGIN simplepwd with PASSWORD = ' simplepwd '; GO

Listing 2.4. An attempt was made to create a login with password violation password policy.

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

You can control the policy when you create or change the login. The code in Listing 2.5 turns off the option to check expiration and policy.

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

Listing 2.5. Change the code to log on to disable the password policy for this login.

The Check_expiration option controls whether the SQL Server check policy and check_policy password age apply to other policies. A must_change option is available that forces the user to change the password at the next logon.

If the user makes too many failed logon attempts, the administrator can reset the account by using the Unlock option, as shown in Listing 2.6, beyond the number set in the Account lockout policy.

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

Listing 2.6. Unlocks the login code that was locked due to a failed logon attempt.

You can enable the Enforce password policy when you run SQL Server on a Windows version before Windows Server 2003. However, SQL Server uses the default setting of a minimum length of six characters, and checks that the password does not match all parts or any part of the login, and is a combination of uppercase letters, lower case letters, numbers, and other characters. These default values cannot be changed. But hopefully you won't be running SQL Server on an older version of Windows, if only because of the security improvements that were made at that time!

Summarize
At this level of SQL Server security, you have already learned about many of the authentication options available in SQL Server. Windows Integrated authentication is the safest, but not always feasible, Microsoft has made SQL Server certification more complete and secure over the years. However, if you are using mixed-mode authentication, do not forget to log on to the SA with a very strong password, or to better disable it! Favorite objects that you can create and change are the good GUI interfaces they use to manage studio or T-SQL code. If you are running SQL Server on a modern version of Windows, you can connect to the password policy of the local security policy.

This article is part of the SQL Server security staircase.

Stairs for SQL Server security Level 2: Authentication