New Incentives Loom for Data(base) Encryption [Updated]

SQL Server 2005's native data encryption features gain new importance as U.S. congressmen and state lawmakers curry favor with constituents alarmed by Internet identity theft. Another 40 million compromised credit card numbers and "security codes" add fuel to calls for protection of consumers' private financial information.

[Update 1/26/2006] The U.S. Federal Trade Commission (FTC) has levied a US$10 million fine on ChoicePoint for violations of the Fair Credit Reporting Act (FCRA). The FTC also expects ChoicePoint to establish a US$5 million "trust fund for individuals who might have become victims of identity theft as a result of the breach." Senators Charles E. Schumer (D-NY) and Bill Nelson (D-FL) introduced S. 768, the "Comprehensive Identity Theft Prevention Act," on April 12, 2005. Subsequently, S. 768 gained Sen. Mark Dayton (D-MN) and Sen. Edward M. Kennedy (D-MA) as cosponsors. According to the Commercial Law League of America (CLLA), the Act would establish an Office of Identity Theft within the Federal Trade Commission and would provide the FTC with broad authority to prevent identity theft and establish limitations on businesses that collect, maintain, sell or transfer sensitive personal information of individuals. The FTC would have civil jurisdiction over all commercial organizations that collect, maintain, sell or transfer sensitive personal information. Sensitive personal information includes an individual's:

• Social security number
• Driver's license number or state identification number
• Bank or investment account number
• Credit or debit card number
• Certain medical information
• Payment history
• Other information specified by the FTC

Unauthorized disclosure of sensitive personal information could result in civil penalties up to $1,000 per violation, depending on the nature of the violation, such as failure to meet "reasonable standards" for data protection.

A recent New York Times article, "The scramble to protect personal data," mentioned the newly-proposed act in conjunction with CitiGroup's loss of a box of unencrypted backup tapes of CitiFinancial records that contained sensitive personal information (names, addresses, Social Security numbers, and account numbers) for about four million customers. If the act were in effect before the loss, CitiGroup's liability could be as much as $4 billion. A June 10, 2005 InfoWorld editorial, "Another week, another few million confidential records lost," provides more details on CitiGroup's lost backup tapes and the 3.9 million notices being sent. The tapes were lost in transit from a New Jersey datacenter to a credit bureau in Texas. According to the article, "Citibank made it clear in its statement that the company had plans to begin encrypting their credit bureau information." Computer security expert Bruce Schneier weighed in with a different take on the CitiGroup loss. Schneier is concerned that the California Information Practices Act (S.B. 1386), which requires entities to notify persons whose unencrypted Social Security, state identification, driver's license, bank account, or credit card numbers have been subject to unauthorized access, will lead to reduced press coverage of personal information theft and loss incidents. A compromise of 100,000 customer records doesn't give rise to a major news story after earlier (and repeated) reports that millions of personal records have been lost or stolen. Update: June 18, 2005: In a record-breaking security breach, MasterCard International reported on June 17, 2005 that about 40 million credit and debit card account numbers and security codes might have been stolen from CardSystems Solutions, a MasterCard processor. About 20 million were Visa cards and 13.9 were MasterCharge; the remainder were American Express and Discover cards. A New York Times article quoted a MasterCard spokesperson: "[A]n infiltrator had managed to place a computer code or script on the CardSystems network that made it possible to extract information." The Times article explained that "MasterCard said its investigation found that CardSystems, in violation of MasterCard's rules, was storing cardholders' account numbers and security codes on its computer systems. That information, MasterCard said, was supposed to be transferred to the bank handling the merchants' transactions but not retained by CardSystems." It's not clear from the article, but the term "security codes" might be the three-digit card validation code that's printed on the back of MasterCharge, Visa, Discover, JCB, or Diner's Club cards or four-digit code for American Express cards. The card validation code usually is required for on-line transactions. A San Francisco Chronicle article states: "Neither MasterCard nor Visa would say what was lacking in the firm's security, except to say it was out of compliance with their minimum security standards. But experts say that in order for a hacker to steal and use the information, it could not have been encrypted, a basic step that is required by the card companies' standards." The article goes on to quote Gartner analyst Avivah Litan: "They [MasterCard] weren't actively monitoring compliance. It wouldn't take that much to send an auditor to see if that data is encrypted or not." Neither Visa nor MasterCard require encrypting stored credit-card data. For example, here's a link to the Payment Card Industry (PCI) Security Standard on the Visa site, and a link to a simplified version on the MasterCharge site. MasterCard paraphrases PCI requirement 3 as "Protect stored transaction data. Keep transaction storage to a minimum and never store sensitive authentication data after authorization. Take precautions to make stored transaction data unreadable through encryption or some other secure and robust approach." [Emphasis added.] Following is the text of the Visa U.S.A. Cardholder Information Security Program (CISP) Frequently-Asked Questions #7: "7. Are there alternatives to encrypting stored data? Stored cardholder data should be rendered unreadable according to requirement 3 of the PCI Security Audit Procedures document. If encryption, truncation, or another comparable approach cannot be used, encryption options should continue to be investigated as the technology is rapidly evolving. In the interim, while encryption solutions are being investigated, stored data must be strongly protected by compensating controls. These compensating controls should be considered as part of the compliance validation process. [Emphasis added.] An example of compensating controls for encryption of stored data is complex network segmentation that may include the following: • Internal firewalls that specifically protect the database • TCP wrappers or firewall on the database to specifically limit who can connect to the database • Separation of the corporate internal network on a different network segment from production, fire- walled away from database servers." MasterCard's Electronic Commerce Best Practices for Acquirers classifies encryption of stored data as a "best practice," not a requirement. (The original title of this document was "Electronic Commerce Requirements and Best Practices for Acquirers.") MasterCard processors (a.k.a. "acquirers") "themselves do not need to go through the SDP compliance process but they must manage the SDP process for their merchants and service providers." [SDP is an abbreviation for MasterCard's Site Data Protection process for merchants.] A TransactionWorld page compares Visa's CISP and MasterCharge's SDP as of February 2004. [Note that an issuer whose account data was exposed to possible compromise as a result of this event has a right to claim reimbursement for costs related to reissuance of cards and monitoring of potentially compromised accounts that remain open. For any given account, the issuer may request reimbursement of up to US$25.00 for each reissued card, or up to US$5.00 for each monitored account without reissuance. In theory, CardSystems could be liable for as much as US$1 billion in issuer charges if all 40 million cards were reissued.] End of June 18, 2005 Update.

