SQL Server Magazine UPDATE, February 6, 2003

SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
http://www.sqlmag.com

THIS ISSUE SPONSORED BY

XMLSPY 5 Release 3 - Download NOW!

SQL Server Magazine Connections
(below COMMENTARY)

Dice. Tech Jobs. Tech Talent.
(below NEWS AND VIEWS)

SPONSOR: XMLSPY 5 RELEASE 3 - DOWNLOAD NOW!
Build XML applications for Microsoft SQL Server 2000 with the NEW XMLSPY 5 - the ultimate XML tool for SQL Server developers and database administrators! XMLSPY supports Microsoft SQL Server SQLXML 3.0 annotations for XML Schema allowing for creation of relational views on XML data, import/export of XML or relational data to and from SQL Server tables, auto-generation of XML Schemas from SQL Server schemas, generation of SQL Server schemas from XML schemas and more - XMLSPY simplifies Microsoft SQL Server programming and data management - Download a FREE trial NOW!

February 6, 2003—In this issue:

Editor's Note: On January 28, we inadvertently sent one of our email newsletters, Developer .NET UPDATE, to the readers of SQL Server Magazine UPDATE. We apologize if you received that newsletter in error. Your name has not been added to the Developer .NET UPDATE list.

1. COMMENTARY

2. SQL SERVER NEWS AND VIEWS

3. ANNOUNCEMENTS

4. RESOURCES

5. HOT RELEASE (ADVERTISEMENT)

6. NEW AND IMPROVED

7. CONTACT US
See this section for a list of ways to contact us.

1. COMMENTARY

(contributed by Brian Moran, news editor, [email protected])

As everyone in the SQL Server community is aware, a worm called SQL Slammer, aka Sapphire, began spreading rapidly around the world January 24, attacking systems through a vulnerability in SQL Server 2000. SQL Slammer didn't try to compromise data stored in SQL Server; instead, it generated massive amounts of network traffic, leading to a global traffic jam in many parts of the Internet. The Washington Post reported that SQL Slammer shut down a Bank of America ATM network, Continental Airlines' online ticketing system, and an emergency call center in Seattle as well as cutting off Internet access for millions of PC users worldwide, including most users in South Korea.

Interestingly, Microsoft first addressed the SQL Server 2000 security flaw exposed by the Slammer in a July security fix. Although systems administrators can choose to ignore security hotfixes as they're released, you can bet that hackers pay close attention to them. Professional systems administrators have no excuse for letting their systems go unpatched for months. Let's set the record straight: SQL Slammer couldn't have spread if professional administrators had applied the patch last July. Notice that I said "professional" administrators.

For better or worse, many systems don't have dedicated, full-time, professional administrators. While professional administrators should be ashamed of themselves for letting SQL Slammer strike, what about the people who manage SQL Server part time or the folks who don't even know that SQL Server is running on their systems? Microsoft SQL Server 2000 Desktop Engine (MSDE), for example, is basically a copy of the SQL Server database engine. It's installed by default or as an add-on option through a host of Microsoft programs and third-party applications. According to reports, many of the "slammed" SQL Servers were actually instances of MSDE. It's hard to protect what you don't know is there.

Software products have bugs. Vendors have a responsibility to patch those problems and vulnerabilities as they learn about them, and professional administrators have a duty to keep security and other key patches up-to-date. But there aren't easy answers for the problems of inexperienced, part-time administrators and stealth software that customers don't know needs fixed. Ultimately, Software Update Services and Windows Critical Updates might provide the solution for automatically updating our applications. For non-technical corporate and home users, computers need to become smart enough to alert us to security holes we've left open through poor configuration choices and failure to apply the latest security patches.

The Microsoft SQL Server team has been telling me for months that it's committed to the goal of Trustworthy Computing, and last week the team demonstrated that commitment by providing resources to help its customers quickly understand the Slammer attack and protect against it. Not long ago, Microsoft would have probably issued a defensive response: "We issued a patch; it's not our fault that people didn't install it." But if you visit the Microsoft SQL Server home page today, you'll find half of it devoted to content about SQL Slammer. In addition, the company has posted three new tools designed to help you find systems that might be vulnerable to the Slammer and similar attacks.

