 TechNet Magazine Spring 2005

TechNet Magazine Spring 2005

Windows Administration: Breathe New Life Into Your Server! From Windows NT To Windows Server 2003

If you're moving up from Windows NT to Windows Server 2003, you'll be pleasantly surprised. But before making the move, find out what you need to consider first and get the best migration strategies for your needs.

Jonathan Hassell Windows Administration: Secure Your Desktops With The New Group Policy Settings In SP2

Group Policy lets you control who can access resources across your network and what types of actions users can perform based on their roles. New settings in Windows XP SP2 offer even tighter control so you can regulate more and worry less.

Jeremy Moskowitz Windows Administration: Redundancy On A Budget: Implementing A RAID File Server

Did you know that you can implement a fault-tolerant departmental file server on a low-end desktop PC? All you need is RAID, Windows Server 2003, a PC, and an extra hard drive, and you're good to go.

Robert Walker Windows Administration: Preserve Your Data With The Backup Tool You Already Have

If you're running Windows XP, Windows 2000 Server, or Windows Server 2003, you already have Ntbackup. It's time to dispel some of the myths about how it works so you can get good, efficient backups without blowing your software budget.

Jay Shaw Windows Administration: 10 Easy Ways To Lock Down Your Computer

Here are 10 important Group Policy settings you can start using for more secure user passwords, authentication, user privileges, and persistence of GPO settings.

Derek Melber SQL Server: Reduce Downtime: Implement SQL Server 2000 On A Cluster

Running SQL Server 2000 on a Windows Server 2003 Server failover cluster will get you more uptime by alleviating your reliance on a single server.

Stephen Strong SQL Server: XML To The Max: Get More Power Out Of Your SQL Server

Using the SQL Server 2000 T-SQL syntax, you can manipulate XML in a variety of ways. For example, you can access data contained in an XML string passed into a stored procedure and produce XML output. Find out what these flexible, powerful XML features can do for you.

Ward Pond SQL Server: Get A Traffic Report: Analyze Your Database Usage With System Tables

By querying data from the master.sysprocesses table you can get almost any information you need about connections to your SQL database, what each process is doing, which processes are blocking, and lots more. Then you can use this data to create some truly enlightening reports.

Matt Evans and Josh Moody SQL Server: Async Lifestyle: Manage Your Tasks With Service Broker

SQL Server 2005 Service Broker is more than just a developer tool. DBAs can use it to schedule tasks, run batch processing, and perform routine database maintenance.

Roger Wolter SQL Server: On The Horizon: Improved Data Security In SQL Server 2005

Microsoft has performed extensive threat modeling and security analysis to identify the threats facing database servers. This work has resulted in an upcoming version of SQL Server, SQL Server 2005, that is secure by design. This article enumerates the various security improvements to the popular database.

Don Kiely Exchange for Experts: Be The Master Of Your Domain Rename With Exchange

Domain renaming is not only possible, it's actually easier than you think, especially with this handy guide to the process and the right tool—Rendom.exe, which can be found on your Windows Server 2003 CD.

Steve Schiemann Exchange for Experts: Migration, Active Directory, And You: A Guide for Exchange Admins

You can use the Active Directory Migration tool to migrate users, computers, and groups from one domain to another. Plus, the new version of the tool lets you migrate user passwords as well as accounts, which makes the Exchange Server migration process a whole lot easier.

Nino Bilic From the Editor:

A few months ago, we launched the first issue of TechNet Magazine, the Microsoft magazine for IT pros. At the time, we were cautiously optimistic about what we were doing, but we were also anxious to see how the people who make everything run—the IT superheroes—would react to the new publication that ended up on their desks.

Letters:

It's about time that a tech magazine showed the "how to" and "why" for Windows administrators. I'd like to see issues that cover Application Center in detail in the future. Also, please show ways to integrate Windows Active Directory and Novell eDirectory.

Toolbox: New Products For IT Professionals

www.gpsoft.com.au

Marnie Hutcheson Utility Spotlight: Limit Login Attempts With LimitLogin

Ever needed to limit concurrent user logins in an Active Directory® domain? Ever wanted to keep track of information about every login in a domain? If so, LimitLogin is for you.

SQL Q&A: Troubleshooting Connections, Using Variables, And More

Your queries answered by Microsoft IT professionals.

Edited by Nancy Michell How IT Works: Decoding TCP/IP

TCP/IP is comprised of a set of protocols including Transmission Control Protocol (TCP), User Datagram Protocol (UDP), Internet Protocol (IP), and Internet Control Message Protocol (ICMP). Most IT professionals understand how these protocols work together to make network communication possible.

Don Parker Beta Box: Discover SQL Server 2005 Express Edition

One of the most promising updates to a Microsoft product this year is SQL Server™ 2005 (formerly known by its code-name "Yukon"). SQL Server 2005 will come in a variety of versions, supporting many different types of deployments from the desktop to the enterprise datacenter.

Brian Randell Post Mortem: Migrating 240,000 Mailboxes And 29 Domains

I recently participated in a project with a large agency of the United States federal government as they migrated and consolidated their servers from Exchange Server 5. 5 to Exchange 2000 Server and Exchange Server 2003.

Dennis Swenson Blog Tales: A Guide To Public Folder Tools

Are you an Exchange Server op who's confused by different public folder tools? With all the various public folder tools out there, it can be difficult to remember what each tool can be used for. Let's straighten it all out now.

Bill Long How IT Works: A Primer On SQL Joins

One of the trickiest things about learning SQL is mastering how various JOIN statements differ in the ways they combine data from multiple data tables. There are three types of joins: inner, outer, and cross.

Joshua Trupin Serving the Web: Get It Right The First Time With IIS 6.0

Server administrators are always on a quest for the secret formula that will solve their performance woes. But no matter what you may have heard, there is no magic pixie dust. Yet the promises keep rolling in, even though few results are delivered.

Chris Adams InterOp: Windows Services For UNIX

If you're an administrator working in a mixed environment, Microsoft® Windows® Services for UNIX (SFU) 3. 5 can make your life a lot easier. SFU provides a full-featured interoperability solution that includes a Network File Server (NFS) client, server and gateway for file system interoperability, and a Network Information Service (NIS).

Charlie Russel Security Watch: Isolate Servers With IPsec

We live in an age of always-connected communication and are able to send e-mail messages or gain access to files, databases, and Web pages at any time, through a proliferation of networking technologies.

Joseph Davies Resources: IIS 6.0 Performance Tune-Up

By now you've probably gotten to know IIS 6. 0, available in all versions of Windows Server™ 2003, and are taking advantage of much that this Web server offers. It's important to keep an eye on the overall goal of IIS 6.

Phil Sherwood TechNet Live Events Roundup: Vacation 1.0

I know the last thing you want to do over the summer is to sit in a classroom for eight hours a day, listening to an instructor drone on, especially when you'd rather be waiting in line for Star Wars: Episode III.

Kai Axford TechNet Update: Highlights From The Microsoft IT Community

The volume of desktop deployment information on the TechNet site can seem overwhelming, and at times it might be difficult to find exactly the information you need. Here we will point you to some of the most popular sites on TechNet, which should help in your search.

Field Notes: IT and Life Experiences

IT pros don't write code. IT pros don't develop applications, and we certainly don't want anything to do with mathematical algorithms and binary search trees. We leave that to the dev geeks with CompSci PhDs and cases of Mountain Dew under their desk.

Joshua Hoffman


 Windows Administration: Breathe New Life Into Your Server! From Windows NT To Windows Server 2003
Windows Administration Jonathan Hassell

Breathe New Life Into Your Server! From Windows NT To Windows Server 2003
At a Glance:
  • Developing a Windows NT to Windows Server 2003 migration plan
  • How Active Directory compares with domains in Windows NT
  • Averting problems with the migration
Windows NT
Windows Server 2003
Active Directory

A lot of companies that currently use Windows NT are now considering an upgrade to the latest server product from Microsoft, Windows Server 2003.
After all, Windows NT 4.0 support expired in December 2004 (with the exception of extended support contracts), making this an excellent time to consider upgrading (for more info, see Retiring Windows NT Server 4.0: Changes in Product Availability and Support).

Things to Consider Before Migrating
If you currently have a Windows NT® domain and haven't yet investigated Active Directory® (the directory service that Microsoft first introduced in Windows® 2000 Server) then there's a lot in store for you. Active Directory is superior to Windows NT-style domains in a number of ways, not the least of which is easier management. You can divide your directory into specific domains and organizational units and manage similar sets of objects with ease. Active Directory is more robust and fits better into more distributed environments, particularly in organizations with branch offices in multiple locations. It is also more secure for your users and is the foundation of many newer versions of Microsoft server products, including Microsoft® Exchange Server 2003.
There are several different steps in moving from Windows NT to Windows Server 2003™ and Active Directory. First, you'll want to analyze your current Windows NT domain environment. Are you on a single domain or multiple domain model, with accounts and computers located in each domain, or do you have a single master or multi-master domain model, with separate domains each for user accounts and machine resources?
The single domain model is the easiest to upgrade, since the existing domain simply becomes the root of the Active Directory forest. However, if you have a particularly large domain or a network that might be restructured one day, then you may want to consider a dedicated forest root model (sometimes called an empty root), in which you create a root domain within a forest and then create child domains from that root. This allows you to change domains in the forest without scrapping your entire Active Directory structure. If you have a single master domain and child domains containing machines, you really don't need to continue that structure upon moving to Active Directory, since you can create organizational units to store specific types of objects within the directory. Multiple masters will want to use the dedicated forest root strategy, since complex networks should still be broken up by domains, even in Active Directory, for easier management.
What sort of trust relationships have you built up with other domains in your environment? Trust relationships can make moving to Active Directory somewhat more complicated, but they don't have to be difficult. If you have trust relationships among a multi-master domain model, such that every domain trusts every other domain, you need do nothing, as all trusts are created automatically.
If you have one-way trusts that you want to preserve for logistical reasons, then you'll need to create multiple forests, which can be a headache. You should make sure that's the route you'd like to take before embarking on it. Figure 1 shows some sample trust relationships in Active Directory and how they fit together.
Figure 1 Trust Relationships within Active Directory
Figure 1 Trust Relationships within Active Directory
How many Primary Domain Controllers (PDCs) and Backup Domain Controllers (BDCs) do you have, and where are they located? All at one location or at separate sites? In Active Directory, there are really no distinctions between PDCs and BDCs (with a couple of minor exceptions). In addition, Windows Server 2003 is more robust than Windows NT 4.0, so you can likely consolidate multiple domain controllers at a single location into a smaller number, depending on their load. Your main concern with domain controllers is their location.
Part of the Active Directory technology is a replication algorithm that sends updated contents of the directory to all domain controllers within the forest, even at different sites. If you have offices in different locations with slow links, you can define these within Active Directory, allowing it to control your replication speed and how quickly those users at the remote offices can get authenticated and receive access to domain or forest resources. You'll want to look at how these locations will play into where you allocate domain controllers. Figure 2 shows how sites and replication works within a sample company's Active Directory structure.
Figure 2 Active Directory Structure
Figure 2 Active Directory Structure
If you have DNS deployed internally, what namespaces are you using and how are they assigned? You'll want to catalog all of these internal domain namespaces and decide how they will "map" into your new Active Directory structure. Of particular importance are how you may want DNS subdomains (for example, corp.acme.com) to map to actual Active Directory domains in a forest and if you'd like to have external DNS services separated from internal DNS services. Of course, DNS is a major component of Active Directory and entire books are written about planning and using DNS in Active Directory environments, so be sure to read up on best practices, or bring in someone experienced in DNS planning to assist you in your migration efforts.
Do you have any Windows NT 4.0 servers that are running the Routing and Remote Access Service (RRAS) or the LAN Manager Replication Service? Computers running Windows NT RAS, be they domain controllers or just ordinary member servers, really don't integrate well within an Active Directory environment. If you have a member server functioning as a RAS machine, you should upgrade it to Windows Server 2003 before the last domain controller is upgraded. The RAS machine has certain security requirements that are incompatible with the different operating system versions. This also means that if you only have one DC in your domain, you need to upgrade your RAS server before beginning any DC upgrades. Also, the LAN Manager Replication Service is incompatible with the new File Replication Service found in Active Directory, so disable that as well. However, you should note that disabling the service means that there will be no further replication to LAN Manager servers. This is fine if the files being replicated are static and will not change during migration, but clearly it's not fine if those files are changing. Therefore, all files replicated by the LAN Manager Replication Service must be static and not subject to change once the service has been disabled.
Do you have any servers that are running versions of Windows NT earlier than 4.0? If you do, unfortunately, you need to bite the bullet and simply rid yourself of these servers, as they're not compatible with either Windows Server 2003 or an Active Directory environment.

Migration Strategies
By its nature, any migration process is risky, since your environment is changing. In this section, I'll take a look at some prudent strategies to mitigate that risk and ensure that the entire move from Windows NT domains to Windows Server 2003 and Active Directory goes smoothly.
First, you'll want to make sure that for all Windows NT domains you're touching with the migration, you have both the BDC and PDC up to date. If the PDC for some reason fails to upgrade, then the BDC can be promoted to PDC and nothing is lost but some time. If you have two BDCs, then the best strategy is to leave one online during the migration, so users more or less don't notice anything is going on, and take the other offline during the upgrade. This way, the offline BDC isn't touched by anything happening during the upgrade and can be plugged in should everything go haywire. Figure 3 shows this procedure.
Figure 3 Taking a BDC Offline
Figure 3 Taking a BDC Offline
Make sure to synchronize your BDCs with their partner PDCs before proceeding. Out-of-date replication partners don't help anything when it comes to restoring service in the event of an outage. In the course of the migration, be sure to keep track of any changes you make after you take your BDCs offline. If your migration fails and you promote your BDC to a PDC, you will lose any changes you made since you took the BDC offline, and you'll need to manually recreate any changes you made in that period.
Take some time to look specifically at the PDC for each domain and decide whether it's sufficiently powerful. When I mentioned earlier that there are virtually no distinctions between domain controllers anymore, there are a couple of exceptions. The first domain controller upgraded into Active Directory will take on some roles that others don't have and will require a bit more operational horsepower.
If you're in doubt as to whether your PDC is powerful enough, a common suggestion is to buy a new machine and load it with Windows NT 4.0 and Service Pack 6 and configure it as a BDC. Then, promote it to a PDC and put it on the network to let the changes settle out and replication finish (usually about 48 hours). Then, take it offline and upgrade the computer to Windows Server 2003. This is the closest strategy to a clean install and usually gives you the best results. If you have more than one domain, do this for each. Note that if you decide to use the dedicated forest root strategy, you'll need to have a native Windows Server 2003 machine with Active Directory and create the forest and root domain first before upgrading any PDCs.

Performing the Move
It's remarkably easy to upgrade any type of Windows NT installation to Windows Server 2003, whether it be a PDC, BDC, or a regular member server. Microsoft has taken great pains to ensure that the upgrade to Windows Server 2003 is as painless as possible. The installation procedure follows a normal clean install of Windows Server 2003 reasonably closely and, in fact, requires less hands-on work. The program doesn't prompt you at all after the installation begins and there is little to no reconfiguration required with an upgrade installation. Existing users, settings, groups, rights, and permissions are saved and automatically applied during the upgrade process. You also don't need to remove files or reinstall applications with an operating system version upgrade. At the beginning, you're only asked for the CD Key and to acknowledge any compatibility issues, and then some time later the upgrade is complete. There are, however, a few points to note.
Service pack levels The Windows NT installation must be running SP5 or greater. The most recent update, SP6a, can be downloaded from Windows NT 4.0 Service Pack 6a. Other acceptable Windows NT versions include Terminal Server Edition with SP5 or later, and Server Enterprise Edition, also with SP5 or later.
Evaluating immediate setup issues On a computer that's a candidate for Windows Server 2003, insert the Windows Server 2003 CD and run winnt32.exe with the checkupgradeonly switch. This will present a report to you with issues that the setup program detects may cause problems with an upgrade to Windows Server 2003.
There are also some storage issues which you should examine before upgrading.
Partition sizes On computers upgrading from Windows NT to Windows Server 2003, ensure there is plenty of disk space on the system partition of each machine. This is especially true of domain controllers, since converting a Security Accounts Manager (SAM) database to an Active Directory database full of the latter's capabilities can increase the size of the SAM by as much as 10 times.
File systems Domain controllers require their system partitions to be formatted with the NTFS file system. While as a general procedure I recommend formatting all partitions on all servers with NTFS, you are not required to do so unless the machine in question is a domain controller.
Volume, mirror, and stripe sets Upgrading to Windows Server 2003 Enterprise Edition from Windows NT on a system with volume, mirror, or stripe sets (including stripe sets with parity) which were created under Windows NT requires some modifications to those sets. Since Windows Server 2003 includes new dynamic disk technologies, support for older enhanced disk features has been removed. This is indeed a change from Windows 2000. You will need to break any mirror sets or, for all other media sets, back up any data on the set, and then delete the set. When setup is complete, you can then replicate your existing disk configuration using native Windows Server 2003 tools and restore any data required from the backups.

Moving Domains to Active Directory
The upgrade procedure for a Windows NT domain is relatively straightforward. You must initially choose the first server to upgrade in your Windows NT domain. As you upgrade different computers, depending on their existing role in the domain, features and capabilities become available with Windows Server 2003 on the upgraded machine. In particular, upgrading a Windows NT PDC enables all the included Active Directory features, as well as the other capabilities inherent in any server running Windows Server 2003, such as improved Routing and Remote Access service features, no matter the role. Note that you can upgrade Windows NT member servers at any time during your migration plan, and most migration plans specify that member servers are last on the list to receive the upgrade. However, no matter your order, when you begin upgrading Windows NT domain controllers to Windows Server 2003, you must upgrade the PDC before any other DC machines.
Here's a checklist of some steps to take immediately prior to your move to ensure that your Windows NT to Windows Server 2003 migration goes smoothly:
  • Make sure that all PDCs and BDCs are running Windows NT 4.0 with at least SP5, or better, SP6a.
  • Clean up your domain account list for both users and computers. We all know these lists can be cluttered with inactive users, multiple accounts for the same user, and other unnecessary entries. Take this opportunity to eliminate excess baggage from your directories before moving these objects into Active Directory.
  • Remove any unused software using its uninstallation facility, and defragment the hard disk to take advantage of any unused space. Active Directory migrations can use a lot of disk space, and contiguous free areas of the disk can increase the speed of Active Directory query response time.
  • Kill any trusts between domains that you don't want preserved over the migration process.