Update, July 1, 2005: Infoworld's Ephraim Schwarz concludes that Visa and MasterCard weren't enforcing their own security requirements. "Frank Smith, vice president of the technology strategy group at Capgemini, said, 'They don’t supply due diligence to the whole system.' Gartner’s [Avivah] Litan said, 'They have everything in place; they just don’t enforce it.' Paul Stamp, security analyst at Forrester Research, said 'The processes were not properly enforced.' [John] Pescatore [a Gartner security analyst] said that the standards 'have been pure eyewash. No enforcement.' End of July 1, 2005 Update:

June 20, 2005 Update: CardSystems' CEO John Perry admitted to the New York Times (free registration required) that his company should not have stored the data that was compromised. CardSystems was using the stored transaction data for "research purposes." The Times article states: "Under rules established by Visa and MasterCard, processors are not allowed to retain cardholder information including names, account numbers, expiration dates and security codes after a transaction is handled." The article confirms that "security codes" refers to the three-digit or four-digit codes printed on the back of the credit card, which are specifically embargoed from storage by merchants or processors. End of June 20, 2005 Update.

Update July 22, 2005: Visa USA announced on July 18, 2005 that it will no longer allow CardSystems to process Visa transactions. Visa will allow banks that use CardSystems to handle merchant transactions until the end of October 2005. The New York Times also reported on July 19, 2005 that American Express will terminate its relationship with CardSystems at the end of October 2005. In congressional testimony on July 21, 2005, CardSystems' CEO John Perry said the firm faces "immediate extinction" and blamed former Cable & Wireless auditors for practices that led to the Visa termination. End of July 22, 2005 update.

H.R.1653 (a.k.a. "Safeguarding Americans From Exporting Identification Data Act" or the "SAFE-ID Act") is entitled "To prohibit the transfer of personal information to any person outside the United States, without notice and consent, and for other purposes." H.R.1653 expands S.768's list of sensitive personal information to include the following:

• Name
• Postal address
• Financial information
• Medical records
• Date of birth
• Phone number
• E-mail address
• Social Security number
• Mother's maiden name
• Password
• State identification information
• Driver's license number
• Personal tax information
• Any consumer transactional or experiential information relating to the person

Consumers must take explicit action ("opt-out") to prevent U.S. firms possessing the personal information from transmitting it to "any foreign affiliate or subcontractor located in a country that is a country with adequate privacy protection." Opt-in is required for countries that don't have "adequate privacy protection." Most privacy advocates would place the U.S. in the latter category. After the CardSystems debacle, ordinary U.S. citizens undboubtedly would do the same. However, it's not so clear that any U.S. government agency would have the courage to so categorize this country. Violation of the Act would be treated as a violation of a rule defining an unfair or deceptive act or practice prescribed under section 18(a)(1)(B) of the Federal Trade Commission Act (15 U.S.C. 57a(a)(1)(B)). Persons could bring a state court action "to recover for actual monetary loss from such a violation, or to receive $10,000 in damages for each such violation, whichever is greater." Related—but more restricted—proposed acts would "regulate information brokers and protect individual rights with respect to personally identifiable information" (H.R.1080 and S.500) and "strengthen the authority of the Federal Government to protect individuals from certain acts and practices in the sale and purchase of Social Security numbers" (H.R.1078). H.R. 1080 would permit persons to bring a state court action "to recover for actual monetary loss from such a violation, or to receive $1,000 in damages for each such violation, whichever is greater." H.R. 1080 doesn't designate a default damage amount. The probability of enactment of any of these proposals in the current congress is low, at best. Few proposed consumer privacy acts have significant bi-partisan support (read "Republican cosponsors"). Heavy-handed and well-funded lobbyists will attempt to kill the proposals in committee. But grass-roots concern with identity theft is growing, so there's hope for some future form of federal protection for "personally identifiable information" beyond that currently provided by HIPAA. Update: June 18, 2005: The Gramm-Leach-Bliley act of 1999 (15 U. S.C. § 6801 et seq., GLBA) includes provisions that are purported to protect consumers' non-public personally identifiable financial information (NPI) by restricting its transfer from financial institutions to non-affiliated third parties. Currently, GLBA applies only to financial institutions that provide services to consumers, such as Visa and MasterCard—but not processors/acquirers like CardSystems. What's worse, consumers must proactively (affirmatively) opt-out of the third-party information-sharing process. Adoption of the "opt-out" method (versus the more commonly accepted "opt-in" approach that applies to health-related information) was the subject of an intense lobbying campaign by the financial industry in general and credit-card issuers in particular. It's not known how many, if any, of the holders of the 40 million compromised credit cards had exercised their "opt-out" rights. A relatively simple (but very unlikely) method of minimizing exposure of NPI to third parties is amendment of GLBA to change "opt-out" to "opt-in" and include credit-card processors and any other organizations in the processing chain as "financial institutions." End of June 18, 2005 Update. --rj P.S. Bruce Schneier points out in his recent "U.S. Medical Privacy Law Gutted" post that a new ruling by the U.S. Justice Department "sharply limits the government's ability to prosecute people for criminal violations" of the HIPAA privacy regulations. Criminal penalties, the department said, apply to insurers, doctors, hospitals and other providers—but not necessarily their employees or outsiders who steal personal health data.

A Business Intelligence Demo with Real-World Data