Microsoft, recognizing that it shares the burden in helping us protect our systems, has also launched a new SQL Server newsgroup called microsoft.public.sqlserver.securitytools, available at msnews.microsoft.com or at http://communities.microsoft.com/newsgroups/default.asp. The newsgroup's goal is to provide community support for security issues and to provide Microsoft support for using the new security tools. You'll also find a site devoted to the Slammer, which offers a wide range of technical resources to help you manage this threat.

Microsoft cynics sneer that the company's response is mere window-dressing and that it isn't really serious about tackling the security problems that are rampant in all computing systems. But the people I've talked to in the company's SQL Server group maintain that database security and Microsoft's Trustworthy Computing initiatives are among their most important priorities. We'll see how the SQL Server team delivers on those goals over the coming weeks and months.

SQL SERVER MAGAZINE CONNECTIONS

If you're looking for 3 to 4 days of technical drilldowns into Microsoft SQL Server, an opportunity to interact live with your favorite SQL Server Magazine writers, or a chance to talk firsthand with Microsoft product architects, then you should register today to attend SQL Server Magazine Connections. Register now, and you'll also gain FREE access to concurrently run Microsoft ASP.NET Connections and Visual Studio Connections. You'll get 3 conferences for the price of 1! Go to:
http://lists.sqlmag.com/cgi-bin3/flo/y/ePWc0FgQMn0BRZ07hd0AV

2. SQL SERVER NEWS AND VIEWS

SQL Server 2000 Service Pack 3 (SP3) provides an enhanced security-related option for configuring cross-database ownership chaining during setup: "Enable cross-database ownership chaining for all databases." The Microsoft article "INF: Cross-Database Ownership Chaining Behavior Changes in SQL Server 2000 Service Pack 3" explains the new option, which lets you control whether or not to permit cross-database ownership chaining. By default, this option is disabled, and Microsoft recommends that you use the default option because it makes your database server more secure.

Microsoft warns that SQL Server 2000 Service Pack 3 (SP3) installation will fail if either of the following conditions are true:

For a workaround and more information about the problem, see the Microsoft article "PRB: SQL Server 2000 Service Pack 3 Setup Fails If Windows Security Policy Is Set".

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "When did you apply the patch for the elevation of privilege vulnerability that SQL Slammer is attacking?" Here are the results (+/- 1 percent) from the 300 votes:

   - 27% Last year, when the patch was first released   - 17% When the cumulative patch was released   - 40% When Slammer began its attack   - 17% We haven't applied the patch yet

(Deviations from 100 percent are due to rounding error.)

The next Instant Poll question is "Did the Slammer/Sapphire worm directly affect your network, connectivity, or computerized activities?" Go to the SQL Server Magazine Web site and submit your vote for 1) Yes or 2) No.

SPONSOR: DICE. TECH JOBS. TECH TALENT.

Dice, the #1 tech job board. If you are a technology expert in areas such as software, security, biotech, aerospace or engineering, Dice will assist you with finding your next great career opportunity.

3. ANNOUNCEMENTS

(brought to you by SQL Server Magazine and its partners)

The industry's best instructors have teamed with SSMU to bring you the finest live online SQL Server training! Whether you're advanced level or just beginning, you'll find training to meet your needs. Plus, you don't have to leave your desk; events are delivered live via the Internet! Click here.

SQL Server Magazine and CSA Research are proud to introduce the Database Performance Portal. IT professionals use the Performance Portal to conduct client, server, and network scalability studies; perform ad hoc systems health analysis; identify infrastructure bottlenecks; conduct off-site diagnostics; and qualify new hardware purchases. To visit the portal, click here.

4. RESOURCES

The concept of NULL has been part of relational databases since their inception, and NULLs have always garnered special treatment. Most developers wish there were a tried-and-true method for determining the best way to test for and set NULL values in their applications, regardless of what language the applications are written in. In classic ADO, developers have to deal with NULL values because they can't assign a NULL to a numeric variable, a string, the Value property of a TextBox control, or any control that doesn't accept a variant data type (which you can set to NULL). But ADO.NET and Visual Basic .NET have better support for NULL values. William Vaughn's February SQL Server Magazine article, "Much ADO About Nothing," explains how ADO.NET lets you store, set, and test for NULL values in a variety of ways. You can read this article online.

