Archive for the ‘Courion Tip’ Tag

Courion Tip – LDAP/AD Queries

I have working SSIS packages that do an LDAP bind and pull in user information directly. I want to post the *.dtsx package here, however, I need to make very sure that no confidential information gets uploaded.

Also, it’s entirely possible that it won’t work when I get done. I will copy the *.dtsx to a virtual machine, and sanitize it there. All of which is to say, “stay tuned”.

Update – I finally have these cleaned enough where I am POSITIVE there is no confidential information. Unfortunately, wordpress only allows certain file types. So, I renamed it from RAW_AD.dtsx to RAW_AD.dtsx.docx (click here to download it).

Once you get it up and running, you might need this link http://www.tek-tips.com/faqs.cfm?fid=5667 to modify it for your needs.

A brief walk – thru (and, NO, these are not best practices – I’m just trying to get your system to collect information. You should tweak it!!)

  • Create a domain admin level account called courion, with a password of ‘password’.
  • Create a database called Courion (unless, of course, you already have one), and give the user courion complete access to it.
  • Start up Business Intellegence Development Studio.
  • File, New, Project, “Integration Services Project”
  • Give it a name that matches your standards, and a file location that makes sense. For me, I used “D:\SSIS Packages” and called the project “RAW_AD” – since it will be doing an import of Active Directory data and minimal manipulation of the data.
  • On the far right side, right mouse click on the words “SSIS Packages”, and choose “Add Existing Package”
    • Change the package location from “SQL Server” to “File System”
    • Click on the 3 “dots” to browse to the file you downloaded from this site (after you took the .docx off). Note that it will copy it to your new solution, so that you don’t need to worry about saving the original – it won’t be touched.
    • Click on Open, and OK.
    • You will probably get an error message about something not being in the right cryptographic state, don’t worry about this now.
  • Double click on the “RAW_AD.dtsx” and it should open up.
  • At the bottom of the screen, under “Connection Managers”, you should see 2 – “Courion Database” and “SMTP Connection Manager”.  For now, ignore the SMTP connection.
    • Double click on the “Courion Database” connection manager.
    • Change the name of the SQL Server to match your environment.
    • Choose whichever authentication matches your environment, but I’d suggest “Use Windows Authentication” – for now.
    • Choose your Courion database under “Connect to Database”. If you can’t choose it from the drop down box, then you probably have the server name incorrect, or you have chosen the wrong instance.
    • Click on “Test Connection” – if the connection fails, then the entire thing will not work. You must fix this.
    • Click on “OK”
  • Repeat, if you want, with the SMTP connector. (though it’s configuration is much simpler)
  • Create the required tables:
    • RAW_AD_temp: Right click on the box that says “Drop and recreate RAW_AD_temp”, and select “Execute Task”. While this isn’t strictly necessary, you’re probably seeing some error messages. By creating this table, the errors/warnings will go away.
    • RAW_AD. Download Create RAW_AD.sql.Again, to get it to upload, I had to rename it, putting a “.docx” at the end of the name.  Open it with any text editor, and then copy it’s contents into your SQL server management studio. Execute the sql statement, and you should be all set creating this table. You might want to create this table on your own. However, since the column names and data types might not match what I chose, you might generate errors. Save that for a future experiment.

At this point, you should probably save your work, and exit out of BIDS (Business Intelligence Development Studio). Then go back into BIDS, and open your project. Why? Mostly, to clear out any error messages that you see and verify that any existing errors/warnings are ‘real’ ones, and not just cached/queued ones.

If you don’t see any error messages, then it’s probably time to test your package. You might wonder about the greyed out boxes – some are for email notifications, and the others are ideas I haven’t fleshed out completely. Perhaps I will post updates to this, but for now, since I am assuming you are new to this, please ignore them.

To run the SSIS package, click on the “Debug” menu, then “Start Debugging” menu item. As each step of the process completes, it should turn green for success, or red for failure. Please read any pop-ups, as they should help you debug the SSIS package.

A few final notes:

  • Data types and data length. There are several places that modifications need to be made, and I have been very careful to avoid generating errors and/or warnings.
    • RAW_AD and RAW_AD_temp tables. Don’t forget that the temp table gets rebuilt on the fly, so you need to edit the script, not just the table.
    • The “Inputs and Outputs” of the “Script Transformation Editor”
    • The vbscript itself
  • Rebuilding the vbscript – to rebuild the vbscript that does the ldap bind:
    • Double click on the box that says “RAW_AD temp – Dataflow”
    • Double click on the box that says “RAW_AD Query Script”
    • Click on “Edit Script”
    • A new window should open, called “ssisscript – Integration Services Script Component”
    • In the upper right of the screen, right mouse click on “ad_query_vbs” and select “Rebuild”
    • Click on “File” and “Save All”
    • Close out this window

