Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

libname works in sas EG but not in SAS VA 7.1

Reply
Super Contributor
Posts: 275

libname works in sas EG but not in SAS VA 7.1

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
Super User
Posts: 3,233

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

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" ;

Super Contributor
Posts: 275

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

[ Edited ]

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

Ask a Question
Discussion stats
  • 2 replies
  • 784 views
  • 0 likes
  • 2 in conversation