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.

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)

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/

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.

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

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.

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)

Know your data.

Let’s start with the basics. Courion (to me) isn’t a solution, it’s a solution framework. So, it (probably) doesn’t have canned answers for what you are trying to do, you have to come up with them yourself. I’ve had requests to base things on the user’s department number – only to find out that HR was creating new department numbers on a regular basis, but not letting anyone know. So, after a while, processes failed. The decision was made to base those same things on the state an employee was in, and we found that over 60% of the user’s didn’t have that populated.

IdentityMap (TM, courion, all that)

This is one of those super critical tables in courion that you really need to understand. It basically ties all the accounts that a user has together. Usually, it’s 3 columns – side note, seems like it’s grown A LOT since I wrote this. But the main 3 columns are still ProfileUID, TargetID, and UserName.

ProfileUID

This is what ties all the user accounts together. You really really need this column, it needs to be unique to each employee, and it needs to exist everywhere. I was at one company where everyone had employee ID’s to log in, and it was already set on every system. No firstname  lastname, or first initial lastname, combinations!! I was A340359 there and everywhere. This made our implementation a dream!! However, at another place, this wasn’t so. Everyone had an employeeID, but no one knew what it was and it wasn’t set on all the target systems.

At this point, you might be pondering if employee id’s are set on your SAP system, or your AS/400’s or …. Find out.  If your systems don’t have an employee id on them, and you’re thinking that firstinitial & lastname will do just fine, good luck.  What about Aaron Adams, and Abigale Adams, or even just the first 2 John Smith’s you hire.

When you import data from your target systems, it is a LOT easier to find all records where employee id is ‘888944’ than it is to determine if that record is from John Smith or Jonathan Smith.

By finding all records in the identitymap where profileuid = some value, you immediately know every system that a uwer has accounts on (that have imports into courion).

Know how long your employee id’s are, and what constitutes a valid employee id. For instance, if all employee id’s are 6 characters long, you should know that. (This also gives you options – perhaps the user accounts you don’t want to touch should be more than 6 characters?). Know how this column is defined in the IdentityMap table. (it’s probably varchar(50), just cos that’s what SQL defaults to). Don’t change it yet, just know what’s what.

UserName

This is the user name on the target system. These will probably be all over the map, and here’s where your users are probably set up as first initial last name…  Hopefully, you have naming standards set up on all of your target systems. Employee ID’s pay off well here, because if you are importing jsmith and jsmith1 how do you know which user is responsible for that account??? If you have employee id’s in place, it is a resolvable situation since you would be keying off of the employee id, not the user’s name on that system.

TargetID

These are probably carved in stone at this point, but if you’re just getting started, stop and think!!  There are plenty of places that this information is used. I strongly recommend that you use target id’s that make sense, mean something to end users, and convey information. For instance, SAP_ABC_110_T tells you right up front that it’s an SAP system, it’s your ABC box, client 110, and it’s a test system.    The target id will come into play when configuring workflow targets, configuring SSIS import processes,  automatically generating emails that go to the end user, and potentially other places (identitymap selection screen in the workflows themselves).

To Do’s –

Date time stamp The only change I might recommend here is to add a date time stamp column to the identitymap.  That way, you will be able to keep track of when a user was given an account on a particular system. Auditors will love you for it, and it can help in debugging (for instance, if you search for all entries in the idenitymap for a given target id sorted by the date time stamp, you’d quickly know when the last time a user was added, or when the last time the process worked successfully)

