Understanding the SQL Server Security System

Security is one of SQL Server's most important jobsand it should be one of yours as well. If you aren't careful or don't follow some basic rules, you can easily disable much of this functionality and expose your data to nefarious eyes or clumsy programmerspossibly to the entire network or the WWW.

Traditionally, SQL Server gains much of its performance by "physical" security, that is, by restricting access to the physical server machine either by locking it away in a limited access room or by carefully managing access to it in other wayslike using a moat populated with underfed alligators. Once a nefarious person has physical access to the SQL Server database .MDF file, it can be copied to another system and accessed at their leisure. Of course, if sensitive data columns are encrypted, the evil-doer won't be able to access anything vital.

SQL Server's software-based security system has two outer layers:

• Authentication, which establishes the identity of an individual (or account) attempting to open a connection or access an object (a database, table, view, stored procedure, or any other object). Login names and their credentials are used to manage "who" can gain access to the server. In some cases, Windows can capture this login name and user credentials. In other (fairly common) situations, these values are captured or generated and provided by the application itself.

• Authorization, which is the mechanism SQL Server uses to determine if a validated identity has rights to access, update, or delete any given object. Usernames are used to validate rights on databases and the objects they contain.

SQL Server's security system is fairly complex, and it's well beyond the scope of this book to attempt to explain it allnot that anyone on the planet besides Kalen Delaney^[4] actually understands it all. In basic terms, SQL Server uses authentication schemes to gate access to the database engine and the objects it manages based on login credentials and by exposing (or disabling) specific TCP and UDP ports to the network.

^[4] Kalen Delaney is an old friend and invaluable resource for the SQL Server community. She's written a number of articles on this subject, the first of which can be found here: www.winnetmag.com/SQLServer/Article/ArticleID/40963/40963.html.

As I've just discussed, the default installation of SQL Server Express Edition doesn't expose its ports to the network, so these must be activated to be able to access this version from other computers.

Addressing Objects in SQL Server 2005

When you start digging into SQL Server 2005, you're bound to notice that the term schema is used in a new way. For decades, I've used the term schema to mean the data model, how the data tables are related, and how the database is designed. In SQL Server 2005, Microsoft has implemented a feature that (according to a credible source^[5] at Microsoft) was specified in the ANSI SQL-99 specificationthe ability to address objects by "schema" and not owner. So what is this "User Schema Separation in SQL 2005" all about? Rather than explain in depth what it is, let's first take a look at how objects are addressed in SQL2000:

^[5] Pablo Castro is another friend at Microsoft who helps us from time to time with sticky ADO.NET issues.

1.	Create a User account in SQL Server 2000 (TestPerson)it can be either a domain account or a SQL Account, although you'll find it more convenient to create it as a SQL Account for this demonstration. Figure 2.14 shows the dialog used to set up this account. Figure 2.14. Creating a new login with SQL Server 2000 SQL Enterprise Manager.
2.	Next, choose a database. For purposes of this demonstration, I chose biblio[6], but you can use any database where you have permission to add tables. Make sure the account has access to the test database. No, don't provide the TestPerson account with dbo rights to the chosen Database, but the account does need permission to create tables, as shown in Figure 2.15. [6] See Appendix I, which steps you through the process of installing the sample databases from the book's DVD. Figure 2.15. Setting the database access rights for the TestPerson.
3.	Next, as the dbo or Sysadmin user of the target database, create a table using the DDL, as shown in Figure 2.16. Using SQL Enterprise Manager, you'll see that the table created is actually created as dbo.A. No surprises here. Figure 2.16. Create a test table "A".
4.	Next, log into the database using Query Analyzer with the TestPerson credentials. Issue the same Create Table DDL and refresh the Tables node in Enterprise Manager. Notice that this creates another Table A, but this time with a prefix of TestPerson, as shown in Figure 2.17. Figure 2.17. Displaying the two "A" tables showing the owner assignment.

So this is nothing new, right? In SQL 2000, objects such as Tables, Functions, Views, SPs, etc. take a prefix of their owner. A SQL 2000 object fully qualified is a four-part name:

<Server>.<Database>.<Owner>.<Object>

When using SQL 2000, programmers don't usually use the fully qualified names in T-SQL. Actually, this is not necessarily all that bad because I've seen databases where novice programmers thought they were smart by fully qualifying the names in T-SQL stored procedures that deleted entries from tables that contained cash income. Being ever so smart, they realized that it's not considered good form to work on a live database (and I agree). To prevent damage to the production data, they created a demo database on the same machine and started executing the stored procedures to clear entries. Unfortunately, while they thought that they were clearing entries from the backup database, they were actually removing data from the live database because of the fully qualified addressing.

