• State Verified Answer
  • Locked Locked
  • Replies 33 replies
  • Subscribers 94 subscribers
  • Views 35908 views
  • Users 0 members are here
Options
Related
This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Designer - SQL Database Import not Updating UNSAccountB

dshieldschk
over 10 years ago

Gurus,

I eluded to this question in a previous post and despite my best efforts, I can't find a solution yet. So here is the situation, one of the applications we are attesting to runs off an MS SQL Database and our DBAs have basically given the Quest account rights to run a few stored processes so that it can synch the users/groups from the SQL application to Quest. The script basically opens a connection string to the database, runs the stored procs and uses the data to populate users (one script) and populate data base roles (other script) into UNSAccountB and UNSGroupB respectively while tagging XProxyContext to be the application name... lets call it DBApp1 for privacy.

In the Production environment, the sccripts work just fine... they pull the data in and populate accordingly. I used the Database Transport tool to import all Change Labels into the Test lab. After verifying that AD synch was working Dev and then making sure that Manager > Unified Namespace has an entry for DBApp1, I ran the import. I watched the job logs intently - refreshing every few seconds, just to make sure everything worked. Yay! The script completed and I got NO errors. I even have the script writing a log file to show me what it does. The log file shows several new inserts into the database. Yay!

THEN... I checked UNSAccountB and UNSGroupB. NOTHING is there... There are no records at all...

I tried the same process again with DBApp2. Again, all things look great! Imports run and NOTHING in UNSAccountB or UNSGroupB. What the heck happened? Where did this stuff go? If the logs think everything is fine and the database is sending data (I verified this). Why doesn't it populate into Quest? Can anyone provide me some insight?