Data cleanup – Before too long, you’ll find that your system is great at importing data about new users on systems, but not so good at removing information. As an example, let’s say you have an SAP account on our SAP_ABC_110_T system today. Next month, your account gets removed (and, let me digress – if ALL of that were to be done inside of courion (adding the account and then deleting it – then it will keep track of the changes, and you need not worry about it. That’s one of the beauties of the identity map). Your identitymap will still show an entry for that system, and you will keep getting password change failure emails every time you change your password.

I would strongly suggest that you encourage the owners of the target systems to send you information about not only ADDITIONS, but also account DELETIONS so that you can avoid this. Why not just delete all the entries that are not in the newest feed? Because, if you are not careful, and the feed fails, you will delete ALL the records for that target system.

Update…. I thought I would throw this in as an example of how not to do something. And note, it’s a real life example – I didn’t make this up!
CREATE TABLE [dbo].[Staging_AD](
[objectGUID] [nvarchar](2500) NULL,
[sAMAccountName] [nvarchar](2500) NULL,
[GivenName] [nvarchar](2500) NULL,
[sn] [nvarchar](2500) NULL,
[mail] [nvarchar](2500) NULL,
[employeeID] [nvarchar](2500) NULL,
[employeeType] [nvarchar](2500) NULL,
[pwdLastSet] [nvarchar](2500) NULL,
[description] [nvarchar](2500) NULL,
[displayName] [nvarchar](2500) NULL,
[department] [nvarchar](2500) NULL,
[streetAddress] [nvarchar](2500) NULL,
[l] [nvarchar](2500) NULL,
[st] [nvarchar](2500) NULL,
[postalCode] [nvarchar](2500) NULL,
[telephoneNumber] [nvarchar](2500) NULL,
[facsimileTelephoneNumber] [nvarchar](2500) NULL,
[memberof] [nvarchar](2500) NULL,
[distinguishedName] [nvarchar](2500) NULL,
[modifyTimeStamp] [nvarchar](2500) NULL,
[useraccountcontrol] [nvarchar](2500) NULL,
[accountExpires] [nvarchar](2500) NULL,
[Disabled] [nvarchar](2500) NULL
) ON [PRIMARY]

Why on earth you would want to store Disabled (a boolean value) as nvarchar(2500) is beyond me. In addition, all the timestamps are also nvarchar(2500). This shows a complete dis-regard for the nature of the data.

I have in mind to do a series of these, but let’s start with the basics.

The first thing I think you should do as a new courion admin would be to make sure you have backups. Backups of what?

Well:

cfgfile.db & log files

The courion support site has a batch file that you can download and set up on your system. Start with that. Basically, it stops all the services, copies the log files, and restarts the services. Modify it to also make a copy of cfgfile.db before it restarts the services. I have mine set to make 26 backups, if you run it once a week that comes out to 6 months worth of potential ‘roll backs’ you can do. More is better. Oh, and while we are using it for something other than it was originally intended for, you should probably include the archive folder (its under your courion folder).

If you want to get really fancy, you can download the command line version of 7-zip and have it automatically compress the files – but, personally, I didn’t bother.

Schedule it to run in your test environment (you DO have a test environment, don’t you?) once a week or more until you are comfortable, then I would schedule it in production for once a week.  I ALSO manually run it right before making any major changes, so I’d put a shortcut to it on the ‘all users’ desktop on that machine. Remote desktop is your friend.

What is the cfgfile.db? My understanding is that it is a berkeley database (ie, non-relational) encrypted with the name of the server you are running on. If you make changes to the workflows that you MUST get back, it can be the thing that saves you.

SSIS Packages

The next thing I would make a copy of would be the SSIS packages that courion (or someone) has set up. That would consist of simply logging into your SQL server server, not the management console, or anything, just a straight remote desktop, and making a copy of the packages. If you have some sort of versioning system, you are lucky. I would still do it anyway. If you are not familiar with SSIS, I would recommend you take a class in it.  Not SQL Server. (well, if you can talk your boss into it, you should take a course in both SQL server AND SSIS. However, learning things like Merge Replication won’t help you so much with Courion)

The scenario is this… We have several similar systems (same target type) with different approvers. So, a user could fill out a request for access to system1, system2, and system3. BUT, in 7.80, there is no granularity in the request. If the approver for system3 logs in, and approves the request, then the user by default would be granted access to system1 and system2.

Making a separate workflow for each system involves too much overhead and maintenance.  The best option would be to only allow the user to request one system at a time, and handle figuring out who the approver is as a separate issue. The question is how to restrict the user to a single choice.

The answer is to make a copy of the *.xslt file, and replace “checkbox” with “radio” so that the user is presented with a Radio Button interface. I am using – not the default *.xslt, but instead the BuildIdentityMapCtrl.xslt.

Here are lines 246 thru 250 – yours may differ….

<xsl:template name=”BuildSortTableRows”>
<!– output the addline –>
<xsl:text disable-output-escaping=”yes”>tSORTTABLE_</xsl:text>
<xsl:value-of select=”../../../../name”/>
<xsl:text disable-output-escaping=”yes”>.AddLine(“&lt;center&gt;&lt;INPUT TYPE=‘radio’ NAME='</xsl:text>