SQL Server Reference Guide

Security in the context of SQL Server deals with multiple areas, including the physical systems, the operating system and environment (or “platform”), SQL Server, users and roles, the SQL Server objects (such as tables and views) and code you write for SQL Server.

In this overview, I'll introduce these areas, and in other tutorials I'll cover the specifics of each. It helps to look security this way, since each of the parts of SQL Server security deal with different aspects, tools, concerns and approaches. Each of these elements in security forms a hierarchy. In other words, an element depends on the one previous to it. If you leave the door open to the server room, for instance, then someone can just steal your server and access the files directly. All of your other security is compromised by simply ignoring the lowest level.

It's also helpful to think of the security hierarchy this way because this is the way you'll implement the product. You'll install and configure your server, create users and groups for it, and then ensure that they can access only the objects they are supposed to. I'll use this same logical approach in our discussions here.

You can think of your database system like a bank building. As the bank building is planned, the architects put security in the design. These designs have areas that the public will access, such as the parking lot and the lobby. Other areas are accessed by certain customers, but not others, such as the safe deposit box room. Still other areas are protected from all customers, such as the main vault. Bank employees go into the vault and add money from the customers, or take money out for them. I’ll use this analogy as we go along.

Some organizations actually have a written set of guidelines for security, called a “risk profile” or “risk assessment” for their computing assets. If your organization is bound by government or other regulations, this might be a requirement for you. In that case, you can map the elements I’m describing here into your overall plan. Even if you aren’t required to keep this kind of a document, it’s often a good idea.

Physical Security

Starting at the lowest level, physical security has to do with anything you can touch and feel. The buildings where your servers live, the locks, even the lighting matters.

Even if the designers of our bank building planned perfect security, it wouldn't be effective unless the builder actually implements the design. This is also true in the database.

If you’re using a Data Center — a building away from the main portion of your campus or something you rent from someone else, or even if you are using a “cloud” or distributed computing model, it’s important that you consider this level. After all, in those circumstances the physical elements are out of your control, but you are still responsible for this security. You should learn as much as you can about the safeguards in place for the physical elements of your security.

These elements include not just buildings and locks on the computer-rack cages, but other things you might not think about. What kind of security is there for the wiring between the systems — and even the wiring coming in and out of the building? In certain situations (such as the requirements for an ISO 270001-level system) you even need to think about the type of printers and who has access to them. It’s not just the physical portions of the system; it’s who has access to those physical elements.

Operating System Security

I'll explain these physical considerations further in another tutorial, but the basics are that you should have good physical access security policies and procedures.

The next level to consider is the operating system. Each generation of an operating system usually improves on the security of the previous one, but you need to understand what vulnerabilities it has. Every computer operating system is designed to let people access resources — your job is to ensure they can only access what they are allowed to.

If you are using a service provider that hosts your SQL Server instance, then it’s important to understand how they handle the operating system for you. If you are maintaining the operating system yourself, you need to know how its security is configured, you need to apply the latest service packs where appropriate, and only allow access to protocols and ports that are absolutely necessary. The key is to reduce the surface area that you expose to the outside world.

Platform Security

The next area of security for your database system is the platform - SQL Server. Just like the bank building, security begins with the design. When you begin to think about the database design, you should follow sound, basic principles. One of those is to think about security, and develop a plan for how you will implement it.

Just as in the operating system, the more recent versions of SQL Server are more secure than their predecessors. Service packs and hotfixes also have security implications, so after careful testing and reading up, you should apply those wherever you can.

With the version and patching complete, you should move on to hardening SQL Server using the Configuration Manager tool. While the mantra of SQL Server is “secure by default”, there are steps you can take to make it safer. Disable protocols that you will not use, start the services with low-privileged accounts (use only the Configuration Manager or Setup.exe to set the service accounts for SQL Server) and configure the firewall for SQL Server — all of these are the least you should do to secure the configuration of SQL Server.

