MSSQL application query to fetch the account details

IdentityIQ (IIQ) IIQ Discussion and Questions
, , , , , , ,
1

Which IIQ version are you inquiring about?

Version 8.X

Please share any other relevant files that may be required (for example, logs).

Sailpoint_MSSQL_Link_Account.PNG
Sailpoint_MSSQL_Link_Account.PNG1750×347 19.8 KB

Share all details related to your problem, including any error messages you may have received.

I am trying to fetch the details of link accounts which are present in MSSQL type application by writing a query. Below is the query

SELECT sp.name AS login,
sp.type_desc AS login_type,
sl.password_hash,
sp.create_date,
sp.modify_date,
CASE WHEN sp.is_disabled = 1 THEN ‘Disabled’
ELSE ‘Enabled’ END AS status,
STRING_AGG(r.name, ‘,’) AS Roles,
m.name AS Principal
FROM sys.server_principals sp
LEFT JOIN sys.sql_logins sl
ON sp.principal_id = sl.principal_id
LEFT JOIN master.sys.server_role_members rm
ON sp.principal_id = rm.member_principal_id
LEFT JOIN master.sys.server_principals r
ON r.principal_id = rm.role_principal_id AND r.type = ‘R’
LEFT JOIN master.sys.server_principals m
ON m.principal_id = rm.member_principal_id
WHERE sp.type NOT IN (‘G’, ‘R’)
GROUP BY sp.name, sp.type_desc, sl.password_hash, sp.create_date, sp.modify_date, sp.is_disabled, m.name;

By using this query I am able to get the roles for some of the users , but not for all the users. Attaching screenshot

If we see it is not fecthing details for " ##MS_PolicySigningCertificate##" this account has role i.e.“PolicyAdministratorRole@msdb”

How to fecth all the roles for the particular to the account ?

3

Queryresult
Queryresult1093×218 15.2 KB

Sharing query result

4

Hi @SDM007,
Just trying to understand your query here
Have you onboarded MSSQL application as a target and you have accounts here
You want to get the roles present in each of the account ?

5

I have onboarded MSSQL application and accounts are there.
Yes, I want all the related data for each of the account including roles.

6

Hi @SDM007,
You can get the user account attributes report.
Please refer identityiq reports guide.

If you are not getting all attributes then there are 2 options

  1. get the sql query by joining identity and link objects to get the all attributes for this mssql application
    Or
  2. create a custom report with query options by joining identity and link objects for this application and get all the attributes present in the account
7

I’m not sure this is an IIQ problem.

Do your MSSQL tables have row-level security set up? Does the account you’re using to query have permission to see that data?

8

Please recheck your query, you might be putting some filters in the query which is filtering out the result.
Also, when you are running this query on MSSQL client, Are you getting the desired result? If not, get in touch with the MSSQL application team to share the appropriate query with you, as this is the responsibility of Application Team to supply all the necessary data. We are mainly looking into the configuration and customization associated with Sailpoint IIQ.
Thank you!

9

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.