If this is your only SQL Server on the network and you don’t plan on having this server interact with any other SQL servers or remote storage servers, you can use the Local System account. If you want to interact with other SQL servers or pull information off of another server you are going to need to create a user account in your domain. If you do use a domain account for SQL Server, it is recommended that you create an account for just SQL server and not use an existing user account. This is important because if your network’s account policy is to have the password expire every 30-90 days you would have to change the account passwords on all your SQL Servers every 30-90 days. To overcome this problem, set the “Password Never Expires” on the account you use for your SQL Servers.
Customizing each service’s account gives you the same options as using the same account for each service, but allows you to set the account each service uses independently. It also gives you the option to start the SQL Server Agent at startup or not. We will look at services in more detail in the next article.
Select “Use the same account for both services. Autostart SQL Server Agent Service,” “Use the Local System account,” and click Next.
13. Selecting the Authentication Mode is the next option we are presented with. The process of authenticating (using your User Name and Password) to a SQL Server for access to the databases can be accomplished by the SQL Server or by the operating system.
If you select “Windows Authentication Mode” all authentication will be handled by the Windows Security Users and Groups. In other words the accounts that you use to login to a Windows computer are also used to access the SQL Server databases. Having the ability to use Windows’ Security provides benefits such as streamlined authentication and much simpler user management. It is recommended to use only Windows Authentication Mode unless you have a specific reason not to.
“Mixed Mode” authentication provides both Windows Authentication and SQL Server Authentication. Unlike Windows Authentication, SQL Server Authentication is handled by SQL Server and does not rely on the operating system. Because SQL Server Authentication is independent, authentication can be provided for platforms other than Windows (ex: Unix). When you choose Mixed Mode authentication you are asked to provide a password for the system administrator (sa) account. This account is available so you can authenticate as a “sysadmin” via SQL Server Authentication. When you use only the Windows Authentication Mode the sa account is disabled.
There are two final notes about Windows Authentication (for either of the two modes): The Windows Security groups BUILTIN\Administrators on the local computer (the computer SQL Server is installed on) and the group Domain Admins (if the SQL Server computer is part of a domain) are automatically granted “sysadmin” rights to the SQL server. Also remember you must use Windows NT or Windows 2000 to provide Windows Authentication. If you are using Windows 98, for example, you can only use SQL Server Authentication.