netnerds.net

While my firm explores using WebDAV and SharePoint 2007 for exchanging large amounts of files, we're temporarily using FTP dropboxes to fill the void. Last Monday, I setup 11 new accounts and it took a total of one hour to complete the same 15 step process (give or take) for each account. By the time I was finished, I decided automating FTP account creation would be my first PowerShell project. What you see below is part of that project.

The code below creates a virtual directory in the "Default FTP Site" of the machine that is running the PowerShell script. The virtual directory called "NewUser" is mapped to C:\FTP\NewUser and is set to be both readable and writable. For the record, I couldn't get WMI to work (get-wmiobject) and that's the reason I decided to use the .NET's Directory Services support.

Alternatively, you could do go straight for the path if you know it (IIS 6 seems to like this):

If you would like to iterate through each of the virtual directories on your FTP server, you can use the following code:

This code is likely applicable to many of the objects in the IIS ADSI provider. While I've only tested this on Vista (IIS 7), this should also work for Windows XP and 2003's IIS 6 as Vista uses IIS 6's MMC for management.

Also, if you are wondering how I know when to use psbase or psbase.children, I really don't. I just fumble around until I get it to work. The 4 lines above, specifically $virtualdirs = $site.psbase.children.Find("Root","IIsFtpVirtualDir").psbase.children took me about seven hours to figure out. I hear PowerShell 2.0 will have much better support for Directory Services and hopefully that will include support the IIS FTP service.

If you've ever dealt with NTFS permissions in VBScript, you will no doubt appreciate just how easy PowerShell now makes it to manage access control lists. Basic examples in PowerShell books and around the 'net look something like this:

In the example above, user "IUSR_CRACKLIN" is given Modify access to the Test directory. Running the code above will not produce any errors but upon checking permission via the GUI, it seems as though the user was added, but no permissions were set.

I thought that perhaps this was an issue with Vista and I tried it on Windows Server 2003. And that's when I noticed that the directory had been given "Special Permissions." When I checked the Advanced permissions, I could see that Modify access had been assigned, but only to "This Folder." Other folders that had the checkboxes checked listed "This Folder, subfolders and files"

Since I wanted the Test directory permissions to match the others, I searched the Google to see which flags would give me "This Folder, subfolders and files." I found Damir Dobric's blog post titled "Directory Security and Access Rules which sported a handy reference table flags that must be set to achieve various scenarios.

So it setting the following should give me what I need:
InheritanceFlags.ContainerInherit, InheritanceFlags.ObjectInherit and PropagationFlags.None.

I then checked the permissions and voila:

Imagine that.. PowerShell can set any number of permissions with about 6 lines of code while VBScript requires over 36 lines JUST to set the constants needed for managing permissions. I'm so excited thinking about the possibilities: PowerShell + Windows Core + SSH is going to be awesome.

When creating a new Active Directory user from the command line in PowerShell, you will likely find yourself using Read-Hosts's asSecureString switch when entering the password.

Next, you'll probably look around the Internets for a few hours or so trying to figure out how to change the password of the newly created user. You will soon discover that the user creation process in PowerShell 1.0 isn't very straightfoward and it even requires a specific order for proper account creation. First, you create the account, then you set some basic properties, next you call SetInfo(), and finally you invoke setPassword using the follwing syntax:

Now you may find yourself with the following exception: Exception calling "Invoke" with "2" argument(s): "Exception has been thrown by the target of an invocation." Originally, this post mentioned using toString() to address the problem but PowerShell team member Lee Holmes wrote to let me know that the password was changed literally to System.Security.SecureString. He also said that "there is no really easy way to convert a secure string to plain text - on purpose. Since a SecureString is supposed to prevent plain text from littering your computer's memory, converting it to plain text defeats the purpose."

My primary reason for using asSecureString is to encode the string into asterisks when typing it at the prompt. So Lee gave me two ways to convert the password to be used while invoking SetPassword. Note that unless you are using a secure LDAP channel, the password will be sent over the network in clear text.

Or, alternatively:

If you continue to see this exception, check to make sure that the password you entered meets your domain's password complexity requirements.

