topic Re: libname works in sas EG but not in SAS VA 7.1 in SAS Visual Analytics

libname works in sas EG but not in SAS VA 7.1

saspert — Tue, 06 Oct 2015 17:36:20 GMT

Hi,

The original libname as I see in SAS MC is below -

LIBNAME WWTest ODBC NOPROMPT="server=CLNTDWPRD03;driver=sql server;Trusted Connection=yes;Database=clientdb;" SCHEMA=dbo USER=myid PASSWORD="{sas002}alphanumeric combination" ;

I usually take out the user and password strings and use the rest of the libname in the stp code -

LIBNAME WWTest ODBC NOPROMPT="server=CLNTDWPRD03;driver=sql server;Trusted Connection=yes;Database=clientdb;" SCHEMA=dbo;

when I run the stored process in SAS EG, then I am not getting any error-

21 LIBNAME WWTest ODBC NOPROMPT=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SCHEMA=dbo ;
NOTE: Libref WWTEST was successfully assigned as follows:
Engine: ODBC
Physical Name:
22 PROC SQL;
23 CREATE TABLE WORK.QUERY_FOR_VW_DASH_DISPLAY AS

Stored Process Error in SAS VA 7.1

21         LIBNAME WWTest ODBC  NOPROMPT=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX  SCHEMA=dbo  ;
ERROR: CLI error trying to establish connection: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'HMI-INC\svc_sassrv'. : [Microsoft][ODBC SQL Server Driver]Invalid 
       connection string attribute
ERROR: Error in the LIBNAME statement

Re: libname works in sas EG but not in SAS VA 7.1

SASKiwi — Tue, 06 Oct 2015 18:41:22 GMT

Your EG SQL Server database connection is using your own userid to login. As this is successful you must be set up as a user in the database.

The SAS VA Stored Process, runs under a SAS service account: svc_sassrv. This is the userid being used to connect to SQL Server via Trusted Connection=yes and it is most likely not set up as a user in SQL Server. Confirm this with your DBA. What happens if you don't use Trusted Connection=yes and substitute your own userid and password instead:

LIBNAME WWTest ODBC NOPROMPT="server=CLNTDWPRD03;driver=sql server;Database=clientdb;" SCHEMA=dbo USER=myid PASSWORD="{sas002}alphanumeric combination" ;

Re: libname works in sas EG but not in SAS VA 7.1

saspert — Wed, 07 Oct 2015 18:25:52 GMT

this is when i remove the trusted connection = yes phrase and just use my userid and encrypted password.

SAS VA session -

21 LIBNAME WWTest ODBC NOPROMPT=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SCHEMA=dbo USER=myuserid PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;
ERROR: CLI error trying to establish connection: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'HMI-INC\svc_sassrv'.
ERROR: Error in the LIBNAME statement.

SAS EG sesion-

21 LIBNAME WWTest ODBC NOPROMPT=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SCHEMA=dbo USER=myuserid PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;
NOTE: Libref WWTEST was successfully assigned as follows:
Engine: ODBC
Physical Name:
22 PROC SQL;

23 CREATE TABLE WORK.QUERY_FOR_VW_DASH_DISPLAY AS

....

update 20150710 : my db admin sent me a note saying that svc_sassrv is added to a db user group that has access to ms sql server db. But I still see the same error in SAS VA 7.1.

Thanks,

saspert