Good night people!
I'm a third party dba consultant for SABIC Innovative Plastics South America LTDA.
We are facing an issue which I'd like to share with you.
It is related to the communication between MS SQL and Progress database services (Appserver). Sometimes when we run an interface program, the communication gets frozen and sometimes the problem does not happen. If our user in SQL db does not have SYSADMIN role added to it, also the process does not work. We couldn’t get an answer from Progress why sysadmin role in SQL could affect schema holders connections and executions.
We created a schema holder to connect to sql db.
-DataService mssbroker1 -S 5163 -H SAPIT341 -N tcp -Dsrv svub,1.
The connection to the SQL works fine, the problem is related when we execute a storade procedure.
A manual test could be done as this:
DB connection code:
CONNECT -db laint -ld laint -S 41003 -H SAPIT341 -N tcp -Mm 8192.
CONNECT -db laintofi -DataService mssbroker1 -ld esp-laint -S 5163 -H SAPIT341 -N tcp -U LAINTERFACES -P GE_lainterfaces -Dsrv svub,1.
storade procedure execution:
DEF VAR i-num-ini AS INT NO-UNDO.
DEF VAR i-num-fim AS INT NO-UNDO.
DEF VAR i-error AS INT NO-UNDO.
DEF VAR h AS INT NO-UNDO.
ASSIGN i-num-ini = 520079781
i-num-fim = 520079784.
RUN STORED-PROCEDURE sp_exec_dts_download_invoice h = PROC-HANDLE (Input string(i-num-ini), Input string(i-num-fim), OUTPUT i-error).
CLOSE STORED-PROCEDURE sp_exec_dts_download_invoice WHERE PROC-HANDLE = h.
The thing is, if we don't have sysadmin role added for our user LAINTERFACES on sql, the codes do not work. We receive a message that the process is already in use by user ??.
If we add the sysadmin role to the user LAINTERFACES, this problem does not happen anymore. That's an issue for me, because grating sysadmin role access to this user, is going to cause a security issue on the SQL server.
Anyway, sometimes even with the sysadmin role granted to our user, the process does not work also. We don't receive any error message but the process don't get completed at all.
Can you help me to verify what's is happening?
Be free to ask me any questions!
tks a lot!