There is certainly room for improvement. For instance, instead of dropping all the data from the RAW_AD table every time, a slowly changing dimension could be used. The authentication for SQL Server could be stored in a variable (in a configuration file) making the code portable. And, of course, what data is collected could be changed.

https://mmdmurphy.wordpress.com/2012/06/01/courion-tip-before-courion-arrives-ssis-and-datafeeds/

Additional notes – SQL server supports a ‘checksum’ calculation, for instance : http://www.mssqltips.com/sqlservertip/1023/checksum-functions-in-sql-server-2005/. This makes a WHOLE lot more sense for checking to see if a user’s information has been changed than a HUGE query. That, coupled with triggers, will ensure that the information is up to date.

Posted June 7, 2012 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – Before Courion Arrives – Hardware and Software   Leave a comment

Basically, you don’t want to scramble ordering software and hardware after Courion has arrived at your door. Why not get ready first?

For starters, please see my other post https://mmdmurphy.wordpress.com/2012/01/11/courion-tip-auxilary-tools/. You obviously need servers – some for SQL Server, and some for Courion, but I can’t give you a simple summary of what you need. It depends on your environment. However, you probably can start off with a virtual machine for SQL Server and a virtual machine for Courion. Try to get that approved and set up as soon as you can. Don’t forget the ability to take snapshots of the VM’s – and I’d take them during the install process so you can always roll back.

Mostly, you need the Business Intellegence Development Studio and the Microsoft SQL Server Administrator tool (sorry, that’s from memory and may only be close to right). You need to get your feet wet with both products. Don’t be shy – courion shouldn’t be there yet, you should be taking snapshots, and you should be ready to loose everything you’ve done – don’t make any permanent changes. (also, make sure you have copies of any SSIS packages you make – preferably in a central place like Sharepoint or VSS or even a network share)

Hardware – Nope, not servers. Your desktop. You will need the biggest monitor you can get. No kidding, it’s not just ‘nice to have’. There are certain Courion configuration screens (specifically, Active Directory Unique Resource Data) that are HUGE and it’s impossible to see the entire thing at once without a large, wide, monitor. It might be a hard sell to management – but it will be worth it. Also, you don’t need the highest performance – you aren’t playing games. Size is the biggest issue.

I would suggest Windows 7, 64 bit, with 8 gig or more of ram. Ideally, you’d like to run a virtual machine on your computer, with SSIS and SQL server, and maybe Couron installed on it. It’s nice to be able to kick around something ‘in private’. However, you’re not trying to replace your developement system, just augment it.

You might consider getting Microsoft Visio and a product like Microsoft Project. It’s good to be able to map out SSIS packages in Visio, and a project management system is obviously a good idea.

(by the way, you don’t need a full blown copy of Microsoft Visual Studio. I wouldn’t bother, but that’s not to say I know the nature of your environment and project)

Posted June 1, 2012 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – Before Courion arrives – SSIS and datafeeds   1 comment

Lessons learned from the installs I have seen…. There’s a lot you need to know how to do, and by doing some of the work ahead of time, Courion can hopefully focus on more advanced issues. By doing these things, you learn the skills you need.

Send (or be sent) to SQL Server SSIS training. NOT SQL Server training, SSIS training. SQL Server and SSIS are KEY to Courion. Why not SQL Server training? Because – unless you are going to be your own DBA – you really don’t need to know how to install SQL Server, nor replication technologies, etc.

Take a VBScripting class. Courion supports both Javascript and VBscript, so this is more of a personal preference. But you need to know one of them – maybe both.

Deciede if you need to support unicharacter – you probably do.

Write an SSIS package that imports a flat file to a SQL Server table. It would be best if you actually import a flat file from your HR system, and populates a real table. Name the destination table RAW_something. “We” get our data from ADP, so I call the table we import to RAW_ADP. Do not “pick and choose” which data you will need, you can do that later. Be sure to import dates as dates, strings as strings. Arrange to have the file generated on a daily basis, and schedule your package to run daily – obviously, after the export happens.

