SyncEditor doesn't show all columns of view, both SSMS and Data Importer do

HI all, 

I was wondering if anyone could help provide some insight as to what might be going on here. 

I am connecting to a new MSSQL view for my current project. When connecting to the view in SyncEditor I am given only a limited list of the columns. However, when looking at the same view as the same account either in SSMS or in OneIdentity Data Importer I can access all of the columns. 

while this means I can accomplish my task with the data importer I would prefer to use the sync editor if I can get this solved. 

Thanks, 

-Aaron 

Parents Reply Children
  • Hi Markus, 

    I paused on the UID screen for the screenshot but the column limitation exist if I finish the connection and browse the system. The SQL view is also below.

  • Honestly, the view definition looks weird on the left-hand side where the tree is shown. The columns are missing the complete data type display and I assume that's the reason the database connector is hiding them because he cannot infer the proper CRUD code.

    Can you please try to generate the view code with SSMS like shown in my screen. It is easier to identify the issue then.

  • USE [ECD]
    GO

    /****** Object: View [dbo].[vwOIMEZCapUser] Script Date: 6/30/2020 4:58:10 AM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE VIEW [dbo].[vwOIMEZCapUser]
    AS
    /*********************************************************************************
    Purpose: Provide user info to One Identity
    Use ALTER VIEW instead of CREATE VIEW to retain triggers on the view

    Revisions:
    DATE AUTHOR DESCRIPTION
    06/02/2020 WKuo Create
    *********************************************************************************/

    SELECT u.[Users_Name]
    ,u.[Users_ID]
    ,u.[First_Name]
    ,u.[Middle_Name]
    ,u.[Last_Name]
    ,r.[Role_Name]
    ,u.[EMail]
    ,u.[User_Description]
    --,u.[User_Type]
    ,u.[Department]
    ,u.[AccountActive]
    ,u.[Account_Disabled]
    ,u.[Termination_Date]
    ,u.[Disabled_Date]
    ,u.[Expiration_Date]
    ,u.[CreatedBy]
    ,u.[CreatedDate]
    ,u.[LastChangeBy]
    ,u.[LastChangeDate]
    ,u.[Report_User]
    FROM [ECD].[dbo].[Users] u
    LEFT OUTER JOIN [ECD].[dbo].[Roles] r
    ON u.Roles_ID = r.Role_ID

    GO

  • Okay my mistake, this doesn't help at all. My assumption is still, that the schema definition of the missing columns is somewhat weird and might confuse the MSSQL Connector.

    What SQL Server version is it you are connecting to? What database compatibility level?

    Can you check the column scheme of one of the missing columns at the originating table? For example ECD.dbo.Users.Users_ID or ECD.dbo.Users.Department?

    Or, you contact support to help you out.

  • HI Markus, 

    That view references the users table and when I try to script out that table I am presented with an error  "DataType not available or permissions" 

    One again your extreme knowledge of the product has shown me another path to walk down. 

    Thank you. I might be back with more info but this seems to be all within the data source and not an issue with OneIdentity. 

    -Aaron

  • HI Markus, 

    Taking this one step further we were able to get access to thsoe tables reviewed and now I am able to see our data types. 

    We still have the missing columns issue but now there might be a reason for it.  All of our missing columns are of a non-standard data type. (see screenshot).  I have not encountered this situation before. Is this a dead end for the sync editor? Or is there a way that I can populate the required info?

    -Aaron

  • If the native MSSQL Connector is unable to infer the data type of these columns you have two options.

    Option 1: In the view convert the data type Flag(bit) to a boolean.

    Option 2: Use the generic ADO.NET connector, because you want to read the information anyways.

  • Thanks again for all your help, Markus.  I appreciate it.