So you sit there thinking to yourself, "That knucklehead. He should just work with the last part of the object name." Okay, so using the Enterprise Manager (or your tool of choice) put some data into the two tables dbo.A and TestPerson.A. Make it easy on yourself and put "dbo" in TestCol of the dbo.A table and "TestPerson" in the TestPerson.A table. Okay, so let's try our luck out with a SELECT query. Take a look at the examples in Figure 2.18 and Figure 2.19.

Figure 2.18. Adding rows and executing a T-SQL SELECT using default and explicit addressing while logged in as "dbo".

Figure 2.19. Adding rows and executing a T-SQL SELECT using default and explicit addressing while logged in as "TestPerson".

The TRUNCATE TABLE operator in SQL Server drops all rows from a table. It's dramatically faster than executing a DELETE or dropping the table and all of its dependencies and rebuilding it.

Actually, the way SQL 2000 works, if the Owner portion of the object name is not specified, SQL Server tests for an object that belongs to the connected user with the current user name before searching for an object of that name owned by the dbo. Based on this behavior, from a performance point of view (albeit, marginally better), all objects in T-SQL statements should specify the owner as well as the name. Otherwise, some redundant mapping takes place most of the time when an object is referenced without a specified owner.

In many development shops, the DBA/sysadmin (the dbo) doesn't want to give blanket rights to developers. They don't want just anyone creating objects owned by dboespecially during development. This leads to the situation where it's expedient for objects to be created that are owned by non-dbo creators.

Suppose our TestPerson has been fired (his job was outsourced to WhoKnowsWhereIstand) and the DBA/dbo finds that they now wish to delete the TestPerson account from the database. Go ahead and try it. If a user owns objects in the database the owner can't be droppedat least not easily. The problem is that in SQL 2000 and earlier versions, the formal "owner" and "schema" were effectively one and the same.

How SQL Server 2005 Addresses the Ownership ChainIssue

In SQL 2005, schema has been separated from owner. From now on, the fully qualified name of an object is:

<server>.<database>.<schema>.<object>

Okay, so you perhaps wonder why this is such a big dealafter all it's not too onerous (groan) to change the owner on objects to another user and then drop the original user. Yes, this is true, but the problem is a little trickier. That's because you could potentially have client applications that are using objects qualified by the owner. This is especially true if those clients applications connect via a trusted connection and the object was not owned by the calling user or the dbo.

Most SQL Server DBAs and developers don't really create logins or user accounts based on people's namesI hope you don't, either. We do create accounts for business functions such as "PayrollClerk2" or "PointyHairedBoss". We also create SQL Server roles for these functions and assign rights to the roles. This (generally) eliminates the need to change the database or applications when someone leaves the company or gets tossed out on their butt.

Think of it this waysuppose I have an application that executes T-SQL such as:

SELECT TestCol FROM TestPerson.A

If I manage to change table A's owner to AnotherTestPerson.A and subsequently delete the TestPerson user from the database, the application is now broken until the application is re-written to refer to table A as:

SELECT * FROM AnotherTestPerson.A

This can be an expensive process if one has to touch each and every program, component, function, stored procedure, and trigger that refers to these hard-coded objects. There has to be a better wayand there is: In SQL Server 2005, objects are effectively "contained in" (not owned by) schemas, and schemas themselves can be owned by different users. This makes authoring manageable ownership in T-SQL much easier because the ownership of a schema can be changed without affecting applications. I'm trying to cajole Peter into writing an EBook on ownership that walks through this in more detail. Perhaps he'll relent before Microsoft changes it all again.

Understanding Logins, Usernames, and Schemas

As I just said, SQL Server supports both logins and usernames to implement authentication and authorization. Logins can be either Windows login accounts such as MyDomainName\billva or SQL Server logins such as "SA". By default, only Windows authentication credentials are supported, but if you enable mixed-mode security on your server instance, you can use both types of logins for authentication.

When you (or the DBA) grant a specific login name access to a database, that login is linked with a username in the user database. As a result, a corresponding security link (an SID) is recorded in the master database. This means that each username in the user database maps to a login in the master database. When adding login accounts, one should specify a default database, as shown in the Management Studio's "create new login" dialog, shown in Figure 2.20. However, before you can do this, you must create the Windows login on either the local development system or (more typically) the Windows domain. In the example in Figure 2.20, I created a local machine account (OCTO4CTP\DevLead) and used Management Studio to register this login with SQL Server.

Figure 2.20. Creating a new user and assigning the default database.