Write an SSIS package that imports user information from Active Directory to a SQL Server table, using VBscript. Pull in as much information as you need. I import mine into RAW_AD table, AD being Active Directory of course. Schedule this to run on a daily basis, but be aware that you might wind up running more often than that.

I hope to scrub the packages I have and upload them, but I cannot be sure I will get approval for this.

If you get these done, you will be a LONG way to understanding SQL Server, SSIS and being able to understand the Courion person when they come out. When the consultant arrives on your doorstep, insist that they base populating the identitymap off of your RAW_tables. They might not readily agree, but if your process is sound, there is no reason not to. They will probably have to modify your package to point to whatever database Courion is actually going to use, that’s fine. They MIGHT want to modify it to remove users that aren’t in the identitymap – don’t let them. Emphasis that this is RAW data, that they should use this as a data source for thier process.

One final comment about SSIS and Courion – And this is going to sound like a personal preference, I don’t think it is – Stress to the consultant that you want SEPERATE SSIS packages for just about everything.

See my other post https://mmdmurphy.wordpress.com/2012/03/21/courion-tip-ssis-package-changes/

https://mmdmurphy.wordpress.com/2012/03/21/courion-tip-getting-started-know-your-data/

https://mmdmurphy.wordpress.com/2012/06/07/courin-tip-ldapad-queries/

Posted June 1, 2012 by mmdmurphy in Courion Tip, SQL

Tagged with

Courion Tip – Restricting Access to Workflows

The business case is this: Only people who’s active directory accounts are in “users” or are in “no mail users” should be able to reset their passwords using Courion. All other accounts are (probably) service accounts or privileged accounts. Courion already has documentation to restrict access to a workflow based on a single group membership, but what about restricting access to 2 different organizational units?
Step 1 – custom macro to return the user’s distinguished name

distinguishedName(sAMAccountName=%Auth Step 1.UserName%)

Step 2 – custom macro to parse the distinguished name.
VBS.PasswordReset_Restriction
Dim strDN, strFound
strDN = “%Custom Macro.AD.Provisionee.distinguishedName%”
strFound = (InStr(strDN, “OU=User,OU=CH,DC=ad-ech,DC=net”) + InStr(strDN, “OU=No Mail User Accounts,OU=CH,DC=ad-ech,DC=net”))
NativeScript = strFound
Step 3 – modify Auth Step 2, Limit Authentication Criteria :

‘%Custom Macro.SQL.GlobalConfigValues.ConfigValue.PasswordReset_Restriction%’>1

SQL Global ConfigValues.ConfigValue.Password_Reset_Restriction = SELECT ConfigValue FROM GlobalConfigValues WHERE ConfigName = ‘PasswordReset_Restriction’
Step 4 – modify the code that displays messages to the end user – so they know why they can’t use the workflow.

Posted April 29, 2012 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – User Home Drive Permissions

The situation – user home drives get created with Courion pretty much ‘straight out of the box’. Permissions can be set, drive letter set. However, the home drive does not inherit permissions from it’s parent folder. How to resolve this?

Start by reading “Configuring Workflows.pdf” that came with your version. For version 8.00, the pages of interest are 446 thru 454. This covers “Configuring Triggers”.

Next, make a copy of “TriggerUtils.vbs”, I called my copy “TriggerICACLS.vbs” since I will be using the icacls.exe program to re-enable inheritance.

And now make a short and sweet vbscript to make sure this actually does what you want. This is the vbscript I wrote:

Dim strHomeFolder, strHome, strUser, objShell, objFSO
strHomeFolder = “{\\path to server or DFS Share}\Home\{username}”
Set objShell = CreateObject(“Wscript.Shell”)
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
objShell.Run(“icacls ” & strHomeFolder & ” /inheritance:e”)

For me, (please test for your use case) I can recreate the issue by using “/inheritance:r” and resolve it by using “/inheritance:e”. In other words, make sure this fixes your issue.

Now for the tough part – editing of the TriggerICACLS.vbs file itself. And, sorry, but the line numbers shown below are only approximate.

