Which accounts are considered a built

Managed Service Accounts

Managed Service Accounts (MSAs) are a combination between domain accounts and virtual accounts such as the “Network Service\MSSQLSERVER” accounts which are discussed in this chapter. Managed Service Accounts are user accounts created within the Active Directory domain for a specific Windows service on a single Windows server. The benefit to the Managed Service Accounts over a traditional domain user account is that Managed Service Accounts never need to have their passwords changes as the Windows server which runs the service which uses the account will automatically change the password for the account every 30 days. Because the password change is automated and controlled by the Windows operating system itself the passwords are able to be changed without the need to take an outage of the SQL Server service. Managed Service Accounts are available starting in Windows Server 2008 R2 and SQL Server 2008 R2.

Managed Service Accounts are created within Active Directory just like any other user account, with the exception of having a dollar sign ($) placed after the username. While a normal domain user might look like “DOMAIN\UserName” a Managed Service Account will look like “DOMAIN\UserName$.” When the account is created the password field should be left blank as the Windows Operating System will set the password manually when you configure the service to use the account.

Due to the fact that the Windows Operating System will be what creates the password and changes the password, Managed Service Accounts are not able to be used on clustered instances of Microsoft SQL Server as of Windows Server 2012 R2 and SQL Server 2014 as there is no way for the Windows servers to exchange the password with other members of the Windows cluster. When using AlwaysOn Availability Groups, Managed Service Accounts should also not be used as you should be using the same domain account for each instance which is working as a database replica within the AlwaysOn Availability Group configuration (more information regarding AlwaysOn Availability Groups and their security requirements is available earlier in this chapter).

Microsoft SQL Server is configured to use a Managed Service Account just like any other account by using the SQL Server configuration manager shown in Figures 13.2 and 13.3. Managed Service Accounts make for a great way to configure domain user accounts for standalone SQL Server instances as the password is changed automatically by the server every time the password expires without any downtime to the SQL Server service.

Network Authentication

Once a user has gained access to a physical workstation, it’s almost inevitable that the user will require access to files, applications, or services hosted by other machines on the LAN or WAN. Network authentication is the mechanism that confirms the user’s identity to whatever network resource the user attempts to access. Windows Server 2003 provides several mechanisms to enable this type of authentication, including Kerberos and NTLM.

Using the previous description of interactive logons, users who log on using a local computer account must provide logon credentials each time they attempt to access a network resource. This occurs because the local computer account only exists within the individual computer’s SAM database rather than a centrally managed directory service like Active Directory.

On the other hand, if the user logs on using a domain account, the user’s identity is proven by domain level authentication mechanisms that are automatically submitted to any network services the user is requesting to access. The mechanism used depends on the configuration of the network and the operating systems involved. Because this happens in the background, the network authentication process is transparent to users in an Active Directory environment. The network operating system handles everything behind the scenes without the need for user intervention. This feature provides the foundations for single sign-on in a Windows Server 2003 environment by allowing users to access resources in their own domains as well as other trusted domains.

Test Day Tip

Network authentication using a domain account can be accomplished via a username and password or with a smart card device.

Interactive Logon Authentication

Interactive logon authentication verifies the user’s logon information to either a domain account or to a local computer. This process of authentication is based on the type of user account, such as a domain account or a local computer account:

With a domain account, a user logs on to the network by providing logon information such as a password or smart card, using single sign-on data stored in the Active Directory directory service. When a user logs on to the network with a domain account, the user can access resources both in the domain to which he or she logs on and any other trusted domains.

With a local computer account, a user logs on to a local computer by providing logon information stored in the Security Accounts Manager (SAM) on the local machine.

Note

SAM is a local security account database for local computer accounts. Local user accounts are usually stored on workstations or servers, and can only be used to access the local computer, not resources on any other computer on the network.

Operating System Administrative Access

As stated earlier, the SQL Server service will be running as a domain account that has already been created in the domain. You need to ensure that the service account is a local administrator on the system that will be running SQL Server. In addition, the account used by SQL Server, Frank’s user account, will also need administrative access to the system because he will be responsible for maintaining this server. You can use the standard Windows 2000 “net” command-line tool to make sure that both these account permissions are configured. The following statements, run at the command prompt on the Windows 2000 server on which SQL runs, will ensure those administrative permissions for Frank and the SQLService service account:

