User Credentials Lookup

Where does Epicor store login information in SQL? What password hashing algorithm(s) do they use?

I want to run a custom report from an internal webpage but first check if the user is able to run the report by checking their credentials against the Epicor database.

I looks like it’s ERP.UserFile and the password field is nvarchar(90) but when I query the table all the passwords are blank except Manager’s password. That’s in there and hashed. But the others are all blank? Where does Epicor really store those?

There’s also an Ice.UserFile that is a duplicate.

Note that for AD authentication, there would be nothing there too. The passwords are salted and hashed. Your best bet to authenticate from a web app is to just log into the site using a REST call (if you’re on 10.1.600+).

Mark W.

1 Like

As Mark said Passwords are salted and hashed you are not going to be able to authenticate against them yourself. Within Epicor there are BO calls to do this, however externally you cant

1 Like

You could, if even not using single sign-on, use the fields to store the domain and user id in Epicor. Assuming your web page already authenticates the user you can then take that username and validate against the domain name and domain user id field to ensure they are listed in Epicor. You could go a step further and also use a quick group membership lookup. The table is ice.SysUserFile. This also would be only if you are not using generic users in AD to authenticate to the web page.

If you ever want to validate password externally, you can ping https://server/share/api/v1/Ice.BO.UserFileSvc/ValidatePassword

It takes the username and password as params. Note this is not ideal from a security approach - hard coded passwords in multiple areas is problematic. https is a must to protect passwords. Stay tuned for more news in this area as well.

Another thing you should investigate is the epicor token server. Get a token with your creds and use that token for authenticating. Standard web bearer token approach.

3 Likes

Definitely wouldn’t hardcode or store the password anywhere, it would just be input from a form on a page that I would test against the Epicor database.

Does anyone know what an example using Bart’s method could look like? I’m not sure how to talk to services but I think this is the kind of thing I need.

I just need to see if the password is valid then I’d let them continue with their report.

I’d be using PHP but if you know what this looks like in .ASP page I could probably translate it to my language.

From PHP you can use the REST Interface

POST: https://YourURL/YourEpicorInstance/api/v1/Ice.BO.UserFileSvc/ValidatePassword
Payload:

{
  "userID": "epicor",
  "password": "epicor"
}

Response:

{
  "returnObj": true
}
1 Like

crap I just noticed you are on 9.05… you’ll have to do the same using WSE / WCF services but from PHP that is Heinous…

Yes, our live is 9.05 but… don’t worry because…

I’m developing against Epicor 10.2.100.7 because we’re in the midst of testing and getting ready to go live later this summer. So do you know any examples I can peek at? I did some research and Stackoverflow doesn’t have much in the way of examples.

1 Like

I just a POST HTTP call with the params I provided. You can use POSTMAN to see what the code would look like in PHP

PHP CURL

<?php

$curl = curl_init();

curl_setopt_array($curl, array(
  CURLOPT_URL => "https://YourServer.YourDomain.com/YourEpicorInstance/api/v1/Ice.BO.UserFileSvc/ValidatePassword",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "POST",
  CURLOPT_POSTFIELDS => "{\r\n  \"userID\": \"epicor\",\r\n  \"password\": \"epicor\"\r\n}",
  CURLOPT_HTTPHEADER => array(
    "Authorization: Basic ZXBpY29yOmVwaWNvcg==",
    "Cache-Control: no-cache",
    "Content-Type: application/json",
    "Postman-Token: e70e4fdb-1269-4747-ad06-66f4742597ba"
  ),
));

$response = curl_exec($curl);
$err = curl_error($curl);

curl_close($curl);

if ($err) {
  echo "cURL Error #:" . $err;
} else {
  echo $response;
}

or using PHP HttpRequest

<?php

$request = new HttpRequest();
$request->setUrl('https://YourServer.YourDomain.com/YourEpicorInstance/api/v1/Ice.BO.UserFileSvc/ValidatePassword');
$request->setMethod(HTTP_METH_POST);

$request->setHeaders(array(
  'Postman-Token' => '6444c111-114f-404f-a7e3-66af2863c925',
  'Cache-Control' => 'no-cache',
  'Authorization' => 'Basic ZXBpY29yOmVwaWNvcg==',
  'Content-Type' => 'application/json'
));

$request->setBody('{
  "userID": "epicor",
  "password": "epicor"
}');

try {
  $response = $request->send();

  echo $response->getBody();
} catch (HttpException $ex) {
  echo $ex;
}
1 Like

Which aspects are you interested in?

Have you looked at the swagger endpoints? The REST docs?

I know kinda conceptually what REST is, but practically not how to use it. I think the examples that Jose posted will get me started.

Read up on the ERP implementation in the Help system. Glance at my original Rest Novel I did coming out of Beta a ways back. Post questions like crazy :stuck_out_tongue:

1 Like

Also take a look at the token service for auth:

Just add the token as a Bearer header and auth done…

1 Like

So I tried to connect to the TRAIN database with manager’s credentials and I’m getting an error in POSTMAN (which is a neat little program).

Are there any user settings in Epicor that have to be set so that I can get a valid response?

And yes, the credentials work if I log into the main Epicor application.

What is an access token? How do I get one? Does there need to be a new one each request?

Bearer tokens? JWT Tokens?

ERP 10 has a Token Service to request a jwt token for use as an authentication token. You can set the key and lifespan on the token service in Admin Console.

How to obtain and use one is documented in the REST help:

Simply as to the header of your server calls instead of username / password.
NOTE - Tokens WILL expire. You need to determine how you wish to handle that.
You can determine how long the token lives for (e.g. 24 hours) and set a client timer to obtain a new one at 23 hours.
Or you can prompt for user to log in again when it expires (Think Office 365 / WIndows Accounts - you go into website and are logged in already (their token is in browser cache or similar). If the token expires, the Windows Account login is popped.

In postman just basic auth with name and password should be enougth

Hello Olga,

In postman for this REST service basic auth worked: https://xxx-epicor10/E10Train/api/v1/Erp.BO.SalesOrderSvc/SalesOrders

However for this REST service basic auth does not work: https://xxx-epicor10/E10Train/api/v1/Ice.BO.UserFileSvc/ValidatePassword

Would you know where I can find out how to correctly get the validatepassword service to work?