SAS 94 Management Console - Cant add Login. Userid already in use by another user or group

[Acf2]

Some time ago, I created a service account user in metadata without a userid.  I am trying to add a domain account domain\userid to DefaultAuth like a regular user but getting this message:
'Cant add Login. Userid already in use by another user or group'

There are no results from a search for the userid so I cannot see where it is embedded. We only have 34 human users and 2 groups and I am stuck.

There is a good chance that the userid was used in our SAS94_M6 metadata but there were no related issues during the M8 upgrade.

It has been a few years since I last had to deal with Metadata issues but I am certain something has changed in our 9.4_M8 Management Console. Was there a Type 'Login' in the search tab ?

Should I dust off my older proc metadata XML queries for user management? Any ideas ?

[MarcoGhiglieri]

I'd try a programmatic approach, i.e. to extract all the logins to check in which group/user it's in.

I'm huge fan of Metacoda/ @PaulHomes work: %metacodaIdentityLoginExtract - Metacoda Identity Sync Utilities is a great tool code I'm always gratefull he shared.

It happened I had to do it manually, but in very simple installations.... 

[PaulHomes]

Thanks for the mention @MarcoGhiglieri It's nice to hear you've found that code useful.

[MarcoGhiglieri]

Saved me hours of work and buckets of tears. Thanks again for sharing!

[Nigel_Pain · Accepted solution]

I'm not on M8 (yet...) but in SMC for us the Advanced button in the identity search allows a search on user ID. If this doesn't work try:

data _null_;
length loginuri personuri name $256;
nobj=metadata_getnobj("omsobj:login?@userid = 'domain\userid'",1,loginuri);
rc = metadata_getnasn(loginuri,"AssociatedIdentity",1,personuri);
rc = metadata_getattr(personuri,"Name",name);
put _all_;
run;

[Acf2]

Got it. Thanks Nigel.

I had forgotten that the userid was added to 'SAS General Servers' group so that Stored Processes could access SQL Server using ODBC.

Our site is all Windows Server 2019 with a lot of integrated authentication that also works with Azure. Any process running as an AD domain user will authenticate without userid or password. Great in most cases but tricky for service accounts used by regular users. Connection to SQL Server 2019 with userid/password is actually blocked. For testing, I use runas a lot.

However, I am very disappointed that the Search and Advanced Search could not find the userid. I tried with and without '\'

Thanks to everyone who responded so promptly.

[Nigel_Pain]

That's a bit surprising with the advanced search. My initial thought was that it might have been an upper/lower case issue but it seems to be case-insensitive on our system. The other thing I've used to good effect, not having access to the Metacoda plugins, is the metadata browser in a desktop SAS session (Solutions>Accessories). It's a good way of seeing the metadata structure.

I note your comments about using IWA and connections to SQL Server being blocked. I seem to remember that the issue here is that authentication isn't propagated through to the database. It's one reason why we don't use IWA. We are also on Windows Server 2019 and have many connections to database servers. We're able to use the DefaultAuth authdomain for the SQL Server ones but because we aren't using IWA the authentication is done for each connection.

[MarcoGhiglieri]

I feel it, I've found a number of customers installs with logins in General Servers.
I tend to move them in dedicated selfnamed groups, then add the SAS General Servers as a member.

[Acf2]

@MarcoGhiglieri Adding members to 'SAS General Servers' is a good suggestion.

Sadly, my users have little interest in using SAS Stored Processes even though they are heavy Excel users with the SAS addin enabled after some effort on my part.....

I think I will move the domain service account to a SAS Batch account used for writing to Prod SQL Server tables. 

On another note, I really miss attending SGF and meeting other SAS admins. Online is not the same....