Users created by PowerShell gets ‘ ’

Hello,

Thank you for contacting us on that matter!

Would it be possible for you to provide me a screenshot of your File -> Data Source window? Usually, that kind of issue is caused by the fact either the database name or the username is misspelled.

Best regards,

To clarify:

If the user is crated whit PowerShell script that connect by database user.

Then when the user tris to connect, it gets Login failed for user ‘<token-identified principal>’

If I then in my RDM that is connected by Active Directory Interactive as the user and inn
RDM User and Security Manager and change the user type to something else and back.

Then the user can log inn without doing any changes on the client.

Hello,

It may be possible for us to reproduce this internally.

Is it possible for you to provide us your user creation script please?

Best regards,

UserName = $GroupeMember.userPrincipalName
$user = new-rdmuser -Login $UserName -AuthentificationType "AzureAD" -SkipCreateSQLServerLogin -SetUser
$user.FirstName = $GroupeMember.givenName
$user.LastName = $GroupeMember.LastName
$User.Email = $GroupeMember.Mail
$User.UserType = 'ReadOnly'
Set-RDMUserSecurity -user $User -CanImport $false -CanExport $false
Set-RDMUser $user
Update-RDMUI

Add-RDMRoleToUser -RoleName 'Group_A' -UserName $UserName

Hello,

After some testing on our end, we have discovered a bug with the New-RDMUser. I have gone ahead and opened a ticket with the engineer working on the Module.
I will keep you posted once I get news regarding a fix.

Best regards,
Eric

Hi,

After investigation, turns out this scenario isn't supported.
Creating a Azure AD user while connected on a database user (unfortunately the only supported way to connect to AzureSQL from PowerShell) should not be permitted. It is not in RDM and as such, I've added the validation and appropriate error message in the PowerShell module.

Regards

Hi,

Is it the use GUID from Azure AD you need to make this to work? If so cant you add is as an parameter to the new-rdmuser.

The script that creates these users has access to Azure AD so if you need some other parameters from azure is should not be a problem.

I really hope this gets fixed because I'll create 600 users in March.

Hi,

The restriction is creating AD user from a data source using a database user.
If you connect to AzureSQL using the same data source used to connect with PowerShell, creating an AD user wouldn't work either.
Right now, since the PS module needs to be compatible with PS5.1 and PS7.2, I'm limited in what I can do. Target both versions unfortunately locks me in a restricted version of .NET.
That being said, I know creating 600 users by hand in RDM wouldn't be optimal and is a good candidate for a PowerShell script. So I'll raise the issue internally and see what we can do about it.

Regards

But the user is created by the script, but you just have to open RDM and make a small change to the user to get it to works.

What change is needed to get the created user to work?
With that information, I could try to challenge the restriction in RDM (and by extension in PowerShell)

I change the User type to User and then back to Read-only user that we use now.

On my end, whenever I create a AD user using a db user, I get this error message
"A severe error occurred on the current command. The results, if any, should be discarded."

Changing the user type to User and back to Read-Only doesn't fix the error.
That leads me to believe the way AzureSQL is setup might play a role weather this works or not.
I'll have to dig deeper before we settle on anything.

Here's where we are currently standing.

Since the move to PowerShell Core, we've lost the ability to connect to connect to AzureSQL using an AzureAD account.
As far I as know, there's no other way to do it.
PowerShell will now restrict the creation of AD user when connected with a database user.
However, since you said you were able to fix these new users in RDM (most probably by connecting to AzureSQL with an AzureAD account), I've added a new switch parameter to bypass the validation. Adding -BypassAzureADValidationEvenIfUnsupported to the New-RDMUser command will let you add an AzureAD user even if you are connected with a database user.

A release (2022.1.0.6) with this fix is on its way.

Hi
Can you send me the SQL commands to create a user and set the correct rights?

This is what is missing when creating the user with the RemoteDesktopManager powershell module.

 I think it is what the “hammer” in User and Security Management dos.

Hi,

From what I've been told, the only reason the hammer fixes your AzureAD user is that fact that you are connected to the data source using an AzureAD user.
Like I said before, database users aren't able to created fully functional AD users.
If you connect RDM to the data source with a database user, are you still able to use the hammer to fix users created using PowerShell?

I am connected direct to the database with a AzureAD user; I create the user but I need to know what rights
I am going to give the user?

@acetico

Here are the SQL Statements that would need to be executed to mimic the Fix SQL Login (hammer) in RDM.

These would need to be executed via the SQL server PS module while connected with an Azure AD user.

Admin User

CREATE USER [_admin_user_@domain.com] FROM EXTERNAL PROVIDER;

ALTER ROLE [db_owner] ADD MEMBER [_admin_user_@domain.com];
GRANT ALTER ANY USER TO [_admin_user_@domain.com] WITH GRANT OPTION;
GRANT CONNECT, DELETE, INSERT, SELECT, UPDATE TO [_admin_user_@domain.com];

Regular User

CREATE USER [_user_@domain.com] FROM EXTERNAL PROVIDER;

GRANT CONNECT, DELETE, INSERT, SELECT, UPDATE TO [_user_@domain.com];
DENY DELETE, INSERT, UPDATE ON [dbo].[DatabaseInfo] TO [_user_@domain.com];
GRANT UPDATE ON [dbo].[DatabaseInfo] ([ConnectionCacheID], [IntelligentCacheID]) TO [_user_@domain.com];
DENY DELETE, INSERT, UPDATE ON [dbo].[GroupInfo] TO [_user_@domain.com];
DENY DELETE, INSERT, UPDATE ON [dbo].[UserGroupInfo] TO [_user_@domain.com];
DENY DELETE, INSERT, UPDATE ON [dbo].[UserSecurity] TO [_user_@domain.com];

Best regards,

Thank you for the always great service.