Logins, Users, and Permissions | Designing Secure Web-Based Applications for Microsoft Windows 2000 with CDROM

[Previous] [Next]

As mentioned previously, we strongly recommend you run SQL Server in Windows integrated security mode. However, several valid security scenarios might lead you to use standard SQL Server security for logins. Therefore, we'll explain standard security exceptions when they come up, but for the most part we'll assume you're using the Windows integrated security mode.

Once you've set the security mode, it's time to examine the security architecture of SQL Server. In SQL Server 2000, a "prelogin" connection is made to SQL Server. This prelogin handshake occurs over UDP port 1434, and the connection returns information to the client, such as which instances of SQL Server are running on the machine and whether the machine is a cluster, as well as information required to establish a Kerberos delegable ticket to the server. It's at this point that the server will also attempt to negotiate Secure Sockets Layer/Transport Layer Security (SSL/TLS) encryption with the client. We'll examine that a bit later on.

NOTE
Prelogin is a feature of SQL Server 2000. SQL Server 7 does not perform this step because it doesn't support multiple instances of the service on a single computer, nor does it support Kerberos delegation.

What happens next depends on what network library the client computer is using. If the client computer uses the Named Pipes or Multi-Protocol network libraries, the Windows Server service will validate the connection attempt via Windows security before it gets to SQL Server. This is true even if you're requesting a standard security login. If the reason you set up mixed security mode was that some users wouldn't otherwise be able to connect to the server, they'll get stopped here, usually with an error message stating that SQL Server is unavailable or does not exist. (However, if you turned on a network monitor, you'd see that they were getting system error 5, which means that access is denied.) The users will not be stopped if you use the TCP/IP network library, in which case Windows does not first validate socket connections because the Windows Sockets (Winsock) interprocess communication mechanism has no notion of secure connections. Rather, it relies on higher-level protocols to provide security services. Figure 6-2 shows the location of SQL Server and Winsock in the network protocol stack.

Figure 6-2. Where SQL Server resides in the network protocol stack.

Now that you've successfully presented some login credentials to SQL Server, we switch to the next phase of SQL Server authentication and authorization. SQL Server essentially has three "layers" of security that a client must negotiate successfully before it can retrieve data from the server. These are the login layer, the database user layer, and the permissions-checking layer.

As you can see from Figure 6-3, the preconnection sequence we examined above takes place. Once the proper handshake is established, your login credentials are presented to SQL Server. If you've chosen to use SQL Server standard security credentials, the login name is compared to the logins available in the sysxlogins system table in the master database. If a matching login name is found, the password you provided is encrypted within SQL Server and then compared to the stored password for that login. If they match, login access is granted.

click to view at full size.

Figure 6-3. SQL Server preconnection and connection sequence.

If you're using Windows integrated mode, your credentials will be passed along to Windows by the SSPI. (SSPI was discussed in Chapter 5, "Internet Information Services Security Overview.") Windows will then return an access token—assuming that the credentials are correct and can connect to the computer—which includes information such as your primary security identifier (SID) and the SIDs of groups of which you are a member. SQL Server will then look for a matching SID in the master database's sysxlogins system table. SQL Server first looks for any entry that has denied you login to SQL Server. It looks through every SID in your access token so that any denial will always be honored. If no denial entry is found, SQL Server tries to find your primary SID. If that's not found, it'll look for any SID in your access token to allow you access to the server. If some matching entry is found, you will be successfully logged in to SQL Server—otherwise, your connection attempt will be denied.

At this point, your connection will switch to a database context. This switch can either be established as part of your login or specifically requested during the connection attempt. Put another way, your connection has moved from being validated by SQL Server to accessing a database. A login does not give you access to any database by default. Well, that's not completely true—SQL Server does include a guest database user account that has limited access rights, and the master and tempdb databases require that the guest user exist. Therefore, if you can't get anywhere else in SQL Server, you'll always be able to get into master and tempdb.

When you attempt to access a particular database, SQL Server will look for a database user ID entry that would allow you to connect to that database. You can gain access to a database in several ways, and we'll examine each of these later in this chapter. Next, you'll probably attempt to perform some action. When you do so, security permissions will be checked based on your database user ID. In general, security rights belong to database users, not to logins. Permissions are assigned separately to database users in each database and are enforced when an access attempt is made. We'll also go into permissions checking in detail a bit later.