SQL Server Magazine UPDATE, September 12, 2002

THE .NET ARCHITECTURE CENTER

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

IT professionals can easily be overwhelmed by the mass of technical information that's available on the Web and in myriad other formats, and separating the informational wheat from the chaff can be a real challenge. You have to figure out how to meld together a jumble of random white papers, Microsoft articles, and newsgroup discussions to create a cohesive body of information that will help you do your job effectively. Often, the only difference between an expert and other technical professionals is that an expert has more time to hunt down arcane information.

I've beat this drum many times in the past, and although I don't fault Microsoft for providing too much information, I do think the company could provide more best-practices-style information to knit together the wealth of information that's spread across numerous Microsoft knowledge sources. So I was pleased when I recently stumbled across an MSDN site that seems to pull much of this information together, and I thought I'd share the resource with you. You can find the home page of the .NET Architecture Center at http://msdn.microsoft.com/Architecture/default.asp.

Don't let the .NET moniker fool you. This site includes plenty of valuable technical information related to SQL Server and data management. The site's welcome message says the Architecture Center is "devoted to business, software, and infrastructure architects." The Center includes content from Microsoft product teams, MSDN, TechNet, and Microsoft's new Architecture Review Board and serves multiple perspectives of enterprise architecture. The Center also provides a way for Microsoft to provide architectural guidance, announce new architectural content on MSDN and TechNet, and highlight community events such as architecture Webcasts.

The vision of the site's developers is ambitious, and keeping the site up-to-date with relevant best practices won't be easy. I'm taking an I'll-believe-it-when-I-see-it approach. But I applaud Microsoft for recognizing the need for a site such as this one. At first glance, the content seems strong. I'll point out nuggets of interest to the SQL Server community in future editions of SQL Server Magazine UPDATE. Does the site live up to the lofty goals outlined above? Spend some time at the site when you have a chance and let me know what you think.

GET REAL-TIME TRAINING WITH KALEN DELANEY!

Fine-tune your SQL Server performance at the next Web Seminar, "Analyzing Locking and Blocking," October 1, 1:00 PM EDT, taught by Kalen Delaney, noted SQL Server author and Microsoft MVP. Learn the details of SQL Server's default locking mechanisms and examine system tables and tools that help track down blocking problems at this event sponsored by Precise Software Solutions. For more information or to register, go tohttp://secure.duke.com/nt/sqlmag/webseminar/index.cfm?Code=&PC=kdei322isu

2. SQL SERVER NEWS AND VIEWS

(contributed by Mark Joseph Edwards, News Editor, Windows & .NET Magazine, [email protected])

ASSESSING SECURITY THREATS TO SQL SERVER

When did you last profile your SQL Server 2000 system for potential threats? If you haven't done so, you might want a toolkit and some easy-to-understand guidelines.

Next Generation Security Software (NGSSoftware) recently published "Threat Profiling Microsoft SQL Server," which describes in detail tools and procedures that you can use to gauge your exposure to intruders. According to NGSSoftware, the paper has "four main sections. The first section will cover attacks that do not require the attacker to have a user ID and password for the SQL Server, that is, the attacks are unauthenticated. The second section will cover those attacks that do require authentication; to succeed the user must be logged onto the SQL Server. The third section will consider those attacks that can be launched from a compromised server. The final and fourth section will touch briefly upon attacks via the Web using SQL Injection."http://www.nextgenss.com/papers/tp-SQL2000.pdf

"Threat Profiling Microsoft SQL Server" discusses SQL Monitor port attacks, network-sniffing opportunities, brute-force attacks, file-system attacks, Trojan horses in extended stored procedures, client attacks (e.g., against Enterprise Manager), navigating the database server, password cracking, bypassing access controls, and more. The paper lists a series of tools you need to obtain before you start. Minimally, you'll need various SQL client tools (such as Query Analyzer and ODBCPing), Microsoft Visual C++, SQLPing, NGSSQuirreL, NGSSQLCrack, and NGSSniff. The SQL Server CD-ROM contains SQL client tools. SQLSecurity.com (see the first URL below) offers SQLPing. NGSSoftware offers the latter three tools through the company's Web site (see the second URL below). According to NGSSoftware, NGSSQuirreL is an auditing tool that can find and fix holes in the SQL Server; NGSSQLCrack can crack the passwords of standard SQL logins; and NGSSniff is a network traffic capture and analysis tool. Overall, the paper contains a wealth of information about securing your SQL Server.http://www.sqlsecurity.com/desktopdefault.aspxhttp://www.nextgenss.com

