Beginning SQL Server 2000 Administration Part 3

This is the third part in the Beginners series and will start the look at security. This is a basic introduction to people in how they connect to SQL Server and what issues they might have. It's a lot of common items that DBAs and developers take for granted, but which administrators might not think of or understand right off. In case you missed them:

• Part 1 - Getting Started - Dealing with the basics of what SQL Server is and how it works from a high level
• Part 2 - Limited Rows - the basics of querying the server and returning a limited number of rows.

SQL Server Security - An Overview

There are basically two methods of security in SQL Server for logging into the server: windows authentication or SQL authentication. Windows authentication is always available, but you can enabled SQL authentication (mixed mode) or not enable it (native mode and the default). Before we dive into what this is....

Think of your SQL Server as an office building that houses many companies. You need a "key" to get into the building, but you also need a key to get into your specific office. Let's think of a building that houses SQLServerCentral.com and SQL Server Standard. Steve works at SQLServerCentral, Brian at SQL Server Standard. Each has a copy of Key A, which opens the front door of the building into the lobby. The office of SQLServerCentral.com, however is keyed to accept Key B only. Steve has a copy of Key B, hence he can open this door and access the filing cabinets, computers, etc. Brian has a copy of Key C, which opens the door to SQL Server Standard, but not SQLServerCentral.com.

You may be wondering what this has to do with SQL Server security? Well, SQL Server security operates mostly in the same way, with a 2 step lock process. There are 2 levels of security just like the two doors in the example above. The outer door to the building is the equivalent of Login security. When you get a login in SQL Server, this allows you access to the outer shell, but not necessarily any particular office.

Each office is like a database in SQL Server, and each has it's own security setup. The "key" to the individual office is the "user" in a SQL Server database, and each user is "linked" to your login, giving you access to the server. To see this in practice, let's connect to a server using Query Analyzer. Below I've connected to my local server using Windows Authentication (which will be explained shortly).

Figure 1

Notice the top title bar of Query Analyzer. IT shows the following:

      DKRANCH11.master.DKRANCH\Administrator

in the title bar. This represents the server, DKRANCH11, the database, master, and the user, in this case, the local Administrator account. I've connected to the server as the same login and user, DKRANCH11\Administrator. This is the most common way of working with SQL Server, though it is possible to have different names for the user and the login.

Extending the analogy one step further, assume that all data in the office is locked inside filing cabinets. You need still further keys to access the data. This is the same in SQL Server in that each "object", whether that be table, view, stored procedure, etc., needs access granted to the user. Even if you are added as a user to a particular database, that doesn't guarantee that you will actually be able to view any data. But that's another article. Let's move on to explaining the differences in the two types of logging into SQL Server.

Windows Authentication

I'll start with Windows Authentication because that's always on and it's the default for many types of connections. Windows Authentication uses your network credentials to connect to SQL Server as a login. The assumption is that you are logged onto a Windows network of some sort, so if you use an ODBC connection from MACOS, Unix, etc. this will behave differently.

When you log onto your Windows workstation your password is authenticated by the local OS or Active Directory. If you now want to access some resource that's secured, say a share on a network server, the credentials that you logged in with are compared to the access control list (ACL) on the resource and access is granted or denied. The same thing occurs in SQL Server. Figure 2 shows another of the folders in Enterprise Manager, the Security folder, expanded and the logins item selected.

Notice there are 4 logins listed: BUILTIN\Administrators, dotnet, DKRANCH11\sjones, and sa. Of these, 2 are Windows Authenticated logins and 2 are SQL Server authenticated, as seen in the "type" column. Of the Windows Authenticated logins, one is a Windows Group and one is a user. As with any other Windows resource, you can grant access based on a group or user. You can also deny access, but that's not that common. The verification of access is determined by the appropriate Windows authority.

This means that anyone in the local Administrators group (including any domain accounts that have been added) or the login "sjones", can log into SQL Server. This does not grant them access to any data or any database, but they can connect to the server. This also means if your network credentials cannot be validated by a domain controller or local security authority, you cannot log on.

SQL Authentication