So I'm making the move to PowerShell. It's painful learning such alien (to me) concepts but books like Lee Holmes' PowerShell: The Definitive Guide help a ton. I was fortunate enough to be the editor for Chapters 1-5 and got a sneak preview. It's a fantastic book and can't wait to receive the title, complete with indexes! For now, I'm searching both the 36 Word documents and the sample code for solutions using Vista's built-in search functions.

My first task, which I'll explain in later posts, includes some AD stuff. One tiny part of the equation is dynamically finding the FQDN of the current Active Directory domain. This should be easy! Using RootDSE, it's super easy to find out the DistinguishedName or even the FQDN of the domain controller being queried, so wouldn't there be a similar entry for FQDN of the whole domain? Apparently not (or if it's there, I can't find it). I've spent the morning and part of last night digging through LDAP filters, looking on Google for examples of objectcategory=crossref, dnsroot, dnshostname, etc. But after finding this useful codeplex page, I played around with GetCurrentDomain() and realized that finding the DNS hostname for an AD domain all boils down to this one line:

Or this one liner, as Lee just suggested to me

Ahh! So it was that easy. Why, then, did it take 8 hours to find that? Now I wonder how to get the FQDN of any domain. Being a newb is hard work.

If you're wondering what dnshostname is for, it's to resolve the local machine you are working with. Michael at brnets.com provides the following example embedded in a big ol Exchange script:

Oh, as a bonus, here's some another query you may find helpful. I started with benp's script then made it a little skinnier (and more prone to errors! ;))

Search for Active Directory User Object in the Current Domain

My original code made reference to GetDirectoryEntry() but John Brennan suggested looking for alternative approaches. Thanks for the tip, John!

While researching for a paper I have to write in class about hierarchical data in relational databases, I was reading Joe Celko's book Trees and Hierarchies in SQL for Smarties. In the intro, he mentions a really useful website for validating SQL-92, SQL-99 and SQL-2003. The site is nice enough but could really benefit from a bit of AJAX. If only I had the code behind the scenes, that'd be a perfect project for learning ASP.NET's AJAX add-on.

Looking for the T-SQL (somewhat) equivalent to VBScript's FormatDateTime function? I've been too, for years. I finally found it within the CONVERT() function. As stated in SQL Server Books Online:

In CONVERT ( data_type [ ( length ) ] , expression [ , style ] ), style is the style of the date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types); or the string format used to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types). When style is NULL, the result returned is also NULL.

Manuj Bahl wrote a nice article covering date and time manipulation in SQL Server 2000 and it in, he summarized BOL's table that lists the different style types. It went something like this:

Try this out with GETDATE() by running the following statement:
SELECT CONVERT(VARCHAR,GETDATE(),7) AS currentdate

Your results should look something like this: Jul 03, 07 Nice! Have fun

For my final project in my database class at USF, I chose to create a database for a small record label looking to sell albums online as well as provide artist bios, tour information and news. The database, which was scaled down version of a project I worked on in the past, looked something like this:

The professor asked us to compose a few sample queries and, while looking at the songs table, I wondered if there was a way that I could use the information in the length column to determine how long each album ran. The song length column was created as datatype datetime for this very reason. Since SQL Server does't support just a time column, I used the dummy 1900-01-01 date when INSERTING my values. A typical song length would looks like this: 1900-01-01 00:02:11.000.

So I wanted to find the full time length of an album without text parsing to look for ":" and so on. Ultimately, I found this to be possible. Here's the query I came up with:

What you see going on above is the following:
1. Using the DATEPART function to break down the song length into seconds. This is done by grabbing the various parts and multiplying them by 60 for minute or 3600 for hour.
2. Using DATEADD function to add those seconds to the date of "0" (1900-01-01).
3. Using the CONVERT function's ability to change datetime into various formats (kinda like VBScript's FORMATDATETIME), I went with style 114 or hh:mi:ss:mmm (24h))

The above query produced the result: 00:56:02:000. That's a 56 minute and 2 second running time. For more information on CONVERT's ability to manipulate datetime, check out SQL Server Books Online.