Other steps you can take toward SQL Server security include keeping up with Microsoft security bulletins and reviewing other resources. Microsoft has issued 11 security bulletins for SQL Server 2000 so far, including a cumulative patch in August 2002 that contains all the other security patches. Be sure you've loaded the ones you might need--or the cumulative patch if you want to load them all.http://www.microsoft.com/technet/security/current.asp?productid=30

SQL Server Magazine and its related Web site often discuss SQL Server security. For example, when you visit the Web site (see the URL below), you'll find Michael Otey's article "Free SQL Server Tools," which discusses his favorite free SQL Server tools, among which are security-related tools. You'll also find Kalen Delaney's article "Safe Transit," which discusses how to ensure that usernames and passwords match up after a database restoration.http://www.sqlmag.com

Regularly reviewing the potential threats to your SQL Server will help keep it secure. I hope the resources mentioned will support that review process.

RESULTS OF PREVIOUS INSTANT POLL: BETA TEST PARTICIPATION

The voting has closed in SQL Server Magazine's nonscientific Instant Poll for the question, "Which SQL Server beta tests are you planning to participate in?" Here are the results (+/- 1 percent) from the 246 votes:

NEW INSTANT POLL: SUBSCRIBING TO MICROSOFT SECURITY BULLETIN

The next Instant Poll question is, "Do you subscribe to Microsoft's Security Bulletin service?" Go to the SQL Server Magazine Web site and submit your vote for 1) Yes, 2) No, but I plan to, or 3) No, and I don't plan to.
http://www.sqlmag.com

SPONSOR: ARE YOU WASTING TIME SEARCHING FOR SQL SERVER ANSWERS?

The SQL Server Magazine Master CD gives you real-time, high-speed access to all the articles, code, and expertise from every issue of SQL Server Magazine ever published. Unique search features let you find what you need fast. Order your copy today!
http://secure.duke.com/nt/sqlmag/archivecd/newsqlcdsub.cfm?code=ei282h1a

3. READER CHALLENGE

SEPTEMBER READER CHALLENGE WINNERS AND OCTOBER CHALLENGE

(contributed by SQL Server MVP Umachandar Jayachandran, [email protected])
Congratulations to Byron Hynes, systems analyst for Tamarack Computers in Yellowknife, Canada, and John Northfield, SQL Server DBA for ANZ Banking Group in Melbourne, Australia. Byron won first prize of $100 for the best solution to the September Reader Challenge, "Creating Indexed Views." John won second prize of $50. You can find a recap of the problem and the solution to the September Reader Challenge at http://www.sqlmag.com/Articles/Index.cfm?ArticleID=26605.

Now, test your SQL Server savvy in the October Reader Challenge, "Importing IP Information" (below). Submit your solution in an email message to [email protected] by September 18. 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: For reporting purposes, Martin uses bulk copy program (bcp) to import Web data about IP networks into a SQL Server 2000 database. Each row in the data file contains a range of IP addresses that defines a network and some of the network's characteristics such as the network type (e.g., Class A, Class B). The bcp format-file information below includes the data file's relevant columns:

8.021  SQLCHAR   0   15   "t"    1   StartIP  SQL_Latin1_General_CP1_CI_AS2  SQLCHAR   0   15   "r"  2   EndIP    SQL_Latin1_General_CP1_CI_AS

Martin also needs to define indexes that will let SQL Server perform range searches efficiently on these networks or IP address ranges. Let's assume the following table schema:

CREATE TABLE IPs ( StartIP varchar(15) NOT NULL, EndIP varchar(15) )

Help Martin design an efficient solution that can perform the data import with no modifications to the input file and optimize the table for queries.

4. ANNOUNCEMENTS

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

GET A FREE DIGITAL OR PRINT SAMPLE ISSUE TODAY!

SQL Server Magazine is the premiere independent resource for SQL Server database solutions--packed with hands-on, how-to articles to keep your database running at peak performance. This technical handbook is now available in two convenient formats. Select your free digital or print sample issue at:
http://www.sqlmag.com/sub.cfm?code=sfei212hdu

IMMEDIATE ACCESS TO T-SQL SOLUTIONS!

Exclusive in-depth articles, tips, tricks, and code samples all at your fingertips. Content you can't get anywhere else--brought to you by the SQL Server experts you trust such as Kalen Delaney, Itzik Ben-Gan, and others. Increase your productivity today! Go to the following URL.
http://www.tsqlsolutions.com

5. HOT RELEASES (ADVERTISEMENTS)

FREE TRIAL OF SQL-EXPLORER FROM BMC SOFTWARE!

