6.5 SQL Server Security

---

Microsoft SQL Server has been used for many years as a secure data repository, but with the product becoming increasingly mainstream, more and more organizations have a need to ensure that their databases are secure.

6.5.1 Modes, Modes, and More Modes

SQL Server 6.0 had three different security modes:

1. Standard

2. Integrated

3. Mixed

Using standard security users would log onto SQL Server by specifying a login name and password. With integrated security, if the default named pipes protocol was used, users were automatically logged in to SQL Server using the Windows NT user name and login. This had the additional bene- fit of not sending the login/password combination across the network. Finally, mixed security was a mixture of standard and integrated security. If SQL Server uses protocols other than named pipes, mixed security will enable any user with either integrated or standard security to log into a SQL Server with a valid login name and password combination.

With SQL Server 7.0, Microsoft changed the security model. Windows NT authentication mode was introduced, which was similar to the previous integrated security mode in SQL Server 6.0. Mixed mode was retained and the old standard mode, which effectively ignored Windows NT security, was dropped.

Windows authentication and mixed mode are the two security models now used in SQL Server 2000. SQL Server authentication is retained in the mixed mode for backward compatibility reasons and because SQL Server running on Windows 9. x does not have access to Windows NT/2000 security.

Once a user has successfully logged in to SQL Server, he or she has passed the first security hurdle. The next step is for the user to have access to appropriate databases on that particular server. This is a useful feature, since permissions can be set at a fairly granular level from now on. A good example would be a developer who may be permitted access to a development database but not to the personnel database that also resides on the same server. Each user account in each database can also have appropriate object permissions assigned-for example, access to specific views or tables. If there is no specific account set up, then guest access can be permitted; the scope of that access is defined by the database administrator for such informal use.

6.5.2 Groups and Roles

Imagine having to set up 500 individual accounts, ranging from the CEO to the reception staff. Each user would need to be given database access tuned to his or her requirements. On a one-by-one basis this would be a time-consuming task that would drive most administrators up the wall. To deal with just such a scenario, SQL Server has the concept of groups and roles.

A group is a Windows NT or Windows 2000 administrative unit that contains individual users or other groups of users. A role is a SQL Server administrative unit that contains SQL Server logins, Windows 2000/NT logins, groups, or other roles.

Roles are normally created when there is no natural match between the existing Windows NT/2000 groups or when the database administrator does not have the rights to set up or manage such accounts. We often use this facility when a team of developers needs to access specific objects, such as tables in a database for a project. A user can belong to more than one role in a database at the same time, which is a useful feature.

6.5.3 Types of Users

Not all users would need to have the same rights in a database or SQL Server installation, so it is useful to have types of users who have different responsibilities and tasks to achieve.

The systems administrator (sa) has full rights throughout a SQL Server installation and can undertake any task in any database. In mixed mode the sa password is blank since SQL Server set up does not allocate a password. This needs to be one of the first items changed following installation; it is surprising to note the number of SQL Servers that still have a blank sa password. In Windows authentication mode, the sa password is already set since it is a SQL Server login. As expected, if you forget your sa password, the only remedy is a fresh reinstall. The sa role is actually called a fixed server role, since each and every SQL Server needs an sa. Other fixed server roles include serveradmin, setupadmin, and diskadmin.

The database owner (dbo) is a special user who has full rights within a particular database. Each member of the sysadmin fixed server role is mapped automatically to the dbo fixed database role.

Any user who is given permission by a dbo or sa to create an object in a database is called a database object owner. Once users have created their object, they can grant access to it to other users; this must be explicitly given for each user or set of users. This has implications regarding the way in which SQL Server references objects in a database since different users can create objects and then call them by the same name. Therefore, the formal name of each object in a database is the object owner followed by the object name. For example, if user Fred decides to create a table called customers_table, the table would be called

 fred.customers_table  

If the object owner name is not specified, SQL Server will raise an error-unless the object is owned by the current user or dbo.

As previously mentioned, the guest account is designed for users with a SQL login but no account in a specific database. If a database does have the guest account enabled, the user will automatically be granted the privileges assigned to that account, which is managed like any other SQL Server database account.

6.5.4 Security Account Delegation

This is a feature introduced in SQL Server 2000 that allows a user to login to a SQL Server and then have his or her credentials retained as he or she passes through a chain of SQL Servers. To implement security account delegation, Windows 2000 must be running on each server, with Kerberos security enabled and Active Directory implemented. The setup process is reasonably involved. Some Active Directory options need to be set, allowing the account and the computer to take part in the delegation. SQL Server must also have a service principal name assigned by Windows 2000, which proves that the server is running the correct installation of SQL Server at that specific socket address (hence, the reason why you must be running TCP/IP and not named pipes to support this feature). This involves running a utility from the Windows 2000 Resource Kit, a task best left to your domain administrator.

By following some logical, straightforward rules there is no reason why your installation of SQL Server should not be robust and secure enough for prime-time applications. Remember, most security violations are due to human failure rather than anything else.

---