NET localgroup Administrators MYCORP\Frank /add

NET localgroup Administrators MYCORP\SQLService /add

One Account Per Sever

The next easiest approach from an administrative standpoint is to use a different domain account for each server which has Microsoft SQL Server installed, but the same account for running each of the installed SQL Server services. This technique requires that a new Active Directory account be created for each SQL Server which is installed in the enterprise. This is a secure technique given that the SQL Servers don’t have access to each other’s data by default, unless specific rights are granted to one SQL Server to access another SQL Server’s data. The additional administrative effort comes from needing to manage all the various usernames and passwords in some sort of repository. With the one account for all servers approach discussed earlier in this chapter there is only a single account to be maintained, which can be easily enough given to new database administrators as needed. With this approach there are dozens if not hundreds of accounts which need to be managed and deleted when SQL Servers are removed from production.

With regard to password changing when using this technique, things are both simpler and more complex all at once. The technique makes password changes harder because there are dozens or hundreds of domain account which now need to be changed, potentially all at once when a database administrator leaves the company. However, the process is simpler as not all the SQL Services in the enterprise need to be restarted at once. The passwords can be changed in smaller batches so that only a few servers and applications need to be restarted at a time greatly reducing the impact of the changes and greatly increasing the chance that the password will be changed.

Kerberos Authentication

Kerberos was introduced as a security protocol starting with Windows 2000. Microsoft’s implementation of this protocol is based off of Request for Comments (RFC) 1510, the RFC for Kerberos version 5 (since superseded by RFC 4120). Admittedly, though, Microsoft has “extended” the Kerberos implementation specified in this Internet “standard.” One advantage Kerberos has over NTLM is that of mutual authentication. In NTLM, only the client is verified. The client must assume the server is legitimate. That is, no rogue server is pretending to be the server being connected to. Kerberos handles this issue by using a trusted third party (in an Active Directory implementation this trusted third party is a domain controller), the details of which are not germane to our discussion here.

However, in order to use Kerberos, the account being used to authenticate must be a domain account. In addition, there is likely some work that’ll be required of a domain administrator in order to ensure the proper Service Principal Names (SPNs) are registered. How to do this will be covered later in this chapter.

Shortcut…

Domain Accounts

In the case of a machine that is a member of an Active Directory domain, the administrator must create and manage domain accounts. There are significant differences in the portability of these accounts, their authentication methods, and the access that they can achieve throughout the enterprise. In this section, I take a quick look at the domain account structure and briefly describe some of the differences that need explanation in relation to the domain accounts and their functions (Active Directory is out of the scope of this book). While looking at domain accounts, it's important to understand that these accounts are created and maintained on domain controllers that replicate their content to each other. DCs that hold the domain account database do not use local accounts for their operation. As the DC is created, the tools for management of the user and group accounts switch from a local management console to a new tool, the Active Directory Users and Computers (ADUC) management console. From within this console, administrators are able to create, modify, and control user and group memberships. Figure 2.5 illustrates the ADUC console with the Users container open.

Note the significant difference in the number of default user accounts that are created as you create an Active Directory structure. This container contains not only the default users, but a number of domain-wide security groups that are used to maintain and manage the domain operations. Some new security groups are also created, which include Domain Computers, Domain Controllers, Enterprise Admins, Schema Admins, and Domain Admins, among others. All of these groups are used for domain-wide groupings that allow you to control or grant access to specific operations within the domain. Security groups also allow you to enforce group policy conditions, which I touch on later in this chapter and fully explore in Chapter 6. Figure 2.6 shows us the Built-in Groups that are created in an Active Directory domain.

This collection of groups allows administrators to assign or delegate permission to work within specially defined areas of control to perform system-based tasks in the domain. These built-in groups provide the ability to delegate control. Notice in Figure 2.6 that there is a group called Pre-Windows 2000 Compatible Access. This group can lead to security difficulties, because it can contain the special group Everyone in its membership. When this is true, down-level machines (or attackers) may establish a null session connection with the use of a blank password for anonymous access. In this case, anonymous users (such as to a Web page) could potentially access and obtain control of your machine. This particular configuration requires much diligence as you prepare file and drive access control settings, but may be needed depending on your network's makeup.