Thanks in advance!

  • 0 over 10 years ago

    Okay, I didn't have time to sanitize the entire log from the user import but here is a sample of what came back from the logs:

    User Import

    Got referenced uid person as: b95e5f12-71f8-48e6-9995-d71f733b9e2d
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)              
    Got referenced uid person as: f2c4a068-6a84-43bd-8ba3-80667816bfa6
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)            
    Got referenced uid person as: d9fc301c-71e0-4752-9030-66fbb3659ebf
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)               
    Got referenced uid person as: d97aa60a-bf0e-43cf-af99-17d7deca37cf
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)          
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)              
    Got referenced uid person as: c891f10c-2205-4911-8720-97756a808c60
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)            
    Got referenced uid person as: 83bdc739-f4be-4fae-9e19-57e06a2ab751
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)               
    Got referenced uid person as: 21397908-e328-4376-8f27-d89652bcfa98
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)             
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)               
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)            
    Got referenced uid person as: 1ad07fc3-e051-4f8c-b4cb-3286f7b23f09
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)                
    Got referenced uid person as: 22561689-83b3-4b9f-b1f4-321a26199e14
    Debug: UID_UNSContainerB is: 
    Creating user (Removed)           
    Got referenced uid person as: a3d8f3fe-b457-4a9f-9a5c-58bea58774b2
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)             
    Got referenced uid person as: 076f13a0-9475-4a46-9643-ab487c152ace
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)              
    Got referenced uid person as: 2e5f256c-7cc5-4cd2-9f23-fd18428d1298
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)              
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)           
    Got referenced uid person as: 516069b6-0460-4b40-b1f1-81bbe8b293a6
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)             
    Got referenced uid person as: 01b92a30-4d51-44c6-b75d-bdaabfb3d824
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)              
    Got referenced uid person as: defa56cc-dd6a-47b9-9ba3-26be7e247793
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)              
    Got referenced uid person as: 5d413e0d-706e-476f-afcb-70d258aa7083
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)            
    Got referenced uid person as: 3a41a88a-3a3e-47d8-ad38-2234ca1870b4
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)           
    Got referenced uid person as: 381ab7bc-1ff4-4ea1-9345-8a29d782e633
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)            
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)          
    Skipping person association for (REMOVED)          
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)      
    Skipping person association for (REMOVED)       
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)          
    Skipping person association for (REMOVED)          
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)       
    Skipping person association for (REMOVED)       
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)          
    Skipping person association for (REMOVED)          
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)       
    Skipping person association for (REMOVED)       
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)              
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)        
    Got referenced uid person as: 33d84bc0-b6d0-4d01-a568-1f6e62372f0e
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)             
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)             
    Got referenced uid person as: 8a237379-515b-4580-ba3f-385180426c59
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)            
    Got referenced uid person as: aab7a8ee-972c-44c5-a8c2-9eb0957a468b
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)             
    Got referenced uid person as: 07c4b77f-28d3-46a5-9c83-f9d7dd69b699
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)            
    Got referenced uid person as: 960f2a85-5dd0-4c67-8243-b6941861d006
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)              
    Got referenced uid person as: 952b2aef-7310-452e-9f9b-3064304c1dd4
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)              
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)           
    Got referenced uid person as: ac8ea4c5-1054-4a7b-9fd0-0124c3c23f05
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)           
    Got referenced uid person as: 4087e3b8-a52b-4b38-9921-ece6739b2494
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)             
    Got referenced uid person as: 3e974d45-6178-45a2-9b52-d41bce65d2a3
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)           
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)              
    Got referenced uid person as: 009d58a8-07fe-4e74-bcbb-8f7f22fa95d4
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)           
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)             
    Got referenced uid person as: 704a6972-19a0-46e1-9162-d7cbb6bc4e6d
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)            
    Got referenced uid person as: de9c8286-9026-461b-be57-7732d23d222c
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)           
    Got referenced uid person as: 90d324e6-0637-4026-9eb2-51082de1fca1
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)              
    Got referenced uid person as: 7bc21107-fbfc-4245-941a-3f55630dcb32
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)            
    Got referenced uid person as: a5f31343-c467-43bc-a9ac-9514f1b5d0a0
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)              
    Got referenced uid person as: 
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)           
    Got referenced uid person as: 3cc95665-6744-4fd4-a978-96cb5c4488d1
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)             
    Got referenced uid person as: 44e1cef5-45cc-4cea-b909-6a89652bfe4d
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)            
    Got referenced uid person as: f4fc527f-b068-4c15-bfcd-cb37194138ec
    Debug: UID_UNSContainerB is: 
    Creating user (REMOVED)

    This is a sampling of what II got from the Group Import:

    Got 0 existing UNSGroupB records
    Debug: UID_UNSContainerB is: 
    INSERT on 1000012
    Debug: UID_UNSContainerB is: 
    INSERT on 1002026
    Debug: UID_UNSContainerB is: 
    INSERT on 1002027
    Debug: UID_UNSContainerB is: 
    INSERT on 1002028
    Debug: UID_UNSContainerB is: 
    INSERT on 1002029
    Debug: UID_UNSContainerB is: 
    INSERT on 1002030
    Debug: UID_UNSContainerB is: 
    INSERT on 1002031
    Debug: UID_UNSContainerB is: 
    INSERT on 1002032
    Debug: UID_UNSContainerB is: 
    INSERT on 1002033
    Debug: UID_UNSContainerB is: 
    INSERT on 1002034
    Debug: UID_UNSContainerB is: 
    INSERT on 1002035
    Debug: UID_UNSContainerB is: 
    INSERT on 1002036
    Debug: UID_UNSContainerB is: 
    INSERT on 1002037
    Debug: UID_UNSContainerB is: 
    INSERT on 1002038
    Debug: UID_UNSContainerB is: 
    INSERT on 1002039
    Debug: UID_UNSContainerB is: 
    INSERT on 1002040
    Debug: UID_UNSContainerB is: 
    INSERT on 1002041
  • 0 over 10 years ago

    Got referenced uid person as: f4fc527f-b068-4c15-bfcd-cb37194138ec

    Debug: UID_UNSContainerB is:

    Creating user (REMOVED)

    Debug: UID_UNSContainerB is:

    INSERT on 1000012

    Ok, so as we do see in the log file, the UID_UNSContainerB can not be retrieved by the import script. Without the UID_UNSContainerB attribute being set, there's not UNSAccountB and no UNSGroupB.

    Could you also post the result of the following sql query from both of your environments (the working and the none-working):

    Select UID_UNSContainerB, CN, DistinguishedName, CanonicalName from UNSContainerB where XProxyContext = '<your XProxyContext>'

    Cheers

    Carsten

  • 0 over 10 years ago

    Okay Carsten, I cannot post the entire results of this SQL Query, there is too much data to santitize. Here is a sample:

    Working DB
    UID_UNSContainerBCNDistinguishedNameCanonicalName
    05318a40-fea0-420a-ac21-b0dec60f1f9eGeneric Role NameOU=Generic Role Name,OU=Groups,OU=DBApp2DBApp2/Groups/Generic Role Name

    NOT Working DB
    UID_UNSContainerBCNDistinguishedNameCanonicalName
    00717378-0c4a-4849-92d7-1a45db83a106Generic Role NameOU=Generic Role Name,OU=DBApp2DBApp2/Generic Role Name

    So what is obvious is that the "Group" part is not being added in the NON Working DB

  • 0 over 10 years ago

    And this is the reason why your import gets stuck here. So you need to find out what happened to the groups OU and i'm pretty sure that the same does appy to the users OU.

    And based upon your scripts, i'm also nearly 99.99999% sure that the imports will work after fixing the OU issues in the UNSContainerB table.

    This should give you enough to go the last missing step on your own, shouldn't it?

    Good luck

    Carsten

  • 0 over 10 years ago

    Thanks Carsten, I looked it over with one of the developers on my IAM team and in the Manager on the working DB, there are OUs for Groups and Users for DBApp2 but these DO NOT exist on the non-working environment. Should I be able to basically add those two containers and then re-run the import? Or would these have been created with the script? I can see where those are added on the script but I am not sure if the import also builds things into Manager or if that is a different process entirely.

  • 0 over 10 years ago

    Dave

    That would require a complete rebuild of your import, which would be a bit too much for this from my perspective. But it seems like the script is at least not creating the groups and users OU, so it might be worth a shot to just create those manually and to reorganize the OU hierarchy and then to rerun the import.

    Cheers

    Carsten

  • 0 over 10 years ago

    Okay Cartsten... where does the Manager build its hierarchical view from?

  • 0 over 10 years ago

    It's built based upon the attribute UID_ParentUNSContainerB. So when creating the new UNSContainerB OUs, you'll name the DBApps2 UNSContainerB as the parent for those. The next step will be to adjust the parent container for the existing generic role and generic user OU's to the new created OU's.

  • 0 over 10 years ago

    Well then!  *Bang head on wall* when I created the Groups and Users as OU's in Manager... THEY ALL IMPORT!