07-10-2014 09:48 AM
SAS ODBC LibnameHi all
We run a SAS Grid with SAS 9.3 on 2 Linux Nodes.
We have SAS/ACCESS to ODBC for use with MS SQL Server databases.
We moved form a Windows SAS Server to a LINUX SAS Grid.
Here is no ODBC Manager, so it is a struggle for us to figure out how to set up the ODBC DSN.
We have a SAS User with trusted security on the SQL Server.
When he sets up a LIBNAME statement as follows:
Libname AR ODBC noprompt="dsn=mssql_autorek;Trusted_Connection=yes" schema=dbo;
We get this error:
1836 LIBNAME AR ODBC noprompt=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX schema=dbo;
ERROR: CLI error trying to establish connection: [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Cannot generate SSPI context :
We are wrecking our heads here trying to figure out what is missing.
Has anyone else faced this issue before?
We would really appreciate some guidance here.
07-10-2014 11:49 AM
Did you configure IWA? This is a more challenging part as you need part of SAMBA being installed for id/gid translation to Windows world.
Often the user en password are getting coded in the SAS code. Of course you do not want to do that. That would be an other question.
How to troubleshoot the "Cannot generate SSPI context" error message
07-11-2014 01:04 AM
I'm pretty sure the string Trusted_Connection=yes is only for Windows authentification so that could be part of the problem. Have you tried the usual user= and password= options?
07-11-2014 03:58 AM
We have tried various options - incl this one:
LIBNAME Autorek ODBC DSN="mssql_autorek" user=<login> pwd=<mypassw> schema=dbo;
produces this error:
ERROR: CLI error trying to establish connection:
[unixODBC][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'xxxxxxxx'.
07-11-2014 02:21 AM
Your Libname command looks o.k. Could it be that SAS Access to ODBC works under Windows and SAS Access to SQL is needed. You might need to contact product support.
07-11-2014 04:12 AM
IWA is not easy to implement SAS(R) 9.3 Intelligence Platform: Security Administration Guide (IWA). Assuming it is correct is nice knowing is better (test/verification).
Even then I would not trust immediate an IWS attempt using ODBC.
Ahh, the login error is more clear. You are not allowed to login with that user. Your AD logs should be able why, Userid missing password failed wrong domain/time etc.
09-05-2014 04:07 AM
We finally got it.
We needed to create a System Account for the SAS User, and then we had to get the DSN correct in the ODBC.ini file.
The Documentation on SAS did not quite get it right for us.
We found an obscure little document somewhere on the web showing the correct format, which we tried, and it worked!!
06-05-2015 10:02 AM
Sorry for taking so long to reply.
I did not actualy find the documentation, the Linux Admins did.
It proposed that the odbc.ini entry show the ADDRESS= option rather than the SERVER= and PORT= option:
Description=DataDirect 7.1 SQL Server Wire Protocol
Here the Address=servername, port number
We installed the Datadirect driver. Currently testing it. It sits on top of ODBC, so it still uses the ODBC.ini, ODBCINST.ini etc.
We also have to set Environment Variables when running SQL queries.
We managed to test with success in Linux, but still unable to get it working in SAS EG5.1. Not sure what we are missing still.
SAS will be contacting us to sort it out early next week.
02-23-2017 05:13 PM
Is there any resolution to this issue, we are also facing same issue in our environment. Please let me know the solution if its has been resolved.
Thanks in advance.
02-24-2017 02:51 AM
I have encountered this error in SAS Management Console. So when i used to clear the credential cache and reenter the username password to generate the LIBNAME statement is usually starts working.
For permanent fix, we created a authtication domin in our server configuration.
and our dsn looks something like
02-24-2017 06:54 AM
Here are few pre-requisites to get connected with DB through ODBC connection.
For Linux platform :-
1. UnixODBC driver
2. SQL driver
4. Operating system must be able to get connected with database.
1. Install the UnixODBC driver by following below url.
in RHEL/CentOS/Fedora :- yum install unixODBC* -y (It will install default driver as per o/s releases) then you need to update it with letest versions.
2. Define odbc.ini file (Perform only 1st step)
3. Operating system access:-
isql -v dsn username password
if you are get connected with database then you can test the same with base sas (in foundation by ./sas -nodms) or sas Enterprise guide.
Now open SAS Enterprise Guide & write this statement to validate the connection.
libname Test odbc dsn=DSN user=sa pwd=xxxxxxx connection=global schema=dbo;
If it’s run without any error then you have made a connection successfully between SQL & SAS.
Final step is you need to create a odbc library from the sas management console as per your set enviornment.