There is a not significant difference in the sphere of influence of these groups in Windows 2000 and Windows 2003. Please remember that in NT 4.0, these groups had access only on machines that were either a PDC or BDC. In Windows 2003, these built-in groups have access and control over any Windows 2003 machine that is a domain member, even if it is not a domain controller. This is a change that you must be aware of as you assign membership to these groups. Now, what about the “Everyone” group that is discussed all the time? Windows 2003 also has a number of groups that are not detailed here, but rather are present and utilized based on actions being performed. For instance, the Interactive group contains users who are allowed to log on locally to a machine. The Network group contains users that are allowed to connect from the Network. Membership in these groups is not assigned, but rather occurs during operation of the machine and network operations.

SQL Mail

One of the critical components to ensuring that an operator receives an alert is e-mail. SQL Mail provides the means to create and send those critical e-mail messages. SQL Mail does not, itself, deliver e-mail to a recipient. Instead, it provides a means to generate an e-mail message, even one that contains the results of a predetermined query, and establish a client connection to an e-mail server in order to transmit the message. The client connection that SQL Mail establishes is an extended Messaging Application Programming Interface (MAPI) connection. MAPI is a standard API used with many e-mail systems, including Microsoft Exchange.

When you configure SQL Mail, you need to have a connection to the e-mail server, a mailbox, and a user account that is capable of logging on to the SQL Server. To automate SQL Mail, you must create a job that uses a stored procedure called sp_processmail. This procedure checks the mailbox for any mail and kicks off xp_sendmail to execute queries in the e-mail text and forward the result to the recipients.

Several stored procedures take advantage of SQL Mail. When a SQL Mail-activated stored procedure executes, SQL Mail requests the defined mail profile for the domain account that triggered the stored procedure. The account for which you will configure a mail profile is the startup account that is listed in the SQL Server properties on the Security tab. This service account must have a mailbox configured on an Internet mail server or Exchange Server. You can create a mail profile this way:

Log on to the server as the service account.

Open the Mail icon in the Control Panel.

Select the appropriate mail server, whether Exchange Server (if available) or an Internet mail server.

Complete the appropriate information to configure the profile for the account.

To implement SQL Mail:

1.

Log on as an account with administrative privileges.

Click Start | Programs | Microsoft SQL Server | Enterprise Manager.

Navigate to your selected SQL Server and expand it.

Expand Support Services.

Right-click SQL Mail.

Select Properties from the pop-up menu. You will see the dialog box shown in Figure 6.36.

Type or select a name from the drop-down list, then click Test.

Click OK to close the dialog box.

Outlook and Autodiscover

In previous versions of Outlook, when you create a new profile you have to tell it that you're using Exchange Server, provide your mailbox name and mailbox server, verify it is correct, and then proceed. Creating a profile in Outlook 2007 is more of an automatic process; when you log on to a workstation using a mailbox-enabled domain account, and start creating a new Outlook profile, it enters the required fields for you. If everything's configured correctly, you don't need to enter any information at all. Outlook sends an LDAP query to Active Directory for your mail-related information—after all, it's all there to be gotten if you give it your username and password—and then configures Outlook automatically. Autodiscover uses the domain account or the email address to provide the following information:

Display name

Mailbox server

Outlook Anywhere settings

Internal and external connection settings

URLs of servers providing Web services (availability, OAB, etc.)

Outlook invokes the Autodiscover process when you create a new profile, but it also invokes it in other situations:

When the application starts Outlook queries Autodiscover every time it starts up to check for any changes.

When it can no longer connect If Outlook loses connectivity with the mailbox or Web services, it queries Autodiscover for any changes. Note that this will also happen if you move a user's mailbox in Exchange; if the user is logged on, Outlook automatically reconfigures itself and carries on working.

Periodically Outlook polls the Autodiscover service every six hours for any changes

Outlook goes through a number of lookups. The first is the AD lookup, and if it's successful, it stops there since it has the information it needs. If the AD lookup fails (for example, in an Outlook Anywhere scenario), it goes through a number of further lookup attempts, which we will explore.

What are built

What is a built

What are the built

Where is built