The other type of authentication is SQL authentication. In this case, the login name and password are stored inside SQL Server. In the master database, the syslogins table to be exact. The password is encrypted with a one way hash, so it supposedly cannot be decoded, though that claim has been shown to be false. In either case, when you attempt to connect using SQL Server authentication, the login name is checked for a matching one in the syslogins table. If a match is found, then the password is checked against the stored value by applying the same hash mechanism. If this also matches, you are granted access to the server.

Note that I mentioned you connect with SQL Server authentication. All clients "choose" the type of connection that they want to use. When you start a connection with Query Analyzer, it allows you to choose the type of authentication to be employed (Figure 3).

In Enterprise Manager, each registered connection has similar properties (Figure 4). If you connect in an ASP page using OLEDB, you specify the connection as "Trusted_Connection=yes". If you connect with the isql or osql command utilities, you'd specify the "/E" switch to indicate a trusted connection.

If you don't specify to use a trusted connection, the client doesn't choose this if the SQL authentication fails.

Now we'll look at adding a user. If you right click the "Logins" items, you'll get the following dialog. Select the "New Login" item to add a new login. We'll also add a user at the same time, which is handy.

Once you select this, you'll get the dialog in Figure 6. There are a few parts of this dialog that are important, so we'll look at each. The first item is the login name. In this top box, you can just type a name that you will use for the login. In this first example, I'll add a SQL Server login.

Notice that the "SQL Authentication" radio button is selected, which enables the password box. Since these passwords are stored in SQL Server, you have to enter the password when you create a login. Just as you enter a password when you create a Windows login. I've entered "testuser" and the cursor is set at the password box. In Figure 7, however, I've entered a ".\" in the name box. This is the notation for a Windows account, given as "domain\user". I've also selected the ellipsis box , which gives me a list of accounts in this domain, both groups and users. These are from my local machine, so there is no domain name needed. If I'd been on the SSC domain, then I could have chosen "ssc\" and been given a list of domain accounts.

You can't see it in Figure 7, but since I selected a Windows Authenticated account, the password box is greyed out and nothing can be entered there. This makes sense because the password is stored in the Windows security system.

A few other things about adding a login, just so you can do it and ask the right questions if need be. The next thing after selecting the account or entering a name and password is the default database. This is the database that the login is automatically connected to when access is granted. Note that this doesn't create the user account or enable it, it's just a default. Also the client can override this on any connection they make.

Selecting the drop down shows a list of databases on the server. This is one of the questions you should ask if someone asks you to create a login.

That's pretty much it on the first tab, now let's look at the Server Roles tab (you did notice there was more than one tab, didn't you?). This tab, shown in Figure 9, shows a list of the server roles for the login. These are like Windows "groups". In general, unless you are adding a fellow administrator, you don't want to check anything here. But it is a question that you can ask.

The last tab is the Databases tab. Shown in Figure 10, when you select this, none of the databases are selected. In this case, I've selected the "Northwind" database. This is because I want to grant access to this database by creating a user in this database.

When I select this, the default is to put the same username in the "User" column. You can override this, but in general you do not want to. However, another question to ask.

Note that there are two places for data here. At the bottom there is the "database role" section, where you can assign database roles, again like groups, to the user. Still one more question to ask, but this will make more sense in a future article.

That's it, hit "OK" and you have a new SQL Server login and user. One last caveat. You do not have to create a user in the database you selected as the default on the first tab, but Enterprise Manager will warn you if you do. Also, you can select multiple databases on the database tab and create users in more than one database at a time. This is also where you would add additional database access to an existing user.

Conclusions

The basics of logging onto a SQL Server and how this works. There's a lot more to learn about security in SQL Server, but this is the basics of getting connected to the server and a database. In a future article I'll look at how security on different objects works from a beginning perspective.

If there are more topics you're interested in, or you have questions, please post a question in our forums or in the comment section below.

Questions To Ask

Ask these when someone asks you for access and they'll think you know a little something about SQL Server security.

• Is this SQL authentication or Windows Authentication?
• If SQL, what is the password?
• What is the default database?
• Are any server roles needed? (If yes, ask why)
• Which databases does the user need access to?
• Same user name as login? (ask for each database)
• What roles are needed in each database?

References

A few references to learn more about this topic:

• SQL Server Security Part 1
• SQL Server Security Part 2
• SQL Server Security Model
• How To Make A Trusted SQL Server DSNLESS Connection

Steve Jones

©dkRanch.net August 2004

Return to Steve Jones Home