SAS EG problem with libname connection ODB sql server

[Pato485]

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?

[LinusH]

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)?

[Pato485]

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

[AlanC]

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 consult...

[SASKiwi]

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.

[Sajid01]

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.