In Chapter 2, "Architecture and Tools," I explained the system architecture by showing the path a query takes from a data perspective. It makes sense to follow that same procedure here, because knowing where the packets go affects what you have to be concerned about from a security standpoint. This overview will help you navigate the rest of the chapter.
Let's start with the concept of endpoints. An endpoint is some combination of transport, such as TCP/IP or HTTP, and a payload, such as a T-SQL query and its results in a Tabular Data Stream (TDS) or a Simple Object Access Protocol (SOAP) call. In effect, it is the interaction between the user (principal) and the database services. The server listens using the Browser Service, which redirects the request to the proper port for a particular instance. You do not have to use this service if your application is using the default instance port (1433) or knows which port to connect to.
You can encrypt the transmissions across the channel and use a certificate between the two systems. A certificate is a piece of software that guarantees an identity. SQL Server 2005 can generate a self-signed certificate for the session, which protects against most "man-in-the-middle" attacks. You can also use a certificate from a certification authority, which provides better protection. In either case, to use the certificate by default, you need to set the Force Protocol Encryption switch on the server using the SQL Server Configuration Manager.
Once the connection is configured, the principal is authorized to an endpoint using either a Windows login or a login created and managed by SQL Server 2005, both of which grant entry into the server. After that, the principal is authenticated (using a user identity value) directly through their account or by virtue of a group (called a role) to perform actions in or on the database.
If you are not using Windows accounts but you are using Windows 2003 Server, SQL Server 2005 can enforce the password policies controlled by the operating system. Otherwise, you can set up a few simple policies within SQL Server 2005 such as password length, complexity, and lockout durations. You can also force the principal to change their SQL Server 2005 login password the first time he or she connects, and you can disable accounts without deleting them.
You can secure the following objects, which Microsoft calls scopes:
Each of these scopes contains other objects, which we explore throughout the chapter. As the administrator, you can grant access to change a table (DDL-type statements) or to view, edit, add, or delete the values (DML-type statements) the table holds. Normally if a principal has the ability to use DDL statements, that principal also has the ability to use DML statements.
Security principals of the "user" type do not own objects within a database; instead, the schema object holds all database objects. Using a fully qualified name in the format servername.database.schema.object, you can grant or deny access on a single object to a principal with fine granularities. Using a schema allows you to bring users accounts in and out of the system without "orphaning" other objects.
Your developers have a couple of other measures to control security, which do not require you to set privileges and rights on securables. They can use tables within the application to implement their own security, or they can use an application role, which I describe shortly. If they use one of these other constructs, you need to be aware of what they are doing, but you will not always be in control of the application security. You will, however, still be in charge of overall data security, so it is best to understand the way the application implements security.
Let's explore the outermost security layer and some of the tools and techniques you can use to develop and implement your security plan. This process involves thinking about of every place that the data travels and identifying each intersection in that path as a security risk. Documenting the path and the data exposure points forms the core of your security plan. The rest of the plan details what you have done about those risks.