Scheduled MAP produces no results, runs fine when manually launched
I have two MAPs that import data from a proprietary database into Great Plains.
The problem began after migrating the proprietary database to a new server.
The connection strings were updated for the new server and both MAPs run fine when manually launched. However when the MAP is scheduled, it runs but produces no results. I say that becasue I have a mail task set to send an email when the task fails (including the error message(s)). I received the email (hence I know the schedule started the MAP) but the body of the email is blank.
When I view the MAP log (for the scheduled run) I see Run Status: unsuccessful, Record count:0, Success count:0, Failure count:0 User:<<domain>>\<<sevice account>>.
When I view the MAP log (for the manual run) I see the numbers I would expect. The main difference being the User (when manually run it is using my credentials). So I assume that I have an issue with the User (for the scheduled run) not having access to the proprietary database.
However the User running the schedule is not part of the connection string to the proprietary database. The MAP connects to the database with SQL credentials.
It wouldn’t appear that I have a firewall issue as I can run the MAP manually.
So I am at a loss as to where to look for the problem.
Most likely your connection is set up as Windows Authentication, then the user assigned to the eOne SmartConnect Windows Service needs to be given access to the proprietary database. We also encrypt the Connection strings so you would need see the user and password associated with that connection.
Follow the steps in this article to add a user to that database. https://www.lifewire.com/creating-sql-server-2008-database-account-1019857
Thank you for your reply however as I stated “The MAP connects to the database with SQL credentials.” Additionally my personal credentials on that server (which is in a different domain) do not have access to the proprietary database.
There is nothing special about how SmartConnect connects to a SQL database, other than provided credentials or windows authentication using ODBC. If you are providing SQL Credentials to the ODBC connection that is what would be used as the connection regardless of running the map manually or through the service.
The fact that the error tells you it’s a login failure and provides the domain/user would make me believe that it is using Windows Authentication.
Again – that you for your response. I don’t know why, but without changing anything the MAP began running successfully (at least connecting to the database – there are still the occassional errors in processing) from the schedule.