SWQL Query returns no results for IPAM.Subnet

rudim1966

Hi All,

We have some powershell scripts running in our environment that connect via the SWIS services. Some time in the last couple of months one of the queries has stopped returning data.

After some testing I have been able to isolate the issue using SWQL Studio to not getting ANY data back from the IPAM.Subnets tables.

Even a generic Query returns no results - I have tried with the distinct keyword for the same result.

SELECT SubnetId, ParentId, AddressFROM IPAM.Subnet

Obviously the query we are trying to run also returns no data from the subnets table...

SELECT na.IPNodeId, na.IPNode.IPAddress, na.IPNode.Status, na.IPNode.IPOrdinal, na.IPNode.SubnetId, na.IPNode.subnet.VLAN, na.IPNode.Subnet.FriendlyName, na.IPNode.Subnet.Address, na.IPNode.Subnet.AddressMask, na.IPNode.Subnet.CIDRFROM IPAM.IPNodeAttr na

Or if we try it with more conventional queries...

SELECT na.IPNodeId, n.IPAddress, n.Status, n.IPOrdinal, n.SubnetId, n.subnet.VLAN, s.FriendlyName, s.Address, s.AddressMask, s.CIDRFROM IPAM.IPNodeAttr na INNER JOIN IPAM.IPNode n on na.IPNodeId = n.IPNodeId LEFT OUTER JOIN IPAM.Subnet s on n.SubnetId = s.SubnetId

Our environment is running IPAM 4.7 connecting to remote SQL server. This was upgraded over 8 months ago, but I can't confirm when the query stopped working...

Your suggestions / comments appreciated in advance.

Thanks

Rudi

Find more posts tagged with

orion_sdk

swis

ipamv4.7

ipam orionsdk

API

orion sdk

Accepted answers

tdanner

Could there have been any changes to the permissions of the account used to connect? Does that same account see data normally in the IPAM web interface?

All comments

tdanner

Could there have been any changes to the permissions of the account used to connect? Does that same account see data normally in the IPAM web interface?

rudim1966

Hi,

After a lot of stuffing around we were able to isolate the issue... it was permissions based. the account was connecting as a local user, rather than a domain\user.

This is part of what created the confusion, because as a local user some tables were still visible...

Results, connecting as user...

Results after connecting as domain\user

Thanks for the assist

Rudi

rudim1966

Just an afterthought for some clarification:

I just checked the IPAM server and there is NO local user configured on this server or in IPAM with the svc account name used to connect via the SDK. the account only exists on the domain...

So is there some default permissions that exist for guest users? or is there an patial authentication that is occurring somehwere

Thanks All,

Rudi

tdanner

What's happening is that SWIS is (correctly) authenticating "user" as "domain\user" and granting access accordingly. However, it is passing the user context to other permission checkers (IPAM has a custom permission checking plugin to apply its own hierarchical permissions model) as the client (SWQL Studio in your screenshots) provided it rather than the canonical form. Since IPAM has no record of permissions for "user" (only "domain\user") IPAM treats "user" as having no access.

This is no good. I'll open a bug.