SQL user for 'sysprogress' user


(Brandon ) #1

Vantage 8.03.408B - SQL DB running Progress 10.1B

We have several custom crystal reports that are pulling data from a sql stored procedure. When we run one of the reports we keep getting a prompt asking for a db user login credentials. When we type in a sql user we created named ‘Epicor’ the report will run but if we try typing in a epicor user or ‘sysprogress’ the report will not connect to SQL, so we know it needs a SQL login to get to the data. The confusion is since progress still sits between SQL and Vantage, I’m not sure which vantage/progress user I need to use to access these reports that point back to our SQL db. Does any one know what SQL user the ‘sysprogress’ user in vantage uses? I know the system agent settings their is the ‘ODBC’ user/pass fields. So I’m assuming a sql user needs to exist named ‘sysprogress’? Any help is appreciated.

Thanks.

image


(Nathan your friendly neighborhood Support Engineer) #2

The super user “syprogress”, for SQL customers, is used to connect to the schema holder database, not the actual SQL database itself. For Progress databases, the “sysprogress” user is the default user created that has ODBC access (after some setup), but, should never be used for ODBC reports as it has too much access to the database-- user accounts used for reports should only have read access in the database in question as a best practice.

For ODBC Crystal Reports, you would need a SQL user defined within SQL that has read access to the tables in question against the SQL database.

The user on the system agent would be the SQL user needed for the custom reports to run–it doesn’t need to be sysprogress.


(John VanZandt) #3

Did you just make some changes to the reports? We run progress but if we dont put the connection string in the report it will give us the same window. Under crystal reports create a new connection with that connection string data then point those tables the new connection that you just created. The prompt should go away when the report runs. At least that is how it has worked for me. Let me know if I am being unclear.


(Brandon ) #4

We created a UDL connection file and pointed the report to this file which contains the SQL login info, we then updated the datasource to point to this file and we are still getting the prompt. Telling the report to use the udl file should bypass any login prompt but it doesn’t in Vantage. We can run the report in crystal itself and we don’t get the prompt.


(Brandon ) #5

Today we tried updating our ODBC u/p to use the SQL user as Nathan suggested but no matter what I tried I can’t get around this ‘Record has been modified by another user’ error. Searching the forum on this I got all kinds of hits for people getting a similar error but in different areas of Epicor/Vantage. Seems like a re-occurring error with Epicor. I was logged in as ‘manager’ and verified that user has full access and security via User Security. I also checked for security being set on the bo.sysagent via process security maint and no security has been set. I even created a new user and gave that user all access and security, tried logging in as that user and I still got the error. I also stopped the task agent and process in Prog. Explorer, went back into vantage, tried updating the ODBC u/p and I still got the error. I did click on the refresh button several times, but it didn’t make a difference and I still got the error. At this point I am fresh out of ideas. Any tricks to get around this?

Thanks.

Error
image