SQL Server Authentication | Security for Microsoft Visual Basic .NET

SQL Server 2000 can authenticate users with one of two mechanisms. The first mechanism is Windows Authentication, where the user’s Windows user code is silently passed to SQL Server. The second type is confusingly named SQL Server Authentication, where the user enters a username/password combination that is maintained in SQL Server. Don’t be fooled by the name SQL Server Authentication—this type of authentication is by no means a preferred option for authentication. As we discuss soon, Windows Authentication is the preferred option.

You can configure SQL Server to use Windows authentication only, or Mixed Mode. Mixed Mode supports both Windows Authentication and SQL Server Authentication, and is provided mainly for backward compatibility. You can’t configure SQL Server to use SQL Server Authentication only. This is a global configuration—it applies to SQL Server as a whole and can’t be applied on a per- database basis. Microsoft recommends using Windows Authentication instead of Mixed Mode. The advantages of using Windows Authentication include:

No blank passwords In Mixed Mode, SQL Server supports a special SQL Administrator account named SA. Some analysts estimate that up to 40 percent of SQL Server databases have a blank password for SA. Consequently, this is the first thing intruders try when attempting to break into a SQL Server database. Using Windows Authentication removes this problem by removing the SA logon account. The Windows domain controller can be configured to ensure that users choose a strong password.
Central administration of user accounts SQL Server authentication requires someone to maintain the list of users in SQL Server. This raises the issue that someone has to remember to update the list of users in SQL Server every time an employee joins the company, leaves the company, or changes roles. This additional administrative task often results in the list of users in SQL Server being out of step with the domain controller’s list of users. Windows Authentication means the list of users is maintained in one place along with the domain logons.
Authentication is done once Windows Authentication results in the user having to enter one username/password combination as she logs on to Windows. SQL Server authentication requires the user to enter an additional username/password combination for SQL Server after already logging into Windows. Some application designers get around this by hard-coding a username/password into the application or setting up all users with a blank password. Neither of these is a good practice because they both bypass the security of having a password that only the valid user knows.

When should you choose Mixed Mode authentication? The only time you should use this form of authentication is when you can’t use Windows Authentication—for example, in non-Windows environments, legacy systems configured to use SQL Server authentication, or when the SQL Server database is installed on Windows 98 or Windows ME.

Changing from Mixed Mode authentication to Windows authentication

If you’ve already set up SQL Server for Mixed Mode authentication, you can change it to Windows Authentication using the following steps. Likewise, if you don’t know what authentication method SQL Server is using, you can use these steps to find out.

Start SQL Server Enterprise Manager by choosing Enterprise Manager from the SQL Server group in the Programs section of the Start menu.
Select a valid SQL server. The lefthand pane of the SQL Server Enterprise Manager child window shows a tree from which you can open nodes to the server you want to administer. This example refers to the local server, but you can also administer SQL Server on remote machines.
Right-click on the server, and choose Properties from the shortcut menu. The SQL Server Properties dialog box will open.
Click the Security tab on the SQL Server Properties dialog box. This page can be used to change the authentication mode to Windows Authentication as shown here:

If you change the authentication mode, the change won’t take effect until the next time SQL Server is started. If you change the authentication mode from Mixed Mode to Windows Authentication, any logons you defined in SQL Server will be disabled. For the remainder of this chapter, we assume SQL Server is using Windows Authentication.

Setting up a logon for Windows Authentication

SQL Server can be configured to grant or deny access to local users, local groups, domain-level users, and domain-level groups. For example, you can configure the server to grant access to the domain group Database users, but deny access to the domain group Database troublemakers. The authentication rules in favor of the most restrictive option. For example: suppose Mike Bond is a member of both Database users and Database troublemakers. In the scenario we just mentioned, Mike will be denied access to SQL Server. In practice, this is a good strategy for granting and denying access to a SQL Server. In this exercise, you’ll add the local group Users to a SQL Server database.

Start SQL Server Enterprise Manager by choosing Enterprise Manager from the SQL Server group in the Programs section of the Start menu.
Select a valid SQL server. The lefthand pane of the SQL Server Enterprise Manager child window shows a tree from which you can open nodes to the server you want to administer. This example refers to the local server, but it applies equally to a remote SQL Server you have the rights to administer.
Expand the SQL Server node, and locate the Security node.
Within the Security node, locate the Logins Node. Right-click the Logins node, and select New Login from the shortcut menu. The New Login dialog box will open as shown here:
In the name field, enter the Windows user to add—in this case, BUILTIN\Users. The builder button to the right of the text box can be used to select the Name. The name entered must be a valid Windows user account or group—either a local account or a domain account. In this example, we grant access to the local machine’s group by using the name BUILTIN to refer to the local machine.
Click OK.
Selecting the Security node will show the list of logins that have been granted access to the SQL Server in the right pane. The list of logins will now include BUILTIN\Users as shown here:

Determining Who Is Logged On

There are two ways to uniquely identify the person using a database—by the user’s Windows logon account, and by the database user account the user is accessing the database with (more on this soon). SQL Server has three useful built-in methods for returning the identity of the current user. SUSER_SNAME returns the Windows usercode (for Windows Authentication) or SQL Server usercode (if SQL Server Authentication is being used). SUSER_SID returns the SID (Security IDentifier) for the usercode. The SID is a unique identifier for the user. The SID remains the same even if the usercode is renamed. The SID is the best identifier to use to uniquely identify the user. The third method is USER_NAME, which returns the database username that the usercode maps to. Figure 12-1 shows the results of the three methods for the Master database on the author’s computer. The author is logged on as Pukeko\Ed and is using the database as guest. Database users are explained in the next section.

click to expand
Figure 12-1: Results of three identifier methods

How SQL Server Assigns Privileges

A SQL Server installation has one or more databases—for example, Master, Northwind, and Pubs. By default, each of these databases has two users authorized to use it: dbo (database owner), which has permissions to do anything such as changing data and altering the database structure; and guest, which can view or change data. Each logon is assigned to one of these database users. So, with a default SQL Server installation, if you access SQL Server with an account with administrative permission, you will be assigned to dbo for all databases. If you access SQL Server using a Windows account without administrative privileges, you will be assigned to guest.

You can also add users to a database. For example, once BUILTIN\Users has been added as a SQL Server login, it can be added to each database as a user. To do this, Start SQL Server Enterprise Manager, navigate to a database, and open the Users node. Right-click the Users node, and select New Database User from the shortcut menu. The Database User Properties dialog box allows you to add a SQL Server user to the list of database users as in Figure 12-2.

click to expand
Figure 12-2: Adding a user to a database

SQL Server allows even greater fine-tuning of permissions. Each database has a number of roles, such as public, db_owner, db_datareader, and db_datawriter. Each role is capable of certain actions, such as read-only access to data. Each database login has one or more roles associated with it that define what the user can or cannot do.

So, to recap: the Windows user is authenticated and assigned to a SQL Server user. The SQL Server user is assigned to a particular database user. The database user has one or more roles associated with it that determine what actions the user is permitted to perform.

There are some additional behaviors to be aware of. First, if the user has not explicitly been assigned to a database user, he will be assigned to a special database user named guest. Second, every database user (including guest) is always assigned a role named public—this role cannot be removed from the user. Third, by default the public role can add, remove, view, and change any information in the user tables. For this reason, you should remove all privileges from the public role and use the other predefined roles, such as db_datareader and db_datawriter. For information on what each of these roles does, see the MSDN article at http://msdn.microsoft.com/library/en-us/architec/8_ar_da_3xns.asp. Finally, you should remove the guest account from each database to ensure only intentionally authenticated users can access the database.