Issue retrieving CSV from MID Server and attaching it to a record

[gjz]

I have a requirement to retrieve a file from the MID Server and attach it to a Record in a Flow.  I'm really struggling with retrieving the file from the MID Server.  For various reasons, Import is not an option.  I've seen a few posts and videos on how to do this, and I think I'm stuck on the authentication step.  I think the issue is the REST authentication.

I've been following Oscar Lopez's post (https://www.servicenow.com/community/itom-articles/automated-data-import-using-mid-server-and-powers...), the ServiceNow KB (https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0817437) and Ivan Betev's video (https://www.youtube.com/watch?v=sjqutAvjXhY)

Both Oscar and Ivan are using the PowerShell script generated by the REST API Explorer using Attachment API.  My issue is the first lines of code: 

$user = "user"
$pass = "pass"

# Build auth header
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user, $pass)))

We would never enter a password into code and instead use this method to read an encrypted credential file in other PowerShell scripts that are not ServiceNow PowerShell scripts:

$encPassword = Get-Content "path to the credential file is entered here" | ConvertTo-SecureString
$encCredentials = New-Object System.Management.Automation.PsCredential("this has the userid",$encPassword)
$Session = New-PSSession -ComputerName "this is the name of the MID Server" -Authentication Kerberos -Credential $encCredentials

Import-PSSession $Session

I tried changing the REST generated code to 

$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f "userid is entered here", $encPassword)))

after retrieving the encrypted credential file, but I'm getting an authorization error.  Considering the MID Server account created the file on the MID Server, I don't see why it would have an authentication issue retrieving the file.

I really don't have any experience with REST APIs, and I'm getting comfortable with PowerShell script, I've written several now.

At a high level, this is what I'm trying to accomplish in the Flow:

1. Check Active Directory for users who have not logged in for a specific period of time.  This list of users are put into a CSV file on the MID Server.  I created an action to run a PowerShell script that reads AD, then creates the CSV file on the MID Server.  This is working fine.

2. Submit a catalog item request for various teams to process the list.

3. Get the CSV from the MID Server - this is where I'm stuck and I will use a custom action once I get the PowerShell script working.

4. Attach the retrieved file to the submitted catalog item request.

Is there anyone who can help me out?  I'm at a loss.

Thanks in advance!

[Its_Azar]

Hi there @gjz 

i think that $encPassword is a SecureString, but the REST auth header needs plain text. You’ll need to convert it before building the header, e.g.:

$plainPass = [System.Net.NetworkCredential]::new("", $encPassword).Password
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user,$plainPass)))

also, avoid hardcoding and use the ServiceNow credential store with a MID alias, its better Then just call the Attachment API from your MID script to POST the CSV into the catalog request. 

Hope this helps

[gjz]

Thanks Its_Azar - my preference would be to use the ServiceNow credential store - do you know how I can code that in the script?

[bgmoreira]

For my issue , i resolved with this

In my case when a create a new user to user in this powershell command my default password is so long (+- 128 caracters). I changed to a short password (16 caracteres) and the 401 problem is resolved.

importante

I put this roles to user

import_transformer

import_set_loader

import_scheduler

import_admin

and flag the option "Internal Integration User" in user register