Microsoft SQL Server 2000 Authentication Mechanisms
Managing access to your databases
Building a secure and reliable database server involves both appropriate security means to be applied to the server as well as implementation of planned security assumptions to cover database objects. With a Microsoft SQL Server 2000 database that is firmly integrated with the Windows operating system, you can attain these goals with relatively little work. Let us therefore distinguish between four separate levels of database security control over access privileges:
- Control over accessing the database at the server level, which applies to all databases. An appropriate configuration of remote access (over the network) to the system, on which the database server is installed - assigning permissions to user and group accounts in line with the organization's security policy. Configuring Web servers to cover security oversights when databases are to be accessed at Web level; if need be additional security solutions like firewall setups or intrusion detection systems. However, discussing details of this level of security is not given consideration in this article. Of course, obtaining remote access to a system running SQL Server does not yet mean being granted access to the database server itself!
- Control over accessing the SQL Server - at this stage, you can control access to databases with the components of the SQL Server security model for users that can access the server but not log in to the database server. Logging in to SQL Server gives a user permission to connect to SQL Server and work with minimum privileges in the master database but not to access all databases.
- Regulating access to the SQL Server database is the next level of SQL Server security - each database managed by SQL Server has a list of valid users authorized to access it and using their logins to connect the server. Database administrators can set up logins and users in databases giving them permissions in accordance with the organization's security policy. Each newly added user is given specific permissions that are valid for specific databases. In this manner, a user may obtain, for example, an administrator privilege (in the X database) whilst being a "normal" user (in the Y database) with no need to log in to these databases separately. The name of the database user is guest, and this guest account is used for logins without a user account of their own in a database. An SQL Server login has no rights to a database until you set up the login as a user of that database. The right to access a specific database does not automatically imply the right to execute any SQL (Structured Query Language) command on any database object.
- The fourth level lets you assign permissions on objects in a database; for example, you can specify which tables or stored procedures a user can run and which views he can see. In fact, this means giving the user specific permissions to execute certain SQL commands on defined objects of the database.
Managing access to your Microsoft SQL Database Server 2000
With Microsoft SQL Database Server 2000 you can choose either of the two security (authentication) modes, namely:
- NT security mode (Windows NT Authentication only),
- Mixed mode (Windows NT Authentication and SQL Server Authentication).
In Windows NT mode, users that NT validates can also connect to SQL Server databases based on their user account names and group membership which already exist in Windows NT 4.0, Windows XP or Windows 2000.
In mixed mode, NT users connect to SQL Server databases under Windows NT mode and users that NT doesn't validate use valid SQL Server (SQL Server Authentication) logins. Users that connect to SQL Server databases under Windows 2000 or Windows NT can make use of trusted connection mechanism for both authentication modes. Whenever the SQL Server database is connected, the access control mechanisms that in this case are lower level-ones (access to databases or database objects only) function identically in both authentication modes.
Windows NT Authentication Mode
This authentication mode is used by default because of its inherent better security. When it is used, Windows NT is responsible for managing users' connections to the SQL Server through the user's account name or group membership. In other words, database administrators can grant access to the database to the user or the user group specified in the Access Control List provided by the operating system. With this security mode, Server SQL tracks users by their individual SIDs (Security Identifiers) stored by the operating system itself.
Mixed authentication mode
With mixed authentication mode, it is possible that access to the SQL Server may be granted or denied, based on the access token created by the operating system (as in Windows NT Authentication mode). Also, SQL Server may take over the authentication process using a table containing login account information.
How is this control over database access seen from the user's point of view? Windows NT Authentication mode can be used to manage database access in two instances. Firstly, if the Server SQL Administrator decides to choose this security mode and secondly, if the mixed mode is enabled. The user account available in the Microsoft Network domain is used to log in to SQL Server. The userid (stored in the operating system) is compared with the userids included in the ACLs of SQL Server. If it matches a valid userid, the validated user will be granted access to the databases (of course, to predefined databases only) with no need for password authentication.
Authentication provided by SQL Server itself is made only when the mixed mode is set. The mixed mode is enforced wherever the possibility to enable Windows NT Authentication mode exists. When may such a situation occur? You should choose mixed security mode when SQL Server is running on older Windows versions (less than 7.0), older platforms (Windows 9x and Windows Millennium) or for clients who cannot login to Microsoft Network (for example, Novell clients). In the case of access authentication, when a user connects to an SQL Server, then SQL Server uses a non-trusted connection that causes the client to use his credentials each time, i.e., login name and password, for authentication purposes. This data can of course be encrypted and stored locally; hence the SQL Server manages its own login validation process for all connections. The user name and password (forwarded to SQL Server mostly with the use of ADO objects) is checked against the credentials stored in ACL tables of SQL Server to grant or deny access to databases by the SQL Server.
Which option is better?
With Windows NT 4.0, Windows 2000 or Windows XP it is suggested to use Windows NT Authentication mode. Why? These Windows systems allow the creation of user accounts and groups. Therefore, this authentication mode may take advantage of the secure mechanism provided with these systems, namely trusted password matching and encryption, using minimum password length, password timeout or account lockout after multiple invalid login attempts. Defining user groups (and possibly their database roles) considerably helps in concurrently managing multiple database users.
Moreover, Windows NT validated users or groups may request access to databases without supplying their name and password. Any change to username or password under Windows does not therefore imply necessity to change application scripts. On the other hand, the SQL Server authentication mode requires that the user will supply its login and password while attempting to access a database or to change application scripts (if usernames or passwords are "buried" in the application script. This is obviously neither recommended, nor secure practice, but one must not exclude that user credentials are embedded in an application script, for example, this may happen with ASP scripts).
SQL Server Authentication mode is less secure - this is not a trusted connection that has provided for backward compatibility. It is also simpler to manage; i.e. SQL Server is wholly responsible for authenticating a user and for enforcing security restrictions on user credentials. From this point of view, it can be applied to execute control over access to non-critical organizational resources. For instance, you can create a single login on a SQL Server for all users to access a certain application instead of creating multiple user accounts or groups under Windows. Obviously, this means that monitoring activities of individual users becomes impracticable and impossible (SQL Profiler will naturally capture all access attempts, but any and all will be associated with the same user of the database). Managing users under the Windows Authentication mode rules, requires knowledge of issues related to creation of user accounts and groups within the operating system and also the necessary skills in using logins, roles and users in a database. However, notwithstanding a more complex process of administration, this mode allows one to impose more precise access restrictions and more efficiently manage multiple users.
Figure 1. Access control level in Ms SQL.
- Light-green color denotes Web-related access permissions
- Violet color denotes the MS SQL Server database access authentication process
- Blue color indicates control over access to specific databases
- Orange color indicates control over access to objects of a specific database in Ms SQL Server
What are the consequences?
If a user attempts to connect to SQL Server providing a null login name, then SQL Server uses Windows NT Authentication. In addition, if a user attempts to connect SQL Server under Windows NT Authentication mode and supplies the username, then this username will be ignored (login authentication is sufficient to authenticate the user at this point).
Some practical aspects
After enlightening (boring) readers with minute theoretical details, it is high time to provide some practical and useful information. The authentication mode can be set either during or after the installation of SQL Server 2000. Alterations to the authentication mode after installation can be made from the level of Enterprise Manager and Windows Register. In Enterprise Manager, click right mouse button on the SQL Server where you intend to introduce changes to the authentication mode, select "Properties" option and then go to the "Security" tab.
Figure 2. Setting the authentication mode in Ms SQL Server 2K from the Enterprise Manager's level.
Both the reading and modification of the authentication mode can be done using the Windows Register. To do this, first you must stop the MSSQLServer service (from another associated software program such as SQLServergent or DTC). Then launch the regedit.exe (or regedt32.exe) program. The security model can be changed by editing the following registry key:
(depending on the parameters selected during the installation). The value of LoginMode can be 1 (Windows Authentication) or 2 (mixed mode). Setting the value of this registry key (DWORD type) you can establish the required security mode. If you have introduced any modifications, you must reboot SQL Server service.
Accessing a database from the application level is mostly done using Active Data Objects (Connection, Recordset and others). In the case of access to the Server database SQL (using SQLOLEDB) under Windows Authentication, you are not required to supply user credentials within the activating series to open the connection with database
"Provider=SQLOLEDB; Initial Catalog= Database_name; Data Source=Server_ name_SQL; Trusted_Connection=yes;"
In case of SQL Server Authentication, however, you must complement this information with user credentials:
"Provider=SQLOLEDB; Initial Catalog=Database_name; Data Source=Server_name_SQL; User ID=User_name; Password=Password;"
Choosing an appropriate SQL Server authentication option is important from both the administrator's and the user's point of view. It is responsible for secure access to the SQL Server, hence the Windows NT Authentication mode is widely recommended to be applied whenever possible. As was already mentioned, mixed mode may enforce the user to supply a login and password while attempting to access SQL Server. In addition, with this security mode, changes to the application scripts may be necessary (if both login and password are embedded in the script) whenever their values are modified.
If you would like us to email you when one of our authors releases another article on WindowSecurity.com, subscribe to our 'Real-Time Article Update' by clicking here. Please note that we do NOT sell or rent the email addresses belonging to our subscribers; we respect your privacy!
- MSDN Library > Microsoft SQL Server > Managing Security > Security Levels, Authentication Modes, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_security_47u6.asp.
- "Essential SQL Server 2000 An Administration Handbook", B. Woody, Addison-Wesley, 2002.
- "Inside Microsoft SQL Server 2000", Kalen Delaney, Microsoft Press, 2001.
- "Implementing and managing SQL Server 2000", B. Combs,
- "Enhance SQL Server Security", W. Myers III, D. Byres, http://www.fawcette.com/vsm/2002_05/magazine/features/myers/default_pf.asp.
- "Microsoft SQL Server's Security Model", M. Spenik, O. Sledge, http://softwaredev.earthweb.com/sdtech/article/0,,12065_721441,00.html.