EXERCISE 4.3C REATE A SQL S ERVER L OGINYOU NEED TO ADD A NEW SQL SERV...

7. click on OK and you have completed creating a new SQL

Server login.

Figure 4.7 General Section

Authentication Mode

When you are installing SQL Server 2008, the selection of an authentication mode

for the database engine is required. The choices are either Windows authentication

or mixed mode authentication.

If you select the Windows authentication mode, SQL Server authentication will

be disabled. Selecting mixed mode authentication enables both Windows and

SQL Server authentication. You cannot disable Windows authentication.

When selecting mixed mode authentication you are required to establish a

strong password for the sa account. If you selected Windows authentication, the sa

account will be created but disabled. It is important to remember this because

if you change authentication modes at a later time, you will need to enable this

account and establish a password at that point if you wish to use this account.

Changing the authentication mode can be done at anytime but it does require

that you restart SQL Server. You can change the authentication mode by right

clicking on the server in the SQL Server Management Studio and the change can

be made under security in the Server Authentication section. See Figure 4.9.

T esT D ay T ip

Make sure that you know what you need to do to change the

Authentication mode.

Figure 4.9 SQL Server – Server Authentication Properties

Using Windows Authentication (integrated security) is recommended and

considered to be more secure than using SQL Server logins or mixed mode.

Windows accounts are subject to the built-in security features in Windows and are

sometimes considered to be Trusted connections to SQL Server since Windows

handles the authentication.

If you are using mixed mode, Any SQL Server Logins created that are not based

on Windows user accounts are required to follow strong password guidelines.

Database users using SQL Server logins will need to login using a user name and

password every time they connect.

User must change password at next login – Established in the Log-in

■

properties in the SQL Server Management Studio.

Enforce Password Expiration – Based on the maximum password age

policy of the computer.

Enforce Password Policy – SQL Server enforces Windows password

policies established on the computer. This is only available in Windows 2003

Server and later.

When you are establishing using SQL Serving authentication, it is important to

be aware of the disadvantages and advantages. Here are some of the disadvantages:

A Windows domain user who has a Windows user id and password will

have to also enter their SQL Server user id and password. The user will

have to present the SQL Server credentials every time they connect, and

maintaining multiple user ids and passwords can be troublesome for users.

SQL Server Logins cannot not take advantage of the Kerberos security

protocol that is used by Windows.

SQL Server Logins cannot offer many of the additional Windows

password policies.

Here are some of the advantages:

SQL Server can support older applications that require the use of SQL

Server Logins.

Users in an environment of mixed operating systems where users are not

authenticated by a Windows domain can connect to SQL Server.

Users can connect from unknown or untrusted domains.

Users can connect when creating their own identities in Web-based applications.

Software developers can distribute applications based on specific SQL

e xam W arning

You will need to understand the advantages and disadvantages of the

Authentication Modes.