SID for a user who has no Navision Login

AK

Hi everybody,

for automatically creating user logins in several NAV 2009 CC databases I need the SID for the user, which I get from table 2000000050 "Windows Object". But this takes one or two minutes per user due to our large and possibly faulty Active Directory. Is there a faster way to get the SID for a user that has no login in Navision?
I don't mind connecting to Active Directory Services or whatever is needed, as I'm already reading the AD Groups of the user.

Thank you!

NavNab

Hi,

Did you checked the function SID?

AK

This works only for users who already have a windows login. But thanks for your advice.

NavNab

it works for me on NAV 2016. I didn't try on 2009.
Maybe you can execute "cmd /C wmic useraccount where (name=''%1'' and domain =''%2'') get sid", save the result in a temporary text file and parse it to get your SID? You can also save all SIDs and load them in NAV?

Have a look at this function:

    PROCEDURE GetUserSID@1000000001(_UserID@1000000000 : Code[30];_Domain@1000000005 : Code[30]);
    VAR
      WshShell@1000000003 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{72C24DD5-D70A-438B-8A42-98424B88AFB8}:'Windows Script Host Object Model'.WshShell";
      Style@1000000002 : Integer;
      WaitOnRun@1000000001 : Boolean;
      CommandeLine@1000000004 : TextConst 'ENU="cmd /C wmic useraccount where (name=''%1'' and domain =''%2'') get sid > C:\TEMP\SID.txt"';
    BEGIN
      Style := 1;
      WaitOnRun := FALSE;
      CREATE(WshShell);
      WshShell.Run(STRSUBSTNO(CommandeLine, _UserID, _Domain), Style, WaitOnRun);
      CLEAR(WshShell);
    END;

AK

Thanks! Although I didn't get this to work, it pushed me into the right direction. For anyone with a similar problem:

MS SQL Server has a function SUSER_SID, that retrieves the SID in binary format. I stole
sqlservercentral.com/scripts/SID/62274/
to convert this to the string SID Navision uses. The code looks like this:

GetSIDfromSQL(user : Text[100]) UserSID : Text[100]

Name	DataType	Subtype	Length
ADOConnection	Automation	'Microsoft ActiveX Data Objects 6.0 Library'.Connection	
ADOCommand	Automation	'Microsoft ActiveX Data Objects 6.0 Library'.Command	
ADORecordset	Automation	'Microsoft ActiveX Data Objects 6.0 Library'.Recordset	
VarConnection	Variant		
chr	Char		

chr := 39;
IF ISCLEAR(ADOConnection) THEN CREATE(ADOConnection);
ADOConnection.ConnectionString :=
                                  'Provider=sqloledb;' +
                                  'Server=049rzserver;' +
                                  'Database=master;' +
                                  'Integrated Security = SSPI';
ADOConnection.Open;
VarConnection := ADOConnection;
IF ISCLEAR(ADOCommand) THEN CREATE(ADOCommand);
ADOCommand.ActiveConnection := VarConnection;
ADOCommand.CommandText := 'select dbo.fn_SIDToString(SUSER_SID(' + FORMAT(chr) + user + FORMAT(chr) + '))  as SID';
ADOCommand.CommandTimeout := 0;
ADOCommand.Execute;
IF ISCLEAR(ADORecordset) THEN
     CREATE(ADORecordset);

ADORecordset.ActiveConnection := VarConnection;
ADORecordset.Open(ADOCommand);

ADORecordset.MoveFirst;
UserSID := FORMAT(ADORecordset.Fields.Item('SID').Value);
ADOConnection.Close;
CLEAR(ADORecordset);
CLEAR(ADOCommand);
CLEAR(ADOConnection);

EXIT(UserSID);

NavNab

That's great. I use the same SQL script for other purpose. But, pay attention, if the user does not exist on SQL you will get blank SID from your function. Also, make the return to Text119 as SID max length is 119

AK

Thank you for that tip, that might indeed have turned into a problem!

NavNab

Hi,

In fact there is a lot more easier solution. If you want to simplify your code or for any other forum menber looking for a solution

There is a virtual table that converts SID to ID and ID to SID: https://msdn.microsoft.com/en-us/library/dd355232.aspx

SIDAccountID.SETCURRENTKEY(ID); // This line is important. Set it to ID if you want to find SID
SIDAccountID.ID := 'DOMAIN\USERNAME';
IF SIDAccountID.FIND THEN
  MESSAGE('%1\%2', SIDAccountID.ID, SIDAccountID.SID);

In my tests, it displays all domain users (no matter if if the user is created on the database or not).

AK

Thanks, this is neat! But instead of

SIDAccountID.ID := 'DOMAIN\USERNAME';

it should be

SIDAccountID.SETRANGE(ID,'DOMAIN\USERNAME';  //you can omit then domain

NavNab

AK wrote: »

SIDAccountID.SETRANGE(ID,'DOMAIN\USERNAME';  //you can omit then domain

This does not work on my machine.

SIDAccountID.ID := 'DOMAIN\USERNAME';

This works fine for me.

The code I suggested is used in standard Codeunit 418 function ValidateUserID (NAV 2009 of course).

Anyway, the goal is to get it work

Slawek_Guzek

There is a Windows Objects virtual table, ID 2000000050, you can read user ID and SID, and even Active Directory user name from it. But it is quite slow if you apply any filters to it.

The best way to approach it is to create buffer table with the same fields, add all necessary keys, declare it as an in-memory variable, and populate in a straight loop at the beginning of your code.

Something like this:

if WindowsObject.findset then
repeat
   TempWindowsObjectBuffer.transferfields(WindowsObject);
   TempWindowsObjectBuffer.insert;
until WindowsObject.next = 0;

Then down the line pick up required values from the in-memory table.

The Windows Objects is not visible in Object Designer but can be used in the code, or as a source table in a form or a report.

Slawek

AK

That might also solve the problem, but Nabil's suggestion works great, so I have no urge to change that.
I would mark this thread a solved, but I haven't found a way to do that...