Once the login has been added to the list of recognized login accounts, Management Studio adds it to the sysxlogins table in the master database and indicates the default database this login is to use; the next step is to specify what database(s) this login is permitted to access.

To choose one or more databases for this login, click on the Database Access icon (upper-left corner of Figure 2.20). This launches a new dialog and permits you to choose one or more databases. After you check a database in the dialog, choose one or more appropriate roles for the username on the selected database.

Repeat this role-selection process for each database the username is to access. Yes, you can also drill down into the database|security|users tree and set the username rights there, as shown in Figure 2.21.

Figure 2.21. Choosing a database and rights for that database.

Understanding SQL Server Roles

SQL Server supports nine fixed database roles, as shown in Figure 2.21. Each of these roles defines the rights the specified username is granted when accessing the database. The basic roles are:

• db_datareader: This role permits username accounts to read any data in any user table in the database.

• db_datawriter: This role permits username accounts to add, change, or delete data in any user table in the database.

• db_owner: This role permits the username account to perform all configuration and maintenance activity on the database. This means db_owner role members can change the database schemaadd, change, and delete objects within the database.

• public: All usernames are members of the public role. You can grant or deny rights to public on a case-by-case basis.

There are six other roles supported, but these are generally of more interest to DBAs and more advanced users. Check out books online for more information on fixed database roles, if you're curious. If you don't assign a role to the database username, it inherits the rights granted to the public database role.

As you can see, these roles are not particularly granularthere are many situations where you're going to want to grant access to specific tables (or even columns within tables) to specific usernames and not to others. This is a fairly involved process that involves considerable planning. Enterprise Studio has a new set of dialogs that permit you to set very specific rights on any object in the database. It's beyond the scope of this book to get into the details of this process.

Understanding Object "Ownership"

By default, the owner of any object is the username that created the object or the schema to which that user belongs. Generally, your DBA won't grant "create object" (db_owner) rights to all usernamesonly to a select few. Keep in mind that logins don't own or have rights to do anything except authenticate access to the databaseit's the username associated with the login that's assigned rights to objects in the database. No, a database is not considered an "object" that can be owned, so databases are "owned" by loginsnot usernames. Only usernames own objects within a database. An object is one of the components in the database, including tables, indexes, triggers, views, keys, constraints, defaults, rules, user-defined types, or stored procedures.

Handling Special Accounts

There are a couple of "special" accounts that need mentioning at this point. By default, SQL Server exposes a "System Administrator" (SA) login accountit's never a username. The other special account is the "database owner" (DBO), a username that maps to a login that's the database owner. At one point in time, most books, documentation, and training sessions used the SA account to log on to SQL Server in their examples. While I still see this practice from time to time, it's generally frowned upon. The problem with using SA as the login account is that production applications should (generally) not use this account unless they are performing a special administrative function. Yes, if you log on to SQL Server using the SA login, your application is granted rights to do anything on the server instanceon any system database or user database, regardless of whom or how it was created. That's because the SA account holds the "skeleton master key" to everything SQL Server instance stores. It's critically important that the SA account password be protected at all costs. Actually, the SA account and its password are hidden during a default installation. You'll need to request "mixed-mode" security to expose it and to set its password. Again, I don't recommend this approach, but if it's necessary, I strongly recommend using a strong SA password and creating a mechanism to change it on an irregular^[7] schedule.

^[7] I say "irregular" schedule to help throw off those who depend on regularly scheduled security changes to make it easier to crack into a system.

IMHO

I rarely recommend using the SA account to develop production applications, and only rarely use it to perform system maintenanceexcept when absolutely necessary.

There are other issues you'll (eventually) encounter when using SA as the development or production login. First, you're masking permissions issues that should be dealt with early in the database design phase. You'll find it tough to rearchitect an application that does not have a robust security plan woven into the design. In addition, at one point in time or another, you're going to have someone do a security review on your implementation. If they discover you've used the SA accounts in production, you had better have your resumé up-to-date.

All of that said, consider that the new SQL Express "User Instance" paradigm encourages developers to use the user account (with Windows Authentication SSPI security) to get SA access to the local "user-owned" instance of SQL Server Express. This way, the user account is granted full SA rightsthe user account "owns" the instance and all it contains. This also means the application can do anything to the SQL Express user instance, including dropping the database, adding new databases, or anything else short of bringing global warming to an end.

Understanding SQL Server Connection Security