The Gartner Group attributes much of the 10.3 percent growth of the total relational database market from 2003 to 2004 to new business intelligence features—data analysis, warehousing and reporting—as well as the weakening US dollar. Gartner's May 2005 "No Clear Winner in Overall RDBMS Market Share Race" report gave IBM 34.1%, Oracle 33.7%, and Microsoft 20% of the total market. Teradata, Sybase, and others claimed 2.9%, 2.3% and 6.6%, respectively. Microsoft's market share increase from 18.7% in 2003 is surprising when you consider that prospective licensees were then anticipating Yukon to release to manufacturing in mid-2004, not November 2005. eWeek magazine's Lisa Vass quotes Microsoft director of SQL Server product management, Tom Rizzo: "BI is a tremendous growth driver for us, especially Reporting Services, which we've seen a ton of customers buying and deploying. That's why we invested so heavily in BI technologies across SQL Server. ... We put a down payment many years ago, and now it's paying off in terms of revenue growth." Unlike its major database competitors, Microsoft includes business intelligence integration, development, reporting and management features in the basic license fee for all editions of SQL Server 2000 (except MSDE) and SQL Server 2005 (except Express and Workgroup editions, which do include Reporting Services.) SQL Server 2005 has a raft of new and improved business intelligence (BI) features. But the standard sample online transaction processing (OLTP) and data warehouse (DW) databases are based on the relational AdventureWorks sample database. Demonstrating the performance of Integration Services (SSIS, formerly Data Transformation Services. DTS) extract, transformation, and loading (ETL) features isn't practical with tables that contain only a few hundred or thousand rows. You need partitions containing multimillion-row dimension and fact tables to emulate the BI systems of, for example, nationwide or multi-national retailers. The table-size issue with performance testing is similar to that I describe in my FTP Online articles about SQL Server 2005's xml data type and data encryption features. Microsoft's Project REAL is "a reference implementation of a business intelligence (BI) system using real large-scale data from a real customer." Phase 1 of the project used source data from a large electronics retailer. Phase 2's "real customer" is Barnes & Noble, the largest U.S. bookseller, who contributed the masked source data and BI scenario. Barnes & Noble have about 40,000 employees and 800 stores in the U.S.

According to the Technical Overview for Phase 2, Project REAL's goal "is to discover the best practices for creating BI systems with SQL Server 2005 and to build a system that exhibits as many of those best practices as we can. This project is not just a demo—we are creating this system for ongoing operation. It is a complete system, including daily incremental updates of the data, large multiuser workloads, and system monitoring."

The Technical Overview is the first in a promised series of articles that will be based on the B&N source data and analytical model. Hopefully, the Project REAL team will provide dynamic, online demonstrations of simulated ad hoc and preprogrammed BI reports. Making Project REAL's warehoused data and Reporting Services accessible to developers by Web service methods similar to those for TerraServer or MapPoint maps would be a major SQL Server 2005 marketing coup.

Hey, Tom Rizzo—are you listening?

--rj

Microsoft Promises RSS 2.0 Support in Longhorn

Steve Ballmer reportedly considers RSS "a little too simple," but damnation by faint praise by Microsoft's CEO hasn't prevented the Longhorn team from climbing on the RSS bandwagon. Dean Hachamovitch, general manager for Longhorn browsing and RSS announced in a June 24, 2005 keynote speech at the Gnomedex 5.0 conference that Longhorn will include support for RSS with a Common RSS Feed List and Data Store, plus an RSS Platform Feed Engine. Hachamovitch also proposed a specification for an RSS extension to support ordered lists. Despite its release under a Creative Commons Share-Alike license, the "Simple List Extensions Specification" generated an extraordinary amount of third-party blogging and comment activity, much of which was uninformed. The next day, a Longhorn RSS Team Blog opened with a post from Sean Lyndersay, senior program manager on Microsoft's RSS team and majordomo of the MSDN IEBlog. Developer feedback is handled by Channel9 Longhorn RSS and SimpleListExtensions WIKIs. Unfortunately, the RSS in Longhorn announcement occurred about three weeks after the publication date of my "Longhorn Redux" FTPOnline article, which concentrated on consumer features and IIS 7.0. It's good to see a feature added to—instead of removed or borrowed from—Longhorn for a change. --rj

Article: ChoicePoint and "Garbage In, Garbage Out"

Baseline magazine's Web site offers a full-length feature article, "ChoicePoint: Blur," which investigates inaccuracies in sensitive personal information distributed by data brokers, in this case ChoicePoint of Alpharetta, Georgia. ChoicePoint sold personal information—such as addresses, Social Security and driver's license numbers, and birth dates and locations—of about 145,000 individuals to Nigerian criminals posing as legitimate businesses. [Update 1/26/2006] The U.S. Federal Trade Commission (FTC) has levied a US$10 million fine on ChoicePoint for violations of the Fair Credit Reporting Act (FCRA). The FTC also expects ChoicePoint to establish a US$5 million "trust fund for individuals who might have become victims of identity theft as a result of the breach." The focus of the Baseline article however, isn't identity theft. Instead, the authors concentrate on the erroneous data that ChoicePoint supplies to its customers—legitimate or otherwise—and the company's failure to even attempt to verify the accuracy of third-party personal data it acquires and publishes. The Electronic Privacy Information Center (EPIC) has been pursuing details of government use of CheckPoint data since filing a Freedom of Information Act request in 2001. EPIC wants the FTC to regulate all CheckPoint data that contains sensitive personal information under the Fair Credit Reporting Act (FCRA), which gives consumers the right to view their records and correct erroneous information. Bruce Schneier weighs in with an observation that persons whose personal information was improperly disclosed by ChoicePoint to identity thieves would not have occurred were it not for California's S.B. 1386. S.B. 1386, the California Information Practices Act, requires data brokers and other organizations to report improper disclosure of unencrypted personal data on California residents. --rj P.S. eWeek magazine's "Garbage In, Garbage Out of Control" article by Linda Voss raises the issue of users drawing incorrect conclusions from bad data, erroneous correlations, improper analytics, flawed visualizations, or all four. If the users are local law-enforcement personnel, as was the case for two examples that the Baseline article describes, the military, or federal anti-terrorist agencies, incorrect conclusions drawn from database applications can have serious consequences.

Tech*Ed 2005 Show Daily Articles from FTPOnline

