topic Re: SAS ODBC to SQL Server issues in SAS Enterprise Guide

SAS ODBC to SQL Server issues

Lenvdb — Thu, 10 Jul 2014 13:48:46 GMT

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.

Our Linux man installed the SQL Server ODBC Drivers.
A new entry now appears in the ODBCInst.ini file.
We added the DSN entry in the ODBC.ini file
We also added the SQL server info on the HOSTS file on the Linux nodes.

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.

Many thanks

Len

Re: SAS ODBC to SQL Server issues

jakarman — Thu, 10 Jul 2014 15:49:43 GMT

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

Re: SAS ODBC to SQL Server issues

SASKiwi — Fri, 11 Jul 2014 05:04:32 GMT

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?

Re: SAS ODBC to SQL Server issues

user24feb — Fri, 11 Jul 2014 06:21:33 GMT

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.

Re: SAS ODBC to SQL Server issues

Lenvdb — Fri, 11 Jul 2014 07:54:32 GMT

We have SAMBA installed on the Linux boxes. I would assume it is configured correctly for interfacing with Windows.

Re: SAS ODBC to SQL Server issues

Lenvdb — Fri, 11 Jul 2014 07:58:25 GMT

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

Re: SAS ODBC to SQL Server issues

jakarman — Fri, 11 Jul 2014 08:12:22 GMT

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.

Re: SAS ODBC to SQL Server issues

Lenvdb — Thu, 17 Jul 2014 16:01:08 GMT

It looks like KERBEROS is not configured properly on the Linux Boxes (SAS Grid). We are currently taking this up with SAS Tech Support...

Re: SAS ODBC to SQL Server issues

Lenvdb — Fri, 05 Sep 2014 08:07:13 GMT

Aha

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!!

Re: SAS ODBC to SQL Server issues

UlfBorjesson — Thu, 19 Mar 2015 19:17:59 GMT

Hi Lenvdb,

You don't happen to have a URL to the "obscure little document somewhere on the web"? Can you post it here?

Re: SAS ODBC to SQL Server issues

Lenvdb — Fri, 05 Jun 2015 14:02:44 GMT

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:

[mySASDDODBC]

Driver=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddsqls27.so

Description=DataDirect 7.1 SQL Server Wire Protocol

Address=MYMSSQL01,1433

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.

Re: SAS ODBC to SQL Server issues

maheshtalla — Thu, 23 Feb 2017 22:13:14 GMT

Hi All,

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.

Re: SAS ODBC to SQL Server issues

ankit___gupta — Fri, 24 Feb 2017 07:51:29 GMT

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

[DSNNAME]
Driver=
Description=
Trace=Yes
Server=
Port=
Database=
user=
password=

Re: SAS ODBC to SQL Server issues

SurajChand — Fri, 24 Feb 2017 11:54:26 GMT

Hi Lenvdb,

Here are few pre-requisites to get connected with DB through ODBC connection.

For Linux platform :-

1. UnixODBC driver

2. SQL driver

3.odbc.ini

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.

https://linuxsasadmin.wordpress.com/2015/09/17/how-to-install-unixodbc-driver-in-linux-rhelcentosfedora/

2. Define odbc.ini file (Perform only 1st step)

https://linuxsasadmin.wordpress.com/2015/09/21/how-to-connect-with-mysql-database-using-sas-enterprise-guide/

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.