Users and Groups — Principals

In our bank example, the users are account holders. Account holders in the bank are allowed different levels of access. People with business at the bank are allowed in the front door, and based on who they are, allowed further access into particular bank accounts.

That's similar to the accounts — or principals - in SQL Server. You want to allow all users with valid reason into the server, and then based on who they are, further along into various data elements. That is the cause of a lot of confusion around database accounts. When we talk about bank accounts, it's the same person at the front door of the lobby that walks through into the safe deposit box room. In SQL Server, however, there are two users involved — one that accesses SQL Server, and another that accesses one or more databases. There are two tables involved in this process. The first is in the master database, called sysxlogins. The rows in this table store the user names, and a unique number for each of them. The second table resides in each database, and it's called sysusers. It has a unique number that corresponds to the one in the master database. That's the cause of "orphaned" accounts when you backup a database on one server, and restore it on another. The sysxlogins table in the new server's master database no longer points to the user accounts in the database you restored.

You can create these accounts in SQL Server, which means that they don't have to exist in Windows. You can also "add" an account from Windows, either from a domain or a local Windows account, to the server. If you create the account in SQL Server, you control the password and other account information. In SQL Server 2000, you have to manually set the complexity of the password, how often it is changed and so forth. In SQL Server 2005 (running on Windows 2003 or higher), you can have those kinds of policies enforced by the operating system.

Whether you create the account in SQL Server or use Windows authentication, you still have to place the server user in a database account. You can also assign the user account to a group, which Microsoft calls a "role." There are two types of these, which I'll cover further in another tutorial. Roles allow you to group users together than perform a similar task.

User accounts are called "Principals." The reason they aren't just called "users" is that the accounts might represent a service or other object that needs to access data. In fact, some applications don't have individual user accounts in SQL Server. The application writes a table of its own to implement security, and accesses the database as a single account. SQL Server even has a special facility for this type of operation, called an application role, which essentially logs in and out for every call to the database. I’ve got more on this topic here and here.

Objects — Securables

At the bank, each object, such as an account or a particular check, has security. While an account holder might be allowed to look at one account, they may not have permission to look at another. In fact, an account holder might be able to view the amount in an account but not be able to withdraw money from it. For instance, a lender can view my balance or even transactions, but they are not able to do anything else in the account. My employer sends my paycheck to my account, but they can't take money from it or see my balance. My wife, however, can see the balance, add money to the account, or remove money from it.

The same holds true in the database. Each and every object (called a securable)  has permissions. You have to grant permissions on each table, view and stored procedure in each database, to each principal that you want to have access.

There are two types of permissions, object and statement. To allow principals to access nouns (such as views, tables, or stored procedures), you grant object permissions. To allow a principal to work with a verb (such as the ability to create, alter or delete nouns), grant statement permissions. I'll explain these further in another tutorial.

It may seem that a lot is happening with SQL Server security, but if you keep the bank analogy in mind and take the process step by step, it's not difficult at all. In the security tutorials that follow, I'll show you how to work with each of these areas one at time.  More information on this topic is here.

Another note here — the data itself can have security. Various forms of encryption are available, and I talk about those here.

Programming Security

With everything from the building to the networking, operating system, configuration, users and objects configured, the final layer of security is in the code you write. All the careful planning in the world won’t help if your code allows someone to impersonate a user, or compromise a password.

There are two broad areas to consider. The first is in the database or “server-side” code, such as Transact-SQL (T-SQL) statements, stored procedures, functions, or CLR code running in SQL Server. Important considerations here are to understand “chain of ownership” basics, keeping track of who has which permissions to run each statement on what object, not using impersonation without understanding what you’re allowing, not calling out to the operating system. There’s much more on this important topic here.

The second area is the application code that a user accesses. Don’t embed passwords in code, ever. I can’t tell you how many times I’ve looked at source code and found high-privileged user account information embedded in the code, in plain sight. More on this topic is here.