If you subscribe to Fawcette Technical Publications' .NETInsight newsletter, you've received three "Show Daily" newsletters that contain links to news from and analyses of Tech*Ed 2005 keynote speeches and breakout sessions. If not, following are links to articles from the three "Show Daily" newsletters, and here's a link to the Preferences page where you can sign up to receive any or all of the FTP newsletters. Day One - Monday Visual Studio 2005 to Launch November 7 Microsoft VP Paul Flessner said today at Tech*Ed that Visual Studio 2005, SQL Server 2005, and BizTalk Server 2006 will launch internationally in a series of events the week of November 7. .NET 2.0 Gets Faster The .NET 2.0 beta outperforms .NET 1.1 by 20 to 40 percent, said Microsoft CEO Steve Ballmer during his opening keynote at this week's Tech*Ed conference. Longhorn Redux The next version of Windows will differ dramatically from its PDC 2003 preview version. Roger Jennings gauges the impact that Longhorn and its back-ported Avalon and Indigo components will have on VS developers. Ballmer: "Look out, Rational." Microsoft CEO declares that Visual Studio 2005 is nearly ready and that its Team System will challenge IBM/Rational head on, says Jim Fawcette in his blog. Where Microsoft Stands With Security Microsoft shows its commitment to security with recent releases such as Windows Server Update Services (WSUS) and Service Pack 1 for Windows Server 2003 with its Security Configuration Wizard. Ballmer Touts .NET Adoption, Office Dev Tools In his Tech*Ed keynote, Microsoft CEO Steve Ballmer quoted the obligatory .NET momentum statistics, but left some questions unanswered, says Jeff Hadfield. Day Two - Tuesday Revisiting Whidbey, Yukon, and Beyond This week's Tech*Ed is in many ways a major milestone for Microsoft's product strategy, especially for its server and tools products. Peter O'Kelly provides an overview and analysis of the latest developments. SQL Server June 2005 CTP Now Available The newest SQL Server 2005 preview version, the June Community Technical Preview, is now available for download. This CTP is the first preview version to be publicly available. Put the 'Smart' in Smart Client Building applications for multiple platforms requires more consideration from developers than ever before. Find out how to make your apps "smarter." Is C# the Only Language that Matters? One promise of .NET has always been that the language you use is up to you. It's a nice theory, says Patrick Meader, but the perception in many circles is that C# is the only language that matters. Ballmer: .NET Outperforms WebSphere, Again .NET 2.0 ups the stakes as Ballmer claims 200 percent better performance than WebSphere, says Jim Fawcette in a blog post. Infrastructure Takes Center Stage Building a solid IT infrastructure is essential to maintaining communications in your organization. Here's a sampling of key products that vendors are showing off at Tech*Ed 2005. Day Three - Wednesday Yukon Tempts Database Developers Long-awaited SQL Server 2005 offers Visual Studio 2005 developers more than just CLR integration. ADO.NET 2.0 opens the door to native data encryption, a new XML data type, and many added T-SQL features. CLR in SQL Server Actually Has a Benefit! During his Tech*Ed keynote, Microsoft VP Paul Flessner briefly mentioned a benefit of having CLR support built into SQL Server 2005, says Jim Fawcette. Connect Systems With Indigo Tech*Ed's Connected Systems track presentations propose the forthcoming Indigo messaging bus as the service-oriented successor to ASMX Web services, Web Services Enhancements (WSE), .NET Remoting, MSMQ, COM+, and, lest we forget, Global XML Architecture (GXA). New Dev Tools Integrate With VS 2005 Vendors at Tech*Ed showcase new development tools, including products that help you build database apps, enhance your app's presentation layer, design Web apps more easily, and more. Admin Benefits of SQL Server 2005 Any database or system administrator will be glad to move on from SQL Server 2000, say Danielle Ruest and Nelson Ruest. Get the scoop on some of the improvements in SQL Server 2005. --rj

Powerful Stuff: WS-*, Single Sign-On, and InfoCard

It's risky to speculate on the nature of the "powerful stuff" that Steve Ballmer mentioned during a Q&A session at the recent TiEcon 2005 conference:

"We are working on more existing powerful stuff around XML Web services that will address many issues beyond RSS."

However, a likely "powerful stuff" candidate is Microsoft's InfoCard initiative for personal digital identity management and Web-based single-sign-on (SSO) in the forthcoming Windows Longhorn client OS. The first public demonstration of InfoCard occurred in May at the Digital ID World 2005 conference in San Francisco. Microsoft more or less simultaneounsly published Kim Cameron's "The Laws of Identity" white paper and a more extensive "Microsoft's Vision for an Identity Metasystem" article. (Kim Cameron is Microsoft's Identity and Access Architect, and publishes the Identity Weblog). The "Pre-Release Software Code Named “Avalon” and “Indigo” Beta1 RC" download, which appeared on May 23, 2005, runs on Windows XP SP-2 or Windows 2003 Server. This download provides the Indigo runtime infrastrastructure for InfoCard Beta 1. (The only references to InfoCard Beta 1appear in the press release and the main Longhorn Developer page's link to the RC, which also has a link to the Release Notes.) Running the Indigo setup program installs the .NET Framework 2.0 April CTP (Beta 2) version. You also can download and install an updated WinFX SDK as an ISO image from a link on the download page. The runtime and SDK compatible with Visual Studio 2005 Beta 2. Johannes Ernst's Blog provides an independent overview of InfoCard and describes its reliance on the WS-* stack. According to Johannes, InfoCard employs the following WS-* members and related specs:

• SOAP [1.2]
• WS-Addressing
• WS-MetadataExchange
• WS-Policy
• WS-Security
• WS-SecurityPolicy
• WS-Transfer
• WS-Trust
• XML Signature
• XML Encryption
• SAML
• WS-Federation (?, unclear)

