Microsoft Access developers and other developers switching to SQL Server need to learn new ways of securing their applications. This article is the first in a series that explores SQL Server security settings that affect the ways users can interact with the SQL Server applications you build with Access projects. SQL Server security involves at least two main steps. First, users must log in to SQL Server. Second, the user account associated with a login must connect to a database on the server so that the login can access the contents of tables and other objects within that database. This article describes how to set up your system and UI so users can make valid login requests. A login request starts when SQL Server validates a user's credentials. After validation, SQL Server logs in the user with whatever server permissions those credentials entitle a user to.
Authentication Modes
Authentication is a process that validates a user's login credentials by checking them against the SQL Server or Windows user account credentials. Access 2000's UI for SQL Server gives you two ways to authenticate user ID (UID) and password information. First, you can designate Windows NT authentication (which works for both Windows 2000 and NT accounts). When you choose this approach, SQL Server trusts the account from the last time a user logged on to Win2K or NT. This authentication mode doesn't let users access SQL Server without a valid Win2K or NT account. Alternatively, SQL Server can use mixed mode to authenticate login accounts. This approach allows logins from accounts that Windows maintains as well as from those that SQL Server maintains directly. Mixed mode provides a way of accessing SQL Server for users who don't have valid Win2K or NT accounts, such as users who connect to a SQL Server 7.0 or Microsoft SQL Server Desktop Engine (MSDE) server that is running on a Windows 95 computer.
The SQL Server interface offers two ways to set the authentication mode. First, you can select one of the two authentication modes when you install SQL Server 2000 or SQL Server 7.0. If you're installing SQL Server on a Win9x computer, the installation process automatically selects mixed mode because NT authentication isn't available. After installing SQL Server on a Win2K or NT system, you can use Enterprise Manager to revise the authentication mode from SQL Server authentication to NT authentication. Open the SQL Server group and right-click the server you want to change. On the context-sensitive menu, click Properties, then click the Security tab. The Authentication options group offers two option buttons—one for each authentication mode. The example in Figure 1 shows a Windows NT only authentication selection. You can change to mixed mode by selecting SQL Server and Windows NT. Before the new setting can take effect, you must restart the server.
Creating Login Accounts
The authentication mode specifies how SQL Server validates a UID and password. Even after verifying a UID, SQL Server won't let a user in until it determines that the UID corresponds to a valid login account.
Two types of login accounts correspond to the two authentication modes. If your login is sa or is a member of either the System Administrators or Security Administrators role, you can add, edit, and delete logins on a SQL Server system by connecting to it from an Access project. After you open an Access project that connects to a database on a server, choose Tools, Security, Database Security from the Database window menu to reveal the SQL Server Security dialog box. Click Add on the dialog box's Server Logins tab to open the SQL Server Login Properties - New Login dialog box. In the General tab's top section, you can specify a login name, as Figure 2 shows. SQL Server authentication has single-level login account names; NT authentication requires a two-level login name with a backslash separating the two parts. The Win2K or NT server name is the first part. The name you enter in the dialog box's top section is the only level for SQL Server logins and the second level for NT logins.
The middle section of this dialog box offers options for specifying a login for NT authentication or SQL Server authentication. By default, Access selects Windows NT authentication. Access enables the controls within and below the option you select. If you specify an NT login, type the server name in the Domain control text box; doing so automatically updates the entry in the Name box to a two-level name. Leave the Grant access option selected. If you're entering a SQL Server account, type the password. Figure 2 shows a login for the CABLAT server with the second-level name cabtest1.
The bottom section of the dialog box lets you specify a default database and language for the login account. Leave the default settings when you're specifying a login but not giving access to a specific database. You might encounter this situation when you create an administration-only login that belongs to the System Administrators server role.
The Server Roles tab lets you assign the login to one or more of the seven fixed server roles. Login accounts derive server permissions from their role memberships. You can maintain security by assigning the most restrictive role memberships appropriate to the login account's requirements. Note that not all logins need membership in fixed server roles. Roles that are likely to have particular interest for Access developers who build SQL Server solutions include Database Creators, Security Administrators, and System Administrators. To successfully invoke the SQL Server Database Wizard and create a new database from an Access project, a user needs a login that is a member of the Database Creators or System Administrators role. (For information about using the SQL Server Database Wizard in an Access project, see "The Power of Two: Office 2000 and SQL Server 7.0," August 1999.) Similarly, the Tools, Security, Database Security command in an Access project won't work correctly unless a user submits a login that is a member of either the Security Administrators role or the System Administrators role. The System Administrators role possesses and can grant all the permissions for all other roles, and it has permission to assign logins to itself and other roles. If you're a member of the Security Administrators role, you have the same permissions as the sa login, except that you can't remove the sa login as you can all other logins. To learn more about the capabilities of each fixed server role, see the sp_helpsrvrole and sp_srvrolepermission entries in SQL Server Books Online (BOL).
Logging In from the Data Link Properties Dialog Box
When you connect a new Access project to an existing SQL Server database or change the database to which an existing Access project connects, you make new settings on the Data Link Properties dialog box. You can open this dialog box by choosing File, Connection from the Database window menu of an existing Access project. The wizard for creating a new Access project that connects to an existing database opens the database automatically. (For details about this process, see "The Power of Two: Office 2000 and SQL Server 7.0.") Figure 3 shows the beginning of a process to create a new SQL Server login named cabtest2. To simplify server access, I made both the cabtest1 and cabtest2 logins members of the System Administrators server role.
Figure 4 shows the simplicity of the Data Link Properties dialog box settings for using the CABLAT\cabtest1 login. A Windows user simply logs in as cabtest1 on the CABLAT Windows server (the server can be either Win2K or NT), then starts Access and creates a new Access project that points at an existing database. Creating a project opens the Data Link Properties dialog box. Select or type the server name in the first text box. Next, select the Use Windows NT Integrated security option. Then, select a database on the server, such as Pubs, to connect to. Finally, click OK to open the Access project on the SQL Server database. Any Windows UID that has a valid login for the CABLAT server can use the same settings the other valid Windows UIDs use.
Using the cabtest2 SQL Server login, select the Use a specific user name and password option in the Data Link Properties dialog box to enable the User name text box; type cabtest2 in this box. If the Blank password check box is selected, clear it to enable the Password text box, then type the password you designated for the cabtest2 login at its creation. Select a database and choose OK to open the Access project on that database. When you use a SQL Server login, the UID you use to log on to Windows is unimportant. One advantage of the SQL Server login is that you don't need a connection to a Windows server. This capability is particularly attractive in SQL Server solutions for road warriors who need to work with an application even when they can't connect to a server.
Using a Login with No Server Role
I also created a third login named cabtest3. Unlike the two preceding logins, this third one has no server roles. Because of default data-access settings (which I'll discuss in the next issue), this login can connect to the Pubs database in the same way that the preceding two logins can. However, because the new login has no server roles, it can't perform server functions, such as creating another login or making a new database.
To demonstrate the lack of login-creation capabilities, I started to create a new login named cabtest4 after I logged in as cabtest3. When I clicked OK from the General tab to create the cabtest4 login, Access responded with the error message that Figure 5 shows. The message reminds the user that this action requires an sa login or membership in either the Security Administrators or System Administrators server role.
Access Granted
Learning about server logins is an important step on the way to accessing SQL Server data, and logging in lets you control server functionality. To successfully log in to SQL Server from an Access project, you need to understand certain aspects of the SQL Server login process, such as authentication modes and login accounts. The step-by-step instructions in this article offer hands-on guidance for creating and using SQL Server logins with Access projects. In the next issue, I'll show you how you can use the logins you just set up to access SQL Server data from an Access project.
