Recently in Exchange - Sharepoint - SQL - AD Category

Here's another simple script that will simply write out your AD group memberships to a csv file with the name of a group. Input is a simple text file with one group name per line. This script is adapted from the original at WiseSoft.

' VBScript source code
' takes a list of groups in a text file and dumps out a text file with each group's membership.
Set objFSO = CreateObject("Scripting.FileSystemObject")
'change this line to wherever you want to read the input from.
Set objTextFile = objFSO.OpenTextFile("c:\scripts\groups\groups.txt",1)

Do Until objTextFile.AtEndOfStream

groupName = objTextFile.Readline
'Debug.WriteLine groupname
If groupName = "" Then
wscript.quit
End if

groupPath = getgrouppath(groupName)
'Debug.WriteLine groupPath
If groupPath = "" then
wscript.echo "Unable to find the specified group in the domain"
wscript.quit
End if

Set objGroup = getobject(grouppath)
Set objFSO2 = createobject("scripting.filesystemobject")
'change the path to where you want the output files to go.
Set objFile = objFSO2.createtextfile("c:\scripts\groups\" & groupname & ".csv")
q = """"

objFile.WriteLine(q & "sAMAccountName" & q & "," & q & "Surname" & q & "," & q & "FirstName" & q)
For each objMember in objGroup.Members
objFile.WriteLine(q & objmember.samaccountname & q & "," & q & objmember.sn & _
q & "," & q & objmember.givenName & q)
Next

Loop
Set objFile=nothing
'***** Users who's primary group is set to the given group need to be enumerated seperatly.*****
getPrimaryGroupMembers groupName

wscript.echo "Completed"

Function getGroupPath(byval GroupName)
Set cmd=createobject("ADODB.Command")
set cn=createobject("ADODB.Connection")
set rs=createobject("ADODB.Recordset")

cn.open "Provider=ADsDSOObject;"

cmd.commandtext = "SELECT adspath from 'LDAP://" & getnc & _
"' WHERE objectCategory = 'Group' and sAMAccountName = '" & groupname & "'"
cmd.activeconnection = cn

set rs = cmd.execute

if rs.bof <> true and rs.eof<>true then
getgrouppath=rs(0)
else
getgrouppath = ""
end if
cn.close

End function

Function getNC
set objRoot=getobject("LDAP://RootDSE")
getNC=objRoot.get("defaultNamingContext")
End function

Function getPrimaryGroupMembers(byval GroupName)
set cn = createobject("ADODB.Connection")
set cmd = createobject("ADODB.Command")
set rs = createobject("ADODB.Recordset")

cn.open "Provider=ADsDSOObject;"
cmd.activeconnection=cn

'***** Change the Page Size to overcome the 1000 record limitation *****
cmd.properties("page size")=1
cmd.commandtext = "SELECT PrimaryGroupToken FROM 'LDAP://" & getnc & _
"' WHERE sAMAccountName = '" & GroupName & "'"
Set rs = cmd.execute

If rs.eof<>true and rs.bof<>true Then
PrimaryGroupID = rs(0)
Else
Err.Raise 5000, "getPrimaryGroupMembers", "Unable to find PrimaryGroupToken property"
end If

cmd.commandtext = "SELECT samaccountname, sn, givenName, distinguishedName FROM 'LDAP://" & getNC & _
"' WHERE PrimaryGroupID = '" & PrimaryGroupID & "'"

set rs = cmd.execute

while rs.eof<>true and rs.bof<>true
objFile.WriteLine(q & rs("samaccountname") & q & "," & q & rs("sn") & q & _
"," & q & rs("givenName") & q & "," & q & rs("distinguishedName"))
rs.movenext
Wend
cn.close

End Function

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?

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."

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.

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.

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?

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.