Querying the database views

Topics: General Topic, SQL Database Schema
Aug 1, 2012 at 12:43 AM

Hi,

first of all let me tell you that your product is excellent. Thank you a lot!

I'm working with 3.6.0.8 version. I'm querying the database views in order to get some general reports of NetSqlAzMan configuration.

This is my query:

select AV.ApplicationId, AV.ApplicationName, AV.ApplicationDescription, 
IT.ItemId AS roleId, IT.Name AS roleName, IT.Description,  
AU.ItemId AS autItemId, AU.Name AS autItemName, 
AU.objectSid, AU.AuthorizationType, 
AGM.ApplicationGroupId, AGM.Name AS memberName        
from netsqlazman_ApplicationsView AV        
	inner join netsqlazman_ItemsTable IT on AV.ApplicationId=IT.ApplicationId        
	left join netsqlazman_AuthorizationView AU on IT.ItemId=AU.ItemId        
	left join netsqlazman_ApplicationGroupsTable AGT on AU.objectSid=AGT.objectSid        
	left join netsqlazman_ApplicationGroupMembersView AGM on AGT.ApplicationGroupId=AGM.ApplicationGroupId        
where IT.ItemType=0

It works great but for some user accounts the memberName column is not translated well. In the Manager Console all the values are displayed OK and the authorization mechanism is working fine. The domain name of stored users are not the same of the database server and there exists multiple domains.

Do you have any idea of this problem?

Do you recommend another approach to translate the AGM.Name column value?

Thanks in advance.

Aug 1, 2012 at 3:08 AM

I don't have enough information about your problem but here is my guess:

The account running SQL Server instance must be trusted to access the domain where the Group member user resides. If not, you will never be able to translate the SID into a name. Two ways to fix this:

  1. Setup the right trust relationship between the domains.
  2. Get back the SIDs and translate under a process ID which is in the same domain of your account, because you just told me your account having no problem to translate them. 

Richard

Aug 1, 2012 at 12:56 PM

Check also if the ADSI provider is fine (under SQL Server, Management, Linked Servers).

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com

Da: tangrl [email removed]
Inviato: mercoledì 1 agosto 2012 04:09
A: Andrea Ferendeles
Oggetto: Re: Querying the database views [netsqlazman:389800]

From: tangrl

I don't have enough information about your problem but here is my guess:

The account running SQL Server instance must be trusted to access the domain where the Group member user resides. If not, you will never be able to translate the SID into a name. Two ways to fix this:

1. Setup the right trust relationship between the domains.

2. Get back the SIDs and translate under a process ID which is in the same domain of your account, because you just told me your account having no problem to translate them.

Richard

Aug 2, 2012 at 12:19 AM

Hi, thanks to both for the response.

I understand the problem and I understand why the Manager Console displays the usernames correctly. Basically it runs in the same domain that the users accounts and it translates each user sid. How can I translate the user sid at client level? I'm thinking return the objectSid and translate it in the client side.

Thanks in advance.

Aug 2, 2012 at 7:33 AM

Using T-SQL:

SELECT SUSER_SNAME(0x010500000000000515000000a065cf7e784b9b5fe77c87705a2e0000);

GO

Using .NET

string GetNameFromSID( SecurityIdentifier sid )
{
NTAccount ntAccount = (NTAccount)sid.Translate( typeof( NTAccount ) );
return ntAccount.ToString();
}

__________________________________
Andrea Ferendeles
NetSqlAzMan Project Coordinator
E-mail aferende@hotmail.com Web http://netsqlazman.codeplex.com

Da: gq2012 [email removed]
Inviato: giovedì 2 agosto 2012 01:19
A: Andrea Ferendeles
Oggetto: Re: Querying the database views [netsqlazman:389800]

From: gq2012

Hi, thanks to both for the response.

I understand the problem and I understand why the Manager Console displays the usernames correctly. Basically it runs in the same domain that the users accounts and it translates each user sid. How can I translate the user sid at client level? I'm thinking return the objectSid and translate it in the client side.

Thanks in advance.