[Note that Indigo bindings for WS-* support use SOAP 1.2, which results in Web services that don't meet WS-I Basic Profile 1.1. As Tim Ewald observes, many organizations require that all Web services they publish or consume to claim BP-1.1 conformance.] If processing InfoCard identities requires implementation of the eight WS-* specs from the above list, support for SAML, and the Indigo messaging infrastructure, is InfoCard destined for HailStorm's fate? At this point, only WS-Security is an official OASIS specification; the remaining members are at varying points in the standards process. So far, InfoCard appears to me to be another example of the overly complex "everything at once" syndrome that doomed HailStorm. The preceding Indigo and InfoCard Beta 1 RC release followed a May 13, 2005 joint publication by Microsoft and Sun Micrososystems of the Web Single Sign-On Interoperability Profile and Web Single Sign-On Metadata Exchange Protocol (WSSOMEX) specifications. These specs provide a mechanism for integrating WS-* and Liberty Alliance identity management of Web-based single sign-on technologies. WSSOMEX represents Sun's first—if tentative—committment to the WS-* standards beyond WS-Security. The press release, transcript of remarks by Steve Ballmer and Scott McNealy's comments, and related links are here. WSSOMEX is the first concrete result of the 10-year Sun-Microsoft technical collaboration agreement of April 2004. Paul Madsen posted an early analysis of WSSOMEX and WS-MetadataExchange:

WSSOME[X] defines how WS-MetadataExchange can be used to determine which Single Sign-On protocol suites (SAML 1.1, ID-FF 1.2, SAML 2.0, WS-Federation, etc) your partner is capable of supporting so that the two of you can actually do something interesting (like enabling SSO for your customers, employees, etc). WS-MetadataExchange defines a SOAP-based request/response protocol. Fundamentally, one provider says to the other 'tell me what you can do'. If the returned list includes something that the asking provider can also [do], then we have an intersection of capabilities and we're off to the races. If [there's] no intersection, [there's] no way forward.

Sun's Hubert Le Van Gong posted a response to Paul's post and added his own initial InfoCard analysis and a follow-up in response to Kim Cameron's comments. InfoWorld's Jon Udell also weighed in with a post about Web single-sign-on with client-side certificates, a much simpler technology that never caught on, versus InfoCard. In fairness to InfoCard, the Liberty Alliance lists a large number of "Liberty-Enabled Products," but, according to Web service analyst Ron Schmelzer, "[T]here are still very few products, if any, that implement Liberty Alliance on the desktop client, and so Microsoft has a distinct advantage."

RSS and Web Services: Keep It Simple, Steve.

Amit Malhotra's May 19, 2005 post on therssweblog includes a transcript of his unscheduled Q&A session with Microsoft's Steve Ballmer at the TiEcon 2005 conference in Santa Clara:

Q1. How important is RSS? A fad, important, huge or will [it] replace the Web/HTML dominance of the internet? A1. We believe RSS is important and will be around for a while, but it is not going to change the world. It is a little too simple; that is also the reason everyone’s using it. We are working on more existing powerful stuff around XML Web services ... that will address many issues beyond RSS. RSS will be around, but whatever we are working next will be cooler and more prevelant. [Emphasis added] Having said that, there are groups in MS that believe RSS has the potential to change everything and many future technolog[ies] will be built around RSS, the internal debate goes on.

Q2. How do you, or do you, see Google/Blogger and similar tools being a threat to MS Office dominance? A2. Not at all, people will always need Office for the complexity of tasks they perform and, as such, Google’s offerings in the strain of Gmail/Blogger will not replace it. We think it will be part of what we offer in future versions of Office. Besides the next release of MS Office will have the tools to publish blogs as part of its collaborative tools; watch for them.

Amit's preceding transcript, to which I've made minor edits and emphasized text, generated a brief flurry of reaction by other well-known bloggers, including Microsoft's Robert Scoble, who left a comment to a related posting on Steve Rubel's Micropersuasion PR blog. A reader named Bud left the following comment that's a propos my preceding post on the issue of overcomplicating Web services and SOA:

When I talk to people who are doing web services and show them RSS, they say something like, "Hey, we could achieve a heck of a lot with just that. We should just implement it. Web services is so complicated and requires so much effort just to work." And, these are fairly sophisticated developers and architects. Simple, easy to implement technologies where developers can quickly do it and people can immediately see the value are going to win the day. That's things like AJAX as well as RSS.

Steve Ballmer's first answer indicates that Microsoft has a Web service-based replacement in mind for RSS that has a higher "coolness quotient," but I question whether any Web service API or toolkit can come close to competing with RSS's current prevelence in Web developer mindshare. [The assumption is that preceding references to RSS also encompass ATOM.] My bookshelf (as well as Don Box's) has a copy of the .NET My Services Specification (517 pp.) from the 2001 Microsoft Professional Developer Conference (PDC). .NET My Services, better known by its "HailStorm" codename, was intended to evelope XML-based identity, address-book, Web site favorites, calendar, travel and much other personal data with a digital wallet in a Web services wrapper. The essence of HailStorm, in the words of Mark Lucovsky, then Microsoft's Distinguished Engineer and Chief Software Architect of .NET My Services, was:

HailStorm embraced the idea of decoupling the data from the application. The idea was to allow a variety of applications to process and manipulate your calendar data, your address book, your email, your favorite web sites, your travel preferences and itineraries, etc. This is not a new, novel idea, but was certainly something that was important and core to the system. Simple applications that we were trying to enable included the ability to overlay your personal calendar with the calendar of your favorite band, or your favorite sports team, or your spouse, etc. We wanted to enable a unified "address book" where your contacts could be used across applications written by any vendor.

The HailStorm announcement aroused a call-to-arms among Microsoft's competitors to prevent Passport from becoming the world's default identity management and authentication protocol. The Liberty Alliance, championed by Sun Microsystems, proposed a "federated identity management system" and successfully thwarted serious consideration of Passport and HailStorm technology by IT management. Another issue with HailStorm was that client applications weren't really "simple." For example, the introductory chapter for the .NET Calendar service specificaton was 68 pages long. Lucovsky subsequently moved on to Google and recently posted a comparison of the core HailStorm concepts with those of RSS 2.0 and Atom. Mark cites the common core concepts as:

• Network Centric, Extensible Data Model, for Everyday Data
• Data Decoupled from Applications
• Anytime, Anyplace, and from Any Device Access
• Identity Centric Data Access

A major difference I see is that HailStorm proposed to standardize a wide range of data structures and methods for reading and updating personal information. HailStorm relied on the proprietary .NET Passport service (now "Passport Network") for authentication, which Microsoft's Kim Cameron now admits is out of context for authentication by non-Microsoft sites. As one commentor observed, HailStorm proposed to do "everything at once." RSS 2.0 and ATOM concentrate on public content sydication and have proven very successful at their assigned tasks, while only the successor of .NET My Alerts—SQL Server Notification Services—has achieved any semblance of industry adoption.

Microsoft Web Services DevChannel Opens at FTPOnline

Fawcette Technical Publications recently added a new Microsoft Web Services DevChannel to the FTPOnline portals collection. The DevChannel consists of links to Microsoft white papers, artlcles from FTP print and online publications, video clips from FTP conferences and MSDN TV, plus related resources. Service-Oriented Architecture (SOA) gets the primary emphasis, as expected, but many articles include sample C# and VB .NET Web service code. My "Build Real-Time Web Images" article from the August 2004 issue of Visual Studio Magazine was the lead link for the initial DevChannel page. The article describes how to write VB. NET 2003 Windows form clients for Microsoft's SQL Server-driven TerraService and MapPoint Web services. TerraService is an example of a set of freely-accessible Web services that enable .NET developers to display tiled aerial/satellite photographic images of most of the earth, and USGS topographic map images of North America. MapPoint Web services render bitmaps from vector-based maps of the U.S. and many other countries. Using MapPoint Web services requires obtaining a commercial or developer license and executing a simple username/password authentication request prior to invoking the desired Web method.

Update 6/14/2005: InfoWorld's Jon Udell commented on developers use of GoogleMaps versus TerraServer:

"Years ago an early reviewer of Visual Basic 1.0 (Steve Gibson, I think) said that VB increased the software developer's leverage by an order of magnitude. That was true, but you can't keep going back to the same well. In a column on Google Maps I wrote:

Developers haven't flocked to TerraServer. What's Google's secret? Web DNA and no Windows tax. Responding in email, Jim Gray reminded me that TerraServer does offer SOAP interfaces [1, 2]. And yet those interfaces demonstrably have not inspired a flurry of innovation. Why not? Microsoft is obliged to portray the Web-based user experience as a dead end that can never be improved, and Windows as the only way forward. So it's going to be Visual Basic and client/server all over again: Windows applications will control the user experience; servers will dish out the data; developers will connect the dots."

For a preview of the TerraService and MapPoint Web service client projects, check out the illustrated online help page on the OakLeaf Web site.

Like OakLeaf's public CFR Web services, the TerraService and MapPoint Web services take full advantage of Visual Studio's Add Web Reference Wizard to autogenerate .NET 1.0+ C# or VB .NET Web service proxy classes. Simplified programming of data-driven Web services and the capability to autogenerate Web service client proxy classes were Microsoft's primary marketing topics for .NET 1.0. VS 2005's new built-in Web server simplifies development and publication of basic SQAP 1.1 and 1.2 Web services, but offers few other Web service enhancements. SQL Server 2005 boasts the capability to host native (in-process) SOAP Web services that substitute Windows Server 2003's and Windows XP's built-in HTTP.sys driver for IIS and VS-generated .asmx files. Promotion of the WS-Security and other WS-* specifications, Web Services Enhancements (WSE) 2.0+, and the forthcoming Indigo message bus appears to have increased the FUD factor surrounding SOA in general and Web services in particular. This uncertainty has reduced IT management and developer interest in implementing basic .NET and Java Web services as the first step in demonstrating the practicality of enterprise-level SOA to corporate and line-of-business management. Perhaps the Web services developer community would be better served by more publicly accessible, data-intensive, real-world Web services and fully implemented client examples, such as the TerraService and MapPoint projects, rather than white papers that describe esoteric SOAP headers and emerging messaging "standards." RSS 2.0 and ATOM adhere to the Keep It Simple, Stupid (KISS) principal, which is the secret to their current success in content syndication. --rj

P&P Enterprise Library, VB, and Data Access Application Block Bugs

Microsoft's patterns & practices group (PAG) released an updated version of seven Application Blocks for the .NET Framework 1.1 in mid-January. The new Enterprise Library (EntLib) Application Blocks are based on the Avenade Connected Architecture for .NET (ACA.NET). Avenade Inc is a joint-venture software consulting company formed by Accenture and Microsoft in 2000. I had created a sample VS 2005 front-end to the earlier (Microsoft-only) version (2.0) of the Data Access Application Block (DAAB) for the book's "Best Practices" chapter, and I expected the new EntLib blocks to be similar to their predecessors. Instead, I discovered that the source code for all new blocks was C# only. (The earlier blocks had C# and VB implementations, and I had no problems compiling them with VS 2005 May 2004 CTP.) The missing EntLib VB implementations have evoked the expected howls of protest from the developer community. As an example, vice-president of the Microsoft Developer Division S. "Soma" Somasegar's "Enterprise Library" blog post of 3/13/2005 (from India) drew several comments regarding the missing VB block source code. Michael Kropp, who runs the PAG team, replied to Bill McCarthy's complaint with the following promise:

While we made a decision not to provide all the application blocks in multiple languages we intentionally did a number of things to specifically help VB developers successfully use Enterprise Library (API Reference documentation, code samples, quickstarts and documentation). Going forward, we plan to make additional investments in Enterprise Library to include end-to-end reference implementations in both VB and C#.

Hopefully, VB block implementations will be included in the promised upgrade for the upgrade to .NET 2.0 and VS 2005. Attempts to build the EntLib source code with VS 2005 February CTP failed as a result of Upgrade Wizard errors. In addition, the architectural changes caused the new blocks to be totally incompatible with their predecessors. So, I compiled the EntLib DAAB source code with VS 2003, upgraded the DataAccessQuickStart.sln client project to .NET 2.0, added the .NET 1.1 Common.dll, Configuration.dll, and Data.dll assemblies as references, created the sample database in SQL Server 2005 February CTP, and attempted to run the client. I had to make a minor modification to the app.config file (removing the <keyalgorithmstorageprovider nil="true"> element) to eliminate a runtime error when creating a new abstract Database object. At first glance all seven test buttons delivered the expected results. A second look at the Update a Database Using a DataSet button's result ("2 rows were affected") piqued my curiosity. The SalesData class's UpdateProducts function includes a Dim rowsAffected As Integer = db.UpdateDataSet(productsDataSet, "Products", insertCommandWrapper, updateCommandWrapper, deleteCommandWrapper, UpdateBehavior.Standard) instruction that executes three commands and should return "3 rows were affected." Inspecting the underlying Products table last row(s) indicated that the deleteCommandWrapper command didn't behave as the Microsoft developers had expected. They had expended large amounts of energy on unit tests for the block code but didn't even run cursory tests on all functions of the QuickStart client code. The basic problem was an attempt to add and delete the same record in a single operation. The required @ProductID identity value for the DeleteProduct stored procedure isn't available until the the AddProduct stored procedure executes and updates the Products DataTable. The DataRowVersion.Current parameter value of the cwDelete.AddInParameter("@ProductID", DbType.Int32, "ProductID", DataRowVersion.Current) instruction always is DbNull.Value. The moral of this story is that developers who thoroughly test only the "interesting" parts of their projects end up with egg on their faces.

Another Alternative to AdventureWorks

Before the days of COM-based VB6 Web Services, I wrote a VB6 client/server demonstration project that emulated early Web-based retail supply chain management (SCM) applications. The "smart client" front end generated orders for consumer electronics products, and included full support for drop shipments from one of three distributors. Unlike AdventureWorks and Northwind, the project included invoicing, credit-card processing, and drop-shipment purchasing from and invoicing by the distributors. Freight charges were obtained from the UPS Web site. The unified client contained about 20,000 lines of code. The SQL Server 2000 back end consisted of six databases: OCE_Cust for sales order processing and invoicing, OCE_Prod for warehousing and shipping, OMB_Network (OmegaBank) for credit card processing, and three distributors: AlphaDist, BetaDist, and GammaDist. The design was based on separate physical locations for each database. For an early book on .NET Web services, I upgraded the Windows client to VB 2002 (with the Wizard) and converted the credit-card and distributor classes to .NET Web services. A flow diagram, description of the project, typical XML document instances and schemas, and links to WSDL documents and database diagrams are here. The final step was conversion of the client—again with the Upgrade Wizard—to a single ASP.NET Web page, as shown here (click the image to open it in a new window):

The page won't garner any design awards, but it displays all information required to process an incoming order, which takes from 0.5 to slightly over 1 second when generating XML purchase and invoice documents for outsourced items. A live demonstration of the project is available here. My April 2002 "Speed SQL Server Data Access" article discusses relative performance of the three successive versions of the OCE project (see Table 1 on page 2).

Note: The Web Services Interoperability Organization (WS-I.org) subsequently created a sample Retailer SCM interoperability test application that's similar to—but much simpler than—the OCE project. WS-I requested permission to republish the flow diagram and some content from the OakLeaf site, which I granted, but I haven't seen any evidence of their use of the material.

AdventureWorks consolidates human-resources, sales, purchasing, and production data in a single database. In the real world, the tables for each schema undoubtedly would reside in separate databases that are under the control of the related groups. Microsoft is heavily promoting service-oriented architecture (SOA) with autonomous Web services, but AdventureWorks' single-database design isn't suited to SOA. A version with four (or more) individual databases, simplified table structures, and more complete sample data would be better suited to the needs of VS 2005/SQL Server 2005 developers and book writers.

VB6 to VB .NET Upgrade Wizard Gets a Bum Rap

As of today, 273 Microsoft MVPs and 4,152 other interested parties have signed a petition to "further develop VB6 and VBA." The petition's request to include "VB.COM" (a.k.a. "Classic VB" and "vbclassic") in future versions of the Visual Studio IDE has fallen on deaf ears in Redmond. One of the petition's FAQs states:

"Microsoft provided a migration wizard intended to ease the transition from VB6 to VB.NET, true. There is near-universal agreement that using this tool to port code assets is an incomplete solution at best, in that leaves myriad "TODO:"'s scattered throughout the translated code. At worst, the migration wizard is an extremely poor choice in that, unlike a complete rewrite, it doesn't take full advantage of all that the new platform offers."

Migrating production VB6 code to VB 2002 or later is a controversial topic, but the VS 2002 Upgrade Wizard worked fine for me. In 2001 I wrote a large-scale ASP Web application (VBScript) with three VB6 data access layer classes (ActiveX DLLs) that connected to a 1-GB demonstration SQL Server database containing the entire contents of the U.S. Code of Federal Regulations (CFR) obtained from the GPO's beta version of the eCFR. (It's still in the beta stage.) Another VB6 project translated the CFR sections' SGML to XHTML and stored the data in a varchar or text column, depending on the size of the section. Subsequently, I used the SOAP Toolkit 2.0 (beta 2) to wrap the classes as RPC/Encoded Web services. Here's the history of the initial version and the first upgrade process:

• CFR-COM (mid-2001) used Active Server Pages and conventional Visual Basic 6.0 ActiveX DLLs for TOC navigation, text display, and full-text search operations. Microsoft Data Access Components (MDAC) 2.7 handled database access.
• CFR-SOAP (late-2001) was CFR-COM upgraded to XML Web services with the Microsoft SOAP Toolkit 2.0 Beta 2 and MDAC 2.7 for database access.

When Visual Studio .NET, as it was called at the time, went gold, I rewrote the client in ASP.NET 1.0. Then I gave the Upgrade Wizard a shot at upgrading the three classes, which totaled about 10,000 lines of code, to VB .NET for conversion to ASP.NET Document/Literal XML Web services. Here's the history:

• CFR-ASPX (January 2002) was CFR-SOAP with all pages upgraded from ASP to ASP.NET with Visual Basic .NET managed code and MDAC 2.7.
• CFR-Final (February 2002) is the final version that replaced COM-SOAP components with ASP.NET XML Web Services for text display, navigation, and search operations. ADO.NET SqlConnection, SqlCommand, and SqlDataReader objects handle database access.

Here are links to the Wizard's Upgrade Reports for the CFRTocWS (table of contents navigation), CFRSectWS (formatted sections), and CFRSearchWS (full-text search). I spent about two or three hours searching for and fixing known VB6 upgrade issues and than ran the Wizard. Each upgrade report had one warning. Not bad for version 1.0 of a very complex code conversion utility, at least in my opinion. I switched from VB6 to VB .NET and never looked back. Writing VBA code to update clients' Access applications now seems to me like trying to write this blog in Brasilian Portuguese.

Note: In August 2002, the OakLeaf CFR Web Service project won the charter Microsoft .NET Best Award for horizontal solutions. The "Use XML Attributes to Navigate Data" .NETInsight article explains the CFRTocWS service's navigation features and "Run Full-Text Database Searches on a Shoestring" describes the CFRSearchWS service.

Northwind vs. AdventureWorks as a Sample Database - Round 2

Here's more on the issue of AW vs. NW as a sample database for books about Visual Studio and SQL Server 2005. This post uses AW's Sales.SalesOrderHeader and Sales.SalesOrderDetail tables as examples.

It's easy to create a master-details form from these two tables but providing the user readable values from foreign-key fields is a pain. As an example, The Sales.SalesOrderHeader table has nine foreign-key fields: CustomerID, ContactID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, and CurrencyRateID.

Here's a screen capture of a sample project that loads the last 1,000 Sales.SalesOrderHeader rows and related Sales.SalesOrderDetail rows in descending order. Click the thumbnail to open it in a resizable window.

Note: VS 2005 introduces "smart captions" for autogenerated detail view controls. Spaces substitute for underscores and are inserted between a sequence of lower-case and upper-case characters. Unfortunately, DataGridView column headers lack this convenient feature.

Each foreign-key field requires a lookup operation to display readable data from the related table; some values require joins to related sub-tables. Lookups require adding 11 or more TableAdapters to the DataSet. Northwind requires only three or four added lookup tables: Employees, Shippers, Products and, optionally Customers.

Some text boxes, such as Sales Order Number and Total Due are bound to computed columns, and the uSalesOrderHeader trigger updates Revision Number, Sub Total, and Modified Date text box values. Thus, these six text boxes are read-only. The DataGridView's Line Total column also is computed, and the uSalesOrderDetail trigger updates the DateModifed column. However, the autogenerated UpdateCommand.CommandText instructions attempt to update the computed columns, which throws an exception. Elmininating the exception requires editing the dataset code to remove the column(s) from the UPDATE statement and the corresponding parameter(s). Partial classes let you supplement autogenerated code but not alter it directly.

Most AW tables have guid columns, which complicates INSERT operations. You must add code to the DataGridView's UserAddedRow event handler to add a Guid.NewGuid() value to the rowguid Cell. The DateModified column also requires Now or the like as a datetime value, which the iSalesOrderDetail trigger overwrites. Similar modifications are required for the corresponding SalesOrderHeader columns.

AW is a useful sample database for demonstrating gotchas with UPDATE and INSERT operations on tables that have computed columns and values inserted or updated by triggers. However, creating a real-world project with dropdown lists to enable editing the master form and detail DataGridView with readable values is more than a challenge-and-a-half, especially for users new to VS and VB. I'll probably include sample projects similar to these in an advanced chapter and a forthcoming Visual Studio Magazine article. Like the VS 2005 help files, I plan to stick with good old Northwind for the majority of the book's sample code and project examples.

Northwind vs. AdventureWorks as a Sample Database

Microsoft "strongly urges" book authors to abandon Northwind as the default sample database in favor of SQL Server (SQLS) 2005's updated AdventureWorks (AW) OLTP database. AW has a very complex, highly normalized structure of 68 tables. The Visio 2003 and HTML versions of an AW database diagram require printing to at least a 17-inch x 22-inch sheet to be readable. AW uses SQLS 2005's new user-schema separation feature, which lets you substitute an arbitrary prefix for the traditional database owner's name (dbo by default). Thus the fully-qualified name changes from SQLS 2000 and earlier's ServerName.DatabaseName.OwnerName.TableName to ServerName.DatabaseName.SchemaName.TableName. The SchemaName lets multiple users own a single schema based on their membership in roles or Windows groups. AW has five schemas: HumanResources, Person, Production, Purchasing, and Sales.

Note: An Accounting schema and Accounting.Invoices table are conspicuous by their absence. Apparently, the firm accepts and fulfills orders but doesn't issue invoices or charge credit cards for goods shipped. Northwind also lacks an Invoices table.)

The Person.AddressType table has six types of addresses: Billing, Home, Main Office, Primary, Shipping, and Archive. The table contains foreign-key values for StateProvince and CountryRegion tables. Thus a T-SQL query to return all columns for an individual customer address is (from the Sales.vIndividual view):

SELECT i.[CustomerID], c.[Title], c.[FirstName], c.[MiddleName], c.[LastName], c.[Suffix], c.[Phone], c.[EmailAddress], c.EmailPromotion], a.[AddressLine1], a.[AddressLine2], a.[City], StateProvinceName = sp.[Name], a.[PostalCode], CountryRegionName = cr.[Name], i.[Demographics], c.[AdditionalContactInfo]