Line 20. Change Const RDK_DEBUG = 0 to RDK_DEBUG = 1
Line 29. Change the DEBUG_LOG file name to “TriggerICACLS_debug.log”
Insert a line 68, which says DIM objShell, objFSO
Line 71. Change the name for TRIGGER_ONE_PARAM1. I set mine to “Directory to reset inheritance on”
Line 87. Change the help text for TRIGGER_ONE_PARAM1_ARRAY(ATTRIBUTE_HELP_TEXT). I set mine to “folder you wish to inherit permissions from parent”
Line 100. Change TRIGGER_ONE_PARAM2_ARRAY(ATTRIBUTE_REQUIRED) to “no”
Comment out lines 672 thru line 709 – and, note that the line numbers probably don’t align 100% with yours. So, its basically all the code that does the copying of the files/folders. The first line I commented out was ‘Open the file system object & the first line I left alone was ‘Call RevertToYourself to go back to who you were before the impersonation!

Insert the following code:

‘Set the strHomeFolder to inherit permissions from it’s parent
on error resume next
Dim objShell, objFSO
‘strSourceDir
Set objShell = CreateObject(“Wscript.Shell”)
Set objFSO = CreateObject(“Scripting.FileSystemObject”) ‘FileSysObj
objShell.Run(“icacls ” & strSourceDir & ” /inheritance:e”)

Now, log into the Courion server, and launch the Connector Configuration Manager. You will be adding a target to the Microsoft ActiveScript Cnctr.
I called mine “AD” since it’s Active Directory. I wasn’t positive which Operations I wanted, so I cheated and selected all of them.
The Script File Name should be the path and name to the vbscript you just created.
Next, enter the

Administrator User
Administrator Password
Server

Click on Finish. For the record, I couldn’t get mine to confirm, and so the rest of this is actually un- tested. It should prompt you to restart the Courion services, and you will need it to do so.

Now, go into the Courion admin and edit your workflow. In my case, I chose the Add action, Trigger, and the event of Microsoft ADS 5.x User Resource Success

Posted April 27, 2012 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – Target ID names

Until you progress beyond reading my posts, I couldn’t suggest strongly enough that you keep the targetid consistent in name structure and in every place it’s used.
To clarify, you need to enter the targetid 3 places in the workflow , 3 places in the AMM connector, and 2 places in the PMM gateway connector.
There maybe good solid reasons to use different names, but I would start by assuming there isn’t.

Posted March 21, 2012 by mmdmurphy in Courion Tip

Tagged with

Courion Tip – SSIS Package changes

First off – MAKE A COPY OF THE SSIS PACKAGES before you do anything. If possible, implement a versioning system. I like to just copy the folder, and rename the copy to yearmonthdate_package name. For instance, 20120301_Courion_SAP_Import

I can’t give you too many specifics, I will probably re-edit this post several times, but here are my gripes and my solutions to something that is more a matter of opinion than anything else.

Make as many copies of the package as you have target system types, then strip out all the other targets. In other words, if you have one package that imports 8 target types, you should wind up with 8 packages, each dedicated to a particular target type. I rename all of them to match what they are importing. For instance, Courion_SAP_Import, Courion_AS400_Import, etc. If you share your SQL server / SSIS server or DBA with other systems, the Courion at the front will help know it’s your package that’s failing.

Why split it up this way?

Well, for one thing, you can schedule them as appropriate. We import Active Directory every few hours looking for new accounts, but AS/400’s only once a day.

Secondly, debugging and updating. The person who did ours was, admittedly, under a time constraint. So, the resulting package worked just fine, but generated something like 40 warnings. This was a little intimidating for me (didn’t know SSIS very well at the time). By splitting it up, I could look at a few warnings and tackle them one at a time to get rid of the warnings. Also, you don’t want to add a target to just a small part of it, only to find out you have broken ALL of it.

Most of the warnings consisted of ‘data truncation could occur…’ Basically, we were importing 100 characters from a file, and putting it into a 50 character column. If you know your data (see my other post), you should know what the maximum is (50? 100? 2000). To correct this, start by setting your column in the table definition to something larger than or equal to the maximum (for instance, no point in having employee id’s that are 100 characters long, when company rules say they should only be 6 characters), and then looking at the connector information in the package. It might take you a while to track them all down, but it will be worth it as a training exercise & you will have a cleaner system.

Learn what *.dtsconfig files are about, and use them!!  We didn’t know how to test our code in our test system, then implement it in production without making fairly big changes to the code. This is the answer.

Your test database table structure must match your production – besides being just plain common sense, good practice and all that – since all you should be editing is the dtsconfig file, your package will fail if the tables are different (or work in an unpredictable way)

Posted March 21, 2012 by mmdmurphy in Courion Tip

Tagged with