About enabling the System Administrator (SA) user name in an Access project

When the Microsoft SQL Sever 2000 Desktop Engine (formerly called the Microsoft Database Engine or MSDE) is installed on Microsoft Windows computers, it is installed with Windows Authentication implemented (this feature is also known as integrated security). When a user connects to the SQL Server database through a Microsoft Access project, the connection is enabled through a Windows user account. SQL Server verifies that the account name and password were validated when the user logged on to the system and grants access to the database, without requiring a separate logon name or password.

With the default installation of the Microsoft SQL Server 2000 Desktop Engine with integrated security, the user must be a member of the administrator group for the machine on which the Access project resides. This makes it difficult to allow other users to gain access to the Access project.

There is currently no simple way for the system administrator to create new logon accounts to the locally installed SQL Server database except by using SQL Tools or Transact-SQL (TSQL) commands. See the Microsoft Developer Network Web site for more information on TSQL.

You can change the security mode for accessing the SQL Server database on a computer running Windows 2000 or later. By setting properties from the View menu, you can enable mixed mode security. Mixed mode security allows access to the database from either a Windows account or through a SQL Server account by using the default system administrator (SA) account with no password.

Using the default SA account, users can access the SQL Server database through the Access project without any additional security requirements. The administrator of the SQL Server can also add additional security by changing the default SA account password.

In order for the Enable system administrator (SA) user name check box to be enabled, the following must be true.

• The Access project must be connected to a SQL Server 7.0 (or later) database or a Microsoft SQL Server 2000 Desktop Engine.
• The SQL Server database must be running on a Windows Server platform.
• The SQL Server must be running on the same computer as the Access project.
• The current Windows user must be an Administrator for the computer.
• The current SQL Server user must be a member of the System Administrator role.

Security  Avoid using the built-in system administrator (sa) logon account. Instead, make system administrators members of the sysadmin fixed server role, and have them use their own accounts to log on. Use sa only when there is no other way to log on. To prevent unauthorized access through the sa logon account, you should assign that account a strong, unique password.

After changing the security mode, you can change the SA password by using the Set Login Password command (on the Tools menu, point to Security).