Security in computer software applications allows you to control the answer to two critical questions. Who has access and what are they allowed to do? Before answering these questions you need to determine the identity of a person, this is the authentication process. Logging in with a user name and password is part of the authentication process, because it can prove your identity. Once you know who a person is, you can authorize the actions they can perform. For example, is the user allowed to drop a table from a SQL database? Authentication in SQL is the primary topic for article. You can read more about authorization in part 2 of this aricle. In this article we will cover Windows and mixed authentication modes for SQL Server, and how to manage users, logins, and passwords.
By the end of these two articles we should have an understanding of the basic security mechanisms offered by MSDE 2000. We will be exploring the security settings in our MSDE database with the Web Data Administrator (WDA) from Microsoft. The link will provide details on how to download, install, and run the utility. The utility will allow us to view security related settings inside a database. We will introduce the different commands available as we progress through the article.
Authentication In MSDE
The SQL Server database engine behind MSDE operates in one of two authentication modes. In Windows Authentication mode the engine relies on the operating system to authenticate users. In mixed authentication mode the database engine can use Windows Authentication mode and can also perform authentication itself by verifying a login and password. By default, MSDE installs in Windows authentication mode. This is the recommended mode for operating MSDE, but it is possible to switch MSDE to mixed authentication mode. See “Turn on mixed mode During Installation” and “Turn on Mixed Mode After You Install MSDE” in the Microsoft knowledge base.
Windows Authentication Mode
Windows authentication mode is the safest authentication mode for SQL Server 2000 and MSDE database servers. Unless you are installing the engine on a Windows 98 based system, Windows authentication mode is the default option during installation.
A user begins by logging into a Windows machine. The local machine or a domain controller on the network will validate the username and password supplied by the user. At this point, the user may launch a local application to connect to an MSDE or SQL Server instance. The connection string may look like the following.
Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=CompName
This connection string instructs the software to establish a trusted connection. The client uses a layer of Microsoft software known as the Security Support Provider Interface (SSPI). A trusted carries the user’s identity to the database. The database will retrieve the account information and compare it against entries for valid database logins. If the engine finds a match, the connection is accepted.
Authentication does not grant access to any specfic database. We will explain this topic in part 2 of the article.
Windows authentication mode allows a database administrator to take advantage of the built in security features of the NT based operating system. This includes password expiration, account lock outs, and enforcing strong passwords. These robust features allow windows authentication mode to offer a higher level of security than mixed mode authentication. Windows authentication also allows your clients to use the same credentials they use to login to the network.
Mixed Mode Authentication
In mixed mode authentication the database engine adds a second mode of authentication known as SQL Server authentication.
The connection string for SQL Server authentication looks like the following:
User ID=tinklebell;Password=pan;Initial Catalog=Northwind;Data Source=CompName
This connection string explicitly specifies a username and password. The client will connect on a non-trusted connection and the database engine will extract the parameters to see if the user ID exists, then see if the password for the user ID matches the given password.
Mixed mode authentication puts more of a burden on the database and database administrator to manage users and passwords.
Database Logins
Both of the authentication techniques we have mentioned require login entries in the database. The Web Data Administrator is provides a web based UI to manage MSDE. Underneath the covers, the database engine offers a set of system stored procedures to perform the same tasks the GUI offers. The stored procedures can be used from any query tool.
Viewing Available Logins
Once you have the Web Data Administrator installed and are logged in to your MSDE instance, you should see a screen similar to the following. The screen lists the available databases in the MSDE instance, and provides a server tools section on the left hand side.
In the above screen, click on Security on the left hand side, and then click on Logins to arrive at the following screen.
We could list the same results from a query tool using the system stored procedure sp_helplogins. The results above include four valid logins for the database engine. The first entry is a Windows account name for the ASP.NET worker process. Windows logins are always qualified with a computer name (ComputerName\Username) or a domain name (DomainName\Username). If you want to run your ASP.NET application with a trusted connection from the ASP.NET account, you will need to add this login to your server.
The third row is an entry for a Windows NT group. Windows NT based operating systems allow you to place users into groups. Adding a group as a SQL Server login allows any of the users who are a member of the group to log in. When adding a special local group such as Admnistrators, a special prefix (BUILTIN) is used instead of a computer or domain name.
The default installation for MSDE and SQL Server 2000 will add BUILTIN\Administrators as an available login.
The second row in the results above is a SQL Server login (sa). A client accessing the database using mixed mode authentication can use this login name as the user ID property in the connection string. The sa login is a special account: the System Administrator login. The sa login is created by default for use with mixed mode authentication and can perform any task on the database server.
Before we begin to add new logins to the database engine, let’s take a brief look at the distinction between users and groups.
Users, Groups, and Logins
A system administrator typically places users into groups to ease system management. For example, suppose Geddy, Alex, and Donna all worked in accouting. Chances are, all three employees need access to the same resources on a machine or network. An administrator would create a new group named accounting and add each user to the group. The administrator then grants permissions to resources like files and printers to the entire group, instead of each individual user. As employees join or leave the accounting department the administrator can add or remove them from the accounting group instead of granting and denying privileges at the user level. For a database administrator, groups work equally well.
Adding and Removing Login Entries
You can add a new login in Web Data Administrator by clicking on “Create New Login” in the top right of the screen of available logins, as shown in the following screen shot.
You can also edit an existing login by clicking on the edit link at the end of the row. This screen is shown below, and you can see where you can set defaults, and grant / deny access.
Unfortunately, Web Data Administrator does not offer all of the available functionality provided by the system stored procedures in MSDE, but we will cover these procedures in more detail. You can connect to the MSDE instance using any query tool (Visual Studio's Server Explorer, for instance).
System stored procedures are used to add and remove login entries. The stored procedure sp_grantlogin will allow a Windows user or group to log on to the database engine. Only a single argument is required: the user name or group name to add. You must precede the Windows user or group name with a computer name or domain name (or BUILTIN if the name is a local built in group) and a backslash. For example, suppose we do have a Windows group for accounting (named accounting). The following command will allow members of the accouting group to login.
EXEC sp_grantlogin 'ComputerName\accouting'
You can grant a login to an individual user with the same stored procedure.
EXEC sp_grantlogin 'ComputerName\Alex'
To remove a Windows user or group, use the sp_revokelogin stored procedure. The following will remove the accounting group from the list of available logins.
EXEC sp_revokelogin 'ComputerName\accouting'
Alex can still login to the database engine since he was previosuly added as an individual user with sp_grantlogin. Revoking login permissions for a group will not revoke login permissions for users within the group if they already have individual logins.
SQL Server logins
Managing logins for SQL Server authentication requires a different set of stored procedures. To add a SQL Server login use the sp_addlogin stored procedure. The following command adds the login name Juan, with the password of kooky:
EXEC sp_addlogin 'Juan', 'kooky'
With Windows authentication, when a user changes their password in the operating system there is no additional work required to synchronize a database password. In windows authentication, the database relies on the operating system to validate the identity. With SQL Server authentication, the password is stored in the database. To change a password for a SQL login, use the sp_password stored procedure. The parameters are the existing password, the new password, and the login name.
exec sp_password 'kooky', '0ass294d', 'Juan'
Use sp_droplogin to remove a SQL Server login. The next command will remove Juan from the list of available logins.
EXEC sp_droplogin 'Juan'
SQL Server will never confuse a Windows group or user name with a SQL Server login name. Windows groups and usernames always have a backslash (after the computer or domain name). The backslash is an illegal character for SQL Server login names.
Logins and Databases
Logins apply to the server instance, so a successful login does not give a user access to a specific database. For instance, we could apply security settings in the Northwind database restricting access to administrators only. Other users could still login to the database server, and perhaps use other databases. However, only members of the Administrators group (or users logging in as sa) would have the ability to retrieve information from Northwind. These details fall under authorization, which is the topic for part 2.
by Scott Allen
Subscribe
Twitter
Search
About
Learn C#