By default, SQL Server is configured to use "Integrated" security, as described in Chapter 1. This configuration is also called "Trusted", "SSPI", or "Windows Domain-managed" security. This means that when a connection is opened, SQL Server expects to be passed the Windows domain credentials of the login account that's currently logged into the Windows workstation. SQL Server looks up this value in its list of known login accounts and assumes the password is correct as it was validated by Windows. This login name can be a person (in which case, the person's Windows credentials are passed to SQL Server for authentication) or an application like IIS hosting an ASP or ASP.NET application (in which case, special "ASPNET" [in IIS 5.0] or "IIS_WPG" [in IIS 6.0] account credentials are authenticated).

Windows domain administrators are automatically granted SA rights on SQL Server. This is another reason not to use administrative accounts to develop or deploy applications.

Windows domain administrators are automatically granted SA rights on SQL Server, so if you must perform operations restricted to the SA, you need to log on to Windows as Administrator or as a member of the Administrators group assuming your IT people have given you these rights (I don't encourage them to do so). If the user logging on to SQL Server is not a member of the domain Administrators group, they need a login account of some kind on SQL Server. Yes, you can define your own custom Roles on SQL Server that contain one or more logins or domain groups, as discussed earlier. This way, you can create a functional group of users that are recognized by SQL Server and granted specific rights to the resources they need on the server. You can also include a Windows group into a custom role to permit the domain Active Directory help manage which Windows logins are authenticated to access the database(s) and the objects they contain.

A new "schema"-based security model in SQL Server 2005 separates users from objects, providing more granular control of data access objects.

When you connect with ADO.NET using mixed mode (SQL Server) security, you must include a hard-coded login and password credentials that match a login and password registered on the SQL Server instance. In this case, the parameters passed to the data access interface in your code must include this login name (ADO.NET calls it a "LoginID") and passwordexposed for all of your development team to see^[8]. This approach won't work at all unless mixed-mode security is enabled on the SQL Server instance you're addressing.

^[8] I show you ways to hide these credentials in Chapter 9, as well as ways to ensure that exposed credentials can do no harm.

To set up this and every user login and password or set up groups of several related logins, you'll need to log on to SQL Server as a member of the domain administrators group (or as SA, if your instance supports mixed-mode security). While I discuss the basic details of setting up these accounts later in this chapter, this approach is generally not recommended. However, if you choose to use mixed-mode security accounts, you'll want to take steps to protect the credentials.

One technique I recommend is to create an application-specific login instead of requiring a user to remember a unique login and password. This is especially useful when you don't really know your users, as when building anonymous logon ASP applications or XML Web Services. This is relatively safe, as an application login is configured, so it can execute only specific stored procedures, so little harm can be done if the credentials leak out. In any case, an individual login or role containing the login must be set up to permit access to whatever user database and objects are appropriate.

No, as I've said before, I don't usually create login accounts for "people," but for their function. For example, I'll create a Login for "AccountPayableClerk1" and let the accounts payable application log into SQL Server with these credentials. This particular login is added as a username in the Accounting database and granted permission to (just) the database objects needed for the applicationand no others. I rarely grant specific usernames access to base tables in the databaseit's simply too dangerous. I usually build stored procedures on the server to perform all of the required queries and updatesbut I'll get to that later.

So in case you missed it, you can build applications that don't require users to enter SQL Server login credentials, as the application can use the credentials assigned to the user when they logged into Windows. On the other hand, the application can use hard-coded credentials or, as in the case of an ASP application, use the credentials assigned to IISthe "ASPNET" or "IIS_WPG" account. Yes, it's also possible through "impersonation" to have Windows credentials to be passed through IIS to SQL Server, but this works only in certain configurations and is more expensive to implement. In Chapter 9, I discuss how to open connections using each of these connection strategies.

Creating Security Personas

At this point, let's try to implement some of the theory and suggestions I've covered earlier in this chapter. So, before you start wading into SQL Server application development, you should consider creating and configuring security personas for at least three types of accounts:

1.	The SQL Server System Administrator. This persona is responsible for dealing with administrative functions on SQL Server and should not be used by developers to create, tune, or test applications.
2.	One or more "developer" personas. These accounts might be created with quite a few rights as their role, as a developer dictates. Depending on the size of the shop and the sophistication of the security scheme, the developer personas might be restricted from accessing (seeing, modifying, and tampering with) sensitive tables, views, or stored procedures.
3.	One or more "client" personas. These accounts are created with very few rights, generally just enough to perform the tasks executed by the applicationthat is, rights to execute specific stored procedures, access specific views, and (rarely) read specific tables.

As you create your database and add tables, procedures, and rights to these objects, you'll use the administrator's account. Your limited-rights developer persona is used by your development team to create applications that use the aforementioned SQL Server objects. The client account is used when testing your application. In each case, you can log on to Windows using the appropriate account or use the "run-as" approach that executes the Visual Studio IDE or your test executable as a different identity.