SQL Server Magazine UPDATE—brought to you by SQL Server Magazine
THIS ISSUE SPONSORED BY
FREE SQL Tool from NetIQ
Lumigent Technologies Announces Log Explorer 3.0!
(below NEWS AND VIEWS)
SPONSOR: FREE SQL Tool from NetIQ
Need to know what's going on in your database environment? Quickly and accurately identify and investigate specific SQL Server problems with NetIQ's diagnostic dashboard, SQLcheck. This FREE tool organizes and explains critical information about your database server hardware, its operating system and SQL Server. Get the information you need for efficient database management today. Download SQLcheck now!
June 13, 2002—In this issue:
- Laziness Contributes to Spida Worm Spread
2. SQL SERVER NEWS AND VIEWS
- Results of Previous Poll: Licensing 6.0
- New Instant Poll: Spida Worm
3. READER CHALLENGE
- June Reader Challenge Winners and July Challenge
- 6 Free Months of SQL Server Magazine Delivered Digitally
- 2-Minute Survey That's All About You!
5. HOT RELEASES (ADVERTISEMENTS)
- Making Data a Strategic Asset
- Ascential Software
- MCP TechMentor Summit on Security in Seattle
- What's New in SQL Server Magazine: Data Mining on a Shoestring
- Hot Thread: New SPID
- Tip: Ownership Changes in SQL Server 7.0's Merge Replication
7. NEW AND IMPROVED
- Integrate Data From Disparate Sources
- Add Management Capabilities to Visual Basic .NET- or C#-Based Programs
8. CONTACT US
- See this section for a list of ways to contact us.
(contributed by Brian Moran, news editor, [email protected])
You've probably heard by now about the Spida worm, also known as digispid.b.worm and SQLSnake. Spida, first identified in May, is a worm that attacks SQL Server instances that have a null sa password. Worms and viruses are always dangerous, but Spida is particularly spine-chilling because careless SQL Server users contributed to its spread.
Spida scans port 1433 on SQL Server machines, looking for a null sa password. The worm connects to SQL Server and uses the xp_cmdshell procedure to add the Windows Guest account to the local and domain administrators' groups. Spida then propagates by copying files that it uses to attack other machines. Spida also collects various diagnostic and SAM information about the server and sends this information to a defunct email address outside of the United States. You'll find a detailed technical description of Spida at http://www.eeye.com/html/research/advisories/al20020522.html . This page tells you exactly what Spida does to your machine and includes information about removing the virus. You'll also find a free scanner that will quickly determine whether the virus has infected your machine.
Why do I call the Spida worm spine-chilling? This worm infected more than 10,000 machines in May, according to reports, but had SQL Server administrators followed basic security practices, the worm couldn't have spread. This particular worm doesn't use a dictionary attack to find passwords that are common words. For example, it doesn't look for weak passwords—ones that don't contain a mix of difficult-to-guess alpha and numeric characters. Spida attacks machines that have a null sa password. SQL Server 2000 administrators must make a conscious decision to create a null password because the Setup program prompts you with an "Are you sure?" message. You've received the same explicit warning about SQL Server 7.0 if you've applied Service Pack 3 (SP3) or later.
Spida didn't spread because busy administrators couldn't keep up with the latest security hotfixes and vulnerability warnings from Microsoft. Spida spread because administrators and developers were too lazy and inattentive to bother using a password for the sa account. I bet these same people wouldn't write their PIN on the back of their ATM card, then leave the card on a busy street.
Why do people make silly mistakes like allowing a null sa password? I confess that I'm one of the careless people I just described. I'm ashamed to admit that I've worked on projects that have a null sa password to ease the workflow for developers. I've never used a null sa password on a production box, but I have on development boxes where the data wasn't considered important. But the Spida epidemic illustrates how dangerous sloppy password administration can be. Spida didn't do much damage, but imagine the consequences if it had used xp_cmdshell to delete all files from every hard disk it could find on the network. People were very lucky this time.
Please create a strong password for your sa account if you don't have one already. Please block port 1433 from the Internet and create a secure demilitarized zone (DMZ) for people to use to access your SQL Server. We can yell all we want at Microsoft and other vendors for not adequately addressing security. But people who live in glass houses shouldn't throw stones. Have you taken reasonable and appropriate steps to secure your SQL Server?
2. SQL SERVER NEWS AND VIEWS
The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Do you think Microsoft's Licensing 6.0 program will benefit your company?" Here are the results (+/- 1 percent) from the 282 votes:
- 10% Yes
- 67% No
- 24% Don't know yet
The next Instant Poll question is, "Have you protected your SQL Server machines against the Spida worm?" Go to the SQL Server Magazine Web site and submit your vote for 1) Yes, 2) No, but I plan to soon, or 3) Doesn't apply.
SPONSOR: LUMIGENT TECHNOLOGIES ANNOUNCES LOG EXPLORER 3.0!
Identify the source of any data, schema or permissions change. Hunt down and resolve elusive problems. Recover lost or corrupted data — even without backups, and while your database remains online. New features include:
- view DDL commands; identify or rollback schema & permission changes
- full automation of recovery of dropped or truncated tables
Download free trial or data sheet. Or request free technical poster — "DTS Object Model".
3. JUNE READER CHALLENGE WINNERS AND JULY CHALLENGE
(contributed by SQL Server MVP Umachandar Jayachandran, [email protected])
Congratulations to Andrei Popovici, software engineer at Softure in Bucharest, Romania, and Mingshou Tang, DBA at Toronto-based DS-MAX International. Andrei won first prize of $100 for the best solution to the June Reader Challenge, "Passing the Values." Mingshou won second prize of $50. You can find a recap of the problem and the solution to the June Reader Challenge at
Now, test your SQL Server savvy in the July Reader Challenge, "Quickening the Query" (below). Submit your solution in an email message to [email protected] by June 19. SQL Server MVP Umachandar Jayachandran, a SQL Server Magazine technical editor, will evaluate the responses. We'll announce the winner in an upcoming SQL Server Magazine UPDATE. The first-place winner will receive $100, and the second-place winner will receive $50.
Here's the challenge: Steve is troubleshooting a performance problem in a VBScript application that uses SQL Server 2000 and 7.0 as its database servers. During his review of a SQL query that the application generates, he notices in the query's WHERE clause an IN logical operator containing a list of column values. Steve determines that the application's slower processing stems from the large number of IN list values that the query checks and the significant overhead required for parsing the long list of values. The following query contains the IN operator in the WHERE clause:
SELECT t.x FROM tbl as t WHERE t.y IN ( 1, 2, 3, 4, 5, 6, 7 /* long list of IDs */ )
How can Steve improve this query's performance? Devise a solution that works in SQL Server 2000 and 7.0.
(brought to you by SQL Server Magazine and its partners)
Register for your next Microsoft exam with VUE and get a FREE 6-month subscription to SQL Server Magazine delivered digitally to your desktop! Plus, you'll enjoy convenient real-time scheduling, on-time exam delivery, and your results will be quickly and accurately returned to Microsoft. Try VUE's same-day testing. Find out more at
Please help us by filling out a quick online survey and you could win 1 of 10 SQL Server Magazine handy calculators! The survey is quick and easy—just five multiple-choice questions. Make sure to input your email address to be qualified for the drawing! Click here to start the survey.
5. HOT RELEASE (ADVERTISEMENT)
Achieving and maintaining high quality data is not easy. Once you have it — it changes. Join DataFlux in our upcoming seminar, Making Data A Strategic Asset. June 20, 2002 1:00pm-2:00pm EST.
Free White Papers, data sheets and presentations on data profiling, ETL, meta data management, data cleansing and data quality, and scalability from the industry's only source of a complete enterprise data integration solution.
Join an exclusive gathering of Windows 2000 networking and security pros looking to get the goods on hardening their network, detecting intrusions and dealing with them quickly. Don't miss the Windows Security Challenge. Register today!
SQL Server 2000 Analysis Services provides all the tools you need to perform routine analyses such as targeting new customers and segmenting markets. In "Data Mining on a Shoestring," author Frances Keeping demonstrates how to perform these tasks and how to analyze the data. The article appeared in the June 2002 issue of SQL Server Magazine and is available online at the following URL:
JeffE reports that when he executes an sp_OA procedure, the process spawns a new server process ID (SPID). Jeff wants to know how he can find out more about how this process works. Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
(contributed by the Microsoft SQL Server development team)
Q. When I try to set up merge replication in SQL Server 7.0, the replication works fine, but I experience an ownership problem. Here's the scenario. A user named System owns all the database objects in an application named system. I set up replication to connect as sa, and I configure the replication objects—publishers, subscribers, articles, and so on—for system user-owned objects. All the published objects are pushed to the subscriber. These objects publish at the subscriber correctly, but the owner of the subscriber database for those objects changes from system to dbo. Why does the owner change?
A. You can expect the owner to change in SQL Server 7.0 because the account that you're using to log in to the subscriber is sa. If you set the Merge Agent's subscriber-side connection login with the system account, system should own all the objects. SQL Server 2000, however, includes a way to specify object ownership at the subscriber (in the article's Properties dialog box), even if you use a different account to synchronize the changes.
Send your technical questions to [email protected]
7. NEW AND IMPROVED(contributed by Carolyn Mascarenas, [email protected])
Embarcadero Technologies announced DT/Studio, software that lets you transform, migrate, and integrate large quantities of data from disparate sources. DT/Studio lets you easily move data to any target location or application by first reverse-engineering the existing data. After the software establishes the data architecture, it transforms the data from the original source into a user-defined data format. The software then populates the target repository or application at optimum speed. DT/Studio supports SQL Server 2000 and can use SQL Server for the metadata repository. Pricing starts at $35,000. Contact Embarcadero Technologies at 415-834-3131, extension 3.
White Bear Consulting announced MSDE Manager, Microsoft .NET software that lets you add SQL and Microsoft Data Engine (MSDE) management capabilities to Visual Basic .NET- and C#-based programs. You can create and manage databases, users, and roles. You can also back up, restore, attach, and detach databases. MSDE costs $31 for a single-user license. MSDE Manager is an ADO-based application that you can use with a full SQL Server license. Contact White Bear Consulting at [email protected]
8. CONTACT US
Here's how to reach us with your comments and questions:
(please mention the newsletter name in the subject line)
- TECHNICAL QUESTIONS — http://www.sqlmag.com/forums
- PRODUCT NEWS — [email protected]
- QUESTIONS ABOUT YOUR SQL SERVER MAGAZINE UPDATE SUBSCRIPTION?
Customer Support — [email protected]
- 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.
Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.