Additional Resources
DNS on Windows Server 2003 by Cricket Liu, Matt Larson, and Robbie Allen (O'Reilly, 2003), a comprehensive guide to DNS configuration and administration.
Active Directory, 2nd Edition, by Robbie Allen and Alistair G. Lowe-Norris (O'Reilly, 2003), highlights specific DNS requirements and how they pertain to operating in an Active Directory-based environment.
Mastering Windows Server 2003 by Mark Minasi, Christa Anderson, Michele Beveridge, C.A. Callahan, Lisa Justice (Sybex, 2003).
Microsoft Windows Server 2003 Inside Out by William R. Stanek (Microsoft Press, 2004).

Domain controllers in Windows Server 2003 digitally sign network communications and verify the authenticity of parties to a transaction by default. This helps to prevent communications between machines from being hijacked or otherwise interrupted. Certain older operating systems are not capable of meeting these security requirements, at least by default, and as a result are unable to interact with Windows Server 2003 domain controllers. Such operating systems are Windows for Workgroups, Windows 95 machines without the Directory Services client pack, and Windows NT 4.0 machines prior to Service Pack 4. You'll also find that Windows Server 2003 DCs require all clients to digitally sign their server message block (SMB) communications by default. The SMB protocol allows Windows systems to share files and printers, and enables various remote administration functions, as well as login authentication over a network. If your clients are running one of the operating systems mentioned previously, and upgrading them to a later revision is not an option, then you'll need to turn off the digital signing and SMB signing requirements by disabling the "Digitally sign communications" policy in the Default Domain Controller group policy object (GPO) that applies to the Organizational Unit where the domain controllers are. Alternatively, rather than disabling this feature, it can be reconfigured to be preferred but not required. This supports backward compatible clients while preserving security on supported clients.
Additionally, Windows Server 2003 domain controllers similarly require that all secure channel communications be either signed or encrypted. Secure channels are encrypted tunnels of communication through which Windows-based machines interact with other domain members and controllers, as well as among domain controllers that have a trust relationship. Computers running Windows NT 4.0 prior to SP4 are not capable of signing or encrypting secure channel communications. If Windows NT 4.0 machines at a revision earlier than SP4 must participate in a domain, or a domain must trust other domains that contain pre-SP4 DC machines, then the secure channel signing requirement needs to be removed. This is also in the domain controllers' security policy, under the GPO entitled "Digitally encrypt or sign secure channel data."
By using the strategies found in this article and a bit of common sense, you can relax with the knowledge that you likely won't need backups restored or disaster recovery plans activated.

Jonathan Hassell is an author and consultant specializing in Windows administration and security. He is the author of Learning Windows Server 2003 and RADIUS (O'Reilly), and Hardening Windows (Apress). Reach Jonathan at jhassell@gmail.com.
© 2007 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.


 Windows Administration: Secure Your Desktops With The New Group Policy Settings In SP2
New information has been added to this article since publication.
Refer to the Editor's Update below.

Windows Administration Jeremy Moskowitz

Secure Your Desktops With The New Group Policy Settings In SP2
At a Glance:
  • Windows XP SP2 firewall
  • Securing access to the Internet
  • Internet Explorer policy settings
  • Setting file-level risk settings
Group Policy Admini- stration
Active Directory
Security
Windows XP SP2

The Group Policy mechanism built into Windows has always been the most effective and efficient way to immediately gain more control over your user, client, and server population. Once you deploy Windows XP Service Pack 2 (SP2), your control will get better. Let's examine some of the goodies that you'll be able to explore once the latest service pack is installed on your Windows® XP clients.
There are over six hundred new policy settings available for machines loaded with Windows XP SP2. Space prevents me from examining each one individually, but I will describe some of the categories of new features as well as some of the most useful policy settings so that you can get to work and put them to use right away.
[ Editor's Update - 5/16/2005: The Group Policy snap-in for the Microsoft Management Console allows you to edit Group Policy Objects. To access this snap-in in Windows XP, go to Start | Run, and enter gpedit.msc. You can also find gpedit.msc in the %windir%\system32 directory.]
Controlling the Windows XP SP2 Firewall
Perhaps the biggest news for Windows XP SP2 is the built-in Windows Firewall. For the record, there was always a firewall built into Windows XP, but with Windows XP SP2, the firewall is turned on by default and is much more controllable via Group Policy. Before the release of Windows XP SP2, the firewall was turned off by default. The policies used to control the Windows Firewall can be found in two locations: Administrative Templates | Network | Network Connections | Windows Firewall | Domain Profile, and Administrative Templates | Network | Network Connections | Windows Firewall | Standard Profile. Inside each node, you'll find a number of new additions that will allow you to achieve fine-grained control. Take a look at Figure 1 to see all the new controls located within the Domain Profile node.
Figure 1 Windows XP SP2 Firewall Settings
Figure 1 Windows XP SP2 Firewall Settings
But what is the difference between the Domain Profile node and the Standard Profile node? The Domain Profile settings take effect when users are inside your home network, that is, when they're actively logged in by a Domain Controller. The Standard Profile is useful for when users are out of the office, perhaps in a hotel or on another public network where they cannot reach your company's Domain Controllers for authentication. In these situations, you might choose to handle firewall settings differently. For instance, your corporate policy might dictate that certain ports need to be opened on each desktop for a specific application or for administrative management, but that users should have an even tighter level of security when they are on the road.
Once a Windows XP SP2 computer receives the policy settings for both the Domain Profile and Standard Profile, that computer is ready to travel both in and out of the office. You can be sure that machine is employing your company's firewall security policy both in the office and on the road.
If you're interested in getting some more information about how a computer determines if it is supposed to use "Domain Profile" or "Standard Profile" policy settings, be sure to read "Determination Behavior for Network-Related Group Policy Settings" on the Microsoft® TechNet Web site.

Securing Computer Access to the Internet
There are two areas containing Group Policy settings for securing Internet access, that will be of particular interest when you want even tighter control on outbound Internet communications. For instance, administrators in academic environments might want to restrict a specific set of computers from connecting to the Internet. Or, a corporate administrator might want to increase protection when it comes to their users downloading (and potentially executing) specific file types.
To locate these areas, first go to Administrative Templates | System | Internet Communication Management where you'll locate the Restrict Internet Communication policy setting. This setting can be used to disable Internet communications for specified machines. Additionally, if you go to Administrative Templates | System | Internet Communication Management and select Internet Communication settings, as seen in Figure 2, you'll find some additional lockdown options when Internet communication is involved. Most of the policy settings in this section are self-explanatory, but they are valuable additions for protecting both corporate and academic networks from adding unnecessary software or potentially misusing the computing resources.
Figure 2 Internet Communication Settings
Figure 2 Internet Communication Settings
Next, go to User Configuration | Administrative Templates | Windows Components | Attachment Manager. You'll find multiple settings on how to process various file types when users attempt to open those files, as shown in Figure 3. As the name of the node suggests, the process that's being managed under the hood is called the "Attachment Manager." The Attachment Manager has some preassigned degrees of risk associated with file types. For instance, .bat, .vbs, and .reg would all be considered "High Risk." Files considered "Low Risk" are those with the .log and .txt extensions. To specify how Windows XP SP2 should handle file types of varying risk, you can use the policy setting named Default Risk Level for file attachments. You can also modify which file types should be considered high, moderate, and low risk using policy settings contained within the same node. If your anti-virus tool can register itself with Windows XP SP2, you can likely use the new "Notify antivirus programs when opening attachments" policy setting, which can tell the antivirus program to take additional action.
Figure 3 Attachment Manager
Figure 3 Attachment Manager
To find out more information on the Windows XP SP2 Attachment Manager, read Knowledge Base article 883260, "Description of how the Attachment Manager works in Windows XP Service Pack 2".

Securing Browser Settings
It's no secret that Microsoft Internet Explorer in Windows XP SP2 has enhanced functionality to protect the home, corporate, and academic user. For instance, Internet Explorer now comes with a pop-up blocker, better control for handling ActiveX® add-ins, and other safety features.
Figure 4 Additional Internet Explorer Policy Settings
Figure 4 Additional Internet Explorer Policy Settings
Internet Explorer users now have a whopping 619 possible policy settings at their disposal. You'll find most of these settings at Administrative Templates | Windows Components | Internet Explorer | Internet Control Panel | Security Page. Figure 4 shows settings for the Internet Zone. You can also change settings for other zones: Intranet, Trusted Sites, Restricted Sites, Local Machine, and Locked-Down Local Machine. You can easily configure what the behavior should be for the new Internet Explorer features when you're within each zone. For instance, you might want to allow ActiveX downloads while in your intranet zone, but block the download of ActiveX controls when you're visiting a restricted site.

Locating the New Policy Settings
You can locate the new policy settings using the built-in filtering available while editing any GPO. Simply open the Group Policy Object editor, and go to User Configuration | Administrative Templates or Computer Configuration | Administrative Templates and select Filtering on the View menu. Once the Filtering dialog appears, as shown in Figure 5, select Filter by Requirements Information. Next, select which requirements you are interested in, such as "At least Microsoft Windows XP Professional with SP2." Once performed, you can easily see which policy settings are new for this operating system.
Figure 5 Filtering Policy Settings
Figure 5 Filtering Policy Settings
Because the text within the Group Policy Object editor is not searchable, I would suggest you download the Excel spreadsheet entitled "Group Policy Settings Reference for .adm Files Included with Windows XP Professional Service Pack 2".
Figure 6 The Group Policy Settings Reference Spreadsheet
Figure 6 The Group Policy Settings Reference Spreadsheet
As you can see in Figure 6, this spreadsheet contains all policy settings and is easily configured to display only the new ones. Indeed, this spreadsheet contains worksheets which show just the new settings for regular, registry-based policy settings known as administrative (ADM) templates, as well as security settings (non-registry settings). All policy settings are searchable as well, making this a handy resource if you're looking for a specific policy setting but can't locate it in the Group Policy Object editor.

Conclusion
There are tons of new policy settings to help you control Windows XP SP2, so get out there and make your world even more secure! As with anything new, though, be sure to perform thorough tests on a test lab or small segment of users before rolling out into full production.

Jeremy Moskowitz (www.moskowitz-inc.com) is an MCSE and a Microsoft MVP in Group Policy. Jeremy is the author of Group Policy, Profiles and IntelliMirror (Sybex, 2004) . He runs www.GPanswers.com, a site for group policy information.


 Windows Administration: Redundancy On A Budget: Implementing A RAID File Server
Windows Administration Robert Walker

Redundancy On A Budget: Implementing A RAID File Server
At a Glance:
  • Implement an inexpensive fault-tolerant file server
  • Hardware and software requirements
  • Configuring RAID
  • Mirroring the boot volume
Windows Server 2003
RAID 1
Fault Tolerance

It happened again—a hard drive crashed on one of your user's PCs, taking irreplaceable data along with it. And again, the user said the same thing every other user with the same problem does: "I thought you guys did backups!"
To make matters worse, other department members were using a shared folder on her hard drive as a defacto file server. They lost data too, and now they hate you, the IT department, and computers (in that order).
This situation is not only unfortunate, but all too familiar. It's also preventable at surprisingly little cost. Did you know that you can implement a fault-tolerant departmental file server on a low-end desktop PC? Well get ready, because all you need is Windows Server™ 2003, a PC, and an extra hard drive.

A Server? Are You Nuts?
I know you're skeptical, and you should be, but have a look in your storage room, scrap heap, or garbage pile before you laugh me out of the room. An older machine is just fine, as long as it meets the hardware requirements for Windows Server 2003, which are surprisingly lightweight. This is because departmental file servers are, in most cases, low-volume. They don't require a fast CPU, although extra RAM and a fast network card may help to speed things up on busy days. Microsoft recommends a 550MHz (or faster) CPU and 256MB (or more) of RAM to run Windows Server 2003. (You can find a comprehensive listing of supported PCs on the Windows Catalog Web site.)
For what it's worth, I consider Microsoft hardware recommendations to be a working minimum. The actual minimum requirements published on the box are technically accurate, but I wouldn't want to run Windows Server 2003 on a Pentium 133 with 64MB of RAM. Whoever established those minimum requirements deserves to have that computer on their desktop for a week, if you ask my opinion. Anyway, I digress.
After you salvage a suitable PC from the scrap heap or are able to purchase a new one, determine how much disk capacity you will need and install two identically sized hard drives of that capacity. The total should therefore be double the capacity you need. Make sure they're actually identical since some hard drives, even with the same brand and model numbers, aren't. Also, remember that if you're using an older PC, you may need to update the BIOS to recognize all of the space on the new hard drives.

Unleashing the Beast
Now it's time to install Windows. This is always an interesting, fun, and educational experience (especially on older hardware), but I presume that you're up for it. Microsoft product support is just a phone call away if you need assistance, and they're a lot better than they used to be. On the primary drive, create a small NTFS-formatted C: volume (10-15GB) and install Windows Server 2003 onto it. Do not create additional volumes on the primary or secondary drives during the installation process; you will do this later. You probably know how to join your existing domain or have a plan to create a new one. If not, don't join or create a domain during setup. Instead, see Index of Windows Server 2003 Technical Articles for assistance in completing your deployment.
RAID Resources
Microsoft Windows Server 2003 Administrator's Pocket Consultant by William R. Stanek (Microsoft Press, 2003) provides a comprehensive discussion and walk-through of RAID options in Windows Server 2003
A compendium of Windows Server 2003 technical articles is located at Index of Windows Server 2003 Technical Articles
For more details about Windows 2000 Server RAID configuration, see Implementing RAID on Windows 2000 Servers.

After you install Windows Server 2003, it's a good idea to go immediately to Windows Update and download all critical updates. Since most departmental file servers are used only during the business day, you may want to automate the updating process. To accomplish this, you can configure Automatic Updates to automatically download and install future critical updates during off-peak hours. Of course, then you're trusting Microsoft to deploy patches on your machine whenever they like, which may or may not cause problems with your applications. For my part, I'd rather have Microsoft update my server than have a hacker do it for me, and I'll probably continue to think this until the first time one of my servers dies horribly (for no obvious reason) after a security update. Fortunately, this hasn't happened to date.

File Services
For security reasons, a fresh installation of Windows Server 2003 runs very few services by default. Use the Configure Your Server Wizard to add the File Server role, which will enable file sharing features. If the wizard does not display automatically after you log on, you will find it on the Start menu under Administrative Tools. It may be tempting, but don't simply turn everything on. Running unnecessary services not only presents a security hazard, but may make your server run more slowly.

Configuring Fault Tolerance
Windows Server 2003 supports RAID 0, 1, and 5 configurations. RAID 0 and 5 are wonderful, fantastic, and useful, but they're not the right tools for this job. A RAID 1 configuration is shown in Figure 1. This is the most appropriate for a small, low-volume departmental file server. RAID 1 creates an exact, simultaneous copy of the same data on two separate hard drives. RAID 1 volumes are also known as mirrored volumes. (In case you don't know, RAID stands for redundant array of independent disks.)
Figure 1 Dynamic Mirrored Volume
Figure 1 Dynamic Mirrored Volume
To configure an array, you first need to convert your disks from Basic to Dynamic. Start the Computer Management snap-in, located on the Start menu under Administrative Tools. Expand Storage, and then click Disk Management. Right-click Disk 0, and click Convert to Dynamic Disk (you can safely click Yes on the warning dialog box). A dialog box will warn that file systems will be dismounted. Again, you can safely click Yes, and then click OK to restart your computer. Note that this process actually requires two restarts. The first reboot is prompted by Disk Management in order to convert the boot volume device from Basic to Dynamic, while the second reboot is prompted by Plug and Play (PnP) in order to install the new boot volume device.
After you restart your computer, repeat this process on Disk 1 to convert it to a dynamic disk.

Mirror, Mirror, On the Drive
After both of your disks have been converted to dynamic, you need to mirror the boot volume. This is easy. Right-click on the C: volume, and then click Add Mirror. When prompted to select the location for the mirror, click Disk 1. The volume will display a Resynching status until the mirror is built. When you see Healthy, you're finished. If you shut down or reboot your server while a volume is resynching, the mirroring process will be aborted and you will have to recreate the mirror (which I learned the hard way).
After establishing the boot mirror, you should test to make sure you can, in fact, boot from the secondary drive. This provides peace of mind that things will go smoothly in case you lose the primary drive.
Figure 2 Resynching Status
Figure 2 Resynching Status
Now that your boot volume is mirrored, it's time to make a volume for your data. This is even easier since you use a simple wizard. On Disk 0, right-click Unallocated, and then click New Volume to display the New Volume wizard. Select Mirrored for the volume type, and complete the wizard with your size, drive letter, and volume label preferences. The volume will display a Resynching status until the mirror is built, as shown in Figure 2. When the volume displays a Healthy status, the process is complete. As with the operating system mirror, don't shut down or reboot your server until you see that the volume is Healthy.

Party Time
Break out the champagne! You now have a fault-tolerant departmental file server, and you built it without spending a fortune. You can now create shared folders and assign permissions on the data volume, and your users will have a safe place to store their files. If a hard drive in the server later fails, you can easily replace it without data loss or undue business interruption.
But now for the hangover: fault tolerance is great, but it's no substitute for regular backups so don't forget this very important safeguard. After all, fault tolerance does you little good if someone steals the computer or the building burns down. There are numerous ways to back up your data, but that's a topic for another article.

Robert Walker is an Operations Technical Specialist for the Department of Clippy Affairs at Microsoft. Among other tasks, he maintains the server infrastructure that Clippy uses to ask whether you’re writing a letter, and if you want some help.


 Windows Administration: Preserve Your Data With The Backup Tool You Already Have
Windows Administration Jay Shaw

Preserve Your Data With The Backup Tool You Already Have
At a Glance:
  • An overview of how Ntbackup works
  • Implementing Ntbackup on Windows 2000 Server, Windows Server 2003, and Windows XP
  • How to decide if Ntbackup is an appropriate solution
  • Ntbackup best practices and troubleshooting tips
Windows 2000 Server
Windows XP
Windows Server 2003
Ntbackup

If the headline of this article has caught your eye, I bet you've seen a message like this in your backup logs: "The operation was not performed because no unused media was available."
Or how about this one: "Operation was not performed, because the specified media cannot be found." Or even: "The requested media failed to mount. The operation was aborted."
I have good and bad news for you. The bad news is that your log messages are just a symptom of a larger problem. The cause of the problem is, more often than not, a misconception about how Ntbackup works. I have a sign on the wall of my office that reads "Things should just work!" But in the real world it's just not that simple.
The good news is that if you are willing to spend a little time doing your homework and learning how Ntbackup works, you can have all the benefits of a solid backup program without purchasing any additional software. It's included with Windows® XP, Windows 2000 Server, and Windows Server™ 2003, and it can back up all versions of Exchange Server as well.
I have seen many people abandon Ntbackup in favor of third-party products before ever really exploring its potential. Many of these products are very robust and offer features that are not available with Ntbackup. They can sometimes be easier to implement and they look really cool, too. As with most things, however, there is a catch. They can be extremely expensive; even more so if you need to back up an Exchange Server. These otherwise costly extra items are included with Ntbackup (assuming the services are located on the same server as the tape backup). If you have a standalone server, why spend more than you have to?
When Windows 2000 Server was released, like most people I opened Ntbackup, found the scheduler, set up a job, and clicked Run. After all, why would I need to read up on backup software, right? I was just so happy I didn't have to load the scheduler service from the Resource Kit the way I had to with Windows NT® 4.0. Big mistake on my part! It did work, but only sort of. I got one backup on each tape and then it began to fail. You see, I hadn't done my homework. Ntbackup is a little intricate and things just aren't quite that easy.

How the Pieces Work Together
Ntbackup on Windows XP, Windows 2000 Server, and Windows Server 2003 works in several pieces. There are three separate programs that are used to schedule a backup. These include: ntbackup.exe, the Scheduler Service, and the Removable Storage service. When you have an understanding of how these parts work together (and sometimes don't) you are in a much better position to find the source of a problem and achieve Ntbackup nirvana.
The first piece is ntbackup.exe itself. As the actual backup program, its job is to determine exactly what it is you would like to back up. It also tells the Removable Storage Manager (RSM) what media you would like to use, whether or not to verify the backup job, whether or not to use hardware compression, and a whole host of other options.
Ntbackup.exe functions with both a command-line utility as well as offering a GUI front end. This really comes in handy when your needs outgrow the basic package. It allows you to easily use batch files to automate more complex tasks with Ntbackup that would otherwise be all but impossible. I have seen several of these scripts advertised on the Internet. Some of them are quite good (and free, or at least inexpensive) and can give you many additional features and greater reliability for your backups. Examples of their features are e-mail notifications, easier management of the RSM, reminders for tape changing, even ejecting the tape at the end of a job. Ntbackup allows you to specify the files to be backed up, where to back them up, and how. These command-line options can be found at the command prompt by typing ntbackup /?.
The next important piece is the Scheduler Service. The scheduling appears to be integrated into ntbackup.exe. At least that's what I thought the first time I saw the GUI. The calendar is right there with options for how often and when backups are to occur. Upon further investigation, however, you will find that this task is actually handled by the Scheduler Service. When you use the Ntbackup GUI to create a backup job, the commands are translated into command-line options and placed in the Scheduler Service.
Figure 1 Scheduling a Job
Figure 1 Scheduling a Job
An example of this is shown in Figure 1. Here you can see that the backup job is scheduled for 11:59 P.M. on weekdays. Once that has been saved, if you go to the control panel and check the Scheduler Service, you will find that the following command has been entered:
C:\WINNT\system32\ntbackup.exe backup
"@C:\Documents and Settings\Administrator\Local
Settings\Application Data\Microsoft\Windows
NT\NTBackup\data\Full.bks" /n "Media created 8/
20/2003 at 3:30 PM" /d "Set created 8/20/2003
at 3:31 PM" /v:yes /r:no /rs:no /hc:on /m
normal /j "Full.job" /l:s /p "8mm AIT1" /um
Basically, this tells you to back up all the files specified to your tape drive at the specified time and not to use RSM to manage media. Now if you modify this command, you can change the job in any number of ways without touching the GUI. Note that if you make any changes you will be prompted for the service account password.
If you need to run additional tasks along with your backup job, you can access the scheduled backup command from the Scheduled Tasks icon in Control Panel. Simply right-click the backup job and select Properties. The task will appear on the Run line, just like in Figure 2.
Figure 2 Running Additional Tasks
Figure 2 Running Additional Tasks
All you need to do now is copy this line to a batch file and add whatever you like. Once you're finished, you can go back into the Command Scheduler and schedule the batch file to run instead of the command that was there.
While you're at it, you might want to add a line to refresh the contents of the tape drive. This can be very helpful since some tape drives do not always refresh their contents when a tape is changed. When the RSM loses track of the drive contents, you will get an error in the log that says "The operation was not performed because the specified media cannot be found." I do this with every job since it certainly doesn't hurt.
First go to the command prompt and type the following command:
rsm view /tlibrary
This will give you the name of your tape library. Add the following to your batch file before the backup command is run:
rsm.exe refresh /lf"library name"
This will ensure that RSM knows what tape is in the drive before any backup job is run.
The third piece to this puzzle is the RSM itself, whose job is to manage your backup media and your backup devices. Windows can use any number of devices for backup. Tape drives are the most common, but many people use JAZ drives, CD-RW disks (though this requires extra configuration), hard disks, even floppies and zip drives. Of all these options, I strongly recommend the use of a tape drive. I know of many small offices using a second hard drive or any number of other unique solutions in order to avoid the cost of a decent tape drive. A tape drive allows you to have many copies of your data and store some of those copies offsite in case of a fire or other disaster. It is a device that is built specifically for this purpose and in my opinion it's the best choice.
The RSM controls which device and, more importantly, which set of media is to be used. It also monitors the status of these devices. In the case of tape drives, it even tracks whether the device has been cleaned.

Managed versus Unmanaged Media
So now that you understand how Ntbackup works, you can just go in, schedule a job, throw a tape into the drive, and you're good to go, right? Well, not exactly. This is where most people (myself included) have run into trouble.
First you need to decide if you are going to use managed or unmanaged media. You have to decide if you want Ntbackup to wait for you to insert a specific tape, for example the "Monday" tape, or if you want to overwrite whatever tape happens to be in the drive at the time.
If you don't really care which tape is which, you can go with unmanaged media. If you add the /um switch to your backup command in the command scheduler, Ntbackup will use whatever tape is in the drive for your backup. This is what I like to call a quick-and-dirty solution. If you forget to change the tape one night you will still get a backup, but you'll lose the backup from the night before. If you back up your entire server on one tape you can get away with this, but it's not optimal. I wouldn't use this method if you have any intention of doing incremental backups. As an alternative, you could add a command to eject the tape at the end of your backup job, but if you're going to do that, perhaps you should instead consider using managed media.
Microsoft strongly recommends that you use managed media. Unmanaged media is still supported in Windows Server 2003, but Microsoft removed the documentation for it from the help file so they must feel pretty strongly about it. Using managed media is a little more complex, but it's also safer. Ntbackup will look for a specific tape that came from a specific pool (a group of tapes) which is in a specific library (a backup device). The best way to do this is to use the RSM to label each of the tapes in your rotation according to the day of the week. You should then schedule a separate backup job for each day of the week that requests the appropriate tape.
The advantage here is that if the tape is left in the drive a second night, it will not be overwritten. The backup job will request the appropriate tape and wait. With this method, you can effectively do incremental backups during the week when time is at a premium, and then you can schedule a full backup over weekends.

Did You Prepare Your Tapes?
Backup tapes need to be prepared and marked as free in order for Ntbackup to make use of them. You only need to do this once, but it needs to be done before the media will be recognized by the system. It's a quick and simple process. Go to Computer Management, click storage, click removable storage, and then click Media. You'll see the screen shown in Figure 3.
Figure 3 Preparing Backup Tapes
Figure 3 Preparing Backup Tapes
Once you insert a tape, you should see it listed on the right side of the screen. All you need to do is right-click the tape and select Free. This clears the tape and allows Ntbackup to use it. This is an important step that is often overlooked. Without completing this process, you're not going to be backing up anything at all.
It can be quite frustrating to work with a package without having a complete understanding of all that's going on in the background. Perhaps now you can see that Ntbackup can be a reliable, feature rich, and inexpensive alternative to third-party solutions. When you understand what is going on in the background, it becomes a lot easier to troubleshoot common problems that would otherwise be quite frustrating.
Remember Ntbackup is a tool just like any other piece of software, so it's important that you keep its intended purpose in mind. If you just need to back up a single server and the backup device is local, Ntbackup will most likely fit the bill. You can have a working backup tool that will suit the needs of most small networks and you don't need to purchase any additional software. It is not an enterprise tool, however. If you are in an environment with many servers, I would recommend that you investigate a third-party solution that will give you an integrated package.
Ntbackup Best Practices
Here is a list of 10 things to check when you are not getting what you want from Ntbackup:
1. Using the correct media Remember Ntbackup works with the RSM to manage your media. By default, Ntbackup will look for a specific tape.
2. The /um command-line switch If you have trouble with a backup running, try adding the /um switch to your backup command. It will tell Ntbackup to use whatever media is available in the tape drive.
3. The RSM refresh command On some tape drives, the RSM fails to notice that you have changed the tape in the drive. Try using this command to force a refresh.
4. Media allocation Don't forget to allocate your media. If you don't, Ntbackup will not recognize your tapes.
5. Hotfixes and services packs There have been several updates to ntbackup.exe. Make sure you have all your patches loaded!
6. The Hardware Compatibility List (HCL). Ensure that your tape drive is on the HCL since hardware changes all the time. There are many backup devices on the market that come with their own software. This is often the case when the device is not meant to work with Ntbackup.
7. Logs Always check your backup and event logs (the latter especially when Ntbackup exits without giving any meaningful error messages). If you don't, you either won't know you have a problem or you won't know what's causing the problem. Make sure you look carefully and double check that everything you need backed up is listed in the log.
8. Remote desktop sessions Whether in a connected or disconnected state, remote desktop sessions can cause Ntbackup to hang. Make sure you don't have any open. See support.microsoft.com/kb/555136 for more information.
9. The command prompt If your scheduled backup won't run, try running the command from the command prompt. This will let you know if syntax is the culprit or if some other problem is to blame.
10. Task manager When Ntbackup hangs, there are often multiple instances of ntbackup.exe listed in the task manager. Don't just cancel them. Make sure you reboot the server or the backup may still not run correctly.

Jay Shaw is an independent network consultant. His company, Network Consulting Services, is located on Long Island in New York. He can be reached at info@ncservices.net.


 Windows Administration: 10 Easy Ways To Lock Down Your Computer
Windows Administration Derek Melber

10 Easy Ways To Lock Down Your Computer
At a Glance:
  • Key security settings for Windows-based computers
  • User password security settings
  • User logon and authentication settings
  • User rights security settings
Active Directory
Group Policy
Admini- stration
Security

Did you know you could use a Group Policy Object (GPO) in Active Directory to secure all the computers and user environments in your Windows network? Here, I'll look at 10 critical GPO settings that handle security configurations across four areas: user passwords, user authentication, user privileges and anonymous access, and persistence of GPO settings.

User Password Security
The strength and security of a user password is at the core of your efforts to protect access to a user account on the network. If user accounts have unprotected or vulnerable passwords, there won't be much you can do to protect your network and resources. This is why there are so many security configurations within a GPO that are critical for security. Note that the password settings discussed here represent an exception to normal Group Policy processing order. They are defined at the domain level and will override settings at any other level.

Minimum Password Length
Computer Configuration\Windows Settings\Security Settings\Account Policies\Password Policy\Minimum Password Length
1 This setting is important for two reasons. First, as long as it is greater than 0, the user account must have a password. This prevents an attacker from gaining access to resources as a user without first trying a password attack. Second, the minimum password length can be set to a large value (as for a pass phrase), which makes it very hard to break with a cracking tool. Passwords are commonly 6 to 8 characters, but pass phrases can be more than 14.

Maximum Password Age
Computer Configuration\Windows Settings\Security Settings\Account Policies\Password Policy\Maximum Password Age
2 This setting controls how long a password is valid. The longer a password goes unchanged, the greater the chance that it gets cracked or becomes public. But you should weigh user convenience and security. It is reasonable to set the maximum password age between 30 and 60 days. Password age values are 0 = never or 1 – 999 days.

Password Complexity
Computer Configuration\Windows Settings\Security Settings\Account Policies\Password Policy\Password must meet complexity requirements
3 One way to strengthen a password is to make it difficult to guess by making it complex. Complexity means that the password contains more than just alpha characters. With this setting, all passwords must contain a minimum of six characters, use three of the four character types (lower case alpha, upper case alpha, numeric, and special), and may not contain part of the user's account name.

User Login and Authentication
These GPO settings can help control login and authentication in the domain: the LAN Manager Authentication level and storage of LAN Manager hashes.

Last User Logged On
Computer Configuration\Windows Settings\Security Settings\Local Policies\Security Options\Interactive logon: Do not display last user name
4 By default, a computer will remember your username for the next time you log in. Therefore, if you log into a shared computer, your username will be remembered on that computer, too. With the username and password being the only two bits of information needed to authenticate to Active Directory®, using this setting to hide the username for the next user makes life more difficult for an attacker.

LAN Manager Authentication Level
Computer Configuration\Windows Settings\Security Settings\Local Policies\Security Options\Network Security: LAN Manager authentication level
5 The LAN Manager Authentication level ensures that the most secure password authentication level is used for down-level OS authentication. This is a key configuration setting for servers and clients. When Windows® XP Professional communicates with Windows NT® Server, or when Windows Server™ 2003 communicates with Windows 95, the LAN Manager authentication level should be carefully chosen. The ideal is to configure the LAN Manager to the highest security level possible. The LAN Manager default is very weak, whereas NTLMv2 is the strongest authentication protocol of the LAN Manager family. Therefore, the most secure setting is "Send NTLMv2 response only\refuse LM & NTLM."

Do Not Store LAN Manager Hash
Computer Configuration\Windows Settings\Security Settings\Local Policies\Security Options\Network Security: Do not store LAN Manager hash value on next password change
6 All operating systems, including Windows XP Professional and Windows Server 2003, store the LAN Manager hash for backward compatibility. This is a security risk because the LAN Manager hash is relatively easy to crack. Unless you are running very old operating systems that are not patched, you won't need to store this hash, so don't.

User Privileges
User privileges control the rights a user account has on a computer. These settings are unique on each computer, but can be controlled through a GPO.

User Privileges Assignment
Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment
7 User privileges are essential to the security of client computers and servers. The primary benefit of the approximately 40 user privileges is that they bypass the security access control list that is configured on a computer. For example, if a user has No Access configured for a file on a server, the user will still be able to back up the file as long as she is given the back up files and directories user privilege. Most of the user privileges are important for servers. Be sure to check which users and groups have been assigned user privileges on all computers.
The anonymous user was created to allow computers to communicate easily with one another without requiring a user account. However, it's easy to exploit this easy access so computers requiring anonymous access must be secured.

Do Not Allow Anonymous Enumeration of SAM Accounts
Computer Configuration\Windows Settings\Security Settings\Local Policies\Security Options\Network Access: Do not allow anonymous enumeration of SAM accounts
8 Historically, computers running Windows have allowed anonymous access to the Security Accounts Manager (SAM) accounts. This access should be removed, unless there are apps that require anonymous access to the SAM accounts. A similar setting is "Do not allow anonymous enumeration of SAM accounts and shares," which controls the SAM and all shares on the computer.

Let Everyone Permissions Apply to Anonymous Users
Computer Configuration\Windows Settings\Security Settings\Local Policies\Security Options\Network access: Let Everyone permissions apply to anonymous users
9 This setting allows the Everyone group to also apply to anonymous users which is insecure. You should therefore not allow the Everyone group permissions to apply to anonymous users accessing the computer.

Persistence of GPO Settings
By default, before the GPO is applied to the computer, the version stored on the domain controller will be compared to the version of the GPO that was last applied to that computer. Sometimes the GPO versions match, but the local computer setting is not the same as the GPO setting. This can occur when the local computer user has administrative control. In this case, the user can modify the Registry value of the GPO setting. This doesn't mean changing the GPO version, just the GPO setting. To eliminate this problem, force the GPO settings to apply regardless of GPO version.

Process Settings in a GPO Even if the GPO Has Not Been Changed
Computer Configuration\Administrative Templates\System\Group Policy\Security policy processing
10 In this setting, you'll find a checkbox that is labeled "Process even if the Group Policy objects have not changed." Checking this box will force the configured settings in the Computer Configuration\Windows Settings\Security Settings section of the GPO to apply at each refresh interval, even if the settings have not changed. This will change all settings back to the GPO-specified configuration even if the local user changed the setting in the Registry.

Derek Melber manages www.auditingwindows.com, the first dedicated Web site for Windows auditing and security. Derek's new book series on Auditing Windows Security is now available. Online training is available at www.auditlearning.org. Reach him at derekm@braincore.net.


 SQL Server: Reduce Downtime: Implement SQL Server 2000 On A Cluster
SQL Server Stephen Strong

Reduce Downtime: Implement SQL Server 2000 On A Cluster
At a Glance:
  • Implement a SQL Server cluster for more uptime
  • Configure your heartbeat networks and nodes
  • Set partition sizes and allocate memory usage
  • Employ the correct security settings
SQL Server
Windows Server 2003
Microsoft Cluster Service
DTC

Hardware failures are inevitable. Even the firmware that provides hardware redundancy at the server, storage, or network level can fail. Failover clustering is an option that provides you with more uptime for your SQL Server installation.
While no feature-rich system can eliminate downtime completely, installing Microsoft® SQL Server™ 2000 on a Windows Server™ 2003 failover cluster should provide more uptime by alleviating your reliance on a single server. Clustering reduces single points of failure by spreading the load and adding redundancy. In this article I'll discuss the merits of clustering with SQL Server 2000 Enterprise Edition on Windows Server 2003 Enterprise Edition using the Microsoft Cluster Service (MSCS), or on Windows Server 2003 Datacenter Edition using the Cluster Service, which is a component of both of these versions of Windows® (not to be confused with Network Load Balancing clustering).
Although many of the concepts discussed apply equally to SQL Server 2000 64-bit on Windows Server 2003 64-bit, I will cover the 32-bit scenarios.

The Cluster Configuration
The first place to start, of course, is with a look at your hardware. Clustered solutions need to be carefully planned and the hardware configuration you use must be supported by both Microsoft and the hardware vendor. A good list of supported hardware solutions is available in the Windows Server Catalog.
The next thing to consider is the network configuration. A basic MSCS cluster must have at least two separate networks. The first network connects the clustered server nodes to other essential applications, such as domain controllers (DC), DNS servers, and application servers. In this article I'll refer to this as the public network.
Also mandatory is the heartbeat network, which connects each of the cluster nodes to one another. It glues the cluster together and keeps it alive. When one node stops responding, the other nodes recognize this and take ownership of resources that were running on the failed node.
It is good practice to have two heartbeat networks. Although a single-heartbeat network is a permitted configuration (with the public network as a backup), it is far from desirable. Nodes send IsAlive messages to each other every 1.2 seconds. This pulsing of messages requires that network latency on the heartbeat network between the nodes must be less than 500ms; therefore, the isolated heartbeat network must not be burdened with non-cluster service traffic. When there's only one heartbeat network and it encounters a failure, the public network (which carries all kinds of other application traffic as well) must take over the job of the heartbeat network at a reduced capacity. Therefore, a more suitable arrangement is to have two heartbeat networks, each with its own switch. Having two identical heartbeat networks permits one heartbeat network to fail and the other to continue providing identical functionality. Figure 1 shows the architecture of the network I am building here.
Figure 1 SQL Server Failover Cluster with Two Heartbeat Networks
Figure 1 SQL Server Failover Cluster with Two Heartbeat Networks
Now let's look at the network interface cards (NICs). Network card teaming, where a number of network cards in a single server all operate as a team, is not supported on heartbeat networks. Running multiple heartbeat or public networks on VLAN-configured switches is also not supported by Microsoft. In this clustering solution, no network can share a single point of failure with another network.
Network cards, like other hardware components, are prone to failure. Many organizations use Network Card Teaming on the public network to provide greater resilience. Although Microsoft doesn't fully support Network Card Teaming in a SQL Server failover cluster, it will allow it on the public network provided it is removed for troubleshooting purposes if a particular problem is related to network connectivity.
If the Teaming solution you use has options for fault tolerance and load balancing, select fault tolerance. If there is a requirement for faster network throughput on the public network, purchase faster NICs and network switches rather than allowing yourself to be tempted by load balancing NIC software which introduces another single point of failure. For information on configuration, see the sidebar "Configuring Heartbeat NICS."
As a final note on configuration, while we've been told that antivirus software is essential everywhere, you shouldn't install it on the SQL Server failover cluster. You'll need to investigate other ways to protect the servers from viruses. Not only will antivirus software negatively impact performance on a SQL Server 2000 failover cluster, it may also destabilize it.

Using Multiple Groups
Patches
Apart from the DTC patch there are also some memory leaks in the Volume Shadow Copy Service that may affect the servers if a backup agent is used to back up session state or the file system. For more information on this, see Backup Program Causes Gradually Declining Performance and A memory leak occurs in an application using the Volume Shadow Copy Service on a computer that is running Windows Server 2003.
There is a bug in the clures.dll that can cause a resource to fail to restart during a group move or failover between nodes after expanding virtual disks on a cluster. See the Knowledge Base article at Volume information is lost when you extend a partition by using the DiskPart tool and then move the volume in a Windows Server 2003 cluster.
The minimum SQL Server 2000 version should be 8.00.818 (SP3A + 818 hotfix) as this is covered in the latest security bulletin. I'd recommend 8.00.952 or start with 8.00.818 and move straight to SP4 (due in 2005). Information on 8.00.818 can be obtained from Cumulative Security Patch for SQL Server.
A list of all patches that precede SQL Server 2000 SP4 can be found here.

Clustering uses multiple groups or partitions where you place your applications or, more correctly, cluster resources. When a cluster is first created it has at least one group which contains the cluster IP address, the cluster Network Name, and the Quorum disk (more on disks later).
When building a SQL Server failover cluster, at least three groups are required. The cluster group, a Microsoft Distributed Transaction Coordinator (DTC) group, and a SQL Server 2000 instance group. Each SQL Server 2000 clustered instance requires its own group.
Groups should be named in a logical manner that is easy to understand. One method is to use a combination of the server name and the group function (see Figure 2), where [ClusterName] is the AD name of the cluster (like "MyCluster").
Figure 2 Naming Groups (Close Figure 2)
[ClusterName]DTC = Group that hosts the MSDTC resources
[ClusterName]SQL1  = Group that hosts first SQL Instance
[ClusterName]SQL2 = Group that hosts second SQL Instance
...
[ClusterName]SQLn = Group that hosts the nth SQL Instance
In a large organization with 50 or more SQL Server failover clusters, it is important to name the clusters according to an enterprise-wide naming standard. Without such a naming standard, the organization's DBAs could get confused very quickly. Each group can be moved or failed over to another physical cluster node independently of any other group.

Microsoft DTC
The DTC requires some special considerations on a cluster. To enable external applications to participate in distributed transactions, you must enable Network DTC Access, a Control Panel setting in Windows Server 2003 under Add/Remove Windows Components (see How to enable network DTC access in Windows Server 2003).
Once you've enabled DTC access, you can install the DTC, as explained at How to configure Microsoft Distributed Transaction Coordinator on a Windows Server 2003 cluster. Be aware, though, that there is an undocumented bug in msdtcprox.dll. If the DTC resource is offline when a SQL Server 2000 instance on the same cluster is also offline, and the SQL Server 2000 instance comes back online before the DTC resource does, then distributed transactions will fail (see Availability of Windows Server 2003 COM+ 1.5 Rollup Package 1, specifically the section with the header "SQL Service May Fail Because of a Nonexistent MSDTC Service"). A workaround for this is to turn the SQL Server 2000 instance off and then back on, but this is not ideal in a high-availability environment. There is a fix provided in Windows Server 2003 SP1, and the patched DLL should also be available from Microsoft Customer Support Services (CSS).
The CSS group (Support Options from Microsoft Services) has escalation paths that go directly to the Windows Server 2003 platform and SQL Server development teams. If you want to implement SQL Server 2000 failover clusters in your organization, CSS will prove to be a valuable resource.

Utilizing Your Resources
When bulk copying data, it is often useful to have clustered file shares associated with a SQL Server 2000 instance. The file share permissions should be set to include at least the SQL Server 2000 service account, the Cluster Service account and your DBA group (all with Full Access). I'd recommend removing the default read-only access for the Everyone group. Don't forget to set the directory-level permissions. Before bringing the newly created file share online, select the Advanced tab from the Clustered File Share resource property page and uncheck "Affect the group". Unless there is a specific reason to do so, you don't want a file share failure to cause the SQL Server 2000 instance in the group to fail over to another node (and worse still, attempt to fail over to yet another node when the file share fails to come online on the next node).
Many organizations require guaranteed message delivery between applications. This often involves sending data stored in SQL Server 2000 to either another SQL Server 2000 instance (local or remote) or to another application. This message delivery functionality will be available inside SQL Server 2005 as a subset of the SQL Server Service Broker feature. Most organizations currently use Microsoft Message Queue (MSMQ) to similar effect. Windows Server 2003 comes with MSMQ 3.0, which provides vast improvements in ease of installation and stability over previous versions (especially when coupled with a Windows Server 2003 Active Directory® in native mode). Place each MSMQ installation in its own cluster group so that it may fail over independently from other applications like SQL Server 2000 instances.
MSMQ may consume large blocks of physical RAM. If the process that writes to the queue is considerably faster than the process that reads from the queue memory for MSMQ, the queue can ramp up to between 1.6 and 1.8GB. Ideally, MSMQ should be run on a node separate from that of SQL Server 2000.
For the sake of security, scalability, and stability I would not recommend using other cluster resources like Domain Host Configuration Protocol (DHCP), Print Spooler, Windows Internet Name Service (WINS), or generic applications on the SQL Server failover clusters.

Employing Security
You can't take chances with poor security in the enterprise space, so you have to make sure your permissions settings are correct. To ensure security, both the Cluster service domain account and the SQL Server service domain account should be standard user accounts in Active Directory, set to "User Cannot Change Password" and "Password Never Expires". The Cluster Service domain account will need to be a member of the administrators group on each cluster node. The SQL Server service domain account will be granted the following local policy user rights assignments on the cluster nodes (there is no need to add these rights to a group policy):
  • Act as part of the operation system
  • Allow log on locally
  • Bypass traverse checking
  • Lock pages in memory
  • Log on as a batch job
  • Log on as a service
  • Replace a process-level token
The SQL Server service account needs to have "Account is Trusted for Delegation" selected and "Account is sensitive and cannot be delegated" unselected if Kerberos Authentication is enabled on the SQL Network Name [Virtual Name] resource. If Kerberos is used, two Service Principal Names (SPN) will need to be created. First set the SQL Server 2000 instance to a predetermined TCP/IP port number using the Server Network Utility. Then create two SPNs per instance:
setspn -A MSSQLSvc/<FQDN> <DOMAIN>\<SQLAcct>
setspn -A MSSQLSvc/<FQDN>:<PORT> <DOMAIN>\<SQLAcct>
Here, FQDN is the fully qualified domain name of the SQL Server 2000 Instance Network Name; PORT is the numeric TCP/IP port number set with the Server Network Utility; DOMAIN is the NetBIOS name of the Windows Server 2003 domain; and SQLAcct is the name of the account that the SQL Server service runs under. The following script can be used to verify that the objects in Active Directory have been updated correctly:
setspn –L <DOMAIN>\<SQLAcct>

Supported Instances
Microsoft supports up to 16 SQL Server 2000 instances on a Windows Server 2003 cluster. Most organizations will not require this many instances unless they are Internet service providers or application service providers with many clients. Although 16 is the supported number of instances, the practical limit is generally far lower. So why have more than one instance? There are many practical reasons, but here are the most common:
Security Although each database within an instance can have its own permissions, business-line owners often don't trust administrators from another business line. For instance, Finance administrators often don't trust Human Resources, and Sales administrators don't trust Marketing.
Uptime requirements You don't want Application X to go down if Application Y goes down first, as would happen with two SQL Server 2000 instances installed on a three-node cluster.
Collation configuration SQL Server 2000 supports collation configuration at the column, database, or instance level. Problems arise when two databases have different collations in the same instance and they need to use tempdb. One of the databases will have to convert the collation of data used within tempdb, and this incurs a performance penalty. By placing the database component of different applications with different collation requirements in different instances, these problems are overcome.
Instances installed with SQL Server 2000 SP3 or later sometimes have difficulties building full-text indexes. This can generally be overcome by running the following script on each node:
Regsvr32 "C:\program files\common files\system\mssearch\bin\tquery.dll"
In a high-availability environment, the N+1 rule is often applied, where for N active instances there is one node for each instance and one additional node to fail over to (see SQL Server 2000 Failover Clustering). The optimum price/performance ratio is achieved with three SQL Server 2000 instances on a four-node cluster (with three active nodes and one standby). Of course this must be weighed against the possibility of two instances failing over to the spare node at the same time. Instead, you might consider an N+I configuration, where for N active instances there are I inactive nodes to which they can fail over.

Sharing Disks
Clusters require shared-disk resources. All nodes must be able to get exclusive access to the disks that it has been tasked to manage (one at a time). The current owner of a cluster group that contains disk resources will attempt to secure an exclusive lock on the disks. If it fails, the disk resources within the particular cluster group will not come online and the application that depends on them (SQL Server 2000, in this case) will also fail to come online.
The shared disks must be either SCSI or fiber attached. Most large organizations use one or more Storage Array Networks (SAN) with fiber-attached disks, giving the flexibility of a centrally managed storage area.
On HP and EMC SANs, create partitions with a 64-sector offset. Windows uses the first 63 sectors of a track. On systems that use 64 sectors per track, this can lead to track alignment problems. Setting the partition to start on the beginning of the next track rather than on the last sector of the first track prevents single read/write operations spanning two physical disks when reading from the first or last sector of a disk.
Do not partition disks from Windows Server 2003. Each partition that is available to SQL Server 2000 should be on its own volume or logical unit number (LUN), which may be striped across multiple disks at the storage layer. Separate disk arrays or groups should be used for data, log, and backup partitions. Although it is tempting to mix data, log, and/or backup volumes on the same striped physical disks, this is a recipe for poor I/O performance.
All SQL Server 2000 failover clusters should have a minimum of five shared volumes. A simple formula to compute the number of shared volumes is this:
((Data + Logs + Backup) * (# of SQL SERVER 2000 Instances)) + Quorum Drive + MSDTC
Due to the lack of support for mount points (mounted volumes) in SQL Server 2000 failover clusters, the practical limit for the number of SQL Server 2000 instances in one cluster is seven. Using the formula I just explained, you get:
((1+1+1)*7) + 1 + 1 + local C drive + CDROM = 25 disks
Data, log, and backup partitions should be formatted with 64KB per cluster. SQL Server 2000 allocates data in 64KB extents (8?8KB pages) and typically performs its I/O in 8KB or 64KB chunks. If the disk controllers can be configured in this area, consider changing them to read/write in 64KB blocks. Consult your SAN vendor and don't forget to test repeatedly with production-size workloads. As SQL Server 2000 stores data in 8KB pages, certain workloads may perform better using an 8KB cluster size. The key here is that there are no magic one-size-fits-all answers. If an organization isn't willing to invest in performance tuning at the infrastructure level, use 64KB cluster sizes.
As a general rule, separating indexes onto separate disks from data on a SAN doesn't provide the I/O gains that it did on direct attached storage (DAS). Depending upon the nature of the workload, there may still be gains in splitting tempdb from the data partitions provided that it resides on different physical disks.
All volumes should be RAID 1+0. RAID 5 should only be used when under extreme duress from the finance department as performance will suffer (please ignore the protests of the SAN vendor on this point). If the virtualizing disk volumes at the storage layer and the data and/or backup disk pools are RAID 5, don't mix virtual RAID 5 volumes with virtual RAID 1+0 volumes within the same pool.

Allocating Memory
Configuring the Heartbeat NIC
  1. Rename the Network Connections "Cluster Network Connection 1" and "Cluster Network Connection 2," or "Heartbeat 1" and "Heartbeat 2," or something similar.
  2. Set the NIC to 100 Mbps Half Duplex.
  3. Remove the Client for Microsoft Networks File and Print Share service.
  4. Clear the "Register this connection's address in DNS" setting.
  5. Disable NetBIOS over TCP/IP in WINS.
  6. Set the IP address to 192.168.0.x where x is the node number (for example, 1, 2, 3, or 4) for the first heartbeat network. Set the subnet mask to 255.255.255.0.
  7. Set the IP address to 192.168.1.x for the second heartbeat network. Set the subnet mask to 255.255.255.0.
Note: Select a different IP address range from that used by the public network so as not to get confused. For instance, if the organization is already using a 192.168.x.x range for the public network, use the 172.16.x.x range for the heartbeat NIC.

SQL Server 2000 Enterprise Edition on Windows Server 2003 Enterprise Edition 32-bit can address up to 32GB RAM (when using Address Windowing Extensions, or AWE). If the hardware supports it, Windows Server 2003 supports Hot-Add memory for those who absolutely need to be online all the time. However, you will need to wait for SQL Server 2005 for dynamic AWE support (in SQL Server 2000 the amount of AWE memory allocated to an instance is fixed at instance startup time, whereas SQL Server 2005 will allow changing AWE memory allocation without a service restart). If running an N+1 cluster, memory can be more generously allocated to each instance. If a single node failure would cause multiple instances to run on each node, more consideration must be given to how the dependencies in each of these scenarios will be played out. Sometimes a mixture of AWE-enabled and dynamic memory allocation is the best solution when there are instances that are more memory-critical than others. Sometimes an organization will say Instance X absolutely cannot suffer performance degradation after a hardware failure but we really don't care about Instance Y as long as it's up and the application still functions (albeit at lower performance levels). Note that AWE-enabled instances behave differently in different scenarios during a failover. If the AWE memory is already allocated to another instance on the node, there will be no AWE memory to allocate to the newly failed over instance, resulting in lower performance than expected. Don't over-allocate AWE memory if multiple instances may end up on the same node, and don't forget to test scenarios both when the cluster is under load and when it is idle. When it's under load, Windows Server 2003 will likely consume more memory than when idle.

Node Recovery
Disaster recovery is outside the scope of this article, but it should be mentioned that it is possible to remove a node from a cluster, rebuild it, and install SQL Server 2000 (including Service Pack 3A) on the rebuilt node without causing further outages. However, in order to hotfix a SQL Server 2000 instance, an outage is required on all nodes in the cluster.

Conclusion
SQL Server is a mature relational database management system that is used in a variety of organizations from single-office environments to the largest of corporations. It is gaining momentum in the enterprise space due to its out-of-the-box rich functionality, ease of use, performance, and cost-effectiveness. When you deploy your SQL Server installations in a cluster, as I've explained here, you can achieve the high availability your organization needs to meet your customers' expectations.

Stephen Strong as a SQL Server Architect in Canberra, Australia, working as a contractor to Microsoft at a large client site. He has been working with SQL Server since version 4.21a. You can contact him at stephen@scsm.com.au.


 SQL Server: XML To The Max: Get More Power Out Of Your SQL Server
SQL Server Ward Pond

XML To The Max: Get More Power Out Of Your SQL Server

Prerelease info in this article is subject to change.
At a Glance:
  • How SQL Server 2000 stores XML data
  • Getting XML into and out of SQL Server
  • New T-SQL syntax for handling XML data
SQL Server 2000
SQL Server 2005
T-SQL
XML

SQL Server 2000 is the first release of the Microsoft flagship database management system to include support for the production and manipulation of XML structures using T-SQL.
In this article, I will present a brief overview of how you can now manipulate XML in SQL Server™ 2000. The focus of the article is intra-system communication between a SQL Server 2000 T-SQL stored procedure and a Web service or user interface component.
I'll discuss how to access data contained in an XML string passed into a stored procedure and how to produce XML output. Figure 1 provides a high-level overview of these constructs, and I'll introduce the following XML-specific T-SQL syntax:
  • sp_xml_preparedocument
  • OPENXML
  • sp_xml_removedocument
  • FOR XML EXPLICIT
Figure 1 Some New T-SQL Syntax in Action (Close Figure 1)

Figure 1

A Sample Case: Batch Validation of User Input
In SQL Server 2000, native storage of XML data is not supported. Rather, an XML string is stored in a (n)(var)char, (n)text memory variable, or table column. In almost all development scenarios, this memory variable will be an input parameter to a stored procedure; therefore, I'll take that same approach here. (You should note that passing T-SQL between tiers is always risky from a security standpoint and generally ill-advised from a performance perspective, two excellent reasons to avoid the practice altogether.)
Let's take a look at a stored procedure my team built several years ago called sptxValidateLookupData. The purpose of this stored procedure is to validate user inputs to a particular system's workflow. In order to minimize database round-trips, the designers of this system elected to collect all the user input and send that which requires validation to the database in a single XML document. The stored procedure runs the validations and returns the results to the calling process, also in a single XML document. The sptxValidateLookupData procedure serves a number of diverse workflows, so both existence and non-existence tests may be requested for any datum or domain in a particular batch. Typical XML input to this stored procedure might appear as shown in the following code:
<ValidateData>
<Validate Type="Countries" Name="CountryCode"
  Value="JA" Test="Not Exists"/>
<Validate Type="Countries" Name="CountryCode"
  Value="BO1" Test="Exists"/>
<Validate Type="Languages" Name="LanguageCode"
  Value="EN" Test="Exists"/>
<Validate Type="ContactPreferences"
  Name="ContactPreferenceCode" Value="EN"
  Test="Exists"/>
</ValidateData>
Under the <ValidateData> root, each <Validate> node describes a validation to be performed. Type describes the domain to be tested, Name describes the attribute to be tested, Value contains the value to be tested, and Test indicates whether to check for existence or non-existence of Value in the Name column of the Type domain. Note that while the sample XML contains four validations against 3 domains, the version of this stored procedure currently in production supports 17 domains and an arbitrary number of tests against them—a highly reusable, highly extensible, highly performing, easily maintained, and, best of all, dirt-simple validation component!
Results are returned in XML as well. If all of the tests in a particular batch evaluate to TRUE (if they turn out as the Test node suggests), an empty <Results/> tag is returned. If, however, any of the tests fail, a list of errors is returned:
<Errors>
<Error ErrorMessage="JA exists in Countries"
  FieldName="CountryCode"/>
<Error ErrorMessage="BO1 does not exist in
  Countries" FieldName="CountryCode"/>
<Error ErrorMessage="EN does not exist in
  ContactPreferences"
  FieldName="ContactPreferenceCode"/>
</Errors>

Using XML
SQL Server Books Online References
The following topics will get you started using XML in SQL Server 2005. Additional information can always be found on the TechNet Web site and at SQL Server Home.
  • Contents/What's New/XML Integration of Relational Data
  • Contents/XML and Internet Support (particularly, Retrieving and Writing XML Data)
  • sp_xml_preparedocument, sp_xml_removedocument
  • OPENXML
  • FOR XML EXPLICIT, FOR XML AUTO
  • Using EXPLICIT Mode

In SQL Server 2000, XML is passed into a T-SQL process as a simple string. Before it can be manipulated relationally or hierarchically, it must be identified to SQL Server as XML or "prepared." This is accomplished via a call to the system stored procedure sp_xml_preparedocument. Let's start at the top of sptxValidateLookupData (see Figure 2). The sp_xml_ preparedocument system stored procedure reads XML text provided as input (the @XMLString parameter in the call in Figure 2), then parses the text using the MSXML parser and provides the parsed document in a state ready for consumption by the OPENXML rowset function.
Figure 2 sptxValidateLookupData (Close Figure 2)
create procedure sptxValidateLookupData
@XMLString ntext
as

set nocount on

declare @idoc int, @Name nvarchar(30), @Value nvarchar(300), @Test nvarchar(30),
        @Result int, @Type nvarchar(30), @TestResult int

--    prepare the input XML for OPENXML manipulation
exec sp_xml_preparedocument @idoc OUTPUT, @XMLString
This parsed document is a hierarchical representation of the various nodes (elements, attributes, text, comments, and so on) in the XML document. This internal representation is stored in the server's cache, which has a maximum size of one-eighth total server memory and therefore needs to be managed carefully in heavily utilized or memory-constrained environments.
The sp_xml_preparedocument procedure returns a handle (the @idoc value in the call in the Figure 2) that can be used to access the newly created internal representation of the XML document. This value is used as a parameter to the OPENXML rowset function of the SELECT statement. OPENXML is your gateway to the prepared XML, and may be used within a SELECT statement the same way you'd use a table or a view (see Figure 3).
Figure 3 Create Table Variable (Close Figure 3)
--    create a table variable to drive the tests
declare @tempValidateLookupData table (
    [Type] nvarchar(30),
    [Name] nvarchar(30),
    [Value] nvarchar(300),
    [Test] nvarchar(30),
    [TestResult] int
)

--    populate the table variable with the tests to be performed
insert @tempValidateLookupData
select [Type], [Name], [Value], [Test], NULL
    from OPENXML (@idoc, '/ValidateData/Validate')
    with ([Type] nvarchar(30), [Name] nvarchar(30), [Value] nvarchar(300), [Test] nvarchar(30))
I created and populated the table variable in order to avoid multiple OPENXML calls that would be less efficient when compared with accessing the table variable multiple times. It's a best practice to copy the contents of the XML to a table variable to avoid repeated, inefficient access to the XML (if you're going to access the XML only once, though, just run the OPENXML query without creating the table variable first). The second statement in Figure 3 copies the input XML into the @tempValidateLookupData table variable.

Opening Up OPENXML
Now let's take a closer look at the OPENXML syntax shown here:
OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]])
[WITH (SchemaDeclaration | TableName)]
The idoc parameter is the document handle of the internal representation of the XML document—in other words, the value passed back from an earlier call to sp_xml_preparedocument. Note that it's possible to manipulate multiple XML strings in the same stored procedure. In such a case, a call to sp_xml_preparedocument must be made for each XML string, and a separate memory variable must be declared for each returned handle.
The rowpattern parameter is used to identify the nodes in the XML document associated with the idoc handle to be processed as rows. This tells the XML parser where in the XML document it can find the data you want.
The flags parameter describes precedence between attribute-centric and element-centric mapping for the query. SQL Server 2000 defaults to attribute-centric mapping when this parameter is omitted, which is sufficient for the purposes of this discussion. Further information on this is available in the SQL Server Books Online topic, OPENXML.
The WITH section of the OPENXML rowset function tells SQL Server 2000 how to map the contents of the XML into SQL Server 2000 datatypes. You can either explicitly delineate the columns or reference a database table with the appropriate structure. Every column from the XML referenced in the SELECT statement must appear in the WITH section (either explicitly delineated or in the referenced table). In this sample
-- populate the table variable with the tests -- to be performed
insert @tempValidateLookupData
select [Type], [Name], [Value], [Test], NULL
    from OPENXML (@idoc, '/ValidateData/
    Validate')
    with ([Type] nvarchar(30), [Name] nvarchar(30), [Value] nvarchar(300), [Test] nvarchar(30))
the column names in the SELECT statement and WITH clause must match the attribute names in the input XML. Note also that since the XML is treated as a recordset, the validation program will run successfully whether it's passed one datum or twelve—the calling process simply passes as many nodes of XML as are required to complete its validations.

Releasing Memory
The internal representation of the XML document will remain in memory until the process's server connection is terminated or reset, or until the memory is explicitly released. The sp_xml_removedocument system stored procedure should be called as early as is practical to release this memory in order to maximize available server resources (note that you must specify the memory handle to release):
--    free the server memory used by the
--    OPENXML image of the input XML
exec sp_xml_removedocument @idoc
This step is especially important in environments in which connection pooling is used. Connections in such environments might stay live for days, and continued instantiation of XML documents in memory without corresponding releases could result in performance degradation as free memory decreases. Connection pooling is designed to reset a connection if the connection is reused by somebody else, which in turn should release XML handles. However, it is a best practice to explicitly release handles rather than depend upon dropped connections to do it for you.
Once the XML is parsed, sptxValidateLookupData runs a large IF branch to find the requested test and sets the results of the @TestResult memory variable as appropriate for the results of the test. The table variable is then updated with the results of the test, as you can see in Figure 4.
Figure 4 Find Requested Test (Close Figure 4)
--    now use the table variable to drive the tests
--    by stepping through the records with NULL TestResult values
while exists (select TestResult from @tempValidateLookupData where TestResult is null) begin

--    pick a record to validate
    Select top 1 @Type=[Type], @Name=[Name], @Value=Value, @Test=Test
        from @tempValidateLookupData
        where TestResult is null

--    now run the appropriate test..

--    Countries (presented as a sample)
    if    @Type = 'Countries' begin
        if exists (select CountryCode from dbo.Country where CountryCode = convert(nvarchar(4),
            @Value))
            select    @TestResult =
            CASE    when @Test = 'Exists' then 1
                else 0
            end
        else
            select    @TestResult =
            CASE    when @Test = 'Not Exists' then 1
                else 0
            end
    end

--    (sixteen other validations follow and are omitted for brevity)

--    now update the appropriate record with the test result
    update   @tempValidateLookupData
    set      TestResult = @TestResult
    where    Name = @Name
    and      Value = @Value
    and      Test = @Test
end

Syntax in SQL
Now that the validations are complete, you need to return the results to the calling process. SQL Server 2000 provides several mechanisms for building XML output via the SELECT statement's FOR XML directive. Before I continue my work on sptxValidateLookupData, I need to take a second, brief detour into new SQL syntax.
There are three types of FOR XML in SQL Server 2000. FOR XML RAW and FOR XML AUTO provide rudimentary XML output with minimal hassle—and a concomitant lack of control over the format of the output. Most serious SQL Server 2000 programmers use FOR XML EXPLICIT. In an EXPLICIT mode query, the programmer fully controls the shape of the XML document returned by the query and also assumes responsibility for assuring that the XML is well formed and valid.

What About EXPLICIT Content?
There are several exacting syntactic requirements for producing a proper EXPLICIT mode query. Every EXPLICIT mode query must contain two metadata columns. The first column specified in the SELECT statement must be named Tag, and be of datatype int. This is the tag number of the current element, essentially a subtree type number. The second column specified must be named Parent and must be of datatype int. This is the tag number of that element's parent element. These columns are used to determine the hierarchy of the XML tree. If a row has a Parent value of 0 or NULL, it is placed at the top of the XML hierarchy. With this single exception, all Parent values must correspond to a previously declared tag value. Note that a resultset should have exactly one row with a Parent value of 0 or NULL, and it must be the first row of the resultset (if more than one row with a parent value of 0 or NULL is present, an XML fragment will be generated).
The names of the remaining columns in the query must follow a specific format that associates the element names with the tag numbers and provides attribute names for the generated XML. The column name format that must be followed is
[ElementName!TagNumber!AttributeName!Directive]
where ElementName is the name of the element (if "Countries" is specified as ElementName, then the resulting element tag will be <Countries>).
TagNumber is the tag number of the element. Along with the Tag and Parent metadata columns, TagNumber is used to express the hierarchy of the XML tree. Every TagNumber corresponds to a single ElementName, and AttributeName is the name of the XML attribute, if applicable.
Working with Directive, along with several scenarios under which AttributeName can be NULL, is beyond the scope of this article. The SQL Server 2000 Books Online topic "Using Explicit Mode" is an excellent source for more information.
In addition, in order for the XML to be built correctly, the resultset must be ordered such that each parent is followed immediately by its children.
Returning now to sptxValidateLookupData, you'll recall from the specification statement that I have two resultset formats to return. If all of the tests in a particular batch evaluate to TRUE, I'll return an empty <Results/> tag. If, however, any of the tests fail, I'll return a list of errors in XML.
Figure 5 shows the code from sptxValidateLookupData that uses FOR XML EXPLICIT to generate the resultsets.
Figure 5 Using FOR XML EXPLICIT (Close Figure 5)
--    if everything passed validation, return an empty result set in XML..
if not exists (select [TestResult] from @tempValidateLookupData where TestResult = 0)
    select 1 as TAG, 0 as parent,
    NULL as [Results!1!]
    for xml explicit

--    ..otherwise, return the failed test cases in XML.
else
    select 1 as TAG, 0 as parent,
    NULL as [Errors!1!],
    NULL as [Error!2!],
    NULL as [Error!2!ErrorMessage],
    NULL as [Error!2!FieldName]

    union all

    select 2 as TAG, 1 as parent,
    NULL,
    NULL,
    ltrim(rtrim(value)) + ' does not exist in ' + type,
    [name]

    from @tempValidateLookupData
    where [TestResult] = 0
    and test = 'Exists'

    union all

    select 2 as TAG, 1 as parent,
    NULL,
    NULL,
    ltrim(rtrim(value)) + ' already exists in ' + type,
    [name]

    from @tempValidateLookupData
    where [TestResult] = 0
    and test = 'Not Exists'

    for xml explicit
EXPLICIT mode syntax can get pretty verbose, so let's break it down. The first branch handles the simple case where all of my tests evaluate to TRUE:
-- if everything passed validation, return an -- empty result set in XML..
if not exists (select [TestResult] from @tempValidateLookupData where TestResult = 0)
    select 1 as TAG, 0 as parent,
    NULL as [Results!1!]
    for xml explicit
Since no AttributeName is specified, the following statement will create a single XML element called Results with no attributes and no children:
<Results/>
The ELSE branch here is far more interesting, as I'll construct the "error" XML for failed tests via a UNION of SELECT statements. Each individual query is responsible for rendering a particular node of the XML output (described in the new comments after each query), as shown in Figure 6.
Figure 6 Rendering Nodes (Close Figure 6)
--    ..otherwise, return the failed test cases in XML.
else
    select 1 as TAG, 0 as parent,
    NULL as [Errors!1!],
    NULL as [Error!2!],
    NULL as [Error!2!ErrorMessage],
    NULL as [Error!2!FieldName]

--    (above query produces <Errors>)

    union all

    select 2 as TAG, 1 as parent,
    NULL,
    NULL,
    ltrim(rtrim(value)) + ' does not exist in ' + type,
    [name]

    from @tempValidateLookupData
    where [TestResult] = 0
    and test = 'Exists'

--    (above query produces nodes like
--    <Error ErrorMessage="BO1 does not exist in Countries" FieldName="CountryCode"/>)

    union all

    select 2 as TAG, 1 as parent,
    NULL,
    NULL,
    ltrim(rtrim(value)) + ' exists in ' + type,
    [name]

    from @tempValidateLookupData
    where [TestResult] = 0
    and test = 'Not Exists'

--    (above query produces nodes like
--    <Error ErrorMessage="JA exists in Countries" FieldName="CountryCode"/>)

    for xml explicit
Notice that the UNIONed queries are sequenced in a manner in which each child will immediately follow its parent, even though there can be children from multiple subqueries (the "exists" and "not exists" queries have the same "tag" and "parent" values). When the UNIONed results are passed through FOR XML EXPLICIT, the FOR XML serializer evaluates the [ElementName!TagNumber!AttributeName!Directive] column names along with the values in the Tag and Parent metadata columns to render the XML in the hierarchy specified by the programmer:
<Errors>
  <Error ErrorMessage="JA exists in Countries"
    FieldName="CountryCode"/>
  <Error ErrorMessage="BO1 does not exist in
    Countries" FieldName="CountryCode"/>
  <Error ErrorMessage="EN does not exist in
    ContactPreferences"
    FieldName="ContactPreferenceCode"/>
</Errors>

Mission Accomplished
With the tools available to you, you're ready to query and generate XML in a SQL Server 2000 environment. As powerful as these tools are, however, the upcoming SQL Server 2005 promises increased programming power and even greater productivity improvements for anyone who wants to access data in XML using T-SQL. For a more detailed look at what XML handling is going to look like in SQL Server 2005, check out the sidebar that follows entitled "XML Programming in SQL Server 2005." You should be looking forward to the new syntax in the upcoming version.
XML Programming in SQL Server 2005
Microsoft has a slew of XML enhancements in the works for SQL Server 2005. For the past two years I've been developing a new system based on internal releases of this product (heaven for a SQL developer), and I'm proud to report that in the XML universe, the news is all good.
While all of the XML-oriented syntax introduced in SQL Server 2000 remains intact, there is also a huge suite of enhancements in SQL Server 2005. Here I'll provide an overview of these enhancements, and explain how they alter your approach to the sptxValidateLookupData stored procedure discussed in the accompanying article. The new SQL Server 2005 features I'll discuss are: the XML datatype; FOR XML PATH, TYPE; and the nodes() syntax.
While all of the XML techniques in SQL Server 2000 (such as passing XML strings into stored procedures via ntext parameters and using sp_xml_preparedocument, sp_xml_removedocument, FOR XML EXPLICIT, and OPENXML to manipulate it) are available in SQL Server 2005, replacements for each of them are introduced in the new product. And once you work with the new syntax, you'll never want to go back.
SQL Server 2005 includes some revolutionary changes to the storage engine itself, including three new datatypes: nvarchar(max), varbinary(max), and xml. Given the difficulties in handling ntext values in T-SQL, the new nvarchar(max) and varbinary(max) are an attractive alternative to using ntext if you want to continue using the same XML coding constructs that you did in SQL Server 2000. However, the provision of an XML datatype in the server engine opens up an entirely new range of programming possibilities.

Reviewing the Sample Case
You'll remember from the article that the sptxValidateLookupData stored procedure provides a single process for multiple, heterogeneous, variable batch validations of user inputs to Web pages. The directives for these validations are passed to the stored procedure in a single XML document. The stored procedure runs the validations and returns the results to the calling process, also in a single XML document.

The New Proc
As in the case of SQL Server 2000, I place the contents of the inbound XML document into a table variable. Rather than calling sp_xml_preparedocument and using OPENXML, however, I change the datatype of the parameter to xml (from ntext) and use the new nodes() syntax, as you see in Figure A.
Let's take a closer look at the nodes method. It provides a reference per row for every Validate element found under ValidateData elements in the @XMLString variable, bound as the node rowset using AS NODE(ref). Each column citation in the SELECT statement then uses the value method to extract the specified attribute values on a row-by-row basis, thus generating a row per Validate element containing the properties.
Each call to the value method contains the attribute name of the element to be extracted from the XML. This is delimited in single quotes and prepended by the @ sign, and is followed by the SQL datatype to which the attribute is to be mapped (which is also quote-delimited).
The logic that ran the validations in the original sptxValidateLookupData remains unchanged in the new sproc.

Returning the Results
You'll recall from the original specification statement that there are two resultset formats to return. If all of the tests in a particular batch evaluate to TRUE, an empty <Results/> tag will be returned. If, however, any of the tests fail, a list of errors in XML will be returned.
Figure B shows the new code from sptxValidateLookupData that uses FOR XML PATH, TYPE to generate the resultsets. Note how much more compact this syntax is than the previous FOR XML EXPLICIT efforts.
As before, the first branch handles the simple case where all of our tests evaluate to TRUE, as shown here:
--  if everything passed validation, return
--  an empty result set in XML...
if not exists (select [TestResult] from
  @tempValidateLookupData where TestResult = 0)
    select null
    for xml path ('Results'), type
The ELSE branch still constructs my "error" XML for failed tests, but this time it does so via a series of nested SELECT FOR XML PATH, TYPE statements. The explicit column names will direct SQL Server to use these names as XML attribute names (they must be single-quote delimited, and must be prepended with the @ sign). The FOR XML PATH ('Error') directives instruct the server to wrap the XML created in the inner selects in an Error element, while the FOR XML Path ('Errors') directive to the outer SELECT provides a root element called 'Errors'. Thus, this SQL will generate the same resultset as the earlier, far more verbose FOR XML EXPLICIT query.
While the immediate impact might not be apparent, the move away from the UNIONed SELECTs used in FOR XML EXPLICIT results in much more compact and maintainable code in heterogeneous queries. As an example, my team recently rewrote one 5000-line SQL Server 2000 user-defined function (which was basically a 43-level FOR XML EXPLICIT query) in 497 lines using this new syntax.
If you wanted to stick with the UNION model (which you typically won't), you could also write the second SELECT as shown in Figure C. This approach wouldn't make much sense if your internal XML nodes contained differing numbers of attributes. One of the major benefits of the new nested syntax is that the subqueries aren't required to have the same number of columns, as UNIONed SELECTs are. It would, however, serve to introduce the FOR XML ROOT construction. This allows a root element for the resulting XML to be declared without building an explicit SELECT for it, as I did in the first sample. For more on this, see XML Options in Microsoft SQL Server 2005.
With these new tools, you're ready to query and generate XML in a SQL Server 2005 environment even more efficiently than before. I've only scratched the surface of what's possible with this new syntax. There is very little you can't do with XML in SQL Server 2005. For all the latest SQL Server 2005 resources, you should visit Introducing SQL Server 2005.

Ward Pond has programmed relational database software since 1980, and has worked at Microsoft since 1999. When he's not tweaking the last millisecond of performance out of a SQL Server database, he can be found at home with his family, dogs, and guitars.


 SQL Server: Get A Traffic Report: Analyze Your Database Usage With System Tables
SQL Server Matt Evans and Josh Moody

Get A Traffic Report: Analyze Your Database Usage With System Tables
At a Glance:
  • Determine who's connected to your SQL Server
  • Dealing with blocked processes
  • Performance killers
  • Clues to SQL Server performance problems
Process Management
SQL Server
Database Performance

When you need information about the current connections to your SQL Server database, what those connections are doing, and which ones are slowing your system down, who do you call? Why, sp_who and sp_who2, of course..
Sometimes you may also view this information graphically in Enterprise Manager. While this info is good, it could be more helpful if you could do some better reporting on it. And why not get good data analysis? You are using SQL Server™ after all.
It turns out that sp_who, sp_who2, and Enterprise Manager are simply presenting data from the master.sysprocesses table. This means that you can write your own SQL queries to display the information you want, and create some enlightening reports. Suppose, for instance, you want to know which applications are connecting to your database. Sysprocesses contains a column called program_name, so a handy query would be:
SELECT program_name, count(*)
FROM
Master..sysprocesses
WHERE ecid=0
GROUP BY program_name
ORDER BY count(*) desc
It will tell you which apps are connected and how many instances there are of each, listing the application names by connection count, in descending order. If you have a machine that allows for parallelized queries, ecid=0 filters out the associated subthreads and only counts queries running in parallel once rather than once per processor on which they're running.
Our teams have an open policy and a fairly large number of technical users, many of whom are writing tools that access the database directly. The results of this query shed light on the tools in use and the breakdown between custom tools and tools supplied by the development team.

Blocked Processes
If you have an open policy about who can write tools that connect to your database, eventually you'll find some bad tools causing a variety of problems. One common problem is blocking. Suppose that process A is modifying data that process B wants to use. Process B will be blocked until process A has completed what it is doing. This is only one type of blocking situation; others exist and are common. What matters to you as a DBA is identifying when blocking is a problem and how to deal with it effectively.
We've found that when blocking is bad enough that users are noticing slowdowns and complain about it, Enterprise Manager does not help—it takes minutes to extract and render data from the production machines. When Enterprise Manager fails, we turn again to the handy system tables.
One of the other columns available in the sysprocesses table is "blocked". Normally, this column has a value of zero, indicating that the process in that row of the table is not blocked. However, if a row has a non-zero blocked value, that value is an integer which is the SQL Server process ID number (SPID) of the process that this process is blocked by.
More concretely, each row of sysprocesses corresponds to one connection/process inside SQL Server. Each of these processes has a SPID (this is the same SPID you use with the kill command). SQL Server can also split processing of a task across multiple subthreads, resulting in numerous rows with the same spid but differing ecids. There will always be a spid/ecid pair where ecid=0, but we only count this one when counting processes.
With a large number of users, it is common for tens or hundreds of processes to be blocked when slowdowns are noticed. Killing these processes may or may not solve the problem because 10 processes may be blocked by process B, while process B itself is blocked by process A. Issuing 10 kill statements for the processes blocked by B probably won't help, as new processes will simply become blocked by B. Killing process B may or may not help, because then the next process that was blocked by B, which is given execution time, may get blocked by process A and become the process that is blocking the other 9 remaining processes. When you have lots of blocking that isn't resolving in a reasonable amount of time you need to identify the root blocker, or the process at the top of the tree of blocked processes.
Imagine again that you have 10 processes blocked by process B, and process B is blocked by process A. If A is not blocked by anything, but is itself responsible for lots of blocking (B and the 10 processes waiting on B), then A would be the root blocker. (Think of it as a traffic jam. Figure 1 will help.) Killing A (via kill) is likely to unblock B, and once B completes, the 10 processes waiting on B are also likely to complete successfully.
Figure 1 Process Traffic Jam
Figure 1 Process Traffic Jam
Since blocking information is stored in sysprocesses, you can use plain SQL scripts to quickly identify root blocker processes, as shown in the following:
SELECT spid from master..sysprocesses
WHERE blocked = 0
AND spid in (select blocked from master..sysprocesses)
This script asks for the id of a process that isn't blocked, but which is blocking some other process. It is usually the case that killing any process identified by this query will get the database running well again.
So how do you know when to look for blocked processes? Once users are complaining, it's too late. You can use Performance Monitor (Start | Run | perfmon.msc) to monitor, graph, and set alerts on the built-in performance counters installed on the system, or on remote systems. SQL Server installs a good variety of performance counters, but for SQL Server 2000 there isn't one built-in that tells you the number of blocked processes. (For SQL Server 2005, you can use the new built-in counter "SQL Server:General Statistics:Processes blocked".) To solve this problem, we used the .NET Framework to write a performance counter that simply logged the value from
SELECT count(*) from master..sysprocesses
WHERE blocked <> 0
as the counter value. Then we could both graph and record our blocked processes count using the normal Windows Performance Counter framework. You can devise your own mechanism which logs or alerts you based on polling this value.
If this value ever gets too high (which depends on your users, database, and applications), you can have Performance Monitor alert you so you can investigate. Then you can resolve blocking problems before your users even notice.
Note that haphazardly killing processes that look like they're blocking may be a career-limiting move. Sysprocesses will tell you who and what you are about to kill via the columns program_name (the name of the application specified by the application programmer when he wrote the database connection code), hostname (the network name of the computer from which the connection is originating), and loginame (the credentials used to connect to the database). In Windows NT® domain environments, loginame will usually be the user's Windows credentials, expressed as DOMAIN\username. If the connection is using SQL-mode authentication, you'll find the SQL credentials. You may also want to run DBCC INPUTBUFFER(<SPID>) in order to determine what the user is running. Unfortunately, this only returns the first 255 characters. If you want more information, you can use the built-in fn_get_sql function, passing as an argument the sql_handle column from the sysprocesses table.
One final word on killing processes efficiently—suppose that there are quite a few processes you'd like to kill, and you can identify them via a query:
SELECT spid from master..sysprocesses
WHERE loginame = 'FABRIKAM\joe'
You can use select literals to make life easier. If that query spits back 20 SPIDs for you to kill, make SQL do the work by changing the query to:
SELECT 'kill ', spid from master..sysprocesses WHERE loginame = 'FABRIKAM\joe'
It will write lines saying "kill <spid>", one per row, that you can select and paste right back into the tool (osql.exe, isql.exe, or isqlw.exe), and then kill the whole group with one keystroke. Try doing that in Enterprise Manager!

Computationally Expensive Processes
Sometimes the database performs poorly due, not to blocking, but to particularly heavy loads. Often the DBA will determine that the database simply cannot support the work that it is being asked to do and maintain adequate performance. This doesn't necessarily mean it is time to create more indexes or throw more hardware at the problem. And if it is time to throw more hardware at the problem, there may be no money available for new boxes.
You can't always assume that periods of high utilization represent legitimate work. There could be problems in the apps that are running, or even problems caused by the user. Maybe the application has a data paging functionality, but the user has opted to receive the entire 100,000 row DataSet every time, even though she has applied a sort which gives her the one row she needs first with each query. Regardless of the root cause of the problem, your job is to identify performance issues and eliminate them.
Let's take another look at two columns in sysprocesses: cpu and login_time. One way we can measure the pain a SPID is causing is by looking at the cpu column:
SELECT TOP 5 * from master..sysprocesses
ORDER BY cpu DESC
One problem with this is that the cpu value is constantly incremented over the lifetime of the process. You may find that this query tells you about SQL Server internal processes that started when the database booted and are running in the background. These use lots of total CPU time because they've been running for days at a time, but they have spent most of that time in an idle state. These can be filtered out by querying for all spid values less than 50, as all values 50 and below are reserved for internal SQL Server system processes.
It would be more helpful to find a process that uses lots of CPU the majority of the time it is connected. You can do that by dividing the cpu value by the time the process has been connected. You can't get process connect duration directly; instead, you get the time of day that the process connected to the database, as "login_time".
Consider the following select statement:
SELECT spid, program_name, datediff(second,login_time, getdate()) as
   ConnectedSeconds FROM master..sysprocesses WHERE spid > 50
This script will give you the SPID, the name of the app, and an integer called ConnectedSeconds, which represents the number of seconds that the SPID has been logged into the database.
Now you've got the pieces you need to find expensive processes, as you see in Figure 2.
Figure 2 Find Bad Processes (Close Figure 2)
SELECT
TOP 30
spid, blocked,
    convert(varchar(10),db_name(dbid)) as DBName,
    cpu,
    datediff(second,login_time, getdate()) as Secs,
    convert(float, cpu / datediff(second,login_time, getdate())) as PScore,
    convert(varchar(16), hostname) as Host,
    convert(varchar(50), program_name) as Program,
    convert(varchar(20), loginame) as Login
FROM master..sysprocesses
WHERE datediff(second,login_time, getdate()) > 0 and spid > 50
ORDER BY pscore desc
The output of this script (as illustrated in Figure 3) will be the top 30 processes, sorted by descending PScore. (PScore is the alias we've given to the computed column which results from dividing the cpu time of a process by the connected seconds of that process.)
Figure 3 Processes by PScore
Figure 3 Processes by PScore
In addition, the script tells you if the process is blocked, what hostname it's running from, who's running it, and what application it is. We're also using the built-in SQL Server function db_name, which takes an integer as its argument and returns the human-readable name of the database. Internally, SQL Server refers to databases by their dbid, and while sysprocesses uses this convention as well, this script converts the dbid to the database's name to make life easier for the human database administrator.
We used the WHERE clause at the end of the query to prevent divide-by-zero errors on processes that connected while the script was still running, and have a value of zero for the number of seconds that the process has been connected.
Running this script will tell you which instance of a process is using lots of CPU on the database server. You can use the loginame or hostname columns to identify the person responsible for the offending process (if your hostnames are related to who owns that workstation, you can often tell who owns a computer just from its hostname), and ask them what they're doing. Often, users have no idea that they're punishing the database. This is a good way to uncover application bugs as well—a program shouldn't necessarily eat up database CPU resources when the user doesn't even realize it's still running!

Computationally Expensive Aggregations
Sometimes you'll need to find an app that by itself doesn't consume many resources, but because the app is widely deployed, the aggregate effect of the instances of this app is really punishing the database server.
For instance, suppose you have an app that polls the database with a simple query every n seconds. The app developer probably chose n to be a compile-time constant in the application. This means that n cannot dynamically change in response to increased database load, increased number of instances of this app connecting to the database, execution time, or database performance degradation.
Suppose this application is set to poll every five seconds. This implies that execution of the query should take less than five seconds, assuming the application is deployed to one machine. If the application is deployed to five machines, then executing its query should take less than one second. It may be the case (but is not always) that executing this query 10 times doesn't take much longer than executing it once because of the caching abilities built into SQL Server, but that's more of a lucky situation than something to count on.
It is often the case that hardcoded polling intervals, especially in database applications, are chosen by the application developer as an afterthought, or because it seemed reasonable. What seems reasonable for one instance of the application, running in isolation against a database with only a few rows of test data in it, quickly becomes unreasonable in production scenarios. One way to look at the situation is, assuming the database server is completely utilized by instances of this application, doubling the length between polling—changing it from 5 to 10 seconds—doubles the number of instances which can be active or cuts the load on the database in half, freeing resources for other applications.
Before you can ask your application developers what their apps are doing, you need to identify the problem apps. In Figure 2, we simply looked at the SPIDs that were using the most CPU resources per unit of time. Now we'll use the same table—sysprocesses—but we'll query on it in a different way. We want to know which programs (program_name) use the most CPU resources (see Figure 4).
Figure 4 Find Resource Hogs (Close Figure 4)
SELECT
    convert(varchar(50), program_name) as Program,
    count(*) as CliCount,
    sum(cpu) as CPUSum,
    sum(datediff(second, login_time, getdate())) as SecSum,
    convert(float, sum(cpu)) / convert(float, sum(datediff(second, login_time, getdate()))) as Score,
    convert(float, sum(cpu)) / convert(float, sum(datediff(second, login_time, getdate()))) / count(*) as ProgramBadnessFactor
FROM master..sysprocesses
WHERE spid > 50
GROUP BY
    convert(varchar(50), program_name)
ORDER BY score DESC
This query does a few new things. First, we're fixing up the column program_name to make it easier to display and then using that as the argument to group by. This script will compute aggregate functions all with respect to a unique list of running program_names currently logged in.
Next we see how many instances of that program are connected, and we present it as CliCount. If it turns out that there are tens or hundreds of instances of a program (CliCount is a large number), then we know we have a program worth investigating first, as the biggest overall gains can be had by getting better efficiency from that app.
The next thing the script does is sum the cpu time and the connected seconds from all instances of this program. Using those two values, it populates the Score column, which is aggregate cpu divided by aggregate connect time (for that program).
The last computed column is called ProgramBadnessFactor. This is the aggregate cpu, divided by the aggregate seconds, then divided by the number of connected instances of the program.
This ProgramBadnessFactor attempts to quantify how bad this program is compared to the others, by dividing the Score by the number of connected instances. A high value for ProgramBadnessFactor would indicate that every instance of the program was CPU-intensive. A lower value would indicate that the program may have some instances that cause performance problems, but also has instances that are mostly idle.

Conclusion
Here we presented just a few examples of the interesting pieces of data you can mine from SQL Server and the performance picture you can paint once you have that data, thanks to system tables. We've looked only at sysprocesses here. However, syslockinfo is an interesting table as well, since it contains information about the objects that are locked. If you find that your database is having lots of trouble with locking, examine syslockinfo in conjunction with sysprocesses to identify programs causing locks.
Of course, these few scripts are certainly not the only tools you should have in your DBA arsenal. While many of them are a good start, you'll find that just having a script handy to run on an ad hoc basis isn't enough. We found that certain scripts were worth automating, and then publishing their results as performance counters. The .NET Framework makes it very easy to execute a SQL query to get a scalar result, and then publish that result as a performance counter value that can then be graphed, recorded historically, and used to trigger alerts. While SQL Server provides a good selection of built-in performance counters, only your specific environment and applications can determine which metrics are especially indicative of the health of your servers. Queries against the system tables are a key first step towards really gaining insight into your databases.

Matt Evans is a software tester working on the Microsoft Business Framework. You can reach him at mattev@microsoft.com.
Josh Moody is a Software Development Engineer in Test at Microsoft working on Visual Studio.


 SQL Server: Async Lifestyle: Manage Your Tasks With Service Broker
SQL Server Roger Wolter

Async Lifestyle: Manage Your Tasks With Service Broker

Prerelease info in this article is subject to change.
At a Glance:
  • Using Service Broker for batch processing
  • Database management on one or across many servers
  • Status and timer messages
Service Broker
SQL Server Agent
SQL Server 2005
Download the code for this article: ServiceBroker.exe (121KB)

Most of the articles written about SQL Server 2005 Service Broker talk about its capabilities as a messaging infrastructure and as a platform for building reliable database applications.
For this reason, most DBAs see Service Broker as a developer tool and yet another thing they have to configure, debug, and maintain. But the truth of the matter is that the asynchronous execution features of Service Broker can really make a DBA's life easier.
In this article I'll explain how to use Service Broker to implement a simple batch scheduling system that you might want to use to run tasks such as data warehouse maintenance and population or routine database maintenance. While I chose to implement a job execution system as an example, the concepts I'll discuss are useful for more limited task execution and scheduling as well. For example, you might use just the conversation timer part of the sample in order to implement periodic log backups or use activation to control parallelism in a data warehouse rebuild operation.
Of course, I'm not advocating the use of Service Broker to replace SQL Server™ Agent. In fact, the best use of the sample application in this article will probably be to run it as a job that is scheduled to be run by SQL Server Agent.
As I proceed, I'll assume you have a basic knowledge of the Service Broker. If not, before you continue you should read "A First Look at SQL Server 2005 Service Broker" or the introduction to Service Broker in SQL Server Books Online.

Service Broker Features
One of the most powerful features of Service Broker is the dialog, which represents a conversation between two end points. Messages in a dialog are guaranteed to be processed in the order in which they were put into the dialog, and any tasks will execute in the proper order as well. Because dialogs are persistent database objects, the order is preserved through any number of database restarts and transaction rollbacks.
Ordering is valuable for tasks that have dependencies or must be executed in a precise sequence. As each task completes, the next one starts just as soon as the transaction for the previous task commits. When a task fails, the execution logic can end the dialog so that the rest of the tasks don't continue with incomplete data. You shouldn't place tasks that aren't dependent on each other into the same dialog because dialogs are processed serially; unrelated tasks in the same dialog can't run in parallel.
Service Broker activation starts a configured stored procedure to process messages in a queue. If messages arrive in a queue faster than the stored procedure can process them, additional instances of the stored procedure are started until either enough stored procedures are started to keep up with the load or a configured maximum are started. If messages represent tasks to be executed, activation will ensure that they are run as they arrive in the queue and the limit on the maximum number of activated procedures limits the amount of parallelism available to the running tasks. By adjusting the number of activated procedures, you can control the size and number of resources dedicated to the tasks run by Service Broker.
A conversation timer is a simple mechanism that puts a message in a queue at a time of your choosing. Because a message arriving in a queue will initiate an activation procedure if one isn't already running, a timer expiring will reliably start a task. A Service Broker conversation timer is unique in that it is persistent and transactional. It will fire even if the database is restarted or down when the timer expires. Plus, if the task processing the timer message fails and rolls back, the timer will fire again.
Even though I started out by saying Service Broker was about more than just reliable messaging, Service Broker does include a high-performance, reliable transactional messaging system. If you use Service Broker for batch processing, you can spread the processing reliably over many systems by adding the routes required to make the Service Broker dialogs distributed. The persistent, reliable attributes of Service Broker messaging mean that the batch tasks will be reliably executed in spite of network and server failures.

Batch Processing
To demonstrate how DBAs can use the Service Broker features to perform batch processing tasks, I wrote a simple batch processing system. It is meant to be an example of how a batch system could be developed using Service Broker rather than a practical batch tool, but it contains some useful examples of what you can do with Service Broker. It probably doesn't make sense to put all this into a single application but it makes a better sample that way. Figure 1 shows how it works.
Figure 1 Batch Processing
Figure 1 Batch Processing
A batch is a sequence of tasks, each of which is a SQL command. A Service Broker dialog conversation is created for each batch and the tasks in that batch are sent, in order, to the target service of the conversation. The queue for the target service has an activation procedure to run the tasks that is throttled by a MAX_QUEUE_READERS option. The result is that batches are processed independently, but with a controlled amount of resources allocated to them.
At this point, you should download the sample code that accompanies this article to make it easier to follow what's happening.
To simplify putting tasks in a queue for execution, the sample includes a table that holds information about the jobs to execute and a stored procedure that reads the rows in the table and puts an entry in the task queue for each row in the task table. The task table looks like Figure 2.
Figure 2 Task Table (Close Figure 2)
CREATE TABLE TaskTable (
    BatchNumber bigint ,
    TaskNumber bigint  primary key(BatchNumber, TaskNumber),
    Command nvarchar(2000),
    NoTransaction    int,
    StartTime    datetime null,
    CycleTime bigint null,
    ConversationHandle uniqueidentifier null,
    ProcessStatus    int null,
    EndTime    datetime null,
    ErrorXML XML null )
The batch and task numbers are used to define ordering and dependencies. Tasks with the same batch number are executed in the same Service Broker dialog in task number order. Tasks with no dependencies or ordering requirements are placed in separate batches. The command column contains the task to execute. The tasks are executed by passing the contents of the command field to the TSQL EXEC command. In this sample, the commands are fixed strings that must contain all required parameters. The NoTransaction column handles backup commands and any other SQL commands that can't run in a transaction. StartTime, CycleTime, and ConversationHandle are used for task scheduling and will be discussed later. The last three columns hold information about the last time the task ran.
The QueueTasksProc stored procedure reads the TaskTable and puts a message in the TaskQueue for each row in the table. The query shown in Figure 3 is used to read the TaskTable.
Figure 3 Reading TaskTable (Close Figure 3)
(SELECT BatchNumber,TaskNumber,Command, NoTransaction
    SELECT BatchNumber,TaskNumber,StartTime, CycleTime, ConversationHandle,
    FROM TaskTable Task WHERE Task.BatchNumber = A.BatchNumber
    AND Task.TaskNumber = A.TaskNumber FOR XML AUTO, ELEMENTS, TYPE)
FROM TaskTable A ORDER BY BatchNumber,TaskNumber
Notice the FOR XML subquery that creates an XML document for the message body. Service Broker messages are often sent as XML because the XML features in SQL Server 2005 make it easy to create and parse XML documents.
The rest of the stored procedure reads through the resultset of this query and sends a message to the TaskQueue for each row. Whenever the BatchNumber changes, a new dialog is started so that each batch ends up in its own dialog.
If a row contains a StartTime value, instead of queuing the job immediately, a conversation timer is started with a timeout equal to the number of seconds until the StartTime value. The dialog handle for the conversation timer is put into the TaskTable so that when the timer fires, the dialog handle can be used to find out which task corresponds to the timer that expired.

Process Tasks
ProcessTasksProc is the procedure that runs to process messages in the TaskQueue. The core of this procedure is a standard Service Broker receive loop that receives messages one at a time and processes them based on the message type. The EndConversation and Error message types are handled by standard Service Broker routines. The TaskRequest message type starts SQL tasks. The tasks are started in a try/catch block so that you can capture any error messages for logging. The task execution block looks like this:
BEGIN TRY
   EXEC (@Command)
END TRY
BEGIN CATCH
   SELECT @ErrorNumber = error_number(),
          @ErrorText = error_message()
END CATCH
The error_number and error_message are captured and used to build a response message which is sent back on the same conversation that the task arrived on (see Figure 4). Note the use of the XQuery modify statement to build the XML message body.
Figure 4 Capture Error Info (Close Figure 4)
Set @ResponseDoc = N'<TaskStatus/>'
SET @ResponseDoc.modify(
'insert (<BatchNumber>{ sql:variable("@BatchNumber") }</BatchNumber>,
    <TaskNumber>{ sql:variable("@TaskNumber") }</TaskNumber>,
    <ErrorNumber>{ sql:variable("@ErrorNumber") }</ErrorNumber>,
    <ErrorText>{ sql:variable("@ErrorText") }</ErrorText>,
    <Time>{ sql:variable("@Time") }</Time>
    ) as last into (/TaskStatus)[1]');

SEND ON CONVERSATION @dialog
    MESSAGE TYPE [TaskStatusResponse] (@ResponseDoc)
If the message type is EndBatch, then I'm finished with this dialog and can safely end it, as shown here:
IF (@message_type_name = 'EndBatch')
BEGIN
    END CONVERSATION @dialog
END
The ProcessTaskProc is registered as an activation procedure for the TaskQueue:
ALTER QUEUE [TaskQueue]
    WITH ACTIVATION (
        STATUS = ON,
        PROCEDURE_NAME = ProcessTasksProc,
        MAX_QUEUE_READERS = 4,
        EXECUTE AS SELF);
Notice that MAX_QUEUE_READERS is set to 4. This means that a maximum of four tasks will be executed in parallel. Adjust this parameter to control the number of SQL Server resources allocated to batch processing. The sample code includes a stored procedure called SetActivatedProcs that can be used to change this parameter.

Processing Responses
Response messages are sent back to the TaskStatusQueue when a task completes. The response message contains the task completion time and the error status. This response message isn't strictly necessary. A simpler implementation would be to have the ProcessTaskProc log the results of the task and not return a response. I included the response message in the sample to illustrate how you could use it to collect all responses from a distributed batch system in a central queue for logging and analysis. In a truly distributed system, the response message would have to include the location where the task executed and more detailed statistics, but the sample just collects a few simple data values.
When a response message is received by the ProcessTaskStatusProc, the procedure parses the XML in the message body and updates the appropriate row in the TaskTable, as you see in Figure 5.
Figure 5 Update Row from Message (Close Figure 5)
UPDATE [TaskTable] SET ProcessStatus =
  @message_body.value('(/TaskStatus/ErrorNumber)[1]', 'bigint'),
  EndTime =
  @message_body.value('(/TaskStatus/Time/text())[1]','nvarchar(100)'),
  ErrorXML = @message_body
  WHERE TaskNumber =
  @message_body.value('(/TaskStatus/TaskNumber/text())[1]',  'bigint')
  AND    BatchNumber =
  @message_body.value('(/TaskStatus/BatchNumber/text())[1]', 'bigint')

Processing Timer Messages
To simplify the sample, the conversation timers use the same queue as the status messages so the timer messages are processed by the ProcessTaskStatusProc also. The ProcessTaskStatusProc handles timer messages by looking up the conversation handle that the message arrived on in the TaskTable and queuing a task in the TaskQueue. This part of the logic is the same as the QueueTasksProc logic for queuing tasks.
If the task has a cycle time specified, another conversation timer is started to kick off the next repetition after the cycle time. Cycle time is used for periodic jobs like log backups. Since the query reads CycleTime from the TaskTable for every repetition, you can update the table and change the period on the fly. If no cycle time is specified, the batch is terminated, as shown in Figure 6.
Figure 6 Using Cycle Time (Close Figure 6)
SELECT @BatchNumber = BatchNumber,@TaskNumber = TaskNumber,
   @CycleTime = CycleTime, @TaskMessage = (SELECT
   BatchNumber,TaskNumber,Command, NoTransaction
   FROM TaskTable Task WHERE Task.BatchNumber = A.BatchNumber AND
   Task.TaskNumber = A.TaskNumber FOR XML AUTO, ELEMENTS, TYPE)
   FROM TaskTable A WHERE A.ConversationHandle = @dialog;
-- Queue a message to start the job
SEND ON CONVERSATION @dialog
    MESSAGE TYPE [TaskRequest] (@TaskMessage);
if (@CycleTime is not null)
    BEGIN
        -- Start a timer for the next cycle
        BEGIN CONVERSATION TIMER ( @dialog ) TIMEOUT = @CycleTime;
    END
    ELSE BEGIN
        SEND ON CONVERSATION @dialog
            MESSAGE TYPE [EndBatch] (' ')
    END

Building and Testing the Sample
Execute the BatchProcessingSample.sql script using your favorite query execution tool: Management Studio or sqlcmd. This will create a TaskTable with a few sample tasks, the Service Broker metadata, and the following stored procedures, which work as their names imply: QueueTasksProc, ProcessTasksProc, ProcessTaskStatusProc, SetActivatedProcs, CleanupTaskQueue, and CleanupStatusQueue.
If everything is created successfully, you can queue up the test tasks and run the sample with:
EXEC QueueTasksProc
Give the tasks a couple of minutes to complete, then run the following query to see the results:
SELECT * from TaskTable
As you can see, the sample batch application isn't a fully featured system but it should give you enough of an introduction to how Service Broker can be used to control asynchronous jobs to get you started on your own projects. If you want to know how to get this functionality on more than one server, read the sidebar "Managing Tasks on Multiple Servers."
Managing Tasks on Multiple Servers
If you decide to use the Service Broker for batch task management, you will soon want to manage tasks on all the SQL Server installations on your network. The transparent distributed nature of Service Broker dialogs make this pretty straightforward to do. You will have to make some configuration changes for the dialogs to work remotely and change the QueueTasksProc to be aware of multiple TaskRequestServices, but the task execution and status parts of the job shouldn't have to change.
To enable a dialog between two SQL Server instances you will have to create Service Broker endpoints in both instances using the CREATE ENDPOINT command. You will also have to create routes in the databases that the dialog connects using the CREATE ROUTE command. For example, if the TaskRequestService lives on a server called WORKER and the TaskStatusResponseService lives on a server called MASTER, then the TasksDB on MASTER will need a route for the TaskRequestService on WORKER and the TasksDB on WORKER will need a route for the TaskStatusResponseService on MASTER. Be sure that these routes include the broker_identifier because you will need to use it to specify which of the many TaskRequestServices you want to send a task to.
In order to make the QueueTasksProc handle multiple remote task services, you can add a broker_instance column to the tasks table and then modify QueueTasksProc to include the broker_instance as part of the TO SERVICE in the Begin Dialog for each batch. You may also want to add WITH ENCRYPTION = OFF to the BEGIN DIALOG if you don't need to encrypt the dialog messages.

Roger Wolter is a Program Manager on the Microsoft SQL Server team responsible for the SQL Server 2005 Service Broker. He has over 25 years of industry experience—the last 6 with Microsoft. He can be contacted at rwolter@microsoft.com.


 SQL Server: On The Horizon: Improved Data Security In SQL Server 2005
SQL Server Don Kiely

On The Horizon: Improved Data Security In SQL Server 2005

Prerelease info in this article is subject to change.
At a Glance:
  • Secure design, default, and deployment with SQL Server 2005
  • New granular permissions
  • Separation of user, schema
  • Proxy accounts for special rights
SQL Server 2005
Security Policy
Permission Types

Security and data integrity have always been two of the most fundamental reasons to store data in a relational database. That was true when SQL Server 2000 was released, but the world of computing security has changed in ever nastier ways since then..
As a result, Microsoft has been talking a lot about the security issues they have grappled with during the design and development stages of SQL Server™ 2005. You can read all the details at the SQL Server Web site, but the following three points summarize the company's Trustworthy Computing initiative:
  • Secure by design
  • Secure by default
  • Secure in deployment
Microsoft has performed extensive threat modeling and security analysis to identify the threats facing database servers, resulting in a SQL Server 2005 that is secure by design. This means that you'll need a deeper understanding of security than ever before, but you'll have a rich set of tools with which to apply that knowledge.
Secure by default means that SQL Server 2005 is secure out of the box when you install it with default settings. Features that are not required by a basic database server are left uninstalled. The biggest impact is that you will have to spend a lot of time after initial installation turning on the features you require and installing additional components. It also means that because you have to do the work to hunt down the feature and install it or turn it on, most installations will have a smaller attack surface. Figure 1 shows a few of the features and their default status.
Figure 1 Default Status (Close Figure 1)

Service | Installed | Running
Analysis Services | No | —
CLR Integration | Yes | No
Database Mirroring | Yes | No
Debugging | Yes | No
Integration Services | No | —
Notification Services | No | —
Replication | No | —
Reporting Services | No | —
Service Broker | Yes | No
SQLeMail | Yes | No
SQLMail | Yes | No
Secure in deployment means that Microsoft is committed to providing all the information and tools you need to make intelligent decisions about deploying SQL Server 2005 securely. As you'll see, there are many more security choices than ever before, and making the wrong choices can too easily circumvent security features. Microsoft is also providing tools to manage security over time. For example, you can now install updates automatically. These initiatives have practical implications for administrators.

Password Policy
The first step in any attack is to get access to the server, exploiting any security weaknesses. SQL Server 2005 has plenty of features that make it more secure and easier to lock down after initial installation. One major change is that if you choose to continue using SQL Server logins, you can tie the password policy to Windows security. This requires Windows Server 2003™, but means you can finally enforce password strength and expiration policies without relying on homegrown solutions.

Granular Permissions
Probably the most sweeping change to the SQL Server 2005 security infrastructure is the new, highly granular permission model. Earlier versions of SQL Server frequently required an admin to grant a user membership in a fixed server role that had permissions far too broad for simple tasks. This violates the principle of least privilege and opens the door to abuse both by the user and attackers. Probably the most egregious example is that auditing used to require system admin (sa) privileges.
SQL Server still uses the basic grant, deny, and revoke permission states of earlier versions. In addition, the general permissions scheme still uses a grantee that receives a permission at either the server or database level and a securable that represents an object such as a table or entire database that needs protection. However, a login can now have permissions granted to it.
There are big changes in the level and type of permissions available, as well as a hierarchy of permissions that let you, for example, grant sweeping permissions to a user at the database level, and then surgically remove similar related permissions at the table level. (See the sidebar "Permission Types" for more information.)
One of the benefits of the newly granular permission scheme is that metadata is now protected as well as data. In earlier versions, a user with any access to a database was able to see the entire structure of the database, whether or not she could access the data within it or execute any stored procedures. Now SQL Server 2005 examines the permissions a principal has within the database and will only display a catalog view of the object if the principal is the owner or has some permission within the object. There is also a VIEW DEFINITION permission that can grant permission to view metadata information even without other permissions in the object.

Proxy Accounts
Like SQL Server 2000, SQL Server 2005 initially has a single SQL Agent proxy account used to access resources across the network. But, as with permissions, SQL Server 2005 provides a much more granular system for assigning rights to users and roles for executing SQL Agent jobs. Microsoft has also added additional subsystems you can use proxy accounts with, although the T-SQL subsystem still executes under the owner as it did in SQL Server 2000.
Members of the sa role, of course, can still do anything they want in any of the subsystems. Granting any other user rights to use subsystems requires the creation of at least one proxy account, which can grant rights to one or more subsystems.
Figure 2 shows how a proxy account, MyProxy, is assigned to multiple principals—here a user and a role. The proxy account uses a credential, which links it to an account, usually a domain account, with permissions in the operating system necessary to perform whatever tasks are required by the subsystem. Each proxy can have one or more subsystems associated with it that grant the principal the ability to run those subsystems.
Figure 2 SQL Agent Proxy Account
Figure 2 SQL Agent Proxy Account
Figure 3 shows the T-SQL code necessary to implement the scheme shown in Figure 2. It starts by creating a credential—a database object that, in this case, provides the link to the operating system account with rights to perform the desired actions in the subsystems. Then it adds a proxy account, MyProxy, that is really just a friendly name for the credential. Next, it assigns the proxy to two principals, in this instance a SQL Server login and a custom role. Finally it associates the proxy with each of the four SQL Agent subsystems.
Figure 3 T-SQL Code to Create a SQL Agent Proxy (Close Figure 3)
CREATE CREDENTIAL MyCredential WITH IDENTITY = 'MyDOMAIN\user1'
GO
msdb..sp_add_proxy @proxy_name = 'MyProxy', @credential_name = 'MyCredential'
GO
msdb..sp_grant_login_to_proxy @login_name = 'MyLogin', @proxy_name = 'MyProxy'
GO
msdb..sp_grant_login_to_proxy @login_name = 'MyRole', @proxy_name = 'MyProxy'
GO

sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_name = 'ActiveScripting'
GO
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_name = 'CmdExec'
GO
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_name = 'ANALYSISQUERY'
GO
sp_grant_proxy_to_subsystem @proxy_name = 'MyProxy', @subsystem_name = 'DTS'
GO
A proxy is not a way to circumvent security in the operating system. If the credential used with a proxy doesn't have a particular permission in Windows®, like writing to a directory across the network, the proxy won't have it either. You can also use a proxy to grant limited execution rights to xp_cmdshell, since it is a favorite tool used by attackers to extend their reach into the network once they compromise a SQL Server installation. The proxy provides this protection because even if the principal has unlimited rights on the network, as does a domain administrator, any commands executed through the proxy have only the limited rights of the credential account.

User/Schema Separation
SQL Server has long supported the full server.database.owner.object syntax found in the SQL specification for fully naming objects, like so:
SELECT * FROM MySERVER.Northwind.dbo.Customers
In this case, the server is MyServer, the database is Northwind, the owner is dbo, and the object of interest is the Customers table. If you have followed the Microsoft advice in versions through SQL Server 2000, all of your user-defined objects are owned by dbo, the database owner, which automatically becomes the owner when a member of the sa fixed server role creates the object.
If the SELECT statement you just saw runs on the MySERVER box and Northwind is the current database, you can leave off all of the prefixes and execute this statement instead:
SELECT * FROM Customers
This is because SQL Server automatically looks in the current server in the active database, then uses either the current user or dbo as owner of the object.
The simplicity of this kind of object reference belies an important simplification in versions through SQL Server 2000: despite what I said in the last couple of paragraphs, "dbo" in the first SELECT statement refers not to the object's owner but to the object's schema. A schema is an abstraction defined in the ANSI SQL specification as a kind of bucket object that owns other objects. Up to now users and schemas were essentially the same thing. When you created a new user, SQL Server would create a schema with the same name and assign any objects owned by the user to the schema.
In SQL Server 2005, users generally don't own objects. Instead, one or more users or roles own schemas, which in turn contain objects, as shown in Figure 4.
Figure 4 User/Schema Separation
Figure 4 User/Schema Separation
Besides being a purer implementation of ANSI SQL, the separation of users from schemas is significant for three reasons. First, it facilitates object ownership by multiple users or roles, making it easier to administer levels of permissions granted to those principals. Second, it makes an administrator's life far easier when a user who owns the object leaves the company or has a change of job responsibilities and is no longer required to own a set of database objects. No longer do you need to meticulously add a new owner and drop the old owner from each object. Now you can make those changes one time on the schema and be done with it, making ownership management a snap.
The third benefit again concerns least privilege. In the past, a user or role may have needed elevated privileges to work with objects owned by dbo, privileges that normally go far beyond what might be needed for users to make changes to their small part of a database. In SQL Server 2005, you can create whatever schemas you need to cover the database's objects and assign ownership of each schema only to the users or roles that require it to do their job.

Execution Context
SQL Server 2005 also allows far more granular and flexible permissions on execution contexts for stored procedures and user-defined functions, except inline table-valued functions. As part of a CREATE PROCEDURE you can specify the security context in which the procedure will run, whether it is in the context of the caller (the only option in SQL Server 2000 and earlier versions), as a specific user, as the user who created the procedure, or as the owner. The user context that is causing the stored procedure to run still needs to have execute permission, but once the procedure is running it is under the specified security context.
The security context of T-SQL code becomes critical with broken ownership chains, when the owner of the procedure does not own all of the objects accessed by the procedure. By specifying an execution context and thereby controlling the user under which SQL Server checks the ownership chain, you have a great deal of flexibility with how you define and manage object ownership.
The procedure or function's execution context is set as part of the CREATE PROCEDURE statement using the WITH EXECUTE syntax, as shown in Figure 5.
Figure 5 Execution Context (Close Figure 5)
CREATE PROC GetCustomersInCountry
    @Country nvarchar(15)
WITH EXECUTE AS 'User1'
AS
    BEGIN
        SELECT * FROM Customers
        WHERE Country=@Country
    END
GO
GRANT EXECUTE ON GetCustomersInCountry TO User2
GO
Once the procedure is created, User2 can execute the procedure but User1, specified in the EXECUTE AS clause, must have the required permissions on the Customers table. If the procedure calls other stored procedures, SQL Server would similarly enforce ownership rules by using User1, rather than User2.
You can use any of four forms of the EXECUTE AS statement:
EXECUTE AS CALLER can be used when you have an unbroken ownership chain or if you have good control over ownership management.
EXECUTE AS USER = 'User1' or the shorter version EXECUTE AS 'User1' is used when you have centralized ownership in User1. This simplifies ownership management by not requiring you to manage many users and their ownership.
EXECUTE AS SELF is a shortcut for the user who created the procedure. The actual user ID is saved in the catalog. This form probably will not be used often since it would be easy to fragment ownership depending on how you log into SQL Server.
EXECUTE AS OWNER can be used when the security context should be the current owner of the procedure, which cannot be a role. If there is no qualified owner, the owner of the module's schema is used as the security context.
When using the EXECUTE AS 'User1' form, the user executing the procedure must satisfy at least one of the following conditions to prevent the use of execution context to circumvent security:
  • He must be a sysadmin or db_owner
  • He must have CONTROL SERVER permissions on server or the database
  • He must have IMPERSONATE permission for username
The trickiest part of managing the developers and administrators who create procedures with execute context switches is first building a solid ownership foundation on which to change the execution context. With that foundation in place you'll be able to keep tight control over who can do what in accordance with the principle of least privilege.

Encryption
It is arguable whether or not data stored in a secure database on a secure server in a secure network needs to be encrypted. After all, if no unauthorized users can get at the data, why bother using the many processing cycles required for encryption, particularly for many fields in a huge table?
The problem is that according to the security principle defense in depth, you have to assume that attackers will penetrate all walls of defense you erect around a valuable resource. Every layer of defense makes it less likely that an attacker will win the prize, and data encryption is a final level of defense against database attackers.
SQL Server 2000 and earlier didn't provide data encryption within the database, but you could do it yourself, using external COM components, for example. Third-party encryption products are also available for you to use. SQL Server 2005 provides full, internal support for data encryption using custom keys, digital certificates, or pass phrases.
Data encryption is easy enough using the mature encryption libraries, such as those built into the .NET base class library and the Win32® CryptoAPI. The difficult part is managing the keys and storing them safely and secretly. SQL Server 2005 can provide key management for you or you can take on the job yourself. Consistent with the new granularity of permissions, even individual users can encrypt data so that only they can decrypt it.
Say that you have a Customers table with a Social Security number field called SSN. This is the kind of data that you have a strong obligation to protect. Since it is going to hold cipher text, the SSN field is defined as a varbinary(60) field. In this case I'll use a certificate generated by SQL Server and symmetrical keys. Because the encryption is contained totally within SQL Server, which manages the keys, there is no need to transmit the key across the network, allowing safe use of symmetrical keys.
The first step in the encryption process is to create the master key used in the database for encryption, a digital certificate, and the symmetric key used for the data encryption (see Figure 6).
Figure 6 Create Master Key (Close Figure 6)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'myPassword'
GO
CREATE CERTIFICATE myCert AUTHORIZATION User1
    WITH SUBJECT = 'User1Cert'
GO
CREATE SYMMETRIC KEY User1Key
    AUTHORIZATION User1
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY CERTIFICATE User1Cert
GO
You can then use the symmetric keys to encrypt the data in the SSN field. The code in Figure 7 assumes that you are connected to the database as User1 or have switched security contexts to that user.
Figure 7 Open Symmetric Key (Close Figure 7)
OPEN SYMMETRIC KEY User1Key USING CERTIFICATE User1Cert
GO
INSERT INTO Customers
    VALUES(1, EncryptByKey(Key_GUID('User1Key'), '987-65-4321'))
INSERT INTO Customers
    VALUES(2, EncryptByKey(Key_GUID('User1Key'), '123-45-6789'))
CLOSE ALL SYMMETRIC KEYS
For simplicity, this example uses only the CustID primary key and the encrypted SSN field, and ignores the data for other fields in the table.
The code in Figure 7 uses the new T-SQL EncryptByKey function to encrypt the data inserted using the User1Key created earlier. You could also use the EncryptByCert to encrypt the data using a certificate or EncryptByPassphrase to use a phrase that SQL Server uses to create a key. Any user that tries to read the data, even with full access permissions on the table, will be unable to read the encrypted data.
Retrieving and decrypting the data follows similar steps, using the DecryptByKey function, as shown here:
OPEN SYMMETRIC KEY User1Key
    USING CERTIFICATE User1Cert
GO
SELECT CustID, CONVERT(varchar, DecryptByKey(SSN)) as SocialSecurity
    FROM Customers
CLOSE ALL SYMMETRIC KEYS
The CONVERT function call is necessary because DecryptByKey returns varbinary data rather than textual data.
This simple example barely scratches the surface of the encryption features that are available in SQL Server 2005. As implied by the code, you can create a complete hierarchy of keys that secure the objects and keys below it. You can also create unlimited keys and certificates that are associated with various users and roles.

Conclusion
As an administrator charged with the security of your database servers you're really going to like SQL Server 2005. Of course, migrating existing databases will take some work from a security perspective, a change more akin to the move from SQL Server 6.5 to 7.0 rather than SQL Server 7.0 to 2000. But with the granular permissions scheme, SQL Agent proxy accounts, data encryption, execution contexts, and other changes I've covered here, you have all the tools that are necessary to make your database servers truly resilient against today's class of attacks.
Permission Types
The following are some of the new permissions you can set in SQL Server 2005 and what they accomplish.
CONTROL Confers owner-like permissions that effectively grant all defined permissions to the object and all objects in its scope, including the ability to grant other grantees any permissions. CONTROL SERVER grants the equivalent of sa privileges.
ALTER confers permission to alter any of the properties of the securable objects except to change ownership. It inherently confers permissions to ALTER, CREATE, or DROP securable objects within the same scope. For example, granting ALTER permissions on a database grants that user permission to change its tables. Granting ALTER TRACE grants auditing permissions.
ALTER ANY <securable object> confers permission to change any securable object of the type specified. For example, setting ALTER ANY ASSEMBLY grants the right to change any .NET assembly in the database, while at the server level granting ALTER ANY LOGIN lets the user change any login on that server.
IMPERSONATE <login or user> confers permission to impersonate the specified user or login. This permission is necessary to switch execution contexts for stored procedures.
TAKE OWNERSHIP confers the permission to the grantee to take ownership of the securable objects.

Don Kiely, a Microsoft MVP, is a senior technology consultant who focuses on security. When he isn't writing database software, he's writing about technology, speaking about it at conferences, and training others. Reach him at donkiely@computer.org.


 Exchange for Experts: Be The Master Of Your Domain Rename With Exchange
Exchange for Experts Steve Schiemann

Be The Master Of Your Domain Rename With Exchange
At a Glance:
  • Preparing for a domain rename
  • How to rename a domain
  • Troubleshooting domain migrations
Active Directory
Exchange Server
Domain Controllers
Windows Server 2003

Systems administrators have always assumed that after you deploy a Microsoft Windows forest for your organization, its topology cannot be changed. Without potentially complex and time-consuming reinstalls and domain controller promotion or demotion operations, this was true, at least until the release of Windows Server™ 2003.
You might make such changes for political considerations, mergers, or acquisitions—but you shouldn't take it lightly.
A tool called rendom.exe in the \VALUEADD\MSFT\MGMT\DOMREN directory on the Windows Server 2003 CD, allows you to rename an Active Directory® domain. There are certain limitations, however, which you can read about at Windows Server 2003 Active Directory Domain Rename Tools.
Domain rename operations are a serious business and entail extensive planning and lab work before implement-ing this process in production. Domains can be renamed in place, or you can restructure the existing topology. Rather than provide a step-by-step guide, I'll offer an overview to help you decide if you want to proceed.

Prerequisites
There are some prerequisites for domain rename operations from a Windows Server perspective, and more from a Microsoft® Exchange Server perspective. One specific Windows Server consideration is that Active Directory must be in Windows Server 2003 forest mode. This means that all domain controllers must be running Windows Server 2003, and the forest functional level needs to be changed using the Active Directory Domains & Trusts Microsoft Management Console (MMC) snap-in. Figure 1 shows the old and new domain structures.
Figure 1 Old and New Domain Structures
Figure 1 Old and New Domain Structures
Unfortunately, from the time Windows Server 2003 was released until the release of Exchange Server 2003 SP1, some users of Exchange Server experienced problems after renaming one or more domains. The Exchange System Attendant service would not start and the domain had to be renamed with the original name, or Exchange Server reinstalled from backup. The Exchange Server 2003 tools download site now has an Exchange Server Domain Rename Fixup tool which allows Exchange Server to function after a domain rename.
When using the Exchange Server Domain Rename Fixup (XDR-fixup) tool, there are a number of Exchange Server prerequisites to consider:
  • All Exchange Servers in the organization must be Exchange Server 2003 SP1 or later. This also means no Exchange Server 5.5 can exist in the org. This includes intra-org Certificate Authorities (CAs) and Site Replication Service; the Active Directory Connector (ADC) service supports only inter-org CAs.
  • Exchange Server 2003 can only be installed on member servers, not on Domain Controllers (DC).
  • Domain rename will not rename the Exchange Server org.
  • Exchange domain rename will not let you merge two Exchange Server orgs (from different forests) into a single Exchange Server org.
  • In other words, XDR-fixup does not replace or extend the functionality of the Windows Server 2003 domain rename tools. XDR-fixup is a script that modifies certain Exchange Server attributes after a domain has been renamed so that Exchange Server can function.
If you meet these prerequisites, have a solid reason for renaming one or more domains in your forest, and like to live on the edge, then the tool combination of rendom.exe and xdr-fixup could be useful to you. Take a look at the sidebar "Renaming a Domain" for the steps involved.

Domain Controller Rename
Quite often companies that rename their domains will also want to rename their domain controller for consistency. If you do rename domain controllers, there are some minor extra steps that must be taken for full Exchange Server functionality. This is a separate process from renaming the domain. After the domain rename, your domain controllers will still have the old domain suffix. If the old domain was Contoso.com, after the random process all domain controllers in the renamed domain will still be called serverx.Contoso.com.
Domain Rename Resources
  • Windows 2003 Rendom Page
  • Windows 2003 Domain Rename Webcast
  • Exchange Server 2003 and Domain Rename Operations
  • Exchange System Attendant Does Not Start After You Rename a Windows
  • How To Use the Exchange Server Domain Rename Fix-up Tool
  • Exchange and Domain Rename Operations
  • When You Rename Your Domain Content Indexing Does Not Work in Exchange
  • How Domain Rename Works
  • Supplemental Steps for Using the Exchange Server Domain Rename Fixup

Member servers will have the new domain suffix, say serverx.NorthwindTraders.com. For more, see "Rename a domain controller" in Windows Server 2003 Server Help and Support Center, which is found at Start | Help | Support.
If you rename DCs, you must point the Recipient Update Service to the newly renamed domain controller. Until you update this configuration, the Recipient Update Service (RUS) will log warnings/errors 8033, 8201, 8284, 8264, and not function correctly. Choosing the domain controller for the RUS is easy using the properties of each RUS. Browse and select the new domain controller name. You can find more detailed instructions for working with the Exchange Recipient Update Service at How to work with the Exchange Recipient Update Service.
If you have statically configured any DSAccess domain controllers via the Directory Access tab from server properties in Exchange System Manager, or directly in the registry, you will have to hardcode them again after they have been renamed. The old fully-qualified domain name (FQDN) of the server will be cached and will need to be updated after you rename domain controllers. The same goes for clients that might have global catalog servers configured in the registry.
Next, check the message queues on each Exchange Server. If messages appear to be stuck, stop the System Attendant service and the SMTP service on the server, and then restart them in any order. Renaming a domain will cause Content (full-text) Indexing to malfunction. However, the Exchange Server MSSearch Administration Tool (which you can download by visiting Downloads for Exchange Server 2003) can be used to resolve this problem.

Troubleshooting
Occasionally the entire rendom/xdr-fixup process doesn't go smoothly. In these cases, the trace file generated by xdr-fixup has been useful. With this output file, you can search for errors such as "Did not convert attribute <attribute>:<attributevalue>". This file output, in combination with an ldifde.exe dump of the Exchange Server organization container, has led to successful Exchange Server functionality after the domain rename process.
And don't forget XDR-fixup—it can make life much easier. Although far from effortless, successful domain renaming is possible as long as certain requirements are met. Check out the additional resources for more information.
Renaming a Domain
One important tool you'll use when renaming a domain is the command-line tool XDR-fixup. You can type "XDR-fixup /?" at a command prompt to see the available switches. The Exchange Server Domain Rename Fix-up.doc (installed with XDR-fixup) gives a brief explanation of these switches. Sample syntax is also shown in the document. The XDR-fixup tool represents just one step in the domain rename process.
First, the tool generates an LDIF file. Next, you import this file manually into Active Directory with ldifde.exe. This will modify certain Exchange Server attributes so they reference the new domain name. You can look at the LDIF file and see exactly what is changed before you perform the import. You definitely want to use the /trace switch when running XDR-fixup since this generates a very useful log file. Finally, verify the changes with XDR-fixup. If the corrections.ldf file is 0 bytes, there are no corrections that need to be made.
The XDR-fixup tool can be run anytime after the rendom /execute step is run, but it's usually run immediately after. Be sure not to use the RTM version of rendom.exe, because it has been updated since then to fix a potential issue with replication. Use the version found at the link I referred to earlier.

The Process
What follows is a view of the entire process performed from a single control station, a server running Windows Server 2003 that is a member of the forest. Steps in your labs and production environments will be more detailed:
  1. Log on as an administrator with full Active Directory and Exchange Server permissions.
  2. Copy Rendom.exe, Gpfixup.exe, and XDR-fixup.exe (all command-line tools) to a folder such as C:\Rendom on the control station. All of the commands related to renaming a domain will be issued from this command prompt at this control station.
  3. Open a command prompt to C:\Rendom and type "rendom /list" (see Figure A).
  4. Open Domainlist.xml in Notepad, and save it as BackupDomainlist.xml.
  5. Edit Domainlist.xml in Notepad to reflect the new domain name.
  6. At the command prompt, type "rendom /upload". Wait for at least 15 minutes (or more, depending on your Active Directory configuration) to allow for a significant amount of Active Directory replication.
  7. The rendom /upload command also generates the state file in the same directory (DcList.xml) that is used to track the progress of the domain rename operation. Verify in DcList.xml that the state of all DCs is set to "Initial", then type "rendom /prepare".
  8. Verify in DcList.xml that all domain controllers are set to the "Prepared" state and type "rendom /execute".
  9. Check DcList.xml. The state of all domain controllers should be set to "Done" or (hopefully not) "Error".
  10. Type "xdr-fixup /s:backupdomainlist.xml /e:domainlist.xml
    /trace:tracefile.txt /changes:changes.ldf /restore:restore.ldf".
  11. Import the changes noted in changes.ldf by inputting "ldifde -i -f changes.ldf" at the command prompt.
  12. Verify that the changes were made successfully by running "xdr-fixup /trace:tracefile2.txt /verify:changes.ldf /changes:corrections.ldf".
  13. Reboot member servers twice.
  14. Enter "gpfixup /olddns:OldDomainDnsName /newdns:NewDomainDNSName
    /oldnb:OldDomainNetBIOSName /newnb:NewDomainNetBIOSName
    /dc:DcDnsName 2>&1 >gpfixup.log".
  15. Finally, at the command line, type "rendom /clean".
Figure A Saving a Description of the Forest Structure
Figure A Saving a Description of the Forest Structure
If possible, you should also plan on having staff standing by in all locations where you have Exchange Servers, just in case something goes wrong and you need to perform a hard reset. Better safe than sorry.

Steve Schiemann has been working with Exchange Server in Microsoft Product Support Services for over seven years. He is now on the Exchange Server administration specialty team.


 Exchange for Experts: Migration, Active Directory, And You: A Guide for Exchange Admins
Exchange for Experts Nino Bilic

Migration, Active Directory, And You: A Guide for Exchange Admins
At a Glance:
  • The concept of SID history
  • Migration of accounts between domains with SID
  • Overview of ADMT
  • Using ADMT with an Exchange migration
Exchange Server
Active Directory
Migration
Security

If you're a Microsoft Exchange Server administrator you should learn about the Active Directory Migration Tool (ADMT). It is, after all, an Active Directory service tool and not an Exchange Server utility.
In my experience, however, the account migration process has caused trouble for many mail admins. In this article, I'll look at migrations from Exchange Server 5.5 to Exchange 2000 Server and Exchange Server 2003. Version 2 of the ADMT, which was released with Microsoft® Windows Server™ 2003, has some extremely useful functionality that can simplify this process.
I work in Microsoft Support Services, so I step in after things have gone wrong. So my goal is to prevent mistakes in the first place. While many customers use non-Microsoft products rather than ADMT, I will focus on ADMT with an overview of its place in an Exchange Server migration and how it helps achieve Windows NT® account migration.

What is ADMT?
ADMT 1.0 was released with Windows 2000 Server; version 2.0 shipped with Windows Server 2003. You can get this latest version from the Windows Server 2003 Web site. You can use the ADMT to migrate users, computers, and groups from one domain to another. This sort of migration is associated with a migration from a Windows NT 4.0 domain to an Active Directory® domain, usually as part of a move from Exchange Server 5.5 to Exchange 2000 Server or Exchange Server 2003.
One of the major improvements in the new version of the ADMT is its ability to migrate user passwords as well as accounts, making the whole process much easier. However, there are situations in which the length and complexity of the previous password does not satisfy the policy requirements of the Active Directory service. In such cases users will have to change their passwords to meet the new requirements.

Using ADMT
Running the Active Directory Migration Tool is a six-step process:
  1. Run Setup for the Active Directory Mi-gration Tool (available on the Windows Server 2003 CD at \i386\ADMT).
  2. In Control Panel or on the Start menu, click Administrative Tools, then click the Active Directory Migration Tool.
  3. When the window opens, right-click ADMT, and click User Account Migration Wizard. (Some options might be unavailable until you perform a successful migration for some accounts.)
  4. Click Next on the initial page of the User Account Migration Wizard.
  5. Select either "Test the migration set-tings and migrate later" or "Migrate now." This is helpful if you are testing your migration before changing your production environment (a recommended best practice). Select the appropriate option and click Next.
  6. Choose the domains to be used in your migration. The domains that are available are determined by your current forest and any trusts that you have in place.
Note that the target domain must be running in Native mode to use the ADMT. If you are running the ADMT for the first time, there are several tasks that it must perform to ensure that your migration is successful. These include, but are not limited to, testing prerequisites, modifying registry entries on the Windows NT 4.0 primary domain controller (PDC) and restarting it, and modifying the security policy on Windows 2000 Server or Windows Server 2003.
The tasks that the ADMT performs depend on the options and types of domains that you have chosen. The ADMT Wizard will prompt you for all the necessary info throughout the migration process. Be sure to keep the readme file included on the CD close at hand during this stage. The documentation can answer questions about the required configuration and prerequisites.

Migration with the SID
The Windows® permission model is based on user security identifiers (SIDs) rather than user names. An SID is a long number that uniquely identifies a user whenever he tries to access any resource in the domain. This makes it possible for you to rename a user's domain account without having to reassign user permissions. This is also why, if you delete a user, you can't simply recreate the user with the same name to allow access to resources for which he previously had permissions. The SID of the new account will not match the SID of the old account, and you will need to reassign permissions for the new account.
The multi-valued sIDHistory attribute is added to the target account being migrated, and is populated with the SID of the original account, from the source domain.
Assume that Domain A is your source domain and Domain B is your destination domain, as shown in Figure 1. If the account is migrated with SID, the migrated account in Domain B will have an attribute called sIDHistory that holds the SID of the original account from Domain A.
Figure 1 Migration with SID Option
Figure 1 Migration with SID Option
There are at least two reasons why it is necessary to use SID History. The presence of the sIDHistory attribute on the migrated account in Domain B allows the account to access any resources for which the original account (in Domain A) had rights. Here a resource is anything with permissions, such as an NTFS share or a printer.
The Exchange Active Directory Connector (ADC) uses the SID in the sIDHistory attribute when matching the Exchange Server 5.5 directory object to the Active Directory account. Let's consider the following scenario. A machine running Exchange Server 5.5 is in Domain A and the primary Windows NT account of the Exchange Server 5.5 mailbox is the account in Domain A. This account is migrated using the ADMT and the SID History. An account is created in Domain B that has the SID of the Domain A account stamped on its sIDHistory attribute. Next, an ADC Recipient Connection Agreement is set up from the machine running Exchange Server 5.5 in Domain A to a DC in Domain B. When the ADC runs, it checks the primary Windows NT account of the Exchange Server 5.5 mailbox and reads its SID. It finds a match in Active Directory because the SID History is stamped on the migrated account. If no SID History was present on the Domain B account, the ADC could create a new disabled account and append a "-1" to its name. For more about this issue, see Knowledge Base article 256862, "XADM: How to Correct Mismatched Accounts After Active Directory Connector Replication".
When you view the sIDHistory attribute in Active Directory using an LDAP tool such as LDP.exe, the attribute will look something like the following:
1> sIDHistory: S-1-5-21-1619521004-1441481110-1935294565-1315;
If you see an attribute on the user account that looks like this, then the SID History is present. If the attribute is missing or marked "Not set" (if you are viewing it using ADSI Edit, for example), the SID History was not migrated for that specific account.
Note that the SID listed on this attribute will match the SID of the original account from the original domain (the domain from which the account was migrated). The migrated Active Directory account will have its own unique SID. This is important to understand. You do not end up with two accounts in two domains that have the same SID. The Active Directory account has its own SID as well as the SID of the "original" account stored in the sIDHistory attribute.
To migrate accounts with the SID, you must select the checkbox titled "Migrate user SIDs to target domains" on the Account Transition Options page of the ADMT.

SID History and EDMW
After your accounts have been migrated from Domain A to Domain B with the SID option selected, you might want to address another issue. Exchange Server 5.5 mailboxes are set up to have Domain A's Windows NT accounts as the primary Windows NT accounts, but the ADC associated those mailboxes with the Active Directory (migrated) accounts based on the SID History.
Optionally, if you want your users to start logging on using the new migrated Active Directory accounts and access the mailboxes that were not yet moved from Exchange Server 5.5, you should use the Exchange Directory Migration Wizard (EDMW) to simplify things. If you did not, later on (when you run ADC and create a Recipient Connection Agreement), the ADC will still match the Exchange Server 5.5 mailbox with the account that was migrated with ADMT using sIDHistory. If you use EDMW first, however, your permissions will be cleaner.
To switch the Exchange Server 5.5 primary Windows NT account to the new migrated Active Directory account, you must run the EDMW; this option becomes available after you migrate the accounts.
The main role of the EDMW is to switch the primary Windows NT account of the mailbox. It should then set the Access Control Lists (ACLs) on the Exchange Server 5.5 directory objects to replace/add/remove (depending on which option you choose) any mention of the Windows NT 4.0 account with the new migrated Active Directory account. Using this wizard, you replace all references to the source account with the migrated account (with the same permissions) on all objects stored in the Exchange 5.5 directory. After you run EDMW, your users will start logging into their Exchange Server 5.5 mailboxes with newly migrated accounts in Active Directory. Next time the users log onto the domain, they should choose the Active Directory domain as their logon domain and use the name and the password of their Active Directory account.
In Control Panel or on the Start menu, click Administrative Tools, then ADMT. When the window opens, right-click the ADMT, then click User Account Migration Wizard. Click Next on the initial page of the User Account Migration Wizard.
At this point, select either "Test the migration settings and migrate later" or "Migrate now." Select the appropriate option and click Next. On the Security Translation Options page, you can select the following tasks to perform using the Exchange Directory Migration Wizard: Replace, Add, and Remove. Replace causes any mention of the original source domain account to be replaced with the target domain account, with the same permissions. Add leaves the source domain account, but also adds the target domain account, with the same permissions. Remove causes the source domain account to be removed after you run the Add option discussed earlier. After making your selection, simply complete the remainder of the wizard, which will modify the Exchange Server 5.5 directory.

Migration with EDMW Only
Because the EDMW can switch the primary Windows NT account on the Exchange Server 5.5 mailbox to be the migrated account from the target domain, you can be certain that the correct match will be found when the ADC is run.
In this situation, you might not need to migrate the SID on accounts. This would be helpful for organizations that have already migrated Windows NT accounts, but have not migrated them with SID, and now need to complete the migration.
There could be situations in which you need to access resources in the Windows NT domain with the Active Directory account, in which case the SID History would be necessary. Using EDMW will resolve access issues only on Exchange Server 5.5 directory objects—it will not help with any source domain NTFS permissions or other resources that the user from the target domain might need to access after the account was migrated. If you choose to use the EDMW and not migrate the SID History, take the following steps:
  1. Migrate the accounts using the ADMT, but without the SID History.
  2. Run the EDMW and in the Exchange Server 5.5 directory replace the security references of the source domain account with those of the destination domain (migrated) account. As a result, among other things, the Primary Windows NT accounts of the Exchange Server 5.5 mailboxes will be changed to the new migrated accounts in the Active Directory domain.
  3. Replicate the Recipient Connection Agreement in the Active Directory Connector snap-in. Because the new migrated Active Directory account is now the primary Windows NT account of the mailbox, the ADC should find the match and no duplicate objects should be created.
You should note that duplicate accounts might still be created if multiple mailboxes are associated with the same Windows NT account on the Exchange Server 5.5 side. Running the EDMW does not resolve this problem. Use the ADC Tools Wizard to avoid this problem if you are using the Exchange Server 2003 ADC. If you are using the Exchange 2000 Server ADC, see the NTDSNoMatch Utility at Documentation for the NTDSNoMatch Utility.
For additional information see: ADMT docs at Windows Server 2003 Tools, Active Directory Migration Tool 2.0 Interactive Simulation, and the Support Webcast at Domain Migration Using the Microsoft Active Directory Migration Tool.

Nino Bilic is a Technical Lead for Microsoft Exchange Administration at the Las Colinas, Texas support site. He posts to the Exchange team blog at blogs.msdn.com/exchange.


 From the Editor:
From The Editor

A few months ago, we launched the first issue of TechNet Magazine, the Microsoft magazine for IT pros. At the time, we were cautiously optimistic about what we were doing, but we were also anxious to see how the people who make everything run—the IT superheroes—would react to the new publication that ended up on their desks.
We were pleased to find that by all accounts, the first issue was a valuable and much appreciated tool. It is against this set of raised expectations that we provide this, the second issue of TechNet Magazine. Over the past months, we have talked to hundreds of IT pros just like you. This second issue builds upon our many discussions to provide more in-depth articles on the products and technologies you're using at work today.
Most of you are either administering Active Directory or are planning to move to it soon. We've put together a wide range of material to help, from articles on the top new group policy settings in Windows XP SP2 to migrating thousands of mailboxes in Exchange Server.
We also heard from a number of DBAs and postmasters about what they would like to see in the magazine. Thanks to these discussions, we have more coverage on SQL Server and Exchange Server in this issue as well. Of course, we also continue our focus on security, as you'll see throughout the issue.
SQL Server 2005 is fast approaching RTM. We know that adoption doesn't happen overnight, but it's always handy to know what's on the horizon when the CIO calls you in and asks you to explain it.
Many of you told us that you'd like to see more utilities you can use at work. As part of our mission is to help you do your job, we're making our Utility Spotlight a regular feature, and this issue we bring you a handy, customizable app that will let you limit the number of concurrent logins on an Active Directory domain as well as a few other neat things.
If you're the IT guy at a small company, a couple of our articles might be of special interest to you. They contain some lessons that I, unfortunately, learned too late. Several months ago, I was at the local electronics superstore and purchased a new 250GB hard drive for only $99. What kid wouldn't love a quarter terabyte? I took it home, moved everything on my kids' machine to it, and had an extra 200GB to work with. Everything was fine for a couple of months.
Last week, the machine wouldn't start. When I turned it on, it went into a permanent reboot loop. I inserted the Windows XP CD and went to the recovery console. The big drive didn't show up at all. Even though hardware's not my game, I followed some instructions I found online, ran fixmbr, tried some other commands, and I got the drive back! Of course, the file names in the directory had been replaced with entries that look like comic book swears. Everything's gone, and it's not coming back.
If only I had taken Jay Shaw's or Robert Walker's advice. In this issue, they'll help you back up your machine and set up a RAID server. I lost a stack of photos and newsletters the kids had created. True, your company's data may not have the same sentimental value, but you know how valuable it is. If you're the IT superhero for your company, make this the year that you implement full backup and redundancy policies. Don't learn the hard way—those system failures can be career kryptonite.—J.T.

Thanks to the following Microsoft technical experts for their help with this issue: Kahren Allakhverdyan, Nino Bilic, Henry Chan, Girish Chander, William Chow, Charles Chung, Eric Deily, Jeetendra Falodia, Oscar Omar Garza Santos, Eric Hanson, Ken Henderson, Christian Kleinerman, Kevin Lam, Peter Larsen, Bill Long, Vikas Malhotra, Andrew Moss, Michael Murphy, Greg Nicholson, Matt Nunn, Michael Rys, Brian Sabino, Dmitry Sarkisov, Steve Schiemann, Dan Stevenson, Cristian Teodorescu, Don Vilen, Dan Winn, Rik Wright, Christoph Zelazowski, and Jason Zions.

Active Directory, ActiveX, Microsoft, MSDN, Outlook, PowerPoint, Visual Basic, Visual Studio, Visio, Windows, Windows NT, Win32 and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Other trademarks or trade names mentioned herein are the property of their respective owners.

Microsoft Corporation makes no representation or warranty, express or implied, with respect to any code or other information herein, and disclaims any liability whatsoever for any use of such code or other information.


 Letters:
Letters to the Editors

More Interop!
It's about time that a tech magazine showed the "how to" and "why" for Windows administrators. I'd like to see issues that cover Application Center in detail in the future. Also, please show ways to integrate Windows Active Directory and Novell eDirectory. Leave the Novell bashing to the marketing guys.
David James

Thanks for the tips. We know what it's like out there in the real world, and we're going to make sure that interoperability is a key component of TechNet Magazine.

Hands Off My Copy
I am reading this first issue cover to cover again and I am amazed at how much info it offers concerning security. I took my copy to a meeting in our state capital today and almost lost it. So many of the members of the meeting wanted it for its content that I almost didn't get it back.
Jim Vaughan

While we are as committed to reducing governmental waste as the next magazine, there are three simple solutions for colleagues who are clamoring for your copy of TechNet Magazine. First, they can go to www.technetmagsubs.com and fill out a qualification form. Second, they can look for it on a quality newsstand (which we define as "any newsstand that carries TechNet Magazine"). Third, if they are a TechNet program subscriber in the United States, they can receive a copy at no charge.

First Time for Everything
I received your TechNet premiere issue and I want to thank you for the first magazine that has provided me with useful information.
Bill Depew

Thanks for the kind words, but we can't believe you've never received any useful information from any magazine before. We sometimes go a full month eating only recipes first printed in Highlights for Children.

Anatomy of a Hack
In the article "How A Criminal Might Infiltrate Your Network" by Jesper Johansson, I had some trouble reproducing some of the commands that were used—"discoverHosts" for example. Are these custom tools you guys created?
I'm asking because I was using the information in the article to test out the security of our own network. I just assumed I would be able to reproduce the steps Mr. Johansson had clearly written.
Alexander Klein

Jesper responds: Yes, you are correct that discoverHosts is a custom tool that I wrote for the article. There are a number of them throughout the article, and to avoid making life too easy on criminals, I cannot give those away. Glad to hear you liked the article, though. Make sure you have permission to test the security of your network before you do anything that is done in the article.

Where's DAVE?
In looking through Jay Shaw's article on securing your Mac on a Windows network, I only saw sparse coverage of DAVE. I wish this was put to the forefront a little more for a Classic Mac solution, given that SFM is difficult to use for several reasons. You cannot use existing share structure, you compromise security with weaker password encryption, and you require either registry tweaks or group policy updates to get it working in Windows Server 2003.
Joaquin Menchaca

Talk To Us!
Do you have something to say about a TechNet Magazine article? Got something you want to tell or ask us?
Drop us a line at tnmag@microsoft.com. Please include your name and where you're writing from. Letters may be edited for length and clarity and may be published in any medium. Letters become the property of TechNet Magazine.

Jay responds: I tried to cover as much as I could in the article. I do think that DAVE is an excellent solution. It provides support for DFS and allows a Classic Mac to act as an SMB file/print server on Windows networks, and truth be told it is more secure than Services for Macintosh (SFM). Still, I like AdmitMac a little better because of its integration into Active Directory. WhileDAVE does support Active Directory, it does not require a domain login. Of course, ifyou're supporting the Classic Mac you can't use AdmitMac.
I like SFM mainly because it's free, but you're right in that it can be a little tricky to administrate. DAVE certainly does make life a little easier. Most companies upgrading Macintosh systems these days are also moving to OS X, so why not use AdmitMac? DAVE does work with both platforms but, if you're going to spend the budget, why not go all the way?


 Toolbox: New Products For IT Professionals
Toolbox
New Products For IT Professionals
Marnie Hutcheson

Utilities
View and Manage All Types of Files
www.gpsoft.com.au
Directory Opus 8.0 by GPSoftware is a file manager that lets you view and manage all types of files, whether on your hard drive, across your network, in .zip files, or over FTP. Directory Opus 8.0 supports all the standard Windows® Explorer functions and options plus additional features like visual synchronization for files and folders, duplicate file finder, dual folder trees, fully customizable commands and styles tab bar, and an SMTP mailer for sending files directly from the viewer.
Directory Opus 8.0 lets pre-Windows XP users have all the seamless view and management capabilities for image files, media files, and .zip files that Windows XP users get (Directory Opus 8.0 runs on all Windows OSs, Windows 95 and later). Most types of image formats are supported so you can view your images in several different ways. Directory Opus 8.0 has an independent full-screen viewer that gives you slide show style viewing. You can also convert images between types on the fly.
Putting your cursor on top of a digital image or other media file will cause its complete EXIF properties set to be displayed in a popup window, along with a thumbnail. This hard to come by metadata can tell you when the picture was taken, camera make and model, aperture, shutter speed, subject distance, metering type, and size.
The Windows Explorer-style interface is easy to figure out and you can personalize the entire interface including the menus and toolbars; you can even create your own custom commands. You can print directory listings and change file attributes, but my personal favorite feature (besides the file synchronizer) is that it remembers the folders I visited recently so that when I open it up, it starts in the right place.
Price: Single user, $59.00. Dual, multiple, and corporate install licenses are available.

Great-Looking Screen Shots the Quick and Easy Way
www.ec-software.com
TNT Screen Capture 2.1 by EC Software creates screen shots that let you make your point and, in fact, look better than the original screen. It has several built-in image filters and add-ons that give your screen shots that professional photorealistic look.
TNT Screen Capture 2.1 has all of the cool stuff that you want in your screen shots. It automatically detects window shapes such as the rounded corners on Windows XP and captures them, complete with the transparency right where you need it. You can select several frame styles; for example, if you want the image to have a shadow, just select the shadow option before you make the screen shot. The torn paper effect is one of my personal favorites. You can include the cursor in the image, and if you really want to call some attention to what you are clicking on, you can add several types of "sparkle" to your cursor just by selecting it in your Capture Options.
There are three different screen capture modes that let you capture everything from application interfaces to popup menus. The only thing you can't capture easily is a screen shot of TNT itself. It is lightweight, and lightning-quick to load and use. I use TNT Screen Capture for all my screen shots because the shots look great and I don't have to mess with them after they're captured.
Price: A single-user license is $44.90.

Don't Buy More Hard Drives—Clean House
www.foldersizes.com
FolderSizes 3.0 by Key Metric Software is a network-enabled disk analysis tool. This utility collects folder and file information and uses it to produce interactive visual disk analysis reports that you can export complete with graphs as XML and HTML. As the name suggests, the most obvious type of analysis is size, but FolderSizes 3.0 actually generates 10 different reports that let you do things such as analyze the characteristics of your drives and directories in addition to identifying who owns what files, and disk space usage by file type.
I was concerned with cleaning up the duplicate directories that my company was accumulating as people simply copied entire directories onto the server instead of only performing incremental backups which would be more efficient. Folder Sizes 3.0 does a good job of cleaning up all the duplicate directories.
You can use FolderSizes 3.0 to identify and remove oversized, outdated, temporary, and duplicate files. You can use the reports generated to create manifests and identify storage requirement trends. In addition to the reports, FolderSizes 3.0 creates a text-based directory tree when you analyze folders or drives, and you can save this tree along with the report that you are generating.
The FolderSizes main window shows you folder sizes, the number of child folders and files, folder and file size on disk, and items consuming more than a specified percentage of the parent are dis-played in bold. File attributes are displayed using configurable colors. You can customize on the options menu just about everything FolderSizes does. FolderSizes offers full support for all versions of the Windows operating system including Windows 95 and later.
Price: A single-user license is $34.95.

Enterprise Monitoring, Helpdesk, and Inventory
Helpdesk and Network Management Services
www.microforge.net
Enterprise Server 1.0 by Microforge.net Limited lets you provide helpdesk and IT management services for multiple networks from any speed of WAN/Internet connection using Microsoft® Internet Explorer or one of the Enterprise Server Windows clients (Ent Net Center and Ent Report Forge). It offers five integrated functions: helpdesk, inventory, calendar, reporting, and administration.
The Ent Net Center client gives you access to administration, networks, inventory functions, helpdesk, and calendar. Administration lets you manage your Enterprise Server settings, including user access rights, group memberships, and helpdesk configuration. The Networks function automatically builds and maintains an inventory of your company's IT equipment and software, with no deployment necessary. You can add non-networked machines to the inventory by entering the information manually.
Besides supporting the helpdesk function, the inventory reports let you track the service history for your IT assets and helps you identify licensing issues. The integrated e-mail client automatically assigns helpdesk ticket numbers to e-mail support requests and tracks progress on them. The Calendar function lets you manage tasks and activities, and facilitates a collaborative work environment for team members anywhere.
Management and productivity reports are provided through Ent Report Forge. The 30 standard reports include helpdesk performance, changes in IT assets, and software licensing issues. You can also create your own custom reports.
Price: $595 for a one-person IT department with a single network, $1,195 for a 10-person IT department on multiple networks and in multiple locations.

An Asset Management and Inventory Solution
www.misutilities.com
If you are just looking for Asset Management and Inventory, rather than the whole help desk package, Network Asset Tracker 2.5, by MIS Utilities might be your solution. Network Asset Tracker is based on Windows Management Instrumentation (WMI) technology so it gathers information about users, connections, running processes, software on individual computers, and software quantity on the entire network.
Network Asset Tracker inventories operating system type, version, and build; service packs and installed hotfixes, and size and free space of HDD volumes. It recognizes processor type, name and type of HDD, CD-ROM, FDD, video card, monitor, printers, and multimedia devices.
It is a client-free utility. The Network Asset Tracker report module can generate reports about selected PCs or the entire network. These reports can be exported to HTML, XML, Microsoft Excel, Visio®, XML Drawing, or RTF files. Network Asset Tracker runs on Windows 98 and later.
Price: Starting at $99 (10 PCs) to $999 (corporate license).

A Real-time Production Monitor
www.avicode.com
If you've always wanted a real-time production monitor that would flag an exception or application error and instantly pop up a window with all the details right down to the state of the user, location within the call stack, the variables, the preconditions, and the offending line of code, then Intercept Studio, and its Management Pack for MOM 2005, by AVIcode is something you might find interesting.
Intercept Studio extends the functionality of Microsoft Operations Manager (MOM) 2005 with two run-time agents that automatically detect and monitor .NET-based Web and Windows Forms applications in the production environment.
The exception monitoring agent, Xmon, collects detailed root cause information about application failures including the complete call stack object and source code. When an exception is reported, the failed function is highlighted in the report and hyperlinked to the variable(s) with the parameter that caused the exception. When you follow the links, Visual Studio® .NET opens to the offending line of code. Intercept Studio will even tell you how often this event is occurring. You can configure Intercept Studio to look at unhandled errors, handled errors, and critical errors. They can be grouped by specific class of error, and you can create exemptions for specific errors.
Pmon, the real-time performance monitoring agent, pinpoints performance bottlenecks in distributed business transactions down to the individual Web service, .NET Remoting, ADO.NET, Lightweight Directory Access Protocol (LDAP), SQL, or external resource request call, and then visually displays the critical path within an application. Pmon identifies performance bottlenecks and reports slow operations, along with the specifics. Pmon does hot spot performance reporting by issue and occurrence, giving you metrics you can use to prioritize development projects.
Critical event information generated by Intercept Studio agents is automatically reported to its console, the Intercept Structured Event (SE) Viewer, which has the ability to feed this information to MOM using the Intercept MOM Management pack. Event information can be sent to other WMI-compliant systems and several issue tracking systems as well. Once an event has entered the SE Viewer, system administrators have access to detailed information about software performance for future reference.
Price: A perpetual license for the initial server (includes the SE Viewer, Pmon and Xmon) is $9,995. Additional server agents are $2,495.

Book Reviews
www.syngress.com
Dr. Tom Shinder's Configuring ISA Server 2004 (Syngress Publishing), which is a revision of the earlier book Internet Security and Acceleration (ISA) Server, gives you a look at the history of firewalls from the earliest proxies to today's ISA Server 2004. It discusses the feature set of ISA Server 2004, compares it to other firewall offerings, introduces the concepts underlying ISA Server 2004, and discusses preparations and considerations for setting up and supporting ISA firewalls.
Client types are also covered along with automated provisioning, installation and configuration essentials, and how to set up remote access and site-to-site VPNs with ISA firewalls. Other topics include stateful inspection and application layer filtering, and accelerating Web performance with ISA Server 2004 caching.
Price: $49.95, 1,013 pages.
Inside The SPAM Cartel by Spammer X was recently released by Syngress Publishing. I found the first few comments rudimentary; after all, most of us know better than to respond to spam, but then I started reading the sidebars and I got hooked. I never would have imagined all the nasty and underhanded things that Spammer X talks about.
The book takes you on a tour through the mind and methods of spammers, covering lots more than just the technical details of how to configure filters and clean mailboxes. Spammer X is quite candid about spammer ploys, angles, hooks, and dirty tricks that honest folks would never dream of, but definitely topics that administrators need to understand. So I recommend it—if you only learn one new thing, you could still save your company tons of expense.
The book starts with a look at how spammers think, and an explanation of spam as an industry, and why it has become a preferred method of advertising for many companies. Then it moves into the more technical details of how spammers take over proxy servers and SMTP relays, how they scan networks to identify security flaws that they can exploit, how they use phishing and other techniques to bait their traps, and the domains most often spoofed in their scams.
Once Spammer X has explained the mission, the book shows you how to use forensics to track a spammer's activities and build a fingerprint from the spam they send and the techniques they use. It covers the rules of the CAN-Spam Act so you get a better understanding of where the "teeth" are in the law. And it discusses how to combat spam with Exchange Server and Outlook®.
Finally, there is a sobering discussion of how VoIP Spam May Be the Next Big Thing: VoIP creates an IP address for your phone number, meaning most of the spammer's e-mail techniques can be readily ported causing you even more trouble.
Price: $49.95, 432 pages.

All prices were confirmed at press time and are subject to change.
Marnie Hutcheson is president of Ideva, a firm that specializes in Web application design, development, and hosting. She has published a variety of technical papers and books on various computing topics. You can reach her at marnie@ideva.com.


 Utility Spotlight: Limit Login Attempts With LimitLogin
Utility Spotlight
Limit Login Attempts With LimitLogin

Download the code for this article: LimitLogin.exe (4,112KB)

Ever needed to limit concurrent user logins in an Active Directory® domain? Ever wanted to keep track of information about every login in a domain? If so, LimitLogin is for you.
LimitLogin is an application written by Yossi Saharon, a Partner Technology Specialist with Microsoft in Israel, with help from Ofer Bar, an application development consultant. The application adds the ability to limit concurrent user logins and to keep track of all login information in an Active Directory domain. LimitLogin capabilities include limiting the number of logins per user from any machine in the domain (including Terminal Server sessions), displaying the login information of any user in the domain according to specific criteria, easy management and configuration through integration with the Active Directory Microsoft® Management Console (MMC) snap-in, the ability to delete and log off a user session remotely straight from the Active Directory Users and Computers MMC snap-in, and the ability to generate login information reports in CSV and XML formats.
While the main purpose of LimitLogin is to enforce concurrent login quotas, it can also be used purely as a login data capture solution that lets you manage your Active Directory environment more effectively. You can configure all users in the domain to have an unreachably high login quota and simply let the scripts do the work of updating your login data, without reaching the quota that was set. The UI tools allow you to set the login quota, and you can do so programmatically using the sample script code provided with the tool in Bulk_LimitUserLogins.vbs. You can also scope this script to an Organizational Unit level. The default script runs on all of the user accounts in the domain.
LimitLogin's architecture is built around three main elements:
  • A Web service that handles the back-end processing on the server
  • An application directory partition that holds the login information
  • Login and logoff VBS scripts
Figure 1 Validating a User Login
Figure 1 Validating a User Login
When a user logs on to the domain, the llogin.vbs file runs and sends the host machine's data (computer name, IP address, session ID, and authenticating DC name) to the LimitLogin Web service as XML, using SOAP. The Web service uses the client's security context against Active Directory and checks to see if this user is configured for LimitLogin and has a login quota in the LimitLogin application directory partition.
If the user does not have a login quota set, then the Web service notifies the script that it should continue to log in normally. If the user does have a login quota in place, then the Web service counts the number of registered logins the user has collected in the LimitLogin application directory partition. If the user's login quota is less than the actual number of logins registered in Active Directory, then the Web service updates the user's login information in the LimitLogin application directory partition and notifies the login script to continue login normally. If the user's login quota is equal to or exceeds the number of logins registered in Active Directory, however, then the Web service notifies the login script to log off the current session. This process is outlined in Figure 1. A related process happens with llogoff.vbs when a user logs off from the domain.
While some similar solutions require SQL Server™ to work, LimitLogin uses your Active Directory database. It creates an application directory partition on a domain controller in the domains for which you want to use the app. LimitLogin supports Windows 2000 Professional Service Pack 4 and later, Windows 2000 Server Service Pack 4 and later, Windows XP Professional Service Pack 1 and later, and Windows Server™ 2003. You can download LimitLogin from the link at the top of this article.


 SQL Q&amp;A: Troubleshooting Connections, Using Variables, And More
SQL Q&A
Troubleshooting Connections, Using Variables, And More

Connection Problems
Q I have been unable to connect to my server via Enterprise Manager or Query Analyzer, either by SQL Server™ login or network login (I am an administrator on that server, and usually I'll log in using my network credentials). The server is definitely running since the Web sites that interact with it are functioning correctly. What might be wrong?

A There are a number of things you can try. If you recently upgraded the server to SP1 of Windows Server™ 2003, or did an upgrade to a more recent build of SP1, then the new security settings will have locked down all the ports until someone acknowledges the install at the console, and even then you may have to reopen the ports for SQL Server.
See if you can successfully ping the machine that SQL Server is running on. This confirms network connectivity from you to the server, although it's possible to close the ICMP ports and stop this. If you can ping the server, then you need to test SQL Server-specific connectivity between you and the SQL Server instance.
If you're using named pipes, you can use the command-line tools makepipe and readpipe to check that you can communicate with SQL Server. After checking this, try using OSQL, or SQLCmd to connect. If you are using Windows Authentication, try something like this:
sqlcmd –S<server name> -E
If you cannot connect using SQLCmd, but can ping the server, then you need to get a Terminal Server session onto the SQL Server itself, check the firewall settings, and confirm that SQL Server is running. SQL Server talks on port 1433 by default so you need to make sure that this port is still open. If all this fails, then you will have to check the port that SQL Server is listening on, and then go back to the firewall and ensure that port is open.

Merge Replication and HA
Q Can I use merge replication in a high transaction or High Availability (HA) scenario when the transaction rate may be 100 transactions per second (tps)?

A Yes you can, but keep in mind that merge uses trigger-based change tracking so there is overhead associated with it. There are definitely shops that are running more than 100 tps in an HA scenario, so it can be done. If you have only two servers and no conflicts, you can consider transactional replication, which can perform bidirectional replication, resulting in higher throughput in this sort of scenario.
There are some caveats, though. With transactional replication, bulk updates (updates that affect 1,000 rows in a single update statement) will be replicated as 1,000 separate transactions on the subscriber database. This can negatively impact performance. Also, setting up reverse replication (after you fail over) will take time and might be an issue. With merge replication for an OLTP system, there is no transactional consistency maintained. The database can get into an inconsistent state during failover to this replicated database.
A benefit of transactional replication is that your 1,000-row update example can be replicated as a single stored procedure execution. Also remember that merge replication moves changes row by row. Transactional replication preserves transaction boundaries, so you won't accidentally apply half of a transaction. However, both are asynchronous so there is the possibility of inconsistency due to the latency between the nodes. In SQL Server 2005, merge has the ability to define a logical relationship between tables, so either all changes to a group of tables are replicated or none are.

SQL Server Memory
Q I have SQL Server 2000 set up to dynamically manage memory. The minimum memory value is set to 0, and the maximum memory value is set to 7+ GB. The machine has 8GB of physical memory. If Address Windowing Extensions (AWE) is not enabled, will SQL Server be able to allocate more than 2GB?

A No, if AWE is not enabled, SQL Server can only see up to 2GB. However, if you enable /3GB in boot.ini, SQL Server can then see up to 3GB. SQL Server will only take advantage of the 8GB memory if you use /PAE, /3GB in boot.ini, and enable AWE. Make sure you set Max memory to about 6.5GB, or so.
The following article would be a good resource to understand SQL Server memory usage: "How to configure memory for more than 2GB in SQL Server".

Clauses and Performance
Q I have a query in a stored procedure that performs well when I supply a constant in the WHERE clause, but poorly when I use a variable with the same value. I have an index on the column in the WHERE clause. What is wrong?

A You are probably getting a table or clustered index scan rather than a seek on your index because the value of the variable you're using is not known when the plan for the procedure is compiled. Because the value of a variable declared within a procedure is not known at compile time, you can't use an index's statistics histogram to calculate the number of rows that will be returned by a predicate that filters on the variable, and instead must use hardcoded number estimates based on the comparison operator specified. For "=", the estimate is 10 percent, for ">" and "<", it is 30 percent. The only exception to this is an equality test; in this case, the density for the index is used (rather than the magic numbers), but not the histogram.
The best solution here is to pass the filter value as a parameter to the stored procedure and use the stored procedure parameter to filter your query. This way, the value of the parameter can be known at compile time and the plan can be constructed accordingly.
If the value you pass into the proc when it is first compiled is atypical, you may get a plan in the procedure cache that does not perform optimally for the majority of values passed into the proc. Investigate using the WITH RECOMPILE option (covered in the Books Online) to remedy this.
Another thing to watch out for here is data conversion: make sure the procedure param and the column to which it's being compared are the same data type. If the data types differ, you'll likely see a CONVERT operator in the execution plan, and the local variable filter caveats apply.
See Chapter 15: "The Query Processor," section "Compilation and Optimization" of Inside SQL Server 2000 by Kalen Delaney (Microsoft Press, 2000) for more information.

Storing Photos in SQL
Q Is it advisable to store photos in SQL? I think it might be better to store only pointers to the file names in a SQL Server database and to move the storage of BLOBs to a file system.

A The answer depends on how many photos you have, how frequently they change, how large they are, what the application using them is, and so forth. On terraserver.microsoft.com, there are 330 million BLOBs ranging in size from 5KB to 40KB. Approximately 1.6 million a day are added or deleted. The about page on the site explains how it's done. The images are physically all the same size—200 x 200 pixel GIF or JPEG, and they average 10KB compressed. This number of images would be very difficult to manage in the file system. TerraServer often gets 300 to 1,000 simultaneous users and has an average of 25,000 to 55,000 site visitors each day.
A good rule of thumb is if there are less than 1 million images, or if there are big images (greater than 1MB), put them in the file system. For more than 1 million images of sizes less than 1MB, put them in SQL Server. For everything in between, either way will work; you just need to pick the issues you'd rather wrestle with.
SQL Server gives you more ways to index an image and store other data related to the image nearby. If you have a farm of Web servers, it is easier to manage all the images in one central store than keeping a bunch of Web servers up to date. SQL Server is great at managing millions of files and is optimized for smaller files. Plus, there is support for validating the consistency of the database, indices, backup, restore, and so on.
Copying and moving images around the file system is trivial. The file system will deliver raw bytes off the disk the fastest, plus getting them in and out of SQL Server requires some custom code. Without a doubt, the file system will be able to move bytes off the disk and down the wire (to a Web server, for example) faster and more efficiently than SQL Server 2000. SQL Server is pretty good at getting the bytes off disk and has good memory caching schemes, but the protocol used to transmit data over the wire (tabular data stream, or TDS) wasn't designed for BLOBs.
Another option is to store the images in both the file system and the database. The database is the "reserve copy" that is backed up with the metadata in case the file somehow gets lost, and the file system is the place where the application looks for the sake of efficiency. If you lose the file, the app can query the database for the BLOB and send it down the wire.
There is some discussion of this in the SQL Server 7.0 Resource Kit, Part 3 "Database Administration," Chapter 11 "Using BLOBs".

Thanks to the following Microsoft professionals for their technical expertise: Tom Barclay, Ken Henderson, Koce Ivanov, Lucien Kleijkers, and David Vest.


 How IT Works: Decoding TCP/IP
New information has been added to this article since publication.
Refer to the Editor's Update below.

How IT Works
Decoding TCP/IP
Don Parker

TCP/IP is comprised of a set of protocols including Transmission Control Protocol (TCP), User Datagram Protocol (UDP), Internet Protocol (IP), and Internet Control Message Protocol (ICMP). Most IT professionals understand how these protocols work together to make network communication possible. They also know that IP is used for routing and that TCP is a transport protocol. However, it's not as well known what the various fields mean in a standard TCP/IP packet. Let's dive into the details.
A TCP/IP connection begins with a three-way handshake, used to initiate communications between computers, and is composed of three packets. This handshake usually begins with a synchronize (SYN) packet sent from a client to a server, followed by a synchronize/acknowledgement (SYN/ACK) packet from the server back to the client, and finally an acknowledgement (ACK) packet from the client back to the server. The final ACK packet is optional depending on the operating system you are using.
Let's look at and decipher an example TCP/IP packet, shown in the figure. This is the first step in the three-way TCP/IP handshake. A SYN packet like this is used to initiate communication and synchronize sequence numbers.
[ Editor's Update - 5/5/2005: This was captured using the tool WinDump. Other utilities will most likely format and augment the results differently. For example, the timestamp discussed in the following section is inserted by WinDump and is not actually included in the data sent across the wire.]
09:09:52.283919
This is the time that my computer actually received the packet (not the time that it was sent). This time is accurate to the microsecond as represented by the six numbers after the last decimal point.

xxx.xxx.xxx.xxx.10459
Following the IP address 192.168.1.100 is the number 10459. This is the source port on the source computer that sent this packet.

>
The greater-than symbol shows the flow of communications, namely that the IP address to the left of the symbol is talking to the one on the right of the symbol.

192.168.1.200.80
This is the destination computer's IP address (192.168.1.200) as well as the destination port (80). You can infer that the client computer is trying to initiate communications with a Web server, since port 80 is the default used by HTTP servers such as IIS to listen for client connections.

S
The "S" that follows the IP address confirms this is a SYN packet.

[tcp sum ok]
This is also known as "TCP Checksum OK," and conveys that the checksum—as calculated by the source computer—is valid. When the source computer sends a packet, it adds to the packet the result of running a mathematical formula called a checksum over the contents of the packet, and it embeds the results into the packet's header. When the packet is received by the destination computer, the destination computer performs the same calculation on the received data. If the destination finds that its calculated checksum is the same as that embedded in the received packet's header, the packet will be processed.

3153110659:3153110659
This long string of numbers is the TCP sequence number. The number in parentheses that follows the sequence number represents the amount of data bytes sent. In this case, the value is zero as a SYN packet contains only a header and no data. In other words, no data has been sent as the two computers have not yet completed the three-way handshake.

win 32768
Window size is represented by win 32768, meaning the client computer has a window size of 32768 bytes. Also commonly known as the receive buffer, this metric is normally controlled by the application in use and indicates to the receiving computer the number of bytes that the client is willing to receive unacknowledged (once the source computer sends an acknowledgement to the destination computer, the destination can then send more data). In this case, we've surmised that the client is starting up communications with a Web server, so it is likely that this window size is that of Microsoft® Internet Explorer. If the server sends more information than the maximum allowed by the window size, the client may simply drop the extra packets due to a full buffer.

<mss 536>
Maximum segment size is represented by the number within the angle brackets. This value, as sent by the source computer, tells the destination to send no more than 536 bytes of data in any one packet. The Maximum Transmission Unit (MTU) for Ethernet is 1540.

(ttl 63, id 60989, len 44)
The value ttl 63 represents the packet's time-to-live, which defines the number of router hops that can be made before this packet will be dropped. In this example, 63 more router hops can be made before the packet will be dropped by the next router in line. When a router receives a packet with a ttl of 1, it will drop this packet and send an ICMP error message back to the originator. This prevents packets from flying around endlessly if they cannot reach their destination. I know that the source computer is running Windows® 2000 Professional, which has a default ttl of 128. That means that this packet went through quite a few hops before this capture was taken.
The TTL on the source computer had been changed from the default to a much lower number, meaning that the number of hops was significantly less than is implied.
Next is id 60989, the number assigned to the IP header. This number is used for packet fragmentation purposes. Should a packet need to be split into multiple pieces by a router, each fragment that is generated will have the same IP ID number assigned to it. The destination computer needs this to be able to reassemble them. Were this packet actually fragmented, there would also be a value called the fragmentation offset included (there's no use having fragments if you don't also know in what order they need to be reassembled).
Most current operating systems have the DF (don't fragment) bit set by default. If a router receives a packet with the DF bit set, and that packet is too big for the router to route, then the router will simply discard the packet, since the router would need to fragment it to pass it along. This will, in turn, cause the router to generate an ICMP error message, which it will send back to the originator of the packet to let it know.
Lastly, there is len 44, which represents the overall length of the packet. This includes not only the byte count of TCP and IP headers, but also the data sent, if any. Everything is measured in bytes.
At the end of the packet is a mass of hexadecimal values as well as some ASCII content. While this may seem cryptic, it actually isn't—all you are seeing is the TCP and IP header information of the packet that was just examined, but this time in a hexadecimal representation. You can, if you choose, decode these hexadecimal values yourself instead of letting the computer do it for you, but doing so is not for the faint of heart.

Don Parker works in a high-assurance environment where he is employed as a network security analyst. He has been a guest speaker at various computer security conferences, and holds the GCIA and GCIH certifications. His Web site is www.bridonsecurity.com.


 Beta Box: Discover SQL Server 2005 Express Edition
BetaBox
Discover SQL Server 2005 Express Edition
Brian A. Randell
Prerelease info in this article is subject to change.

One of the most promising updates to a Microsoft product this year is SQL Server™ 2005 (formerly known by its code-name "Yukon"). SQL Server 2005 will come in a variety of versions, supporting many different types of deployments from the desktop to the enterprise datacenter. At this point, Microsoft has announced two SKUs—the Developer Edition, and the Express Edition—and the pricing for the Express Edition. The price, you ask? Free.
So if it's free, it won't be very interesting, right? Wrong. SQL Server 2005 Express Edition is full of features for the single user, workgroup, and the enterprise. See the "Express Features" box.
SQL Server 2005 Express Edition has been designed to be a core component of many different types of solutions. The desktop user gets a reliable, enterprise-grade data store that has easy-to-use GUI tools. The workgroup gets shared access to data with unrestricted concurrent access and industrial-strength data safety. Even the enterprise benefits from having a component that can integrate into a myriad of applications as well as be a reliable data store for administrative data.

Personal, Workgroup, and Enterprise Component
Express Features
  • No workload governor
  • Can have up to 50 instances
  • Can make use of up to 1GB of RAM
  • 4GB database size limit
  • GUI tools as a separate download
  • .NET Framework 2.0 is required
  • Supports Windows 2000 Service Pack 4, Windows XP Service Pack 1, Windows Server 2003 (including Web Edition)

As a personal and workgroup database, SQL Server 2005 Express Edition is a natural choice for a relational database store. The removal of the workload governor from previous versions, known as the Microsoft® Database Engine (MSDE), allows concurrent database operations without any performance penalty. In addition, each machine can have up to 50 distinct SQL Server 2005 Express Edition instances installed with each one supporting thousands of individual databases. This eliminates worries about ISV applications and your own internal apps impacting each other during installation, use, or removal.
As an enterprise component, SQL 2005 Express Edition plays well when integrated into large-scale solutions that include the full version of SQL Server 2005. SQL Server 2005 Express Edition uses the same core SQL Server engine, which includes the query optimizer, advanced locking, and transaction support. This provides your developers with access to all of the existing data management and manipulation features in SQL Server 2005 such as views, stored procedures, and triggers.
In addition, features specific to the 2005 version, including integration with the common language runtime (CLR) and a new XML data type, provide a two-fold advantage. First, applications built on top of SQL Server Express don't need specialized data access code for the desktop and workgroups and a different set for line-of-business applications. Second, applications that push the limits of SQL Server 2005 Express Edition can be transparently upgraded to the full-blown versions of SQL Server 2005.
The ability to integrate as part of enterprise solutions is particularly evident in two features of SQL Server 2005 Express Edition—replication and Service Broker. As a subscriber, SQL Server 2005 Express Edition supports both transactional and merge replication, both long-time SQL Server features. Transactional replication allows you to have synchronized copies of data via a publisher-subscriber model. As changes are made to the publisher, they are propagated transactionally to the subscribers, ensuring data integrity. In addition, filters can be applied to allow you to send only certain sets of data to specific subscribers. This can reduce the load on your main servers, especially for read-only operations.
Merge replication is useful when you want multiple subscribers to be able to update data at various times and have these updates propagated back to publishers as well as other subscribers. It's handy in many scenarios in which you want to allow satellite locations to work with data at any time and have their changes synchronized with publishers and other subscribers.
Service Broker, a new feature being introduced with the release of SQL Server 2005, lets you write asynchronous, queued applications without using an external product such as Microsoft Message Queuing (MSMQ). The need is easy to understand. Many database-centric applications try to do too much in a single transaction or operation. Take for example a typical e-commerce transaction that runs when a customer buys a book online. If you wrote a list of all the business processes that have to occur from the time the user clicks submit until the book is actually shipped, your list could include well over 20 tasks. The problem is that often almost all 20 or more operations are attempted in the period of time between the click of the submit button and the return of the response page. In years past, one way this problem was solved was by using technologies such as MSMQ and SQL Server 2000 all wrapped in a distributed transaction. In such a solution, only operations that have to take place in real time—creating the order number, verifying payment—are performed in real time. All other operations take place asynchronously via queues and databases.
An alternative to this solution is to build the transactional messaging process and run it all inside the database using Service Broker to manage it. By doing this you can increase transaction throughput, store data and messages in a central location (providing centralized management and data recovery), and have a uniform programming model. SQL Server 2005 Express Edition can participate in Service Broker conversations, making it a cost-effective technology when you're already using SQL Server 2005 for your primary database needs. (For more information about Service Broker, see Roger Wolter's article at A First Look at SQL Server 2005 Service Broker).

Management
From a manageability standpoint, SQL Server 2005 Express Edition is like any other version of SQL Server 2005. When deployed, a local copy of the SQL Computer Manager is installed. This new tool allows you to manage all types of SQL Server installations. And for the first time, Microsoft is going to ship a GUI tool to let you create databases and their objects, execute queries, and back up and restore databases. The tool is called Express Manager (XM) and it will be an optional component of SQL Server 2005 Express Edition installation.
While GUI tools are nice, if you have hundreds or even thousands of installations, you'll be happy to know that SQL Server 2005 Express Edition is a good command-line citizen. First there's SQLCMD, which is designed to not only replace its predecessor, OSQL, but to go beyond it. Extensions to the T-SQL language allow SQLCMD to perform a variety of tasks including database backup. In addition, SQL Server 2005 Express Edition is easily controlled from COM scripting languages like VBScript via the new exposed management interfaces—SQL Management Objects (SMO).
Express Edition Resources
  • Express Editions: Get a Lean, Mean Dev Machine with the Express Editions of Visual Basic and SQL Server 2005
  • Express Beta Products Site
  • Visual Studio Developer Center
  • Microsoft SQL Server Home Page
  • SQL Server 2005 Express Edition Beta 2 Page

According to Microsoft, their GUI tools are being built on top of SMO. So whatever they can do, you can do. In addition, don't forget that SQL Express can be a data store for your own administrative applications. You could easily write a script that uses Windows® Management Instrumentation (WMI) to gather machine information on a daily basis, store the data in a SQL Server 2005 Express Edition database using ActiveX® Data Objects (ADO), and then run reports against the data over time. If you need more power, the new Visual Studio® 2005 Express Editions, such as Visual Basic® 2005 Express, provide full-blown development environments, at a very affordable cost and size, to build more complicated management applications that require a GUI or compiled binaries.

Deployment and Servicing
Management is one thing, deployment and servicing is another. This is another area that has received a lot of attention from Microsoft. Installation is through Microsoft Installer (MSI) files—no more merge modules that prevent you from servicing using Microsoft updates. SQL Server 2005 Express Edition is designed to support updates via Windows Update to make it easier to get updates out to a large audience.
Getting your databases installed is easy too. Not only does SQL Server 2005 Express Edition support traditional connection strings, but a new option, called AttachDBFileName, will auto-attach and register a database if it has not already been used. This significantly reduces deployment headaches and enables XCopy deployment of applications and their databases.
SQL Server 2005 Express Edition is a free, easy to use and administer database engine. At its core is SQL Server 2005—an industrial strength, enterprise-capable Windows data platform.

Brian A. Randell is a senior consultant with MCW Technologies, a Microsoft Certified Partner specializing in custom apps development with .NET, SQL Server, and Office. He can be contacted via his blog at www.sqljunkies.com/weblog/brianr.


 Post Mortem: Migrating 240,000 Mailboxes And 29 Domains
Post Mortem
Migrating 240,000 Mailboxes And 29 Domains
Dennis Swenson

The Project:
I recently participated in a project with a large agency of the United States federal government as they migrated and consolidated their servers from Exchange Server 5.5 to Exchange 2000 Server and Exchange Server 2003. In order to perform the migration and consolidation, an easy-to-manage process needed to be developed to synchronize the Exchange Server 5.5 directory with the agency's existing Active Directory® forest.

Challenges:
  • The Exchange Server 5.5 organization consisted of 272 Exchange Server 5.5 sites spread across the United States (including Alaska and Hawaii), Puerto Rico, and the Philippines—a very large project.
  • A Huge number of Exchange Server 5.5 objects needed to migrate: approximately 240,000 mailboxes, 60,000 distribution lists, and 48,000 custom recipients.
  • The agency's existing Active Directory forest consisted of 29 domains.
  • The migration and consolidation project spanned multiple years, so the directory synchronization process needed to be easily supportable throughout the life of the agency's Exchange Server migration project.
  • The agency's three administrative branches each had their own security concerns, management styles, and business functions, which had to be accounted for when developing the synchronization solution.

The Plan:
Following guidelines in the Microsoft Operations Framework (MOF) and Microsoft Solutions Framework (MSF) , a solution using Microsoft Identity Integration Server (MIIS) was to be evaluated in a test lab and then moved to limited pilot deployment for evaluation in the production environment. Barring any insurmountable technical difficulties, MIIS then would be deployed into production to begin the Exchange Server 5.5 to Active Directory forest directory synchronization process preceding the Exchange Server migration and consolidation project. One of the planning team's main goals was to limit the size of the server farm required to perform directory synchronization to a manageable number, preferably a single server if possible.

The Consultants:
Microsoft Consulting Services (MCS) partnered with AC Technologies Inc., an IT integrator and solutions and services provider for government and military agencies. This consultant team would perform the overall technical planning, testing, deployment, and management of the directory synchronization solution throughout the life of the Exchange Server migration and consolidation.

Background:
At the onset of the Exchange Server migration and consolidation project, approximately 80 percent of the users had been migrated to the Active Directory forest in a separate project. Therefore, the directory synchronization solution had to be able to integrate and readily handle the instances where users of Exchange Server had not yet migrated to the new Active Directory forest.
As the legacy Exchange Server 5.5 organization was a single entity, the synchronization process needed to be treated as such. That is to say, it would be nearly impossible for one agency branch to use the Active Directory Connector (ADC) to synchronize their portion of the Exchange Server 5.5 organization while another agency entity used a third-party Exchange Server migration tool that did its own style of directory synchronization. A one-size-fits-all solution was a definite requirement.

Procedure Analysis
Before beginning, let's take a quick high-level view of the MIIS component setup, shown in Figure 1. You can think of the MIIS metadirectory as a conglomeration of two subcomponents: the metaverse (the center circle) and the connector space (represented by boxes on either side of the metaverse). The conduit that connects the MIIS metadirectory to the directory of interest is called a management agent. I will refer to the diagram in Figure 1 throughout the discussion as the components listed in the diagram are defined and discussed in the following paragraphs.
Figure 1 MIIS Component Structure
Figure 1 MIIS Component Structure
In order to read the entire Exchange Server 5.5 organization (remember, that's 272 sites) and still design an easily managed solution that could run on a single machine, the team had to develop an alternative to building a synchronization connector to each Exchange Server 5.5 site.
MIIS has many out-of-the-box connectors or management agents, including two management agents specialized for Exchange Server 5.5 directory connection. One of these Exchange Server 5.5 management agents is called the bridgehead management agent. This agent allows MIIS read/write access to a single Exchange Server 5.5 site while also allowing read-only access to the entire Exchange Server 5.5 organization, providing for organization-wide object discovery. This management agent is only able to write to the Exchange Server 5.5 site it is connected to, much like the Exchange Server 5.5 management UI.
When configuring a management agent required tasks include defining the objects and determining which object attributes will be copied in with the object. The agency decided that they wanted the Exchange Server 5.5 directory to own and propagate the mailbox attributes pertaining to personal and business information to the corresponding associated user object in the agency's Active Directory forest. This included attributes like departments, titles, street addresses, phone numbers, and postal addresses. All Exchange Server 5.5 mailbox objects pertinent to the migration were discovered and automatically placed in a special section of the MIIS database (a series of SQL tables) called the Exchange Server 5.5 bridgehead management agent connector space. Because the objects were from Exchange Server 5.5, MIIS was instructed to "project" the objects into another special area of the MIIS database called the metaverse. The metaverse is where MIIS is able to perform most of the identity management tasks when two or more disparate directories are involved.
Like the Exchange Server 5.5 management agent, MIIS also has a management agent specifically designed for connection to an Active Directory domain. This Active Directory management agent was configured to connect to different "partitions" in the Active Directory forest. For the purposes of this discussion, a partition is equivalent to an Active Directory domain in the forest. The user objects were discovered and placed into their own particular Active Directory management agent connector space of the MIIS database. Therefore, Exchange Server 5.5 management agent has its own connector space separate from the Active Directory management agent's connector space. This became the staging area for each management agent.
From here, the Active Directory management agent was configured to find a mailbox in the MIIS metaverse whose associated Windows NT® account security identifier (SID) matched the SID on the Active Directory user object (plus matching on first and last name as well). When a successful match was made, the Active Directory management agent was instructed to join the Active Directory user object to the corresponding mailbox in the MIIS metaverse. After this association is made, MIIS can freely flow mailbox attribute information out to the user object in Active Directory.
That briefly covers how user objects and mailboxes are matched and managed. In similar fashion, all distribution lists and custom recipients in the legacy Exchange Server 5.5 organization were discovered by the Exchange Server 5.5 management agent and projected up from the Exchange Server 5.5 connector space into the metaverse. Since there is no representation of these objects in the Active Directory forest, MIIS must create (provision) these objects in the forest so Exchange Server 2003 users can see these same objects in their version of the Global Address List (GAL), which, for Exchange Server 2003 users, is based on the Global Catalog. MIIS was instructed to provision these groups and custom recipients to the proper area in the Active Directory management agent connector space based on which Active Directory domain the two sets of objects were to be created in.
The domain owner was determined by looking at the Exchange Server 5.5 distinguished name on the object, then looking at a table to determine which domain correlated with that Exchange Server 5.5 site. Once the objects were created and exported to the appropriate Active Directory domain, the final step was to have the Active Directory management agent confirm the newly created distribution lists and custom recipients (now called distribution groups and contacts) via a delta import. Through provisioning, there is a natural join from the provisioned object to the metaverse object.
Again, as with the user objects and mailboxes, once the Active Directory management agent successfully provisioned and joined the distribution groups and contacts to their legacy Exchange Server 5.5 counterparts, MIIS was able to freely flow attributes from the Exchange Server 5.5 objects to the Active Directory objects. These attributes included, but were not limited to, distribution list memberships as well as personal information contained in the custom receipts.
For further illustration, consider Figure 2 which shows a provisioned distribution list, or DL, (now called distribution group, or DG) in the agency's Active Directory forest and a projected mailbox with a joined user object. MIIS can read attributes in from the Exchange Server 5.5 organization (such as the mailbox's primary SMTP address, mailbox's phone number, and distribution list's membership) and flow them from Exchange Server 5.5 to Active Directory.
Figure 2 Provisioned Distribution List
Figure 2 Provisioned Distribution List
Up to this point, the directories have a baseline synchronization process that keeps the objects in sync between the agency's Exchange Server 5.5 organization directory and the agency's Active Directory forest. MIIS can now simply monitor both directories for changes (adds, deletes, modifications) to objects in one directory and pass them on to the other directory. But, what about directory changes that occur as a direct result of Exchange Server 5.5 site migrations? Here's where it really gets interesting.
When an Exchange Server 5.5 site was about to be migrated, that site was put into what was called "migrate mode" by adding the Exchange Server 5.5 site name to an XML input sheet that is read in by the Exchange Server 5.5 management agent. For mailbox/user-joined metaverse objects, a series of well-orchestrated imports, synchronizations, and exports of Exchange Server 5.5 bridgehead management agent and the Active Directory management agent then took place, which essentially deleted the metaverse object and forced the exact opposite of the original mechanism to occur. Now, the Active Directory user object projected up to the metaverse and the Exchange Server 5.5 mailbox was joined to it. At this point, the ownership and flow of information also changed. Active Directory now owned the attributes and attribute change flow now went from Active Directory into Exchange Server 5.5. When this procedure was completed successfully for all mailboxes in the site, the site was free to commence with the mailbox migration.
At the end of a successful mailbox migration, the mailbox was simply deleted from the Exchange Server 5.5 site. The Exchange Server 5.5 management agent removed the mailbox from the Exchange Server 5.5 management agent connector space and from the joined metaverse Active Directory user object. MIIS then automatically created a representative custom recipient in the Exchange Server 5.5 site that the Exchange Server 5.5 management agent has write access to. This custom recipient then replicated throughout the Exchange Server 5.5 organization so that legacy Exchange Server 5.5 users can continue to see the migrated mailbox in the Exchange Server 5.5 GAL. MIIS flowed information to the newly provisioned custom recipient to allow this, namely by ensuring the custom recipient has an SMTP address that correlates to the target Exchange Server 2003 organization.
Responses to old e-mails are allowed through a similar mechanism, as MIIS flows information to the Active Directory user object regarding the mailbox's legacy X.500 addresses, such as where in the legacy organization the migrated mailbox came from. MIIS also adds this same legacy X.500 address to the newly provisioned representative custom recipient, thereby allowing replies to messages which originated prior to that mailbox's migration.
For distribution lists, the migration process is a little different. Once an Exchange Server 5.5 site is in migration mode, as with the mailboxes, MIIS changes the ownership and direction of attribute change flow to favor the agency's Active Directory forest. The distribution list "migration" was essentially a deletion of all distribution lists from the Exchange Server 5.5 site (they still exist in the agency's Active Directory forest via MIIS provisioning). Once MIIS detects the deletion of the distribution lists via the Exchange Server 5.5 management agent, it provisions a representative custom recipient to the Exchange Server 5.5 site to which MIIS has write access.
This new DL representation then replicates throughout the entire legacy Exchange Server 5.5 organization. However, in Exchange Server 5.5 sites with a larger number of DLs, it was found that sometimes their deletion would be detected by MIIS in more than one batch, which meant that if DLs were members of other DLs, there was a potential that the member DLs could drop out of membership. This was due to the latency of the Exchange Server 5.5 directory replication from the remote Exchange Server 5.5 site to the MIIS Exchange Server bridgehead site.
To counter this, the exact same procedure was performed on the mailboxes during the orchestrated "migrate mode" management agent runs. The metaverse objects pertaining to distribution lists were deleted and the Active Directory management agent projected the distribution groups from the Active Directory management agent connector space up into the metaverse. The DLs from the Exchange Server 5.5 connector space were then joined to the projected metaverse object. As a result, the Active Directory forest now owns all attributes (including membership) prior to the DLs being deleted, thereby safeguarding DL membership in another DL.
Custom recipient migration was a simple approach. MIIS provisioned all custom recipients in the agency's Active Directory forest (exactly like provisioning DLs) in their proper Active Directory domain based on the legacy Exchange Server 5.5 distinguished name. Once the entire Exchange Server 5.5 site had been migrated, the site was decommissioned, thereby leaving the remaining legacy Exchange Server 5.5 organization void of these custom recipients. This logic was based on the fact that those who created the custom recipient (CR) had been migrated over to the new Exchange Server 2003 organization, and this external recipient already existed over there as a contact (via MIIS provisioning).
The people who used the CR in Exchange Server 5.5 now have the same contact representation in the Exchange Server 2003 GAL. If the legacy users in other Exchange Server 5.5 sites needed to have this representation back, the CR was recreated manually in that Exchange Server 5.5 site. This was all in an effort to keep the MIIS provisioning code simpler. However, if it is required, these "migrated" custom recipients could be re-provisioned back into the legacy Exchange Server 5.5 MIIS bridgehead site in similar fashion to the migrated mailboxes and DLs.

Conclusion
Through testing and a well-planned pilot process, the directory synchronization design team was able to prove that MIIS could perform the synchronization tasks quite readily and predictably. Equally important is the fact that the design team met the goals of the planning team in that the directory synchronization process could be designed to run on a single server which simplified management of the solution considerably over what would be required for a multiserver solution.
MIIS Resources
  • MIIS Technical Walk-Throughs for Basic Operations
  • General Information on MIIS
  • MIIS Design and Planning Documents and Worksheets Collection
  • Exchange Server 2003 Information
  • Exchange Server 2003 Downloads

For the directory synchronization efforts, only two MIIS management agents were used (one Active Directory management agent and one Exchange Server 5.5 bridgehead management agent). Compare this to the amount of ADC connection agreements that would have been required to accomplish the same synchronization task, plus the number of servers that would have been required in order to run that many ADC connection agreements.
Because this approach uses a single management agent and a single Exchange Server 5.5 site to discover a very large Exchange Server 5.5 organization of 272 sites, the project relied heavily on Exchange Server 5.5 directory replication. This reliance was identified during the planning and risk analysis phases to be the point of highest risk for the directory synchronization subproject. This can be somewhat mitigated by decreasing the directory replication interval in the Exchange Server 5.5 sites in the directory replication chain to the MIIS Exchange Server 5.5 bridgehead site.
Care should be taken to not make the directory replication cycle too aggressive, as it could affect overall Exchange Server 5.5 performance and/or network performance. The moral of this story is that for larger, geographically dispersed Exchange Server 5.5 deployments, do not put too many direct MIIS tasks on the reliance list of Exchange Server 5.5 directory replication. For example, don't wait to do something based on seeing a mailbox deletion at the MIIS Exchange Server 5.5 bridgehead site. That kind of latency can throw a wrench in the machine.
The key to pulling off a successful synchronization is planning in advance how each object will be handled throughout the life of the migration process. This includes where the object is to be provisioned, as well as any customized attribute mapping/modifications to be performed during the flow operations. All of this can readily be hashed out in a full lab or even a representative virtual lab. It is highly recommended that you test all directory synchronization for an Exchange Server migration in a lab setting prior to production piloting or deployment, as directed by the MSF and MOF principles and guidelines.

Dennis Swenson is a Senior Infrastructure Consultant for Microsoft Consulting Services Public Sector and has been involved with Active Directory and integrated Exchange Server since the beta version of Exchange 2000 Server. He loves to write customized code using application APIs, but don't tell his friends as he does not want to appear geeky. Reach him at dswenson@microsoft.com.


 Blog Tales: A Guide To Public Folder Tools
Blog Tales
A Guide To Public Folder Tools
Bill Long

Are you an Exchange Server op who's confused by different public folder tools? With all the various public folder tools out there, it can be difficult to remember what each tool can be used for. Let's straighten it all out now. Note that these tools can be found in the indicated subdirectories of ftp://ftp.microsoft.com/pss/tools/Exchange%20Support%20Tools.

pfadmin 1.3
This is the original pfadmin from the days of Exchange Server 5.5 and is used for imports. It works with Exchange Server 5.5, Exchange Server 2000, and Exchange Server 2003. It's typically used for running permissions imports that were generated with pfinfo 3.x, which are made up of SETACL command lines. You can also use it interactively to run SETACL and SETREPLICA commands. Pfadmin lets you view the current permissions and replicas using LISTACL and LISTREPLICAS, respectively. However, these dumps cannot be imported since they don't use the SETACL or SETREPLICAS command format. You can find pfadmin 1.3 at /PFAdmin.

pfinfo 3.x
This is the original pfinfo from the time of Exchange Server 5.5 and can be used with Exchange Server 5.5 only. It's used for exporting permissions in pfadmin 1.3 command syntax and also reports certain properties. Pfinfo is typically used for running permissions exports—actually just a list of SETACL commands. These can be imported using pfadmin 1.3. It also generates a report that includes various other properties of the folder, such as item count and folder size. The property export cannot be imported. You can try running this against Exchange Server 2000 and Exchange Server 2003, but in some cases subfolders will appear as root folders in the export because pfinfo gets the folder path from the directory object, which is not always populated in Exchange Server 2000 and Exchange Server 2003. See /PFInfo/55.

pfadminE2K
This is an updated pfadmin for Exchange Server 2000 and is used for imports. However, pfadminE2K isn't widely used due to several shortcomings. First, its command and permissions syntax is completely different from pfadmin 1.3, meaning you can't import your old pfinfo exports with the new pfadminE2K. Second, with Exchange Server 2000 SP1 and later, most operations fail. It only works reliably with the RTM version of Exchange Server 2000. You can find it at /PFAdmin.

pfinfoE2K
This is the updated pfinfo for Exchange Server 2000, which was also not widely used for similar reasons. It's used for exporting permissions in pfadminE2K command syntax and reports certain properties. The new permissions export format made it incompatible with any tool except pfadminE2K, and it works reliably with Exchange Server 2000 RTM only. See /PFInfo/2000.

OutlookFolders
This utility accesses Exchange Server via a MAPI profile, and thus it works with any version of Exchange Server. It generates permissions exports in pfadmin 1.3 SETACL command syntax and can be found at /Outlook_Folders.

PFDavAdmin
PFDavAdmin accesses the information store through WebDAV and can be used with Exchange Server 2000 and Exchange Server 2003. Originally written to correct non-canonical discretionary access control lists on public folders, it eventually expanded to include imports and exports of permissions and replicas in pfadmin 1.3 command syntax. Version 2.3 and later can also export properties such as item count (PR_CONTENT_COUNT) and folder size (PR_MESSAGE_SIZE) into a tab-delimited file similar to the old pfinfo output which also cannot be imported, just like the old pfinfo output. It can be used to propagate single permissions and replicas down a tree of subfolders, instead of overwriting as Exchange System Manager does. Get it at /PFDavAdmin.

pfmigrate
Pfmigrate can be used with Exchange Server 2000 and is especially useful for Exchange Server 2003 SP1 site consolidation, since it supports changing the legacy domain name of a public folder. No other utility discussed here can do this. Pfmigrate can also be used to simply add replicas of all folders on one server to another server, and remove replicas of all folders from the original server. You can find it at Downloads for Exchange Server 2003.

exdeploy
The exdeploy /pubfoldcheck switch is of interest in regard to public folders since it runs a part of the DS/IS Consistency Adjuster, which removes the unknown accounts from public folder access control lists (ACLs). Those unknown accounts, if unhandled, could then get replicated to Exchange Server 2000 and Exchange Server 2003 as zombie users (accounts that appear in ACLs but not in Active Directory®). Exdeploy /pubfoldcheck can be scripted and run against multiple servers. Earlier versions of exdeploy help stated that /pubfoldcheck also synchronized the public folder store and the Exchange Server directory (which could cause public folders to be rehomed). This is incorrect and has been fixed in the new help file available with the tool. It's available at Downloads for Exchange Server 2003, as part of the Exchange Server 2003 deployment tools.

Bill Long began his career at Microsoft in 1997, supporting Exchange Server on the night shift. In 2001 he moved to a daytime position in troubleshooting. In 2003, he wrote the PFDavAdmin tool and updates it as time allows.


 How IT Works: A Primer On SQL Joins
How IT Works
A Primer On SQL Joins
Joshua Trupin

One of the trickiest things about learning SQL is mastering how various JOIN statements differ in the ways they combine data from multiple data tables. There are three types of joins: inner, outer, and cross. In addition, there are three types of outer joins: left, right, and full. It can be frustrating trying to keep them differentiated, so here's a quick guide. All of the following examples involve joining the authors and publishers tables in the Pubs sample database included with SQL Server™.

Inner Joins
In an inner join, records from two tables are combined and added to a query's results only if the values of the joined fields meet certain specified criteria. If you use an inner join to combine the authors and publishers tables based on their city and state columns, the result would be a list of all authors who live in the same city as a publisher (see Figure 1):
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
AND a.state = p.state
ORDER BY a.au_lname ASC, a.au_fname ASC
Figure 1 Inner Join (Close Figure 1)

au_fname | au_lname | pub_name
Abraham | Bennet | Algodata Infosystems
Cheryl | Carson | Algodata Infosystems

Outer Joins
An outer join returns all rows from the joined tables whether or not there's a matching row between them. The ON clause supplements the data rather than filtering it. The three types of outer joins, left, right, and full, indicate the main data's source.

Left Outer Joins
When you use a left outer join to combine two tables, all the rows from the left-hand table are included in the results. So, for the authors and publishers tables, the result will include a list of all authors along with a publisher's name column. If a publisher exists in the author's city, it's listed. Otherwise, the field in the publisher's column is set to NULL (see Figure 2):
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors a LEFT OUTER JOIN publishers p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Figure 2 Left Outer Join (Close Figure 2)

au_fname | au_lname | pub_name
Reginald | Blotchet-Halls | NULL
Michel | DeFrance | NULL
Innes | del Castillo | NULL
Ann | Dull | NULL
Marjorie | Green | NULL
Morningstar | Greene | NULL
Burt | Gringlesby | NULL
Sheryl | Hunter | NULL
Livia | Karsen | NULL
Charlene | Locksley | NULL
Stearns | MacFeather | NULL
Heather | McBadden | NULL
Michael | O'Leary | NULL
Sylvia | Panteley | NULL
Albert | Ringer | NULL
Anne | Ringer | NULL
Meander | Smith | NULL
Dean | Straight | NULL
Dirk | Stringer | NULL
Johnson | White | NULL
Akiko | Yokomoto | NULL
Abraham | Bennet | Algodata Infosystems
Cheryl | Carson | Algodata Infosystems

Right Outer Joins
A right outer join is conceptually the same as a left outer join except that all the rows from the right-hand table are included in the results. They can be included more than once, if more than one author exists in the publisher's city. If no author lives in the publisher's city, the author name fields are set to NULL (see Figure 3):
USE pubs
SELECT a.au_fname, a.au_lname, p.pub_name
FROM authors AS a RIGHT OUTER JOIN publishers AS p
ON a.city = p.city
ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC
Figure 3 Right Outer Join (Close Figure 3)

au_fname | au_lname | pub_name
Abraham | Bennet | Algodata Infosystems
Cheryl | Carson | Algodata Infosystems
NULL | NULL | Binnet & Hardley
NULL | NULL | Five Lakes Publishing
NULL | NULL | GGG&G
NULL | NULL | Lucerne Publishing
NULL | NULL | New Moon Books
NULL | NULL | Ramona Publishers
NULL | NULL | Scootney Books

Full Outer Join
As you might have gathered, a full outer join retrieves all the rows from both joined tables. It returns all of the paired rows where the join condition is true, plus the unpaired rows from each table concatenated with NULL rows from the other table. You usually won't want to use one of these.

Cross Join
A cross join returns not the sum but the product of two tables. Each row in the left-hand table is matched up with each row in the right-hand table. It's the set of all possible row combinations, without any filtering, as shown here:
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
ORDER BY au_lname DESC
The resultset contains 184 rows (authors has 23 rows, and publishers has 8; therefore, 23 × 8 = 184). The first 11 rows look like Figure 4.
Figure 4 Cross Join (Close Figure 4)

au_fname | au_lname | pub_name
Akiko | Yokomoto | New Moon Books
Akiko | Yokomoto | Binnet & Hardley
Akiko | Yokomoto | Algodata Infosystems
Akiko | Yokomoto | Five Lakes Publishing
Akiko | Yokomoto | Ramona Publishers
Akiko | Yokomoto | GGG&G
Akiko | Yokomoto | Scootney Books
Akiko | Yokomoto | Lucerne Publishing
Johnson | White | New Moon Books
Johnson | White | Binnet & Hardley
Johnson | White | Algodata Infosystems
However, if you add a WHERE clause (like WHERE authors.city = publishers.city), a cross join functions as an inner join—it uses the condition to filter all possible row combinations down to the ones you want (see Figure 5):
USE pubs
SELECT au_fname, au_lname, pub_name
FROM authors CROSS JOIN publishers
WHERE authors.city = publishers.city
ORDER BY au_lname DESC
Figure 5 WHERE Clause (Close Figure 5)

au_fname | au_lname | pub_name
Cheryl | Carson | Algodata Infosystems
Abraham | Bennet | Algodata Infosystems

Joshua Trupin is the Executive Editor of TechNet Magazine and MSDN Magazine.


 Serving the Web: Get It Right The First Time With IIS 6.0
Serving The Web
Get It Right The First Time With IIS 6.0
Chris Adams

Server administrators are always on a quest for the secret formula that will solve their performance woes. But no matter what you may have heard, there is no magic pixie dust. Yet the promises keep rolling in, even though few results are delivered. There is no bulletproof mechanism for enhancing server performance.
IIS 6.0 and Windows Server™ 2003 comprise the Microsoft® Web serving platform. While IIS 6.0 has powerful features that improve security, architecture, manageability, and even troubleshooting, it takes administrator or developer intervention to pump up performance. Out of the box, it is tuned to serve many thousands of static requests, but dynamic content is what drives the Web. So high-performance dynamic content is a serious goal.
Simply put, IIS 6.0 can't do performance alone. It's up to you, the IIS 6.0 user, to understand the significant architectural and platform improvements Microsoft made when redesigning the Web server for IIS 6.0 and Windows Server 2003, as well as understand how to exploit these changes to enhance performance.

Architecture 101
The IIS 6.0 architecture consists of three interdependent components: IIS Admin Service, W3SVC service, and the worker processes (W3WP.exe). Unlike IIS 5.x (and earlier versions), IIS 6.0 eliminates any confusion about which component is not performing optimally. In IIS 5.x, it was possible that code not written by Microsoft could be executed inside the Web server's core. In the IIS 5.x architecture, the core Web service (W3SVC) and user code in the form of ISAPI filters and extensions would run within the main process called Inetinfo.exe (see Figure 1). Besides the obvious security concerns, this also posed a dramatic risk to the overall health of the server by allowing code not properly tested in the IIS 5.x core to cause exceptions and fail.
Figure 1 IIS 5.0 Architecture
Figure 1 IIS 5.0 Architecture
In IIS 6.0, the W3SVC service is separated from any user code (see Figure 2). W3SVC now executes at all times in a process that is hosted by SVCHOST.exe, which does not allow any custom code to be loaded or injected into it. This process is responsible for two tasks: configuration and process management. It does not serve any requests.
Figure 2 IIS 6.0 Architecture
Figure 2 IIS 6.0 Architecture
IIS 6.0 improves reliability by using W3SVC to monitor the health of worker processes that host Web applications. Unlike IIS 5.0, which depended on external sources for monitoring such as Application Center 2000, W3SVC either maintains a certain level of health or else restarts the worker process.

Application Pools: The Building Blocks
The fundamental activities for maintaining the health of Web applications are the responsibility of the W3SVC service. In IIS 6.0, this service owns and manages a collection of "application pools." These application pools create a boundary between groups of applications or even two applications. In a nutshell, an application pool contains a single application or a collection of applications with the goal of isolating them.
The Web server governs apps in an app pool with a single set of criteria and determines overall health. Specifically, it determines the health of the host processes, which has the secondary effect of helping to ensure good application health. Application pools can be restarted or recycled based on different criteria. The criterion chosen is based on overall health parameters or performance characteristics of the hosted apps.
For example, DefaultAppPool is the single application pool installed on Windows Server 2003 when IIS 6.0 is configured. This application pool is fairly "reserved" by being set to recycle at 1740 minutes (every 29 hours). It is also set to ensure maximum resource use by shutting down after 20 minutes of no new requests (and no currently running requests), thus ensuring that resources are freed for the system or other services to use. This is important, because application pools should group apps with respect to recycling, health, usage, and performance characteristics.

Application Pools and Web Administrators
Performance tuning with IIS 6.0 takes planning and work; there is no one formula that will work in all circumstances. Developers can program for IIS using a variety of supported technologies such as ASP, ASP.NET, and other ISAPIs (for Perl, PHP, or Cold Fusion, for example). No single solution can remedy Web server performance problems because there is no single cause.
For example, tweaking the performance of a server running mostly Cold Fusion applications is much different than tweaking an ASP.NET application written in C#. To apply one practice to all situations is to risk unexpected and undesirable behavior.
A framework, however, can and should be applied to ensure maximum reliability and overall better performance. The framework depends almost entirely on the core Web server and very little on the actual code being executed.
It is very important to understand that frameworks can be applied at two independent layers: the core Web server layer or the application layer. IIS 6.0 helps you with your reliability and performance efforts by letting you tweak, optimize, and customize the framework at the core Web server layer.
The first step in performance tuning is to understand the different avenues by which an application pool might be restarted (recycled). The three possible options are: request-based, time-based, and resource-based.
Request-based recycling means applications are recycled according to the number of requests the server receives (often called "load"). This typically produces a seemingly random recycling behavior.
Two varieties of time-based recycling are available; the administrator must determine which is best for a given environment. The first is based on total minutes of run time (the default) while the second is schedule-based over a 24-hour period. A good example of when scheduled-based recycling is useful is an application pool serving a timecard application that requires all employees to enter their work hours for the previous day into a Web application. In this case, it might be beneficial to set the recycle to occur every morning at 3:00 A.M., thereby releasing any used resources and starting fresh for the new day. This would maintain the health of the application by ensuring that it is fresh when it goes into service. Note, though, that recycling this hypothetical timecard application would not be a good idea if it made extensive use of in-process caching. In many scenarios, caching is critical for good performance and the cache takes time to build up and use. The in-process cache is lost when recycling is done, however, so the cache built up from previous days would never be used.
For this reason resource-based recycling, which uses memory consumption as the basis for restarting, was included in IIS 6.0. If the timecard application were written in ASP.NET, for example, it would be a good move to disable any request-based or time-based recycling and choose resource-based recycling. ASP.NET, specifically the common language runtime's garbage collector, maintains its own health by ensuring that unused memory is released. ASP.NET is often capped at a memory limit, thereby allowing applications to run as long as their health is maintained within the individual application domains.
When too much memory is being used, IIS 6.0 can restart the process. A good starting point for application pools hosting ASP.NET apps is to set the PeriodicRestartPrivateMemory limit to the minimum of 800MB or 60 percent of physical RAM, whichever is smaller. This helps to ensure that the worker processes are recycled before ASP.NET applications experience out-of-memory exceptions.
Before choosing the appropriate recycling strategy, you should first decide exactly what applications will be installed on the server and then map out a strategy for implementing application pools. This often entails breaking down applications according to criteria such as:
  • Business units (HR, IT, Marketing, for example)
  • Application types (ASP.NET, PHP, CGI, or static files only)
  • Application demands (such as CPU or memory)
  • Application behavior (putting misbehaving applications in one group or pool and well-behaved applications in another)
By default, IIS 6.0 starts with a single application pool designed to own all applications, and these applications will execute within the same worker process. This can lead to failures or bottlenecks as applications are forced to share resources.
Above and beyond the health of applications, the benefit of the application pool architecture is code isolation. Sandboxing apps by isolating code has long been a best practice, but IIS didn't do this until the current version. IIS 6.0 is capable of running many concurrent and completely isolated app pools. In fact, no memory is shared between worker processes running on the same IIS 6.0 server, not even ISAPI filters, extensions, session data, or connections.
The W3SVC service is responsible for configuring the HTTP.sys kernel driver and starting the worker process. It does not host any Web applications or serve any requests.
W3SVC first creates application pool queues in HTTP.sys. It then instructs HTTP.sys to listen for the URLs configured in the metabase and tells it how to route the URLs and which app pool to send them to. This all occurs before the first request to IIS 6.0 is served, thus starting at the connection layer (HTTP.sys), no application data is shared. The request queues are unique and tied to pools. At the time of the first request, HTTP.sys notifies the W3SVC service that a new request is pending for an application pool. The W3SVC service then starts a new worker process, which then pulls the request from HTTP.sys, executes it, and returns the response to HTTP.sys. To recap, at run time and only on the first request the following occurs:
  1. HTTP.sys notifies the W3SVC service that a pending request has been received for a given application pool and that there are currently no processes serving that application pool.
  2. W3SVC creates the new worker pro-cess for that pool.
  3. W3WP.exe (worker process) opens the application pool in HTTP.sys, pulls the request for that pool from HTTP.sys, and executes it.
  4. W3WP.exe returns the response to HTTP.sys.
This provides a fail-safe architecture for processing requests that are presented to IIS 6.0 by removing the single-process bottleneck. In versions of IIS prior to 6.0, all new requests, regardless of the URL, had to be routed through the Inetinfo.exe process prior to execution.

Ping: Are You There?
Every administrator at some time has used the ping.exe utility to verify that communication was established between two machines. This is a tried–and-tested method for ensuring that each machine is configured for network communication. Why mess with what works? In IIS 6.0, a simple communication channel that allows status data to be communicated is opened between the W3SVC service and each worker process. This status data describes the overall health of the worker process. This kind communication is known as "pinging" in the IIS 6.0 IIS Manager. Pinging, enabled by default, allows the W3SVC to check that the worker process is able to do work on requests, and health reporting piggybacks on top of this mechanism. This feature is configurable, but Microsoft strongly recommends that administrators do not disable this feature.
IIS 6.0 has two options for recycling: overlapping (the default) and non-overlapping. To maximize availability of Web applications, the W3SVC service by default starts a new worker process while the unhealthy worker process continues to churn along. When the new worker process is fully started, W3SVC notifies HTTP.sys to start routing the new requests to the new process. When this notification is complete, the old process is allotted a time to shut down (90 seconds by default), otherwise the W3SVC service terminates it. This overlapping process ensures that the client's experience with the application is maximized by removing a potential bottleneck and starting a new process.

Building Reliability from the Ground Up
The ability to recycle is useful for various reasons, but many applications are simply sick from the start. An application, or a set of applications, that repeatedly perform poorly are detrimental to the server's overall health. With IIS 6.0, an application pool that often causes worker process crashes is marked as unhealthy. Multiple failures over a short period of time could be exceptionally bad for your overall Web server health due to the repeated starting of new processes. IIS 6.0 protects against multiple failures by including a special feature called "rapid-fail protection."
This feature eliminates the application pools that crash but are not dealt with by administrators and developers. Plus, it also prevents infinite crash and restart loops. An application whose worker processes fail five times in a five-minute time span (these are the default values, as shown in Figure 3) is considered unhealthy and marked as unusable until an administrator has manually restarted the application pool. IIS 6.0 cannot always detect the reason for failures, but it does attempt to minimize the amount of CPU or memory these applications take from other well-behaved applications and application pools. The beauty of rapid-fail protection is that it allows the administrator to configure both the number of failures and the time period in which the failures can occur. You can do this in the IIS Manager or by using your favorite command-line script.
Figure 3 Application Pool Properties
Figure 3 Application Pool Properties
Rapid-fail protection is unique in that it doesn't apply to recycles based on requests and resources. Instead, an application pool is placed into rapid-fail protection if the IIS 6.0 W3SVC fails to ping the worker process, if the worker process crashes, or if its startup or shutdown time limit is exceeded. This is a sign to the service that the worker process is no longer available and for health reasons should be shut down. When a pool is placed into rapid-fail protection, W3SVC actually stops the app pool, and HTTP.sys returns 503 responses to all queued requests and to all new requests to that pool.
The easiest way to see IIS 6.0 go into rapid-fail protection is to use the Windows® kill.exe utility to terminate the process (without notifying W3SVC) and restart it. After five consecutive failures, IIS 6.0 no longer creates a new worker process and returns a 503 Service Unavailable error to the unfortunate user. To see rapid-fail protection in action, follow these steps:
  1. Open a browser and make a request to the IIS 6.0 server using http://localhost.
  2. In Task Manager, click the Processes tab and highlight W3WP.exe. Then click Kill.
  3. If you repeat these steps four more times, W3WP.exe will fail to load again.

It's a Wrap
IIS 6.0 moved the management overhead from the customers to the W3SVC service while also offering a high level of flexibility. It offers administrators the ability to use a variety of solutions for maintaining the health of their services and offerings, and can be expanded or contracted as needed to suit a particular implementation. The key to performance and reliability is the ability to remove unused resources and recycle the resources that are affecting Windows Server 2003 overall health.
The ability to recycle allows customers to approach a true "shared" hosting environment. Whether you're an intranet site host or a service provider, IIS 6.0 can effectively host thousands of Web sites with many hundreds of applications while continuously maintaining effective management and performance capabilities. The architecture of IIS 6.0 is designed to use resources efficiently while also allowing for maximum client satisfaction by maintaining the internal health of all currently running processes.

Chris Adams is the Web Platform Supportability Lead at Microsoft. Chris is currently certified as a MCP, MCSA, and MCSE on Windows NT 4.0, Windows 2000, and Windows Server 2003. Reach him at chrisad@microsoft.com.


 InterOp: Windows Services For UNIX
InterOp
Windows Services For UNIX
Charlie Russel

If you're an administrator working in a mixed environment, Microsoft® Windows® Services for UNIX (SFU) 3.5 can make your life a lot easier. SFU provides a full-featured interoperability solution that includes a Network File Server (NFS) client, server and gateway for file system interoperability, and a Network Information Service (NIS). SFU also gives you User Name Mapping and password synchronization for identity management, along with the Interix subsystem.
In this article I'll focus on the Interix subsystem and utilities that make SFU an ideal interoperability environment for cross-platform scripting. When combined with the cross-platform file system client and server of SFU, and the versatile identity management features, you get the ideal toolset for managing and administering a diverse UNIX and Windows environment.

Interix Subsystem
The Interix subsystem is a full UNIX application and scripting environment. Unlike other interop products available from third-party vendors, the Interix subsystem is not just an emulation layer running on top of the Win32® subsystem, but a full subsystem of its own. As such, it enables higher performance and fewer compromises. Figure 1 depicts the Interix architecture.
Figure 1 Interix Architecture
Figure 1 Interix Architecture
The Interix subsystem behaves just like any other UNIX machine and feels much more natural to the UNIX user or administrator. Applications can be ported to the Interix subsystem with relatively few changes, and the SDK includes more than 2000 UNIX APIs. The following sections explain what you must change and what will work without modification.
The Interix subsystem is fully case sensitive for both paths and filenames by default (as is UNIX), though case sensitivity can be disabled on Windows XP and Windows Server™ 2003. This does present some security issues that you'll need to understand. Specifically, under the Interix subsystem you can create multiple versions of a file with the same name, for example "temp1", "Temp1", and "TEMP1". When using the Interix subsystem, they will all appear to be different, as they should.
From the Win32 subsystem, applications will only see one of them. So, in the example of the three versions of temp1, only one can be edited with Notepad, and even if there is different content in the three files, only one of them will be accessible from the Win32 subsystem, and you can't easily predict which one that will be.
SFU implements a single rooted file system and all paths on the machine are relative to the Interix "/" (root) directory, which is equivalent to %SFUDIR% from the Windows shell. Drive letters are available via "/dev/fs/DRIVELETTER". This enables scripts and applications ported from UNIX and Linux to work more naturally, and with less conversion.
SFU treats slash ("/") and backslash ("\") characters exactly the same way as UNIX does. Scripts that must call Win32-based applications must protect backslash characters from being interpreted by the Interix shell. Pure UNIX scripts will have no problems, and should move to Interix without having to convert paths and parameters.
SFU and the Interix subsystem support both hard and symbolic links. The hard links are completely visible both from the Win32 subsystem and the Interix subsystem, but the symbolic links are only supported under the Interix subsystem.
UNIX and Linux users use a variety of different shells, from the Bourne shell (/bin/sh) to the Z shell, with just about everything in between. The relative merits of particular shells are the subject of rather heated debate, like you might find when discussing favorite sports teams. Interix ships with a Korn-compatible shell and a C shell, and others are available for download from InteropSystems (www.interix.com).
The included Korn-like shell (/bin/ksh) is based on a publicly available Korn shell clone, and there is also a symbolic link to it from /bin/sh for those scripts that require a shell be at /bin/sh.
The included C shell is a somewhat outdated version of the Tenet C shell (/bin/tcsh). A symbolic link to it from /bin/csh provides compatibility for scripts that expect to find csh in /bin. Again, an updated version is available from InteropSystems.
Additional shells, including bash and zsh, are precompiled for SFU and available from InteropSystems. You can compile others using either the cc front end to Visual Studio®, or the gcc compiler available with SFU as an installation option.

Utilities
SFU comes with more than 300 UNIX utilities as part of the Interix subsystem, with additional utilities available either from InteropSystems or by compiling from available source code. These utilities cover all the major UNIX utilities and areas—everything from addr to yacc—and behave exactly as you and your UNIX users would expect them to behave.
The utilities include familiar text processing tools, including grep, less, awk, sed, pr, and tr, batch processing tools such as at, cron, and batch, as well as job control tools like ps, nice, kill, and so on. They're all there and they work exactly as you would expect. Even the man command is just as ugly (but infinitely useful) as it's always been.
Utilities such as ps and kill work against both Interix and Win32 processes, making SFU particularly appealing for the system administrator. Need to find and kill all instances of a particular process? The script to do it in Interix is straightforward, whether the process is running in the Win32 subsystem or the Interix subsystem.
As a simplistic but useful example, suppose you have an unknown number of copies of a process running on a machine with SFU. Figure 2 shows a script that will kill them. This script would work exactly the same running on a UNIX or Linux system.
Figure 2 Kill Processes (Close Figure 2)
#!/bin/ksh
#
#  Script to find all instances of a process and kill them
#
#    Takes a single argument, the process name to kill
#

script_name=${0##*/}    # save it off as a simple filename

#********************************************************************
# Check to see that we got exactly one argument
#********************************************************************
if [[ $# -ne 1 ]];
then
   print "usage: $script_name <process_name>"
   exit
else
   PROCESS=$1
fi

#********************************************************************
# Count the number of instances of the process
#********************************************************************
let NUMPROC="$(ps -e | grep -i $PROCESS | grep -v grep | wc -l )"
print -n "Found $NUMPROC versions of $PROCESS, OK to kill them?(y|N) "
read ANSWER
case $ANSWER in
   Y|y) ;;
     *) print "Exiting $script_name, failed to get confirmation of kill"
        return ;;
esac

#********************************************************************
# Now, to actually kill them...
#********************************************************************
let TRIES=0       # Must have a value assigned to the variable

while (( $NUMPROC > 0 )) && (( $TRIES <= 4 )); #Make sure we have a way out!
do
   # first, do the kill.
   kill $(ps -e | grep -i $PROCESS | grep -v grep | tr -s " " | cut -c 1-5 )
   # Now, check to see if there are any left, and loop back if there are
   let "NUMPROC=$(ps -e | grep -i $PROCESS | grep -v grep | wc -l )"
   let "TRIES=$TRIES+1"
   print "This is try #$TRIES"
done

Programming
The Interix subsystem and SDK provide a full application development and migration environment, with more than 2000 UNIX APIs. Both XWindows and Curses-based applications are supported, along with a complete application execution environment that behaves exactly as UNIX applications expect. No special considerations for file locations or syntax are required. Symbolic links are fully supported, providing maximum location flexibility. SFU includes the GNU compilers, a version of Perl, and a cc front end to Visual Studio. Additional open source libraries, utilities, and compilers are available from InteropSystems. This makes it not only possible, but relatively easy, to port an existing UNIX application to run under Interix.

Scripts
As you've noticed, scripts can easily be written that run transparently in SFU, UNIX, or Linux. When I ran the IT department of a software development company, we wrote all our scripts to create, support, and manage more than 60 instances of Oracle in a mixture of Korn shell and Perl. The scripts resided on a central NFS share that was accessible from any of our Windows, UNIX, or Linux servers, and we used them transparently against whatever Oracle instance we were working on, regardless of what platform it happened to be on. There were minor differences that were easily handled by doing what one should always do when writing robust and well-planned scripts: we had a central function library, used variables rather than hardcoded locations, and included solid error handling.
While enterprise-wide scripts are an important benefit of using SFU, there's also a place for quick, simple, one-off scripts for running simple jobs. The real benefit of SFU for those scripts is the much richer shell and utility environment. One task that was a never ending problem, especially for my database administrators, was knowing how much hard drive space they were consuming. Yes, they would be notified when they had gone too far, since we had procedures and scripts in place to automatically send a warning e-mail message to those who exceeded their allotments. But no one really wanted to get one of those e-mails, since their team lead got cc'd on them. So I created a simple little one-line script that they could run themselves whenever they wanted to know how much space they were using, especially in shared areas where several users might be storing files.
find . -user ${1:-$LOGNAME} -exec ls -oa {} \; | tr -s " " " " | \
   cut -f 4 -d " " | awk '{sum += $1 } END {print sum}'
This is a good example of how SFU makes life easier. It runs equally well in Windows or UNIX, and quickly tells the user how much space she (or someone else) is using. It's not particularly robust, doesn't have any error handling, and certainly isn't modular. But try doing the same thing from cmd.exe. Even after you'd done it, you'd still only have solved the Windows problem.

Limitations
There are limitations to what you can do from the Interix subsystem. Because it is a completely separate subsystem, interprocess communication with applications in the Win32 subsystem is only available through pipes (which allow streaming of data between the two environments), memory-mapped files, sockets, and NTDLL-level semaphores. You can certainly call Win32-based applications and even batch files from an Interix subsystem application or script, but you'll have to do some testing to make sure that everything works as you expect, and you may have to deal with issues like protecting back slashes from evaluation by the Interix shell.
Scripts and applications that create or use files that then need to be accessible from the Win32 subsystem and regular Windows-based applications will need to be cautious about taking advantage of the Interix subsystem's full case sensitivity.

Device Files and Network Shares
SFU uses device files in the same way as UNIX or Linux, but you can't use the NFS file system as a bootable device for a diskless NFS workstation, and some common device files like /dev/tcp aren't possible. Windows drives are available as /dev/fs/DRIVELETTER—that is, /dev/fs/C, /dev/fs/D, and so forth. While you can create symlinks and/or aliases to mask this difference, it isn't recommended as a best practice. Windows network shares are available without mounting to a drive letter by using the convention /net/MACHINENAME/sharename. Shares that have been mounted on a drive letter are, of course, available as /dev/fs/DRIVELETTER.
Microsoft Windows Services for UNIX 3.5 provides all the features and tools necessary to give the user or administrator in a mixed environment the ability to use and manage their resources transparently across Windows, Linux, and UNIX systems.

Charlie Russel is a chemist by education, an electrician by trade, a Unix sysadmin and Oracle DBA because he raised his hand when he should have known better, an IT director and consultant by default, and a writer by choice. He is the author of more than a dozen books on operating systems and enterprise environments, and is a columnist for the Windows XP Expert Zone.


 Security Watch: Isolate Servers With IPsec
Security Watch
Isolate Servers With IPsec
Joseph Davies

We live in an age of always-connected communication and are able to send e-mail messages or gain access to files, databases, and Web pages at any time, through a proliferation of networking technologies. However, the ease and ubiquity of constant connection has also exposed computers to risks like never before. That same connectivity also allows malicious programs (viruses and worms) and users to attack computers or their resources 24 hours a day from anywhere in the world. A more secure world of computing needs to allow this constant access, but only to authenticated and authorized users and computers. The resources of your network must be isolated, not only from the Internet, which is commonly done today, but also from unauthorized and unmanaged computers on your intranet.
For example, the data on sensitive servers is typically protected by access control security at the application layer. Before accessing the files on a sensitive file server, a user must typically provide security credentials, such as a user name and proof of knowledge of a password, before being granted access. However, specifying access control lists (ACLs) at the application layer does not protect the server from various kinds of attacks launched from unauthorized or unmanaged computers connected to your intranet.
To provide an additional layer of protection for sensitive servers, you can isolate them from unauthorized and unmanaged computers by implementing additional authentication and security at the Internet layer with Internet Protocol security (IPsec). With IPsec-based server isolation, unauthorized and unmanaged computers cannot initiate any type of IP-based communication with isolated servers. This additional level of protection is needed for organizations (such as those in the financial service or health care industries) that routinely send sensitive data on their networks and must provide extra protection for sensitive data assets.

The Process
To isolate servers, you need to first create a set of credentials so that computers that are attempting to communicate with isolated servers can prove their identity. When a computer joins an Active Directory® domain, an Active Directory domain controller creates a computer account with a set of credentials. In subsequent connections, the initiating domain member computer sends its credentials to authenticate a communication attempt, which can then be verified by any Active Directory domain controller.
Next, distribute network policy settings to configure the isolated servers and the computers that are allowed to communicate with them. By being a member of an Active Directory domain, the computer can receive centrally configured network policies through Group Policy. Network administrators use Group Policy to distribute computer and user settings to the member computers of an Active Directory domain.
All of the elements to isolate servers are already built into computers running Windows® XP, Windows Server™ 2003, or Windows 2000. The only requirement is to ensure that computers are members of your domain and to configure the appropriate Group Policy setting to enforce IPsec authentication and protection for traffic to and from the isolated server. No new hardware equipment is required.
Server isolation provides an extra layer of security and access control that operates independently and compatibly with security technologies such as IEEE 802.1X and Secure Sockets Layer (SSL). IEEE 802.1X requires a computer to authenticate itself prior to being allowed to send any frames on a network, but it does not protect data sent on the network. SSL provides computer authentication and data confidentiality (encryption), but only for SSL-enabled client and server applications. IPsec and server isolation operate either independently or in combination with 802.1X and SSL, providing end-to-end security services for IP traffic sent between computers.

Benefits of Server Isolation
Let's look at some of the benefits of server isolation. Server isolation restricts incoming communications to managed computers. A Windows-based managed computer is defined as a computer that is a member of an Active Directory domain. Managed computers can receive centrally configured network policies through Group Policy and other types of security updates, such as operating system updates and antivirus software signatures. Managed computers use their domain credentials to authenticate communication attempts with isolated servers. Unmanaged computers, consisting of standalone, unknown, and guest computers, do not have domain credentials, and therefore cannot authenticate communication attempts with isolated servers.
Server isolation also works to complement other security mechanisms designed to prevent unwanted communications. It provides additional security above and beyond existing security mechanisms deployed on your network, such as ACLs, 802.1X, SSL, and firewalls. For example, if your Internet firewall is somehow compromised, malicious users from the Internet will not be able to initiate communications with isolated servers.
Server isolation also encourages domain membership. By isolating critical organization servers such as e-mail servers, users on the organization network are no longer able to access the critical resource from an unmanaged computer. To receive valid domain credentials for performing IPsec authentication with isolated servers, users must join their computers to the domain. Once this occurs, the IT department of the organization can also ensure that the user's computer has the latest operating system and antivirus updates.
Another benefit of server isolation is that it protects traffic sent both to and from isolated servers. This traffic is cryptographically protected in such a way that the receiving computer can verify that an authenticated computer sent the packet and that it was not modified in transit. Additionally, the isolated server traffic can be encrypted, providing protection from malicious users on your organization network who are attempting to capture and interpret network traffic. By encrypting your traffic, your organization can comply with governmental regulations and business partner requirements to encrypt sensitive traffic on the network.
For improved security, isolated servers protect applications that cannot protect themselves. Applications running on servers that do not have facilities for enforcing access control or security at the application layer can use server isolation to enforce authentication, authorization, and communication security at the Internet layer.
To perform server isolation, you must configure Group Policy settings to require that all communication with isolated servers be authenticated and protected with IPsec. With IPsec protection, traffic is protected from data tampering attacks such as address spoofing, data injection, session hijacking, and replay attacks. Packets can be protected with data confidentiality. You can also configure exceptions so that specific computers that are not domain members are allowed to initiate unprotected communications with the isolated servers.

Configuration of Server Isolation
The components of server isolation consist of the Active Directory domain, member computers, Group Policy, and IPsec policy. The Active Directory domain includes domain controllers and the appropriate trust relationships to trust other domains or directory trees within an organization network. Also included here are member computers that have joined an Active Directory domain and have been assigned domain credentials.
Group Policy will be used to specify computer and user settings that are automatically downloaded to member computers and active IPsec policy determines the server isolation behavior of managed computers. To show how server isolation works in a simplified example deployment, configure and activate IPsec policy with rules that require secured traffic from domain members and permit unsecured traffic from specific excepted computers.
Once the policy settings are complete, the IPsec policy is activated for the appropriate Active Directory system containers such as sites, domains, and organizational units. The member computers in the Active Directory system containers to which the Group Policy settings apply automatically download the Group Policy settings.
After the IPsec Group Policy settings are downloaded and applied, managed computers that have both the correct IPsec policy for supporting server isolation and domain credentials are able to communicate with isolated servers. Unmanaged computers that do not have the correct IPsec policy for supporting domain isolation and domain credentials are unable to communicate with isolated servers.

Group-Specific Server Isolation
The server isolation configuration described in this article assumes that all managed computers have the ability to communicate with an isolated server. However, servers differ in the level of sensitivity of their data and whether they allow universal access or access only from specific computers. For example, e-mail servers typically have to be available to all the managed computers to allow someone on an arbitrary managed computer to be able to read their e-mail. However, finance or legal department servers should only be available to a specific subset of computers.
To further isolate sensitive servers and prevent managed computers that do not have authorization from communicating with them, there are several steps you could take. First, you could separate the sensitive servers and the authorized computers by IP address onto a separate subnet. You could then create a new subnet-based IPsec rule to require secure communication between the computers sharing the same subnet address prefix. However, this method requires additional network planning to get all of the servers and their authorized computers on a specific subnet. In addition, this method does not allow users' computers to roam off of their subnet, such as with a wireless laptop.
Another step you could take is separating the sensitive servers and the authorized computers by using a different authentication method. For example, you could specify an IPsec rule that uses an authentication method that differs from the method being used to access the other isolated servers. If the other isolated servers are using Kerberos authentication, the sensitive servers can use certificate-based authentication. Once this is configured, only the computers that have been allocated certificates can access the sensitive servers. This method requires the installation of a certification authority whose sole purpose is to assign certificates to access the sensitive servers.
Both of these methods use the parameters of IPsec policy to further isolate a subset of servers that are more sensitive and enforce an additional layer of authorization that prevents access except to a specified subset of computers.
For Windows-based servers, you can use another method to enforce authorization for access that does not rely on IPsec policy settings. With the "Access this computer from the network" access right (a local Group Policy setting), you can specify the set of computer accounts or Active Directory security groups that are allowed to access a server over the network. When IPsec processes the credentials of the computer requesting communications, Windows checks this access right.
Resources
For Information About Active Directory
For Information About Group Policy
For Information About Windows IPsec

To further isolate sensitive servers based on Active Directory group membership, you must do three things. First, ensure that the sensitive servers have been added to the existing isolated servers IPsec policy. Second, create an Active Directory security group and add the computer accounts of the authorized computers to the group. Third, administer the local Group Policy settings of each sensitive server and change the "Access this computer from the network" access right so that it only contains the Active Directory security group that you created in the second step. (This access right is found in the local Group Policy at Computer Configuration\Windows Settings\Security Settings\Local Policies\ User Rights Assignment\Access this computer from the network.)
For example, for a file server that contains sensitive financial data, add the financial server to the isolated servers IPsec policy, create a ConfidentialFinancial security group, and then add the authorized computer accounts. Next, change the "Access this computer from the network" access right on the financial file server so that it contains only the ConfidentialFinancial security group.
By leveraging Active Directory's authentication and Group Policy infrastructure, IPsec server isolation can easily provide an extra layer of protection for sensitive servers. This extra layer ensures that unmanaged computers on your intranet cannot initiate any type of IP-based communications with any isolated servers. With group-specific server isolation, you can specify that managed computers on your network must also belong to a specific Active Directory security group that you designate, further reducing the number of computers that are going to be able to communicate with specific servers that contain sensitive data.
How Are You Communicating?
A Managed Computer to an Isolated Server
When a managed computer with both Active Directory credentials and domain isolation IPsec policy settings (for example, Computer 1) initiates communication with an isolated server (for example, Server 1), the following occurs:
  1. The initial communication packet being sent by Computer 1 [for example, a TCP Synchronize (SYN) segment destined for the IP address of an e-mail server that is isolated] matches a rule of the active IPsec policy.
  2. Because the rule requires secured traffic to isolated servers, Computer 1 uses IPsec to authenticate itself to Server 1 and to negotiate the use of IPsec protection.
  3. Computer 1 uses its domain credentials to perform IPsec authentication with Server 1.
  4. Computer 1 then uses its IPsec policy settings to negotiate the cryptographic settings for IPsec-protected communication with Server 1.
  5. Computer 1 sends the IPsec-protected initial communication packet (the TCP SYN segment) to Server 1.
  6. Server 1 sends the IPsec-protected response to the initial communication packet [for example, a TCP SYN-Acknowledgement (SYN-ACK) segment], to Computer 1.
  7. Subsequent packets that are sent between Computer 1 and Server 1 are encrypted.
As a result of the IPsec policy and domain membership, managed computers authenticate and IPsec-protect communications to all isolated servers.

An Unmanaged Computer to an Isolated Server
When an unmanaged computer (for example, Computer 2) initiates communication with an isolated server (for example, Server 1), the following occurs:
  1. Because it does not have any IPsec policy settings, Computer 2 sends its initial communication packet (for example, a TCP SYN segment—without encryption) to Server 1.
  2. On Server 1, the initial communication packet sent by Computer 2 matches a rule of its IPsec policy, which requires that the incoming communication be IPsec-protected.
  3. Because the rule does not allow incoming packets without IPsec protection, Server 1 silently discards the TCP SYN segment sent by Computer 2.
  4. Subsequent communication initiation packets sent by Computer 2 to Server 1 are also discarded by Server 1.
  5. Eventually, Computer 2 fails in its communication attempt with Server 1.
As a result of the IPsec policy on Server 1, unmanaged computers that attempt to communicate with an isolated server never receive a response, and therefore cannot connect to them. If a user on an unmanaged computer was able to duplicate the IPsec policy settings of a managed computer, it would still be unable to connect to Server 1 because it would not have a set of valid domain credentials with which to authenticate itself.

An Excepted Computer to an Isolated Server
When an excepted computer (for example, Computer 3) initiates communication with an isolated server (for example, Server 1), the following steps occur:
  1. Because it lacks IPsec policy settings, Computer 3 sends its initial packet (a TCP SYN segment) to Server 1 without IPsec protection.
  2. On Server 1, the initial packet sent by Computer 3 matches a rule of its IPsec policy, which permits unsecured communication to its own IP address from the IP addresses of specific excepted computers.
  3. Server 2 sends a response to Computer 3 without IPsec protection.
  4. Subsequent packets sent between Computer 3 and Server 1 are sent without IPsec protection.
Excepted computers can now communicate with isolated servers without IPsec protection.

Joseph Davies is a technical writer with Microsoft who has been writing about Windows networking topics for more than ten years. He has written five books for Microsoft Press and is the author of the monthly TechNet Cable Guy column.


 Resources: IIS 6.0 Performance Tune-Up
Resources
IIS 6.0 Performance Tune-Up
Phil Sherwood

By now you've probably gotten to know IIS 6.0, available in all versions of Windows Server™ 2003, and are taking advantage of much that this Web server offers. It's important to keep an eye on the overall goal of IIS 6.0: providing an optimal Web experience for your customers. The resources discussed here will help you tune your IIS 6.0 installation for optimal performance.
Most of these resources are from Microsoft; they range from very short to encyclopedic. They all assume familiarity with IIS 6.0 and the Windows Server 2003 environment, and the intended audience includes IT administrators, operations staff, developers, and architects. Not all the resources will exactly suit each of those professional groups—an IT administrator won't be directly concerned with the nuts and bolts of building a Web application, for example, but will be interested in the application design so as to better manage the application pools.
In short, there's plenty of technical material—online technical documentation, on-demand webcasts, PowerPoint"> 2003, and are taking advantage of much that this Web server offers. It's important to keep an eye on the overall goal of IIS 6.0: providing an optimal Web experience for your customers. The resources discussed here will help you tune your IIS 6.0 installation for optimal performance.
Most of these resources are from Microsoft; they range from very short to encyclopedic. They all assume familiarity with IIS 6.0 and the Windows Server 2003 environment, and the intended audience includes IT administrators, operations staff, developers, and architects. Not all the resources will exactly suit each of those professional groups—an IT administrator won't be directly concerned with the nuts and bolts of building a Web application, for example, but will be interested in the application design so as to better manage the application pools.
In short, there's plenty of technical material—online technical documentation, on-demand webcasts, PowerPoint® slide shows, and third-party articles—for everyone associated with building and running a high-performance IIS 6.0 Web server that enables an optimal customer experience.

Two Short Places to Start
When you have only a moment to delve into the IIS 6.0 performance question, start with this how-to article posted in November 2002: Performance Tuning Your IIS Web Server. Brief and to the point (just over four screens), it covers using and customizing Performance Monitor, tuning your Web server memory for optimal IIS performance, adding performance counters and alerts, monitoring server CPU utilization, using Task Manager (including an easy way to check on application memory leaks), and using Event Viewer.
A bit longer is Fundamentals of Web Site Acceleration: Performance Starts at the Web Server, a white paper from Port80 Software, which develops IIS 6.0 security and performance tools. The mantra for this clearly written and well-organized paper (178KB download, nine pages in Adobe Acrobat Reader) captures the fundamental Port80 Software IIS 6.0 optimization strategy: "Send as little data as possible, and when you must send data, do so as infrequently as possible."
To achieve that goal, this fundamentals paper advocates three basic performance-improvement approaches, all of which emphasize software and process enhancements to Web front-end source code and the origin server rather than expensive, cumbersome hardware solutions: code optimization, cache control, and HTTP compression. Port80 Software does, of course, offer tools for working in those three areas, but they do so unobtrusively and, more importantly, provide guidelines for you to think about as you consider whether you need add-on tools and different tools options from other sources as well.

A Downloadable Slideshow
Building Scalable and High Performance Web Applications on Internet Information Server (IIS) 6.0, a PowerPoint presentation by Bill Karagounis, takes a generally graphical, summary approach. It covers this outline in 29 slides:
  • Performance and scalability features of Windows Server 2003 in Web infrastructure, including a review of the contrasts with Windows® 2000
  • Three programming environments (ASP.NET, ASP, and ISAPI)
  • Guidelines for building applications to take advantage of the .NET Framework
This compendious resource captures much information in summary form without voiceovers or accompanying notes and so could make a useful "crib sheet" or refresher after you've digested the longer texts and the webcasts noted in the next section.

Online Documentation
The next three items are the heavy hitters in the lineup, the detailed and in-depth texts that form the basis for the webcasts and slide shows. Web and Application Server Infrastructure—Performance and Scalability, written by Bill Karagounis of Microsoft (when he was a Lead Program Manager on the IIS team) is the "textbook reading" behind several of the other resources covered here. It is intended for a thoroughly technical audience—operations staff, IT administrators, developers, and architects. It's a Web page but the equivalent of 50 or 60 printed pages, so don't count on absorbing the entire content in a single sitting. The page does provide jump heads for easy navigation, and the text includes plenty of supplementary graphics and tables.
This article explains the Windows Server 2003 Web and application server infrastructure with an eye to both the business benefits to be realized and the creation of a solid foundation for .NET-based applications and Web services. The four main sections cover architecture (for all four audience groups), programming (primarily for developers and architects), performance (for all four audience groups), and application scenarios (primarily for operations staff and IT administrators).
"Performance Tuning", one section of the comprehensive and exhaustive Internet Information Services 6.0 Product Documentation, is an excellent place to start if you want to further solidify your IIS 6.0 operational fundamentals before delving into the technical esoterica.
The subsections, each on its own Web page, comprise terse how-to summary information on HTTP compression and keep-alives, limiting connections and setting connection timeouts, throttling bandwidth, enabling CPU monitoring, configuring application pool queue length limits, and scalability.
The detailed discussion on each of those topics appears in a must-have item for IIS mavens: the Internet Information Services (IIS) 6.0 Resource Kit. You can download the 42.6MB document in .zip format, or you can pick and choose individual chapters in .doc format. Part 1, which includes seven chapters and two appendices, covers deployment. Part 2, which includes 11 chapters and seven appendices, covers operation and performance. Of special interest is Part 2, Chapter 6, "Optimizing IIS 6.0 Performance".
To drill down into the subject of building .NET-based applications, curl up and spend some time with Improving .NET Application Performance and Scalability. You'll need plenty of time to take in all this comprehensive Patterns & Practices resource offers.
For architects, designers, developers, testers, and administrators, this guide provides assistance for managing performance and scalability throughout your application lifecycle and provides a framework that organizes performance into categories. It provides processes for modeling performance, measuring, testing, and tuning your applications and for improving the performance of managed code, ASP.NET, Enterprise Services, Web services, remoting, ADO.NET, XML, and SQL Server™.
Viewable online or downloadable in PDF format (6.1MB, 1120 pages), the guide is organized into five sections:
  • Introduction to Engineering for Performance (Chapter 1)
  • Designing for Performance (Chapters 2-4)
  • Application Performance and Scalability (Chapters 5-13)
  • Database Server Performance and Scalability (Chapter 14)
  • Measuring, Testing, and Tuning (Chapters 15-17)
And as if to put new shades of meaning on the words "thorough" and "follow through," the guide includes four features beyond its text that further extend its usefulness:
  • "Performance Best Practices at a Glance" summarizes the solutions presented in "Improving .NET Application Performance and Scalability" and provides links to the detailed material in the guide so you can easily locate the information you need to implement the solutions (for example, "How to Improve Code Access Security Performance").
  • "Fast Track—A Guide for Getting Started and Applying the Guidance," aimed primarily at managers who want to implement the guidelines set forth in the technical material.
  • Checklists: printable, task-based quick-reference sheets to help you put the information and details that you learned in the individual chapters into action (for example, "Checklist: Managed Code Performance").
  • A host of task-specific how-to instructions complete with example code and cross-references back to the full text (for example, "How To: Use CLR Profiler").
  • A list of newsgroups covering development and performance in the .NET Framework, common language runtime (CLR), caching, and several ASP.NET topics (not in the document itself, but toward the foot of the Improving .NET Application Performance and Scalability page).

Webcasts
Three webcasts offer an excellent way of obtaining much the same information as in the text-based resources already noted, but with the additional dimensions of both visuals and commentary from Chris Adams, Microsoft Web Platform Supportability Lead. Chris is an authoritative IIS information source, having spent several years at Microsoft supporting IIS products. He is a certified MCP, MCSA, and MCSE on the Microsoft Windows NT® 4.0 and Microsoft Windows 2000 platforms.
Primarily for system administrators is Have Your Cake and Eat It, Too: Understanding How ASP.NET Works with IIS 6.0, a 90 minute, intermediate-level TechNet production. Titled "Scalability & Reliability Best Practices for IIS 6.0" on the first slide, the presentation reviews the ways in which IIS 6.0 works differently from previous releases, then covers many reliability and scalability features and best practices. In so doing, Adams provides some specific, real-world system management advice, such as what to look for, what to change, and what to leave alone—as if he were a sysadmin himself. He clearly understands the challenges people in that role face in overseeing Web servers.
The presentation assumes your familiarity with IIS 6.0 and maintains a rapid pace throughout its discussion, whiteboard note-taking, and demonstrations using built-in system tools. For further reading, it suggests the IIS Resource Kit already mentioned, especially Part 1 Chapter 4 and Part 2 Chapters 2, 6, and 7, and a paper on ASP.NET performance monitoring.
In Lessons Learned with IIS 6.0 (45 minutes) Adams is quite frank in discussing many of the lessons learned at Microsoft subsequent to the release of IIS 6.0 and explains the rationale, for better or worse, behind some of the software's coding. Further, he provides numerous specifics, not all of which are summarized on the presentation's slides, about what to do in different situations and under different conditions. This webcast is not really a "tips and tricks" collection, but rather a session that addresses some of the specific issues users have raised as of mid-2004.
Tips and Tricks for Optimizing Performance in IIS 6.0 (90 minutes), which Adams co-presents with Jeffrey Johnson, who provides the IIS Performance Test Lead perspective, is an advanced-level presentation for the IT professional wanting peak performance from an IIS 6.0 Web server.
Adams and Johnson provide plenty of tips and tricks and demonstrate some tools, but Adams is careful to point out that they're offering basic frameworks, not one-size-fits-all panaceas or actions. Topics include network latency; efficient coding practices; and methods to ensure that IIS 6.0 serves content the right way, at the right time. It also examines some of the performance tools the IIS product group uses and concludes by making these points:
  • Performance is ultimately driven by key decisions affecting, in this order of importance, end users (if they're unhappy, soon everyone becomes unhappy), throughput (the value-per-second rates you're getting), and capacity (analyze the real problem rather than throw money at it)
  • You must understand your applications and how end users actually use them before you can really tune for performance
  • Attack your current bottleneck as the best way to improve performance
  • Don't count on an immediate, magical solution—performance improvements are achieved iteratively
  • Test your application scenarios thoroughly
Adams and Johnson suggest three additional webcasts that are of particular interest:
  • "IIS Data Mining with Log Parser 2.x"
  • "Recycling IIS 6.0 Applications: The Good, the Bad, and the Ugly"
  • "Using Granular Compression in IIS 6.0"

The IIS Webcast Catalog
To find those three titles, as well as the entire catalog of IIS-specific webcasts going back to July 2003, go to Internet Information Services Webcasts. That page also has links to the schedule of upcoming webcasts and the complete Microsoft collection. The latter page includes webcasts on all subjects, though, not just IIS, so use the Ctrl-F search window to quickly find terms and topics of interest to you.

Phil Sherwood (pts@centurytel.net) is principal of Witan Consulting, which provides technical and marketing writing, products and program management, and general business management support to both small businesses and large technology development companies.


 TechNet Live Events Roundup: Vacation 1.0
TechNet Live Events Roundup
Vacation 1.0
Kai Axford

I know the last thing you want to do over the summer is to sit in a classroom for eight hours a day, listening to an instructor drone on, especially when you'd rather be waiting in line for Star Wars: Episode III. But summer is such a good time to get some technical training. What's an IT pro to do? Wouldn't you love to find a free live technical seminar that is pertinent to your work and free of marketing hype? Yeah, right.

Off to the Movies
Well, actually, TechNet Briefings are held in local movie theaters, last only four hours, and are just like Star Wars (but without the amazing special effects). These events cover topics like IPSec Filtering and RPC over HTTPS so they're perfect for the IT professional, technical decision maker, and technical channel partner. Anyone else will get a headache.
Sessions delivered in the past year included migrating Windows NT® 4.0 to Windows Server™ 2003, migrating Windows NT 4.0 line of business applications, migrating Exchange Server 5.5 to Exchange Server 2003, Exchange Server 2003 disaster recovery, an ISA Server 2004 technical overview, and lots more.
The new briefings for May and June 2005 will include a lot about SQL Server™ 2005 since it's the next big application upgrade many of you are planning. Learn more or sign up at technetbriefings.com.
Each four-hour seminar will cover two or three topics that range from a 200-300 technical level. Plus, technical presenters will be on hand to answer your questions and stay after the show to discuss the material. They'll even follow up in e-mail if necessary. Plus you'll get a free TechNet Kit that comes with lots of great reference material on DVD including whitepapers, presentations, and many other resources and training opportunities.
You can even arrange to have some of our presenters speak at your user group meeting. You can find out more about utilizing our speakers at www.culminis.com.
For more about the program, e-mail the TechNet Briefings Managers at tnmgr@microsoft.com. Tell them Kai sent you!

What About P-Learning?
You know, poolside learning. Don't you learn better while basking in the sun? Then TechNet webcasts are for you. All you'll need is your waterproof laptop, a floating chaise lounge with one of those drink holders, and a wireless Internet connection. Each 90-minute webcast has live demos, slideshows, and an Ask Questions interface. If we get more questions than we answer in 90 minutes, we can run live chat sessions immediately after the event. See Upcoming Chats.
Here are just some of the more than 100 topics we've been talking about during our recent webcasts:
  • Windows Server 2003
  • Migrating Oracle and UNIX Systems to SQL Server and Windows
  • What's New for the DBA in SQL Server 2005
  • Scripting (with "The Scripting Guys"!)
  • Windows Server 2003 Service Pack 1: Technical Overview
TechNet is available 24×7 through the On-Demand webcasts option. Check it out at Events and Webcasts.

Server-Side Rentals: Inquire Within
Have you ever been given a piece of software, maybe even an evaluation copy, and thought, "If only I had the right hardware to test this!" or even, "Is there a manual or something for this?" Unfortunately TechNet hasn't given out hardware to test the software on...until now.
Microsoft recently introduced the wildly popular TechNet Virtual Labs. Now when I mention Virtual Labs, I'm not talking about some Shockwave demo that you just watch. I'm talking honest-to-goodness, real, hands-on labs. Essentially they are pushing out a Virtual PC session to your local machine through a Microsoft® Internet Explorer ActiveX® control. That's right, in the event you'd need multiple machines to finish the lab, we'll actually set that up as well. We just make the connections from the local machine to the other machines using Remote Desktop, which is already configured. In Figure 1, I'm using the Microsoft Operations Manager (MOM) 2005 Virtual Lab to explore a preconfigured error in my SQL Server 2000.
Figure 1 Working with MOM 2005 in the Virtual Lab
Figure 1 Working with MOM 2005 in the Virtual Lab
Not only do you get the Virtual PC session running on your local machine, a PDF lab manual is included so that you're actually doing what needs to be done, the recommended way. You can even print it out. Of course, if you're the guy who just likes to click random buttons, the lab will accommodate you as well, since this is your server—at least temporarily.

Knowledge is Power
It's always difficult to schedule technical training for network administrators. Something as simple as a user with a forgotten password can keep you from attending some training you need to do your job. Taking a week off to attend a class is not always an option, and even then, network "issues" (remember, network issues = network users) don't always wait until you're out of class.
I sincerely hope you attend some of the live TechNet Briefings this year. The team really looks forward to meeting the people in the trenches. As an IT pro, I know how important it is to get the right information or at least enough info so you can fix the network before all your "issues" return from their vacations.

Kai Axford CISSP, MCSE-Security, has been with Microsoft for the past five years as a support engineer and TechNet speaker. He is currently serving as a Lead Security Presenter and speaks to thousands of IT pros at live events. Reach Kai at kaiax@microsoft.com.


 TechNet Update: Highlights From The Microsoft IT Community
TechNet Update
Highlights From The Microsoft IT Community

The volume of desktop deployment information on the TechNet site can seem overwhelming, and at times it might be difficult to find exactly the information you need. Here we will point you to some of the most popular sites on TechNet, which should help in your search.

Desktop Deployment Made Easy
For deployment projects, look to the Desktop Deployment Center. The Deployment Center offers information organized logically by the different functional areas of most business desktop deployment projects. For example, all of the information about disk imaging is brought together in one place. Other functional areas include application compatibility, infrastructure management, deployment, user state migration, security and patching, and provisioning. Each section contains links to articles that provide you with more detailed technical information.
The Desktop Deployment Center is organized around the Solution Accelerator for Business Desktop Deployment, a framework for planning, building, and deploying business desktop deployment projects (see Microsoft Solution Accelerator for Business Desktop Deployment). The solution includes step-by-step guidance for deploying desktops and includes planning templates.

A Virtual Lab
The TechNet Virtual Lab for Desktop Deployment gives you access to training labs for application compatibility, user state migration, Systems Management Server Operating System Deployment Feature Pack, and more. These training labs include lab manuals and virtual environments offering an experience similar to live seminars with hands-on labs, but you don't have to leave your desk and you don't have to do any prep work. There are related labs at TechNet Virtual Labs, including labs for Microsoft® Operations Manager (MOM) 2005 and Systems Management Server 2003.

Webcasts
TechNet has numerous desktop deployment webcasts (see Desktop Deployment: Webcasts and Virtual Labs) covering a wide range of topics, including planning, application compatibility, disk imaging, distribution, and security. If you're a TechNet subscriber, check your disks for e-learning courses on business desktop deployment, Windows Server™ deployment, and a MOM 2005 overview.
Scripting
As scripting has become a more integral part of the IT pro's deployment work, a popular resource on TechNet is the Script Center. The TechNet Script Center contains hundreds of scripts, which offer solutions for many scripting problems.

Additional Resources
TechNet also links you to great business desktop deployment information beyond its own site. Resource Kits are an essential source of technical information. The three most valuable links on this page are Windows® Deployment and Resource Kits, Windows XP Professional, and Office 2003. The Resources page also provides links to resources for finding and downloading device drivers (see TechNet Driver Resources).


 Field Notes: IT and Life Experiences
Field Notes
IT and Life Experiences
Joshua Hoffman

IT pros don't write code. IT pros don't develop applications, and we certainly don't want anything to do with mathematical algorithms and binary search trees. We leave that to the dev geeks with CompSci PhDs and cases of Mountain Dew under their desk. Okay, maybe that's a gross generalization and completely unfair to IT pros (not to dev geeks, though—you know who you are). But let's face it, there are those who work in infrastructure (welcome to TechNet!) and those who work in appdev (don't complain, you've had MSDN® for years), and never the twain shall meet. Except code can make your life easier. Scary as it may be, if IT pros are willing to open their hearts and minds a little to the idea of squeaking out a few miniscule lines of code, they'll have more free time, and what could be more important than that?
I'm not asking you to dig into header files or author COM objects or anything like that. We do, after all, have to remain loyal to our infrastructure core. But a few lines of plain-text batch or VBScript can go a long way towards finding some of that free time. For example, imagine your boss tasked you with creating, oh I don't know, 5,000 test accounts in Active Directory® in order to do some performance and application testing for an upcoming project. Definitely not a process you want to do manually, right? That's a few lines of VBScript. A few variables, a few loops, and you're finished. Want to create virtual machines instead of Active Directory accounts? Remember, each machine needs a virtual hard drive to go with it, as well as a network connection, memory settings, and user permissions. That's a grand total of 13 lines of code, and most of it is just defining the names of the machines.
Maybe you need to monitor Exchange queues or security logs. Or maybe you just need to rename a bunch of files, or get a list of disabled devices on your computer, or check the free space available on every computer in your organization. These tasks are a drop in the bucket when it comes to examples of things you can do with very simple scripts.
Personally, I'm a desktop deployment guy. Most of my time is spent helping companies deploy the very latest and greatest operating systems, applications, and updates (namely Windows® XP Service Pack 2, and Office 2003). I've done this work for a lot of companies, and not a single one of them was the same. Every organization likes their desktop to look and behave just a little bit differently. One company may want My Computer on the desktop while another may not. One company may want to show hidden files and another may not. And, of course, every company wants to use their custom wallpaper, and some of them may even want to prevent the end user from changing that wallpaper to a picture of their kid (or their car).
Almost anything you want to do to your desktop can be automated in a pretty simple script. For most desktop builds, I generally will create two files—an OSConfig.cmd and an OSConfig.vbs. The CMD file primarily includes reg.exe commands (the Console Registry Tool for Windows which allows command-line arguments to modify the registry). This is used to make simple modifications to the registry in order to customize the environment. OSConfig.vbs may make some of the more complicated changes, where Windows Management Instrumentation (WMI) may be involved (as the changes may be hardware-specific).
To learn more, go to The Script Center. Under the esteemed supervision of The Scripting Guys, you will find everything you need to get started. I highly recommend taking a look at the sample scripts repository first. There is a huge collection of scripts there that will help you perform a wide variety of tasks, including all of those I've mentioned already. Of course, there is also the "Hey! Scripting Guy!" advice column, real-life stories in "Tales from the Script," and a whole lot more.
Your next stop should be the Microsoft Windows Scripting Self-Paced Learning Guide. The Learning Guide will walk you through the necessities of scripting, including VBScript, Windows Script Host, Windows Management Instrumentation, and Active Directory Service Interfaces (the Trinity+1 of scripting).
Now please keep in mind that scripting alone is not generally a replacement for enterprise-scale products when it comes to monitoring and administration. But I guarantee there is a task in your day-to-day life that you can make easier with a script. Knowing that, I hope you'll consider taking that first step towards letting a little bit of scripting into your IT life.

Joshua Hoffman has been a desktop and infrastructure consultant with Microsoft Consulting Services for the past four years. A graduate of Rensselaer Polytechnic Institute, he currently operates out of the Microsoft office in Malvern, PA.