Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server security

This tip describes the two-part process of SQL Server security.

SQL Server security is a two-part process. Users must first log in to SQL Server and then the user account associated with a login must connect to a database on the server so that the login can easily access the tables and other objects within that database. The login request begins when SQL Server validates a user's name and password. Once validated, SQL Server logs the user in with server permissions assigned to that user.

Authentication validates a user's login credentials. It checks them against the SQL server or Windows user-account credentials. Access 2000's User Identification for SQL Server lets you use two ways to authenticate user ID and password information. If you designate Windows NT authentication, SQL Server will trust the account from the last time a user logged on to Windows 2K or NT. This mode will not let users access SQL Server without a valid Win2K or NT account. However, SQL Server can use the mixed mode approach to authenticate login accounts. This approach provides another way of accessing SQL Server if you don't have a valid Win2K or NT account.

SQL Server gives two ways to set the authentication mode, and when you install SQL Server, you can select on of them. For example, if you install SQL Server on a Windows 95 or 98 computer, the installation will automatically select the mixed mode, simply because NT authentication is not available. If you install SQL Server on a Win2K or NT system, you can use Enterprise Manager to revise or select the authentication mode from SQL Server. First, open the SQL Server group and right-click the server you want to change. Click the Properties tab on the context-sensitive menu and then click the Security tab. There will be two option buttons, one for each authentication mode. You can change to mixed mode by selecting SQL Server and Windows NT, but you must restart the server before the change takes effect.

Two types of login accounts correspond to the two authentication modes, so if your login is sa, you can add, edit, and delete logins on a SQL Server system by connecting from an Access project. Once the Access project is open, choose Tools, Security, Database Security from the Database window to display the SQL Server Security dialog box. Click on ADD tab to open the SQL Server Login Properties -- New Login dialog box. Under the General tab's dialog box, specify a login name. SQL Server authentication uses single-level login account names and NT authentication requires two-level login names with the backslash separating the two parts. Access will select Windows NT authentication by default. Access will enable the controls within and below the option you select. If you want to specify an NT login, type the server name in the Domain control text box. This will automatically update the entry in the Name box to a two-level name. Be sure to leave the Grant access option selected. If you are entering an SQL Server account, type in the password. You can also specify a default database and language for the login account. Leave the default settings in place if you specify a login, but do not give access to a specific database like Access.

About the author
Barrie Sosinsky ( president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.

This was last published in December 2002

Dig Deeper on Database Security Management-Enterprise Data Protection

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.