FROM [Sales].[Individual] i INNER JOIN [Person].[Contact] c ON c.[ContactID] = i.[ContactID] INNER JOIN [Sales].[CustomerAddress] ca ON ca.[CustomerID] = i.[CustomerID] INNER JOIN [Person].[Address] a ON a.[AddressID] = ca.[AddressID] INNER JOIN [Person].[StateProvince] sp ON sp.[StateProvinceID] = a.[StateProvinceID] INNER JOIN [Person].[CountryRegion] cr ON cr.[CountryRegionCode] = sp.[CountryRegionCode]

WHERE i.[CustomerID] IN (SELECT [Sales].[Customer].[CustomerID] FROM [Sales].[Customer] WHERE [Sales].[Customer].[CustomerType] = 'I')

Creating (or attempting to create) an updatable typed DataSet for individual customers requires six DataTables and generates a DataSet designer file of more than 6,000 lines. I haven't yet attempted to write FillBy... methods for a parameterized details form that would enable updates and inserts with bound text boxes and dropdown lists.

Following is a simple DataGridView of the Sales.vIndividual view. Click the thumbnail to open it in a resizable window.

Without a parameterized FillBy... method, the Fill method returns 18,508 records and populates the lookup lists. Thus, it takes about 10 seconds to open the release version of AppName.exe with the client app and SQLS 2005 instance running under Windows 2003 Server on a 2.6-GHz Pentium 4 box with 1 GB RAM. TaskMan shows that the AppName.exe process consumes 101 MB. Parameterizing the seven SELECT queries with individual FillBy... methods can solve the opening time and resource-consumption problems but doesn't address the complexity issue.

