Information Systems Grad School

What do we study in Information Systems Grad School? If there's a single topic that comes up in every class, it's databases. If we haven't memorized the first three normal forms by now, we haven't learned much. While few of us will bother going into Boyce-Codd Normal Form, 4NF and 5NF, every specification for a system we write that has a database needs an entity relationship diagram. An ERD is a visual representation of your data model, and your data model is probably the single most important part of any system you design. A good data model will survive several major versions of your software; a poor data model will make your system useless. Thus, we spend a lot of time doing data models and documenting them with ERDs.

As much as I love Visio, drawing the things from scratch is somewhat tedious. It's much easier to design and test in Access. (I have it on good authority that even elite Oracle DBAs who hand-tune Solaris for better performance will design and test in Access just because it's easy.) So what do you do when you have a decent test DB in Access and you don't want to diagram every little change in your masterful Visio ERD? Reverse engineer.

In Visio, it's fairly easy, but there are a couple of spots where it doesn't behave as nicely as it should. I'm going to refer to Visio 2007, still in Beta and free for the download and registration. Visio 2003 is almost the same. Visio 2003 Enterprise Architect Edition will create the database from your diagram, in case you can design an enterprise DB but don't know how to create the tables in SQL. (Not really someone you'd want touching your SQL server.)

1. Open Visio and select New | Software & Database | Database Model Diagram with the units of your choice.
2. Now that you have a database model diagram open, the database menu will appear. Select "Reverse Engineer" off of the database diagram.
3. A confusing dialog box will appear. Use it to verify you have the right drivers installed.
4. For Microsoft Access, choose Microsoft Access as your driver, and hit Next.
5. A username and password dialog box will pop up. Unless you've assigned a username and pw to the database, leave it blank and hit OK.
6. Navigate your filesystem and select your database. Ignore the clunkiness and be grateful that you can see filenames longer than 8.3.
7. When you have found your .MDB file, choose it and hit OK.
8. Select the types of objects you would like to import and hit Next. (No, you don't get stored procedures and triggers in Access, but you would in SQL.)
9. Select the specific tables, queries, etc. you want to see in your diagram and hit Next.
10. Select Yes to add them to your current diagram, and hit Finish. (Select no you have a lot of tables, queries, etc.)
11. You should see your tables in the diagram.
12. To add the crow's feet and cardinality, select options on the database menu. (Database | Options | Document )
13. You get three sections to change here: The General tab covers symbol sets: IDEF1X or Relational, Conceptual, Physical, both, or names based on symbol set. The table tab lets you display keys, indexes, non keys, and the IDEF1X optionality 0. The relationship tab lets you display relationships (duh), crow's feet, cardinality, and referential actions. You must select cardinality before you select crow's feet. (caridinality gets greyed out when crow's feet is checked.)
14. To update your diagram, select "Refresh Model" on the Database menu...

Now that you can see your information model, you know why it's messed up. That database that your business/organization/department runs on -- it's not in any kind of normal form. Or it has about 100 tables more than you thought it should.

The database people in my office have a debate: were the software engineers just trying to make it impossible to wean your organization off of their support, or were they just bad at information modeling?

Posted by Larry at 11:33 AM | Permalink | Comments (0) | TrackBacks (0)

Just when I thought I would stop experimenting with new Microsoft products and hunker down into my new job and my capstone project, I find a couple that will save me time and aggravation: Microsoft's Threat Analysis and Modeling Tool. This dot.Net 2.0 application has a wizard to create for us a CRUD matrix, that will can paste into our system security chapter. A CRUD matrix is simply a table of which users need what kind of access to which tables. It can get more complex if you have column-level security in your database. We don't, so it won't be overly detailed.

The other tool that can save me time, and possibly my group members, is Microsoft's Office Groove 2007 Beta. Like many cool Microsoft products, Groove did not originate at Microsoft. Most people I talk to about Groove don't really get what it does, but I blame that more on bad IT and IS metaphors than anything else. Groove is simply a shared workspace where a team can share documents, discussions, and contacts. When you set up Groove, you can set up one or more shared workspaces. You then add specific files to that workspace. Then all your invited and verified team members can access those documents whether you are online or off. No more uploading to Sharepoint or Blackboard, or whatever. You've got anywhere, anytime sharing, provided you have an Internet connection. I have it on my work computer and my home computer and share documents between them without having to email them back and forth. So far, I have no firewall issues either.

What if you want to secure your data and not have it stored on third-party servers? Use Groove Server, and give more money to Microsoft. Before the Office 2007 launch, both Groove and Groove server are free for the download, registration and product keys required. Try it and at least you'll know what people are talking about. The next big thing is enabling teamwork and collaboration and making the world "flat."

Posted by Larry at 8:02 AM | Permalink | Comments (0) | TrackBacks (0)

After tiring of redoing samba.conf files over and over again, I finally tried out Microsoft's (free) Services for Unix for simple file sharing between my Fedora Core 4 box and servers on my domain.

1. Copy over /etc/passwd and /etc/group to a secure folder on the windows machine where you are going to install SfU.
2. Download and install Microsoft's Services for Unix, and tell it to use password files during the installation process. (This is not a lesson on setting up a NIS domain -- just connecting one Linux box to your Windows server quickly and reliably. SfU installs several other items by default, like Unix Perl and grep; ActiveState Perl is optional. You want NFS Server.) Reboot.
3. In Windows, right click on the folder you'd like to share, click and NFS sharing tab, and select "share this folder."
4. On your linux box, as root, add a line like this to /etc/fstab:
myserver.com:/somefolder /somefolder nfs defaults 0 0
5. On linux, mount /pub
6. cd /pub and ls -la to your heart's content.

There are many security implications for Windows and Linux, like sharing your password and group files, and I'll sniff and trying cracking the passwords later.

Posted by Larry at 8:35 AM | Permalink | Comments (0) | TrackBacks (0)

My grad school project group finished our data mart for class. I learned a few things about SQL Server 2005 Analysis Services, Reporting Services, and Business Intelligence Studio along the way. One of our group members is a SAS programmer, so he provided us with simulated data: attendance records for a theoretical amusement park that included zip code and promotion type, with zip-code ACORN differences. Our idea was that if you came to the park with a coupon, we would know from the bar code where you came from; if you didn't have a coupon, the gate would ask you your zip code, just like at the retail store.

We had six million rows in our fact table, which included dateID, promotionID, zipID, and attendance, giving us three dimensions. We were going more for scalability rather than trying to pile in a lot of dimensions. I took the flat files from our SAS master and imported them into a SQL 2005 database. For some reason, SQL 2005's import tool defaults to a nvarchar(50) type. While six million records isn't much, the records were fixed-size and much smaller, so I was able to stuff them in an nchar type that was small and efficient because it doesn't require an offset column array. Our six million fact table records matched to 5 types of promotion, about 10,000 zip codes, and individual days for 3 years.

Once we created the database, I exited SQL Server Management Studio and opened up a new Business Intelligence Studio project. One difference between Management Studio and BI Studio is that BI Studio does not use SQL authentication. You must use Windows/Domain accounts. There is no sa in Business Intelligence Studio.

Once in BI Studio,you create a data source, just like with any project that involves a database. Then you create a data source view by selecting the tables you want to use and creating a dimensional model diagram. Microsoft calls this their Unified Dimensional Model. While your fact table doesn't need a primary key, your dimension tables do. The arrows should be pointing from your fact table to the dimension tables. I don't know why, but I often get this wrong when I'm creating a new data source view.

Finally, your fact table columns need to reference the right dimensions. Matching these up took a little longer than expected because I had to guess which fields were what, because our SAS genius didn't document which numbers were what. I looked at the 8-digit numbers and guessed wrong. The auto-build cube wizard worked fine, and the dimensions looked OK. Processing the cube failed when it timed out, and the error messages didn't say anything about orphaned fact rows.

Once the columns were matched up correctly, everything worked fine, and the auto-build cube wizard is pretty impressive. If you did everything right, building the cube should give you dimensions. You can create new dimensions if the wizard missed some. The time dimension has many options. At first I set time to be a regular dimension to avoid promblems: is day of week a number or a name? Monday or 1? While this will give you good reports, the order of weekdays and months will be alphabetical. (Which makes be think we should rename all month and day names so they can be alphabetical and still be in order, or re-sort them so that April is the first month and Friday the first day of the week.) Setting the time dimension as a time dimension rather than a "standard" dimension will fix this.

Processing the cube took under five minutes. (You also need apppropriate account permissions to process the cube) If the cube processes sucessfully, you can browse pivots immediately with the browser in BI Studio. Once you see that your dimensions work, you can start connecting to SQL Server Analysis services from Excel or from SS Report Server. (Again, with appropriate Windows Domain accounts.)

Microsoft's BI Studio Cube and Dimension wizards work the way they should: if you have defined your dimensional model accurately and your data are valid, they'll create the right dimensions for you. The wizards are no substitute for a valid dimensional model and they can't fix bad data or orphaned records.

Next: Creating Pivot Table Reports using our cube and SQL Server Reporting Services for IIS.

Posted by Larry at 10:26 AM | Permalink | Comments (0) | TrackBacks (0)

Microsoft's future for the Office suite emphasizes online collaboration. Microsoft's purchase of Groove and upcoming Office Groove and Groove Server emphasize collaboration, online and off, as key to their future success. (Sign up for the free Office 2007 Beta already -- Microsoft is pushing this pretty hard.)

I have been using Sharepoint since verson one to help solve simple problems with clients and their online office space, including collaboration. I have used Sharepoint to do everything from host photo albums and documents to doing really obvious things like "click here to connect to printer x in room y." I'm trying to see what Groove has to offer, but first I am playing around with Sharepoint Version 3.

Sharepoint Version 3 Beta has three requirements that it checks before the install: .Net Framework 2.0, since it's a .Net 2.0 application, ASP.net 2.0, and Windows Workflow Foundation Beta 2 v.3.0.3807.7 or above. Windows Workflow is the new process that will be available in Office 2007.

Once you have the installer running, it tells you that it can do an in-place upgrade of your Sharepoint v.2 site if you database is under 30 GB. However, it will need to restart IIS, Sharepoint, and the Sharepoint timer during the upgrade process. This is where I ran into one of two problems. I didn't look at all four tabs of the installer, so I neglected to check that my Sharepoint was front-end only. The database for mine is hosted on SQL 2005. Thus, the post-install script choked at step 5 of 10, stalling on registering SP services. The preupgrade install script posts details here:
\PreupgradeReport_632880453377812500_Log.txt and here PreupgradeReport_632880453377812500_Summary.xml, proving that everything has an XML file in the future.

The other problem was a result of my original Sharepoint v.2 install. Sharepoint v.3 will not let you connect to a configuration database with an SQL account. You must use a domain account. I didn't want to use NT Authority\Network Service, so I tried to change the SQL 2005 permissions for the configuration database, to no avail. I ended up doing a SQL Profiler Trace of the connect step to see what I could change in SQL to make Sharepoint use a different account. Thus result I got is a little opaque: a lot of exec sp_resetconnection and exec dbo.proc_getObject @Id='68430B8A-6365-44B4-99E2-CC842773FCDA', which results in:
68430B8A-6365-44B4-99E2-CC842773FCDA 8446FC57-4D84-4D79-8EA9-4B1C9C02C40C 9920F486-2FF4-4D10-9532-E01979826585 Central Administration, and more, which didn't help much. Sharepoint Version 3 help wasn't an aid here either.

Since I hadn't done much with the old Sharepoint except install the SQL Report Server web parts, I created a new site. The Sharepoint installer had already nuked my Default Web Site, so I didn't feel like I had much to lose. If I had to reconnect to the old database, I would have had to reset the password on the NT Authority\Network Service account. Once you get a configuration database, you can use SQL accounts for the individual web site connections.

Installing the web parts again was no problem. I used the same web parts I did for Sharepoint v.2 from the SQL 2005 install. Our old friend, stsadm.exe hasn't changed:
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\STSADM.EXE -o addwppack -filename "C:\Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint\RSWebParts.cab", assuming both are on the same box.

There's a lot more to Sharepoint 3 Admin tool: you can select specific users for inbound and outbound mail, for instance. You can also backup and restore content databases, as well as configure antivirus settings. There's even a built-in best practices analyzer tool.

Finally, there's a recycle bin. Now Sharepoint administrators won't have to figure out ways of protecting content from users. Next installment: how granular are the permissions?

Posted by Larry at 9:42 PM | Permalink | Comments (0) | TrackBacks (0)

Since I started using visio in 1999 or so, I loved it. Microsoft bought Visio for $1.5 billion, which was the most Microsoft had paid anyone for anything. Since then, Microsoft has incorporated it into its Office line.

I usually don't use a lot of Beta, but I had no choice. Visio 2003 does not connect to MS SQL Server 2005, even with SQL Native Client installed on my laptop. I had two choices: download and install my (student free) copy of Visio for Enterprise Architects on my soon-to-be-dead laptop, or download a free Visio 2007 Beta.

I use Visio for diagramming almost anything technical, from rack diagrams to network and Active Directory diagrams to schoolwork like data flow diagrams, class diagrams, statecharts, and entity relationship diagrams. (I can't afford ERWin.) You can even export from MS Project into MS Visio to create GANNT and PERT charts that are more customizable than what you can do in Project. (Although for updating diagrams on large projects quickly, nothing beats Critical Tools which does a much better job of creating Work Breakdown Structures and PERT charts than MS Project.

One of my favorite features in Visio is reverse-engineering databases. I find it much easier to create databases in Access and then reverse-engineer the diagram in Visio. I can also test out the Access database and see if I can get the reports I need with the right queries. (I hear that even Oracle DBAs with years of experience test things in Access.) I can also use this feature to investigate vendor-supplied databases. (One-size-fits-none databases tend to have hundreds of tables.)

In Visio, I just create a new database diagram, then select Database | Reverse Engineer and point it at my data source, which is still a little cumbersome to set up on a new non-Access database. After importing the tables, indexes and queries I need, I can select Database | Options | Document and hit the checkboxes for cardinality, crow's feet, and actions for relationships. This box has changed slightly for Visio 2007, and it looks like the IDEF1X symbol set is also new, and it will be especially helpful to defense contractors.

Another good thing about Visio 2007 is that I can use all my old stencils, including the giant pack of slightly dated Altima stencils that came with a 3com switch. Since I can't afford to buy lots of custom stencils, I am very happy to see that more vendors are offering free equipment representations for their products at places like the Visio Cafe.

If you're looking for a free version of Visio to work with, the Visio 2007 Beta will work. Mine hasn't even crashed yet.

Posted by Larry at 8:10 AM | Permalink | Comments (0) | TrackBacks (0)

It seemed simple: Export OLAP reports from SQL 2005 Reporting Services into Sharepoint. I like Sharepoint because it solves a ton of problems in organizations. I'm still surprised at how many Microsoft shops don't use Sharepoint because it's free and it integrates into Active Directory. (Sharepoint Portal Server, a different product, costs money, scales more and is personalizable.) All you need for Sharepoint is IIS and SQL or the MSDE; and FrontPage 2003 if you want to edit graphics. Microsoft has a lot of Sharepoint resources available for download, but they're not well organized.

The details slowed me down a few hours. There are several different ways of configuring security contexts, and you will have to keep your accounts and passwords straight. I have yet to find a step-by-step on Technet, but I'm still looking. I did see a page showing cool OLAP reports in Sharepoint on Technet, but no link to help me set it up.

The biggest problem that I've seen many other folks have is the 404 Bad Request error in the /Reports ReportManager Virtual Directory. /ReportServer worked the first time, but without the ReportManager Virtual Directory, it's not so useful. At first I thought this was a DCOM security issue because of the event log entries I got. (Ten of these on the first request for http://myreportserver/reports after restarting IIS and then no more until restarting IIS.)

The application-specific permission settings do not grant Local Activation permission for the COM Server application with CLSID {BA126AD1-2166-11D1-B1D0-00805FC1270E} to the user NT AUTHORITY\NETWORK SERVICE SID (S-1-5-20). This security permission can be modified using the Component Services administrative tool.

The trouble with that message is that there's no DCOM component in Component Services that corresponds to the CLSID. This didn't stop me from searching the registry for a while, finding that the CLSID is involved with about a dozen basic network services, none of which are in the Component Services MMC.

I gave up searching the Registry and I added NT Authority\Network Service to the DCOM user group on the local machine and restarted IIS. No joy. I was able to clear the event log of that error this way, but I still got the same error requesting http://myreportserver/Reports, just with no event log entries. I rechecked all the settings in SQL Server's Report Configuration Tool, which is very useful, but still didn't solve the problem.

I googled the source code on the error page:

System.Net.WebException: The request failed with HTTP status 400: Bad Request.

and found a site at MIT concerning a totally unrelated applicaton that threw the same error. I had one other Virtual Web on the machine, so I deleted it and reset my Default Web Site set to All Unassigned IP addresses and restarted IIS. Bingo. I can manage reports over the Web -- it just takes a while to start up the first time you request http://myresportserver/Reports. I can access it from http://localhost/Reports on that box now; before localhost requests failed, and I didn't know why.

I still have to set the right permissions for everything. I also need to choose whether to share a data connection or use the web visitor's security context. Just listing all the security contexts makes me dizzy: The Sharepoint App Pool, the Report Server App Pool, SQL Report Server Data Sources, the DCOM permissions mentioned above, and finally, your users' accounts in Sharepoint and Reports.

Sharepoint doesn't hold the Report -- it just passes your request on to the Report Server. Thus, you'll need to set permissions for the Sharepoint and the SQL Report Server. If you have Sharepoint permissions but not Report Server permissions, the Report Explorer web part will be blank.

Steps that worked for me:
1. Start with a good SQL 2005 install with all necessary components -- like Reporting Services.
2. Install IIS and ASP.net 2 if they're not installed already. I installed SQL 2005 Service Pack 1 after this step. (Make sure you have only a default web site on IIS to avoid my issues.)
3. Use the SQL 2005 Report Configuration Manager. This is when you'll need to decide which security schema you're going to use before you can complete this. The Configuration Manager saves a lot of time because you won't have to touch IIS Manager. (The whole scripting IIS configurations in XML thing is going to make my IIS skills obsolete before long.)
4. Create a simple report. SQL Books Online has a tutorial using the Adventure Works database.
5. Verify that http://yourreportserver/Reports and http://yourreportserver/ReportServer work.

Now move to your Sharepoint box running WSS.

6. Use stsadm.exe to install the web part. You will find the report explorer and report viewer web parts on your SQL box: (Search the Report Services library for Sharepoint for more details.)
C:\Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint\RSWebParts.cab
7. Open your SharePoint site and add the Report Explorer Web Part from the Virtual Server Gallery.
8. Point the Report Explorer at http://yourreportserver/Reports and leave the start path blank for now.
9. You should be able to see your SQL Reports on your Sharepoint site.

My example runs on two boxes: SQL 2005 and Reporting Services/IIS on one box, (along with the Exchange 12 Beta), and my Sharepoint on another box. Sharepoint doesn't seem to run on the same box as the Exchange 12 Beta.

Posted by Larry at 11:17 AM | Permalink | Comments (0) | TrackBacks (0)

Data warehousing and data marts would be simple to construct if only the data were in a standard format. Five years from now, businesses will take OLAP for granted. (OLAP is a fancy way of saying we're going to automate the sums and averages of your sales data over time so you don't have to do all that stuff in Excel any more.) Five to ten years from now, businesses will live or die by their data mining algorithms. (I classify DM as a step above standard OLAP.) Before this can happen, the data have to be available in a usable form.

I come from an information security background, thus I spend far too much time poring over computer logs: web server access logs, firewall logs, Windows event logs, not to mention /var/log/*. I have learned lots of stupid log tricks, like using logwatch, grep (my favorite), Snare to send Windows logs to syslog, and now, Microsoft's free Logparser tool. Logparser has poor documentation but will certainly pay you back for time taken to learn to use it. There's even a non-Microsoft site dedicated to logparser.

Note: Syslog does not store data in 3NF rows. If you want to be able to sort by fields with destPort, sourcePort, sourceIP, destIP, without doing text search, you'll be doing a LOT of ETL work.

This week I was thinking about replacing my firewall/router (a Netopia R9100 with the hardware VPN upgrade that I trade off with a Linksys WRT-54GS (v3) when I'm not paranoid about using wireless.) And yes, I'm not supposed to tell you that, but it doesn't really make a difference if we're both using nmap. So I looked at firewall vendors websites to learn what I could about logging capabilities. I'm slightly less concerned about security in my home lab than I am about collecting data on attacks. Firewalls have been around for over ten years now, so you'd think they would have logging down.

Watchguard: several logging options, including syslog and XML, SNMP costs extra.

Juniper/NetScreen: syslog, SNMP, NetIQ (If I feel like paying for that, too.)

Checkpoint: "Eventia Reporter™ is a complete reporting system that delivers in-depth network security activity and event information from Check Point log data." This means I can look at CheckPoint logs, but I can't correlate them to anything else. This Checkpoint vs. Cisco page is also interesting.

SonicWall: "ViewPoint®, Local Log, Syslog, WebTrends" I can pay extra for SonicWall's "Viewpoint" product, but I still can't correlate SonicWall logs to any other logs. One SonicWall includes a "secure" switch in their firewall: I would love to see what happens when I try an arp spoof. (If I wanted a switch, I would buy one.)

Cisco PIX: SNMP, Syslog, and AAA ("Authentication, Authorization, and Accounting Support") It does Cisco logging. It also has a CLI. (Command-Line Interface.) Unless Cisco starts giving me free hardware, I'm not sure why I'd use a PIX. If I blow a command, my network is not secure. A CLI is fine when it's obvious if a command is working or not, as with routing, but with firewalls, it makes me nervous. Then again, you should test every port after entering a rule change on your firewall.

Microsoft ISA Server: "ISA Server 2004 provides detailed security and access logs in standard data formats, such as delimited text files, Microsoft SQL Server databases, or SQL Server 2000 Desktop Engine (MSDE) databases."

I don't even like software firewalls, but Microsoft makes it easy for me. At $1,500 plus $250 for decent software, Watchguard is more expensive than ISA server. Checkpoint and Juniper won't even tell me how much their products cost. Sonicwall, Watchguard, and ISA Server are all priced on CDW.

If firewall data are this disparate, I can't imagine what a pain it must be to build data warehouses with data from other sources. Current firewall products seem to create their own silos and make it difficult to track intruders across a network rather than just at the perimeter.

Posted by Larry at 10:59 PM | Permalink | Comments (0) | TrackBacks (0)

Using syslog, MS SQL 2005, SQL Server Analysis Services, and MS Excel, I can build a cube with my firewall log violations and then import the cube into Excel and produce pivot tables. While this might seem more complicated than it needs to be, I could produce a daily scorecard of attacks. The only catch is that I need a firewall that logs to SQL server or a syslog to SQL server connector. The syslog => SQL connection would be tough because my router/firewall doesn't do uniform syslog notifications. I know enterprise-level firewalls do much better logging, like the Watchguard X-series which I was fond of just because I could make them do almost anything. The last time I checked, though, they still cost $1,500 for the base model plus $500 for the appropriate software.

With the Watchguard's new XML logging, I could create a SQL Server Integration Services package to import the data regularly. From there, I could get SQL Server Analysis services to process my cube each night. Then I use Microsoft Sharepoint's Scorecard or OLAP web part to display statistics. Best of all, I wouldn't have to mess with doing my own manual extract-transform-load (ETL) of my router log data.

The graph below represents a simple count of attacks by port on my router. Port 0 corresponds to ICMP. (I don't respond to ping requests.) The rest of the ports are closed, except for port 80, which you're using now. I ban a few IPs on port 80 because they won't stop posting junk trackbacks onto my blog. The ports are in alphabetical order rather than numerical order because I must store them in text fields rather than numerical fields in the database. If the port numbers aren't text then SSAS will OLAP them and I'll end up with the sum of all ports, which is nonsense but nevertheless might make a good statistic for MBA-types. While the graphic may not be all that impressive, the scalability is. Using SQL and SSAS, I could track probes and attacks on hundreds of firewalls at a time, track trends over time, and even predict the level of future probes.

Posted by Larry at 6:14 PM | Permalink | Comments (0) | TrackBacks (0)

Summer may be the best semester at George Washington University because all the undergrads are gone. The Marvin center is almost completely vacant, and the food court is closed. I am taking two electives this summer, which will give me more electives than I need. Why am I doing this? I’m actually learning something useful. People keep asking me why I don’t go for the CIO Certification. Instead of doing databases, I’d be taking MGT 272 Information Resource Management and MGT 274 Survey of Advanced IT Technologies. I have yet to read a single government job description that even mentions CIO Certification. (The Government Services Administration “invented” the CIO Certification, but the Office of Personnel Management sets job standards.)

I’ve taken academic classes in Information Systems at GWU for a while, and I have also taken classes at Learning Tree in SQL, Exchange, Solaris, Security and the programming language, C. The more advanced my classes get at GWU, the more they resemble a Learning Tree class, with one exception: at GWU, they teach theory and practice. At Learning Tree, it’s just practice. You can learn how Microsoft SQL Server works without learning a thing about normalization. Learning Tree is training database administrators, not database designers. My professor for both classes, John Artz, argues that vendor certifications will include more theory in the future; otherwise the vendor certs will become less relevant over time.

So what are my classes? Data Warehouse Design; and Database and Expert Systems. Database and Expert Systems includes no expert systems (I covered them in Decision Systems), and is mostly T-SQL for Microsoft SQL Server 2005. Data Warehouse Design is mostly theory with some implementation on SQL 2005 Analysis Services. Relational database theory hasn’t changed in about 30 years now, so you’d think I would have learned more relational database management systems earlier, but hey, it’s easy to become distracted with security and email and the web servers and Linux. Not that I haven’t used plenty of relational databases as back-ends to applications – I just didn’t think about the relational algebra that drives my queries.

While I can’t post class notes here, I can tell you which books the professor has chosen. When I’m searching for technology books, it’s hard to tell which ones are good.
Database and Expert Systems:
Dusan Petkovic’s SQL Server 2005: A Beginner’s Guide
Ken England’s Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook. (There isn’t one for SQL 2005 yet.)
Data Warehouse Design:
Ted Lachev’s Applied Microsoft Analysis Services and Microsoft Business Intelligence Platform 2005.
(And the John Artz manuscript.)
Both classes:
Connolly and Begg’s Database Systms. This is the book I wish my database class from last semester used. It covers a lot more than that textbook: Hoffer, Prescott and McFadden’s Modern Database Management.

Posted by Larry at 11:43 AM | Permalink | Comments (0) | TrackBacks (0)

I'm continuing to use the Exchange 12 preview beta, with mostly good results. Everybody that sees the new Outlook Web Access loves it and wants it. Thus users will have a reason to upgrade and it won't be just us messaging engineers that care. Since Exchange 12 will require 64-bit hardware, having a greate user interface may help justify the cost to many organizations.

I had one no-connect issue, which I tracked down to a DNS issue between my Windows Server 2003 Domain Controller (ADI-DNS) and my Red Hat Federa Core 4 box running Bind 9.3.1. While I don't do secure dynamic updates to BIND (yet), I do secure transfers from AD to BIND. (When I make sure BIND knows AD is authoritative for my AD zone.) It's funny seeing all the Active Directory DNS records in BIND: _gc, _kerberos, _ldap, etc. (Which brings up a typical AD interview question: what version of BIND do you need to run AD? 8.6.3 is the minimum. BIND 9 is better because it won't make you set the don't check names option because it supports a wider character set for domain names.)

I recently set up an account for the lab here in AD for mail to forward to a couple of folks in Outlook 2003 on Windows 2000 SP4. This account is not an administrator on the local box, and it sends and receives mail fine, but on Send/Receive I get a

Task ‘Microsoft Exchange Server’ reported error (0x8004010F): ‘The operation failed. An object could not be found.’

Microsoft has a solution for Exchange 2003 here: http://support.microsoft.com/?kbid=905813 ; but this isn't possible using the current Exchange 12 implementation. The error doesn't occur when I log on as a user with local admin privileges (yet). The server event logs don't show any errors. (I can grep my event logs because they go to my linux box via syslogd.)

I'm left with using the Exchange Management Shell's new-OfflineAddressBook command; first you must create an address list: "new-Addresslist." Fortunately, the shell features tab-command completion. All I have left to do is add addresses to my offline address list, but at least I have an offline address book, so my error should disappear, right? Wrong. I still get the error above. I'd fire up my Etherreal to do some more diagnosis, but I don't get the error on accounts wtih local admin privileges. Aargh.

Sometimes I miss the GUI.

Posted by Larry at 2:45 PM | Permalink | Comments (0) | TrackBacks (0)

People (mostly database people) keep asking what I meant by reflexive query in my previous posts. Some thought I was confusing a self-join with a recursive query that would allow my DNS server to answer a DNS query for a domain for which it is not authoritative. What I mean is a query that returns a caller and a callee; and then another query that returns the callee's callees. In a self-join, I can match employers to their managers, since both are in the same table. While the NSA-phone tracking system might use some self-joins, what makes the network part work is getting queries from queries, and jumping from callers to callees. Of course I'd like all the results timestamped, too. Recursive queries are a part of this, and SQL 2005 can do it. If I had a few gigabytes worth of phone data, I'd love to let SQL 2005 loose and see what connections I could see. When I say reflexive, though, I mean that I'm going to use my queries to start other queries, and not just as sub-queries. The recursive part could lead to infinite loops. I wonder if the NSA hit any infinite loops when testing their system. Fortunately, you can specify limits on recursion in SQL 2005.

The scary part of this is what would happen if I had the resources to make it run really fast and tuned it to be as efficient as possible. I could select a target and find all of its connections in a few seconds or a few minutes. The difference between seconds and minutes would make a huge difference. In a system where it takes a couple of seconds to generate results, nobody would notice if I ran a few "unofficial" queries on my friends. If it took a few minutes and precious computer time, then people would notice. Utilitarian ethics.

This begs the question: how much computer time (and tax dollars) does our government use tracking down everyone who calls reporters? Then again, leaking classified information is unethical, but people do it anway. Contextual ethics. Of course, there's also the issue of selective enforcement, but that's a legal issue, not an ethical one.

Thus we're left with utilitarian ethics vs. contextual ethics. Who knew that efficient queries and more processing power could give one type of ethics an advantage over the other?

Posted by Larry at 12:55 AM | Permalink | Comments (0) | TrackBacks (0)

I was curious as to what Exchange 12 opened on my old Dell, so I ran a quick nmap scan. I also have SQL 2005 running, so that's open, too. As you can see from the list below, not all nmap service reports are accurate. Pretty short compared to my Fedora Core 4 box running Apache, MySQL, and Sendmail.

PORT STATE SERVICE
25/tcp open smtp
80/tcp open http
135/tcp open msrpc
139/tcp open netbios-ssn
443/tcp open https
445/tcp open microsoft-ds
593/tcp open http-rpc-epmap
1040/tcp open netsaint
1083/tcp open ansoft-lm-1
1155/tcp open nfa
1433/tcp open ms-sql-s
3389/tcp open ms-term-serv
5001/tcp open commplex-link
6001/tcp open X11:1
6002/tcp open X11:2
6004/tcp open X11:4
8009/tcp open ajp13

Two System Log Errors from the scan, One System Log Warning:
None, message: An anonymous session connected from 10.10.10.15 has attempted to open an LSA policy handle on this machine. The attempt was rejected with STATUS_ACCESS_DENIED to prevent leaking security sensitive information to the anonymous caller. The application that made this attempt needs to be fixed. Please contact the application vendor. As a temporary workaround, this security measure can be disabled by setting the \HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Tur nOffAnonymousBlock DWORD value to 1. This message will be logged at most once a day. , Matched on: Type: Error , timestamp: 16:54:50 04/22/106

TermDD:50 on xxxx, category: None, message: The RDP protocol component X.224 detected an error in the protocol stream and has disconnected the client. , Matched on: Type: Error , timestamp:16:55:08 04/22/106

The Security System has received an authentication request that could not be decoded. The request has failed.

The Exchange roles running on this box include everything except gateway. (Client Access, Mail Store, Bridgehead).

For the full Nessus 3.0 report, read on.

Continue reading "Exchange 12: Open Ports" »

Posted by Larry at 4:36 PM | Permalink | Comments (0) | TrackBacks (1)

All I want to do is build a completely 64-bit AMD dual-core box.

Oh, the many 64-bit options and the lack of details. You can buy a 64-bit box, but are the storage and network drivers 64-bit? Does my 64-bit PCI storage controller have 64-bit drivers? For Vista CTP 64-bit? When Solaris was the only 64-bit game in town (available to me anyway) I could get answers. Now that everyone is making motherboards that support Intel and AMD 64-bit processors, it gets much harder to track down answers

Hypothetical: Today, you need to buy a server to run Exchange 2003 Sp2 on W2k3 R2. You want to make sure it will run Exchange 12 with no bottlenecks that will hurt performance, like a 32-bit storage driver. What do you buy? Microsoft has tips for the folks writing drivers. Where’s the 64-bit hardware qualification list? Why is it easier to find Sun servers that meet Microsoft’s 64-bit HQL? This is close. But are all the drivers 64-bit? Vista? ATI has Vista drivers available.

Posted by Larry at 12:48 PM | Permalink | Comments (0) | TrackBacks (0)

On the install, the Exchange installer didn't ask me to run ForestPrep
or ADPrep -- it just tweaked AD during the process with my credentials.

There are also a couple of neat tool shortcuts inside Exchange System
Manager. At the bottom of the tree part of the MMC are links to
Exchange Best Practices Analyzer (installed automatically), which
auto-updates as soon as it is launched. The first time I ran it, I got
a squawk about having no WINS server. I run a brand-new Windows Server
2003 Domain at the W2K3 functional level (on a separate box), have DNS
working great (even replicating to BIND 9.3.1), but Exchange 12 still
wants to see WINS. This makes me think that WINS may not be going away
in my lifetime.

The next shortcut in the toolbox is to PerfMon, (as long as I can run
it with “perfmon” I won’t call it “System Monitor” with a nice default
set of SMTP send and receive stats, Mapi.net and IS RPC hits, total
memory pages, and total processor time.

The final toolbox shortcut is Exchange queues, which saves time from
drilling down to find the SMTP/IMAP/whatever server you’re looking for
and hitting F5 a bunch of times. You get there faster, but you still
need to hit F5 for faster updates.

Also: Exchange 12 and SharePoint Services don’t seem to get along.
Exchange 12 and SQL 2005 share the same box fine so far, and SharePoint
installs and extends sites fine, but when it comes time to create a
site, it chokes, using the web-based administrator and stsadmin.exe.
Thus I may be installing IIS and SharePoint services on my Domain
Controller.

Even in my home lab, I hate compromises.

Posted by Larry at 3:58 PM | Permalink | Comments (0) | TrackBacks (0)

The other day I logged into my Exchange 12 Beta OWA from a campus lab here at GWU. (It comes with its own, untrusted certificate for SSL: "Exchange Edge Certificate.") I had previously explored OWA options and went to the general settings and saw an appearance tab. I selected the "black" appearance and reloaded OWA. It is a hip-looking white type on black interface, except for the actual message window, which is black type on white.

One of my classmates, looking over my shoulder, said how much cooler it looked than his OWA, which looked the same as everyone else's OWA on E2k3.

It looks like there will be skins for Exchange 12 OWA and they will make users want you to upgrade to Exchange 12 ASAP. I just wish everything in the beta OWA was working like security and spelling, because my friends who have checked it out are now worried that OWA won't support it in the next version.

Posted by Larry at 4:52 PM | Permalink | Comments (0) | TrackBacks (0)

As Microsoft creates new versions of its business software it is creating new Metaphors. As I explained previously, Exchange Server 12 will no longer be just front-end back-end but client access, gateway, bridgehead, and mailbox storage. Microsoft SQL 2005 has changed, too. No more Enterprise Manager; and no, this doesn’t mean you’re going to have to learn all the options of dbcc. If you try to connect to SQL 2005 using Enterprise Manager, you get the error, To connect to this server, you must use SQL Server Management Studio or SQL Server Management Objects (SMO).. However, my old Query Analyzer works just fine. What has changed (among many things) is the management interface: SQL Server Management Studio.

SQL Server Management Studio looks a lot like Visual Studio.Net – instead of database administrators (boring), we’re now database developers providing solutions. (Does this mean we get paid more?) There’s a solution explorer in the Management Studio console. There’s a GUI drag-and-drop manager for creating backup jobs, index rebuilds, that makes working with SQL 2005 look better, or at least sexier-looking. After logging onto EM about a thousand times, change is good.

Also: SQL mail in 2005 no longer requires an Outlook profile. You can use just SMTP and even authenticate to it. I love being able to choose the context under which every last process runs.

Posted by Larry at 12:47 AM | Permalink | Comments (0) | TrackBacks (0)

The Exchange 12 Beta installation is simple and somewhat
elegant. It doesn’t ask you to install Forest Prep or Domain Prep – it just has
a step where it does it. You can configure your Exchange 12 server to perform
several functions: Gateway, Bridgehead, Client Access, Mailbox, and Unified
Messaging. Gateway is not compatible with the other functions, since it’s
supposed to operate outside the Exchange org, screening and securing your
messages. Thus the metaphors from current Exchange parlance have changed –
Front-End has become Client Access, for instance.

Once you’ve installed the beta, there are four items
installed under the Microsoft Exchange folder in the start menu:

1. Exchange Console Manager, which replaces the Exchange System Manager
2. Exchange Server 12 Help, the sole source of documentation, even documenting parts of the ECM that haven’t been implemented yet.
3. Exchange Queue Viewer, a really fast way to check your mail queues.
4. Exchange Management Shell: A DOS box in which you can enter commands to manage your Exchange 12 environment. If you’re not comfortable at the command line, don’t try the Beta, because there are many things you can’t configure any other way. On the other hand, it’s still simpler than trying to edit Sendmail’s config.cf directly.

Posted by Larry at 5:36 PM | Permalink | Comments (0) | TrackBacks (0)

Microsoft’s Exchange 12 Beta arrived in my TechNet pack, and I had a box on which to install it. Why not? The Beta supports 32-bit hardware; production versions will not, according to the release notes. Installation was only a minor pain. My prerequisites, dot.Net 2.0, IIS and ASP were installed, but Microsoft Management Console, version 3, was not. Fortunately, the installer GUI had a link to MMC 3.0, just not the right one. The one you want for Exchange 12 is MMC 3.0 Pre-Release (RC1 Refresh). It’s mo’ Beta.

First install: everything worked except OWA and SMTP, which was kind of a big deal. However, I had done several test configurations on ASP and created so many Application pools running under different contexts that I had probably messed it up totally, so I uninstalled Exchange 12 and IIS.

Second install: the “client access” server install returned an error. I uninstalled Exchange and deleted every registry key that had Exchange or it’s path in it.

Third install: no errors, OWA worked, I could connect from Outlook. I couldn’t yet send or receive mail, though, because I hadn’t configured any connectors. Looking into the new Bridgehead server role configuration on the Exchange Management Console, as it’s now called, I saw only a blank screen. The help file explains how to use it to configure inbound and outbound connectors, but there was nothing there. Back to the release notes. The EMC does not yet implement a GUI for configuring connectors, so I had to start entering commands on a line.

Which brings us to the new way of administering Exchange 12: the command line.

Next to the EMC in the start menu is something called the Exchange Management Shell. I had to look at the help and start learning the new command structure. Fortunately, the release notes mention that you might not want to use the example given in the help file that creates an open relay for all.

Here’s what a command looks like:

New-ReceiveConnector -Name Internet2 -Type FromInternet -Bindings:10.10.10.202:25 -AnonymousAllowed:true -AdvertisedDomain xxxx.net -RemoteIPRanges
0.0.0.0-255.255.255.255

Once you’ve done a few commands, you’ll get it. And when you want to tweak one item, you don’t have re-do the whole command. You can do just one in a command. So try the Beta of Exchange 12 if you like, just be
prepared to learn a new command language.

One other thing: I did use the EMC to move the Information Stores, which was really easy. It dismounted them automatically, moved them, and remounted automatically.

Posted by Larry at 12:02 PM | Permalink | Comments (0) | TrackBacks (0)