topic Re: SAS EG problem with libname connection ODB sql server in SAS Enterprise Guide

SAS EG problem with libname connection ODB sql server

Pato485 — Fri, 12 Apr 2024 09:50:31 GMT

We tried to connect to sql server throught ODB connection, test was completed successfully. so connection is working OK:

But when we tried to connect to this database in SAS EG we got this error:

LIBNAME IDB ODBC DATAsrc=IDB SCHEMA=dbo USER=danasova PASSWORD="xxx";

What we did incorrect?

Re: SAS EG problem with libname connection ODB sql server

LinusH — Fri, 12 Apr 2024 11:05:57 GMT

To be sure, is EG connecting to a local or a remote SAS session?

And is your ODBC Manager test done on the host where SAS executes (and using the same user/pw)?

Re: SAS EG problem with libname connection ODB sql server

Pato485 — Fri, 12 Apr 2024 11:23:58 GMT

IT is local session. 2nd question - dont think so, when i tried to log through odbc driver 17 for sql server - it worked in odbc and sas, but when we use sql server odbc connection - it doesnt.

This is working:

LIBNAME mylib ODBC dsn='NMI' schema=dbo;

Because it is windows auth, but when we tried it through username and password - and odbc connection sql server, it doesnt work. And we dont know why

Re: SAS EG problem with libname connection ODB sql server

AlanC — Fri, 12 Apr 2024 13:56:18 GMT

Try this (with your changes):

libname SQLREF ODBC NOPROMPT="server=SVDSQLDCP01;driver=ODBC Driver 13 for SQL Server;trusted_connection=yes;database=DEVAPP10_Shredding" STRINGDATES=NO IGNORE_READ_ONLY_COLUMNS=YES SCHEMA=DBO;

From here: GitHub - savian-net/SasTipsTricks: Tips and tricks learned over 20+ years as a SAS/Microsoft consultant

Re: SAS EG problem with libname connection ODB sql server

SASKiwi — Fri, 12 Apr 2024 23:04:47 GMT

The SQL Server ODBC driver is really old and may not work with more recent versions of SQL Server. Try creating an IDB connection with the Version 17 driver.

Re: SAS EG problem with libname connection ODB sql server

Sajid01 — Sun, 21 Apr 2024 01:59:51 GMT

Hello @Pato485
I see this in your post

"This is working:

LIBNAME mylib ODBC dsn='NMI' schema=dbo;

Because it is windows auth, but when we tried it through username and password - and odbc connection sql server, it doesnt work. And we dont know why"

Please check authentication mechanism defined in SQL server.