Northwind's dbo.Customers table provides readable BillTo addresses, and the dbo.Orders table supplies ShipTo information without requiring a series of INNER JOINs. It's my contention that AW's complexity will distract readers when learning typical tasks, such as creating a Customers-Orders-LineItems master-details-subdetails form. Thus, I'm using old-timey Northwind for most design and coding examples in my book. Another reason for using Northwind is it's popularity in VS 2005's offline help files. Searching the February CTP with 'Northwind and Walkthrough' returns 62 hits; 'AdventureWorks and Walkthrough' returns 3. Similarly, 'Northwind and HOWTO' returns 47 hits; 'AdventureWorks and HOWTO' returns 0. If the SQLS team "strongly urge[d]" the User Ed(ucation) folks who write the VS 2005 help files to use AW, the recommendation appears to have fallen on deaf ears.

Note: One of the book's sample projects lets readers populate a Northwind.SalesOrders table—an updated clone of the Northwind.Orders table—with an unlimited number of rows containg randomized data for sequential dates, a specified average number of line items per order, and random ProductID values.

The Opening Salvo - Visual Studio 2005 and SQL Server 2005

Welcome to the OakLeaf Systems blog. I'm in the process of writing a new book about database programming with Microsoft Visual Basic 2005, the successor to Visual Basic 6.0 and Visual Basic .NET. I plan to share my experiences—good and not so good—with the latest Community Technical Previews (CTPs) and betas of Visual Studio 2005 and SQL Server 2005/SQL Express. Stay tuned ...