Is troubleshooting your SQL statements affecting the performance of your applications? With SQL-Explorer from BMC Software, you'll be able to simulate, analyze and fine-tune your SQL statements without impacting your production database. Click here!
http://ad.doubleclick.net/clk;4600779;7448876;c?http://www.bmc.com/offers/sqlexplorer_trial/

SQL SERVER MAGAZINE LIVE!

SQL Server Magazine LIVE! will co-locate with Microsoft ASP.NET Connections and VS.NET Connections this October. Early Bird discount expires soon; register today to save $2,990 and access all three events (over 160 sessions) for the price of one!
https://secure.win2000mag.com/events/sql_register.asp

6. RESOURCES

WHAT'S NEW IN SQL SERVER MAGAZINE: FREE SQL SERVER TOOLS

Many free tools are available on the Web to help DBAs and database developers. In "Free SQL Server Tools," Mike Otey tells you about his seven favorite free SQL Server utilities. This article appears in the September 2002 issue of SQL Server Magazine and is available online at
http://www.sqlmag.com/Articles/Index.cfm?ArticleID=25703

HOT THREAD: PREVENTING THIRD-PARTY ACCESS

Graeme, a junior member of the forum, needs to prevent users from running queries against his SQL Server through a third-party application. He can't rewrite the vendor application, so he's not able to use application roles as SQL Server Books Online (BOL) suggests. Offer your advice and read other users' suggestions on the SQL Server Magazine forums at the following URL:
http://www.sqlmag.com/Forums/messageview.cfm?catid=6&threadid=8439

TIP: UPDATING COLUMNS FROM ONE TABLE TO ANOTHER

(contributed by the Microsoft SQL Server development team)

Q. How can I update three columns in table A from three columns in table B in one UPDATE statement?

A. For this question, you can use the power of relational algebra. The sample code in Listing 1 demonstrates that you can use the FROM clause combined with a JOIN operation to update specified columns from a second table. When you design relational expressions, you have to decide whether you want a single row to match multiple rows (in a one-to-many--1:M--relationship) or you want many rows to match a single row in the joined table (i.e., you want to update all rows). In a 1:M relationship, SQL Server always uses the last row it finds for the update. However, you have no way of influencing what the last row will be, and on multiprocessor machines where the query might be parallelized, the last row might differ from execution to execution. Therefore, we recommend that you avoid the 1:M relationship. Also, if the table you're updating is the same as the table in the FROM clause and the FROM clause contains only one reference to the table, an alias might not be specified. If the table you're updating appears more than once in the FROM clause, only one reference to the table can omit a table alias; all other references to the table must include a table alias.

LISTING 1: Updating Specified Columns from One Table to AnotherUSE tempdb GOCREATE TABLE #t1 (c1 int NOT NULL, c2 char(5), c3 char(5), c4 char(5))GOCREATE TABLE #t2 (c1 int NOT NULL, c2 char(5), c3 char(5), c4 char(5))GO-- Seed the data.INSERT #t1 values (1,'hello','there','fred')INSERT #t2 values (1,'how','are','you?')-- Make the update.UPDATE #t1 SET #t1.c2 = #t2.c2, #t1.c3 = #t2.c3, #t1.c4 = #t2.c4FROM #t2WHERE #t1.c1 = #t2.c1-- Check the results.SELECT *FROM #t1 

Send your technical questions to [email protected].

7. NEW AND IMPROVED

(contributed by Carolyn Mader, [email protected])

MANAGE DATABASE USERS

Crockett & Associates announced SQL Security Administrator, software that lets you add database users, add groups or roles, and set database object permissions. You can view and maintain permissions for multiple databases. The software also lets you view a user's permissions, including permissions inherited from the roles they've been assigned to. You can also view specific user-assigned permissions. The software costs $149 and supports SQL Server 2000, 7.0, and 6.5. Contact Crockett & Associates at 480-802-0141.
http://www.tmgca.com

MANAGE YOUR DATABASE ON YOUR PDA

PhatWare announced Pocket dbExplorer 2.0, a database-management utility for Windows-powered mobile devices. You can browse, view, edit, and manage databases on Pocket PCs, Handheld PCs, and palm-sized PCs. You can back up and restore one or more databases and system registry settings to a file, which you can store on a PDA's memory card or copy onto a desktop PC. The utility costs $19.95. Contact PhatWare at 510-796-8949 or [email protected].
http://www.phatware.com

8. CONTACT US

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

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.
http://www.sqlmag.com/sub.cfm?code=ssei211x1y

Receive the latest information about the Windows and .NET topics of your choice. Subscribe to our other FREE email newsletters.
http://www.winnetmag.net/email