In-Depth
Use the Best Security for SQL Server
Learn tips for administering SQL Server securely.
Examine the similarities and differences between Windows security and SQL Server security and you'll find that most of the concepts you already know from managing a network will map quite nicely into SQL Server principles.
If you manage a Windows server currently, then you're familiar with security from the user and group standpoint. Whether you're using the older LAN Manager structure (Windows NT-style domains), the newer Active Directory (AD) constructs, or neither, you have three basic modes of managing security.
A Windows user has a security context, which means that something controls access to objects. In the case of an older networking schema such as LAN Manager, you create users and groups in a space called a domain, and each object must be unique in a given domain. When you create users in a domain, the system generates an underlying number called a security identifier (SID) to identify the account. The SID involves many things, not the least of which is the domain's SID. Users can be part of groups, which also have SIDs. Users get access to objects (such as files, directories, and printers) based on the SID. Their access in a LAN Manager network is based on the most-permissive level, which means that if a user is part of a group that has access to items 1-3 and is also part of another group that has access to items 3-5, the user has access to items 1-5.
The SID is still with us in AD, but AD is a tree-based hierarchical structure that makes the SID unique based on location in the tree. This extends the granularity of the system significantly. It can grant rights and access to a much finer degree than on a LAN Manager setup. AD creates tokens for the users, so the passwords and other data aren't flying around the network, free to be intercepted by the bad guys. In addition, AD can add other authentications in the mix, making for a secure—though more complex—network.
Of course, you might not have a domain at all. In this case, each server maintains its own SID database of users and objects. If users want access to something on that type of network, they either need authentication at the time of the object's access or they must have exactly the same name and password on their own system (called pass-through authentication). This type of network is a nightmare to administer, but it does have its uses.
Finally, there's the hybrid network. The premise here is that your server belongs to either a LAN Manager or AD domain, but it's not a domain controller in that domain. What that means is that the server has access to the domain accounts but can also maintain its own list of users. Say you have a server called BuckStation joined to the domain called BigDomain. If BuckStation has a local account called Jim, its reference in the security context of BuckStation would be BuckStation\Jim. Also assume that Jim has an account on the domain. That account would have the security context of BigDomain\Jim. Each of these accounts will have a different SID. It's also important to note that the two accounts can't "see" each other—BuckStation\Jim can't access objects in the BigDomain domain. That's the case in the absence of the pass-though networking I mentioned above.
An important concept to keep in mind is the way you use groups in networking security. Whether you're using LAN Manager, AD, or local security, it's best to enforce security where possible with groups. Apply permissions and rights to either the share or the individual file, and you do this with a SID. If you apply the rights to a group, and users leave or their account changes for some reason, the rights schema doesn't have to change with them—the group maintains the rights, and you can add another user to the group.
Understand SQL Server Security
SQL Server grew out of the UNIX world, and administrators maintained accounts (at that time) in a fashion similar to the "no domain" scenario in Windows. They created accounts in SQL Server, and Windows was ignorant of these accounts. SQL Server has continued with this concept, largely for maintaining backward compatibility.
In addition to what you're about to learn, keep in mind that you can create two kinds of users. You create and maintain the first type of user inside of SQL Server with its own passwords, groups (which SQL Server calls roles), etc. With this type of account, the user or application will have to provide a name and a password to access SQL Server, which isn't a super-secure solution.
You create the second type of user by prefacing the account creation with a domain and the name of the user or group. In that case, SQL Server will not maintain the password but instead trusts Windows for the security.
In a Windows network, you only need one login to gain access to the server and then you control access to the objects such as files or printers. This is where the differences between SQL Server security and networking security begin to show up.
With SQL Server, you need to grant access to three areas: SQL Server, the database, and the objects in the database such as tables and views.
It might be helpful to think of SQL Server as a large building, with lots of rooms and boxes in the rooms. Using this analogy, the building is the SQL Server installation, the rooms are databases, and the boxes in the rooms are tables, views, and so forth.
To allow people to get to the boxes, you need to give them a key to the front door. SQL Server accomplishes this with SQL Server logins. Everyone who wants access to a database has to have a server login, whether they use a SQL Server account, a Windows account, or a Windows group as an account.
You can use one of two tools to create a SQL Server login. The first is graphical, and the second involves using commands. I'll show you both methods in this article.
The graphical tool is Enterprise Manager, and you can find it on the SQL Server (or you might find it installed elsewhere) on the Start menu under the Microsoft SQL Server item. Once you activate Enterprise Manager, click on each plus symbol until you're at the Security object, and then double click on the Logins object (see Figure 1).
Notice that there are a few items already there, and you will have some, too. To add a new server login, right-click on the Logins object and select the New Login command from the menu that appears.
After you've selected that command, you'll see a panel (see Figure 2). If you're going to create a user that the SQL Server will maintain and control, enter the name you want in the box and change the radio button to SQL Server Security. Next enter a password, and once you click on OK the system will ask you to confirm the password.
If you're going to use a Windows user or group, click on the box with the three dots (ellipsis) and select the user from the domain, AD tree, or local account that you want. Notice that you can also select a group at this point. You won't be asked to verify a password because SQL Server won't store a password for the user.
You could continue on and set the database access for the user as well as place him in various roles. That's the easiest way, but I'm going to bypass that step to demonstrate user creation directly in the database. For now, leave the user here.
After you click OK, the Enterprise Manager displays the new account.
An alternative method to using the graphical tools is to use commands. You can use a tool called Query Analyzer, which you can find in the same place as Enterprise Manager, or you can use a command-line tool called object SQL (OSQL). First learn the commands to type using Query Analyzer (I'll cover OSQL in separate article).
After you open Query Analyzer, it will prompt you to enter your name and password. If you've logged on to the server as an administrator, you probably can select Windows Authentication and leave the server name set at the default server name. When you get in, you're left with a blinking cursor inside the command window. If you didn't log on as an administrator, you'll need to locate someone who can provide the proper credentials for SQL Server.
Type these commands to create the same user that I did with Enterprise Manager—substituting your own domain and username, of course.
EXEC sp_grantlogin 'BUCKSOFT\Marjorie'
GO
Notice that what you're doing is granting a current Windows account access to the server. If you want to create a SQL Server user, you need to add that name anew, and you also need to create a password for the new user. You use a different command for that:
EXEC sp_addlogin Marjorie, mypassword
The graphical method definitely is easier, but creating multiple lines in a text editor and then running them all at once is definitely faster if you're creating multiple users at one time.
Now that you've let Marjorie in the building, you need to give her a key to the office to let her get to the boxes (the tables, views, and so forth) inside.
To allow Marjorie to get into the database, you need to connect her server login to a database user account. It doesn't matter at this point whether the server login is a Windows or SQL Server account; they each need you to grant it access to the database.
Returning to Enterprise Manager, drill down to the Databases object, and then double-click on the name of the database that the user needs to access. Now select the Users icon, and you'll see a new screen (see Figure 3).
Right-click on the Users icon and select New Database User from the menu that appears. That brings up yet another panel (see Figure 4).
You can see that I've pulled down the list of names found on the SQL Server. If you do the same, you'll see that the only names you have access to are found in the SQL Server login area. You're linking these names together so that the SQL Server login has access to this database. SQL Server manages this by creating an account in the database as well. You don't have to make the account names the same, but you'll save yourself a huge amount of stress by keeping everything the same.
This panel displays the various roles available in the database. I'll return to those in a moment, but for now let's create the database account.
You can also create these database accounts with a command. Here's the syntax:
-- Replace with your database name
USE databasename
GO
EXEC sp_grantdbaccess 'BUCKSOFT\Marjorie'
GO
The first parameter ('BUCKSOFT\Marjorie') is the name of the SQL Server login you want to grant access to this database. You can supply a second parameter for the Database user name, but if you leave it, the server will default to the same name as the SQL Server login, which is usually the best way to go.
Now you've given Marjorie a key to the front door of the building and let her into the office. Next you need to grant her access to the boxes, and that's where the security becomes a bit more complex.
Inside every database are various objects, such as tables, views, stored procedures (bits of code), and more. Each of these objects has permissions associated with it.
If you think back to the analogy of the boxes in a room, Marjorie has several rights associated with those boxes. Perhaps you want her to be able to touch the box, open it, put things into it, take things out of it, or throw it away. Each of those rights is a separate item. If you asked Marjorie to put something into the box, you wouldn't be happy if she threw it away. The same holds true for a database object, such as a table. You might wish to grant Marjorie's account the rights to see things in the table, add things to the table, or delete things from the table.
SQL Server handles these permissions using two categories: statement permissions and object permissions. Statement permissions deal with creating or deleting objects, such as a table. Object permissions allow the account to select data, and the ability to add, change, or delete data.
You've probably seen this concept before. If you've ever applied the special permissions on a file using Windows security, then you've seen the checkboxes that allow an account to execute, list, read, or change files. This is the same concept with SQL Server objects.
To work with object security, move from the user-centric view you've been using to an object-centric view. There is a table called Clients in the SecurityTest database on this server. Return to Enterprise Manager and move away from the User object and drill down to the Databases object. Open the SecurityTest database and then double-click on the Tables item (see Figure 5).
Double-clicking on that table brings up a panel with a button marked Permissions. Click on that, which brings up the panel where you set the permissions (see Figure 6).
You can probably guess what SELECT, INSERT, UPDATE, and DELETE permissions allow Marjorie's account to do. However, there are two other permissions that might need some explanation. The EXEC permission is for objects that you can execute. These types of objects are similar to batch files or EXEs on a Windows system. They include stored procedures and other types of SQL Server code.
The DRI item on the menu refers to declarative referential integrity, which means that if an object such as a table has other things pointing to it, you only have to grant the permissions here.
The permissions are in columns, and there are boxes below running along the user's account. Clicking once on a box changes the icon to a checkmark, meaning that the user has that permission on the object. Clicking on the box one more time changes the icon to a red X, which denies the permission to the user.
Clicking on the box again removes the red X, indicating that you have revoked the permission. When you revoke a permission in SQL Server, you remove the ability from the user account, but not from the user's role, or group. This means that the user can access the object if she is part of a role that has the permission granted. If she isn't part of the role, she can't access the object.
In the example, you've granted Marjorie's account the ability to SELECT and INSERT items into this table, but not to UPDATE any of them. You haven't granted her the permissions to DELETE any items, but if she's in a role that can do so, she will be able to delete items from the table.
It's certainly easy to manage permissions with Enterprise Manager, but commands can be useful as well. The three commands you use to accomplish the same thing you just did are GRANT, DENY, and REVOKE. Using Query Analyzer again, you can grant Marjorie's account those two permissions on the Clients table in the SecurityTest database:
USE SecurityTest
GO
GRANT SELECT , INSERT ON Clients TO BUCKSOFT\Marjorie
GO
DENY UPDATE ON Clients TO BUCKSOFT\Marjorie
GO
Notice that you can combine the rights on one line.
There are a couple of interesting parts to SQL Server security that don't map directly to concepts from Windows. For one thing, each and every item has its own security. For example, a database view is a set of SELECT statements that present a tablelike structure to the users. It's useful to show only parts of a table or to combine tables into another window on the tables. A view is a database object, so it has its own security. The problem is that the tables from which the view gets the data (called base tables) also have their own security. That means that you might grant permissions on a view and forget to grant permissions on the tables it references, and then users won't be able to see the data. The same holds true for stored procedures.
SQL Server solves this dilemma by passing through permissions on the tables from the view—with one caveat. The same account that creates the view must also own the tables it references. This is not a problem normally, but if the users have the statement permissions mentioned above, they can create tables and views. If they create a table, and then someone else creates the view that references the table, that's where the problem comes in.
Add Parallel Groups
SQL Server also provides a parallel to Windows groups, called roles. There are two types of roles, one for the server and one for the database.
Server roles are groups that allow you to control the management of SQL Server. They allow you to grant others the rights to add user accounts, create or back up databases, and so forth (see Table 1).
It's simple to add an account to a server role. You can use Enterprise Manager in the Security object to double-click on a role and pull down Logins to add to it. You can also use commands to do the same thing. Add Marjorie's account to the dbcreator role:
EXEC sp_addsrvrolemember 'BUCKSOFT\Marjorie', 'dbcreator'
Server roles are static, so you can't create new ones. It's also important to remember that these roles are serverwide, and you don't use them to manage permissions in a database for data access. For that, you turn to the database roles.
Database roles control access to data and programs in a SQL Server database. SQL Server provides several built-in roles for you as a default (see Table 2). It's easy to add database user accounts to database roles. Using Enterprise Manager, drill down to the database, open the Roles object, double-click on the appropriate role, and select the accounts you want.
The command for adding accounts to the database roles is similar to the one for adding a login to a server role, so add Marjorie to the db_datareader role:
EXEC sp_addrolemember 'db_datareader', 'BUCKSOFT\Marjorie'
Unlike server roles, you can create more database roles so that you can be more granular in your database security. Creating the role doesn't allow it to do anything; it's exactly like creating a group in Windows. After you create the role, you assign users to it and then assign permissions to the role. Here's how you create a database role called NewRole:
EXEC sp_addrole 'NewRole'
Use Best Practices
Now that you have the basics of security in hand, I'll discuss a few best practices. Keep in mind that each situation is often unique, so take what you've already learned and apply it to the concepts below. You'll find that working with SQL Server security using these simple guidelines will make your administration simple.
If you have to apply the security manually for a SQL Server-based application to one user, Enterprise Manager is often the quickest route. It provides the graphical feedback with which most people are familiar, which prevents a good number of mistakes. When you create the SQL Server login, you can move to the Database tab and add the account to the database at the same time.
If you're adding multiple accounts, however, it's often easier to use a spreadsheet and Transact-SQL (T-SQL) commands. Use the concatenate feature in the spreadsheet program to create the commands. Keep this script handy in case you need to rebuild the database.
Another best practice is to use roles (like you use groups in Windows security) so that when a user leaves or changes jobs in the company, you don't have to change the entire security structure of your database.
Use Windows accounts or groups whenever possible. SQL Server accounts are difficult to maintain and track, and SQL Server is a frequent target of hacker attacks.
Here's one final piece of advice: Set up a test installation of SQL Server if you don't have one. Practice the concepts you've learned here, and see how the layering works.