SQL connection code that works in SAS 9.4 doesn't work in SAS EG 8.3 ("Error trying to establish...)

[MattJans]

Hi there! I googled and checked the SAS Communities but didn't see any help on this. The code below works fine on SAS 9.4, but doesn't work, and gives an error (see below). In case it's not obvious, the XXX and YYY are real names in my working code. Just didn't want to post those here. I also tried (in EG) changing single quotes to double, and putting the code all on one line. Still gives the same errors. I think it's the first error that's most relevant. On 9.4 this opens a dialogue box where I enter my UN and PW for this server.  

libname XXX oledb provider=sqloledb prompt=YES

        dataSource='YYY' properties=('Initial Catalog'=XXX_db)

        schema=dbo access=readonly;

ERROR: Error trying to establish connection: Unable to prompt for connection information.
ERROR: Error in the LIBNAME statement.

Any help would be great. Thanks! 

[Tom]

PROMPT means that it wants to open a window on the CONSOLE to ASK YOU to complete the signon details, like password, etc.

But if you are using Enterprise Guide to submit the SAS code then there is no way for SAS itself to interact with you.  All your interactions are with Enterprise Guide instead.

Get it to work without the PROMPT in Display Manager and then the same code should work when submitted in the background via Enterprise Guide.

[ChrisHemedinger]

Tom is correct --  you will need to provide the credentials for the OLEDB connection. Your options are:

- user/password (you can use PWENCODE to obscure a clear-text password)

- set an AUTHDOMAIN in an administered environment with the credentials you need, and reference that in your code.

- use single-signon/Integrated Windows Authentication -- requires that the same identity using EG is connecting to SAS which is then connecting to the OLEDB source, and all steps are set for Trusted for Delegation