Rangan, a new SQL Server administrator, asks whether he can use Performance Monitor or a script to monitor the number of SQL Server user connections and to view or retrieve the usernames connected to the database. Offer your advice and read other users' suggestions on the SQL Server Magazine forums at this URL.

(contributed by Brian Moran, [email protected])

Q. I'm looking for shareware or an inexpensive tool that will let me maintain a SQL Server data dictionary. I've used sophisticated data-modeling tools that provide this functionality. However, I'm now in an organization that has many SQL Server databases but no data dictionary and no budget to buy expensive tools. Do you know of a poor man's version of a data-dictionary tool?

A. Although a free or inexpensive shareware tool might exist, I'm not familiar with it. However, I suggest that you try using SQL Server 2000's extended properties. In SQL Server 2000, Microsoft added extended properties to let you define and manipulate user-defined properties on various database objects. You can use these user-defined properties to add metadata to your database that you can use to create a homegrown data dictionary integrated directly into SQL Server. You manage these properties by using the system stored procedures sp_addextendedproperty, sp_updateextendedproperty, and sp_dropextendedproperty. In addition, you can use the system function fn_listextendedproperty() to retrieve an existing property value. Microsoft uses extended properties to populate and manage the description value that you see associated with a column in the design view of a table in Enterprise Manager.

Note that using these stored procedures and the fn_listextendedproperty() function to create and manage extended properties isn't intuitive. The SQL Server Books Online (BOL) topic "Property Management" gives you only basic information about extended properties. Fortunately, SQL Server Magazine has published several good articles about this topic. For more information about using extended properties, see Michelle A. Poolet, Solutions by Design, "A Business Metadata Repository," October 2002, InstantDoc ID 26273; William Vaughn, "Managing Extended Properties," July 2001, InstantDoc ID 20886; and SQL Server Savvy, "Tip: Managing Data Dictionaries Based on SQL Server 2000 Extended Properties," November 2001, InstantDoc ID 22399. All three articles explain how to use extended properties, and each article provides a shell that you can use for writing your own poor man's data dictionary.

Send your technical questions to [email protected].

5. HOT RELEASE (ADVERTISEMENT)

Learn how to make the most of your tuning time by understanding all of the results returned by SHOWPLAN_TEXT and the Graphic SHOWPLAN tool. Download this FREE T-SQL Tuning Tips white paper today!

6. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

Application Security has enabled AppDetective, its vulnerability-assessment software, to scan your SQL Servers for SQL Server 2000 Service Pack 3 (SP3). By selecting a computed column that references a user-defined function (UDF), you can verify whether your SQL Server installation is vulnerable to a Denial of Service (DoS) attack. You can also check SQL Server to see whether or not database ownership chaining has been disabled. New AppDetective capability also verifies that a patch hasn't been installed that prevents someone from revealing the account name that SQL Server runs under through an invalid OPENROWSET command. For pricing, contact Application Security at 212-420-9270 or 866-927-7732.
http://www.appsecinc.com

Shavlik Technologies is providing free tools that can detect missing SQL Server security patches on your computers and servers and can guard against the SQL Slammer worm. HFNetChkLT and HFNetChk.exe are tools you can download from Shavlik Technologies' site. You can use the tools to determine which machines in your network are missing crucial patches. HFNetChk is especially important because Microsoft Windows Update doesn't support SQL Server patches.

7. CONTACT US

Here's how to reach us with your comments and questions:

* WANT TO SPONSOR SQL SERVER MAGAZINE UPDATE?

More than 102,000 people read SQL Server Magazine UPDATE every

week. Shouldn't they read your marketing message, too? To advertise in

SQL Server Magazine UPDATE, contact Beatrice Stonebanks at

[email protected] or 800-719-8718.

SQL Server Magazine UPDATE is brought to you by SQL Server Magazine, the only magazine completely devoted to helping developers and DBAs master new and emerging SQL Server technologies and issues. Subscribe today.

The SQL Server Magazine Connections conference—loaded with best-practices information from magazine authors and Microsoft product architects—is designed to provide you with the latest SQL Server tools, tips, and real-life examples you need to do your job.