topic Re: Unable to establish connection from SAS to SQL servers using windows authentication in New SAS User

Unable to establish connection from SAS to SQL servers using windows authentication

Purushotham11 — Thu, 28 Nov 2019 15:46:39 GMT

Hi All,

This is the first time we're trying to establish connection from SAS to SQL servers using windows authentication and unable to make it possible and would required help to fix this issue.

1. we added the db entries in odbc.ini file as below

DSN=Microsoft ODBC Driver 13 for SQL Server
[DSN]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0
ApplicationIntent=ReadOnly
Database=XXXXXXXX
MultiSubnetFailover=Yes
Server=TCP:server.domain.com,1433
AuthenticationMethod=9
Domain=XXXXX
Trusted_Connection=yes

2. And initially tried to connect outside of SAS using 'isql' command (isql -v DSN User Password) and we get the below error.

[S1000][unixODBC][Microsoft][ODBC Driver 13 for SQL Server]SSPI Provider: Credentials cache file '/tmp/krb5cc_9177' not found

[S1000][unixODBC][Microsoft][ODBC Driver 13 for SQL Server]Cannot generate SSPI context

Kindly suggest us how to fix this issue?

Re: Unable to establish connection from SAS to SQL servers using windows authentication

VDD — Thu, 28 Nov 2019 15:56:37 GMT

have you created an ODBC connection to the SQL server that works?

After you have an ODBC connection you can use a libname assignment in SAS to make the connection to the SQL server.

Re: Unable to establish connection from SAS to SQL servers using windows authentication

SASKiwi — Thu, 28 Nov 2019 19:26:06 GMT

This isn't a SAS problem. I suggest you check Microsoft forums.

Re: Unable to establish connection from SAS to SQL servers using windows authentication

Purushotham11 — Fri, 29 Nov 2019 07:11:20 GMT

Hello, Thanks for the reply!

We just want to clarify one more thing here.

Earlier we had added the entries in odbc.ini file as like below and were able to connect SQL servers using sql authentication.

Now we're trying to connect to new SQL servers with Windows authentication. So wanted to make sure whether the below odbc entrie would work for windows authentication as well or need to add entries in any other format?

Re: Unable to establish connection from SAS to SQL servers using windows authentication

SASKiwi — Fri, 29 Nov 2019 07:24:06 GMT

Have you tried it? I know this approach works fine on Windows OS, not so sure regarding Unix.

Re: Unable to establish connection from SAS to SQL servers using windows authentication

Purushotham11 — Fri, 29 Nov 2019 07:36:45 GMT

We're using SAS on Unix OS only but the thing is will that entry works to connect SQL server using windows authentication (Domain\Username)?

Yes, I've put the entry in this format only but when we run the libname we get the below error.

libname test odbc user="XXXXX\XXXXXX" password='XXXXXXXXXXXXXXX' datasrc="test" schema=XXXXXXXXX;

ERROR: CLI error trying to establish connection: [unixODBC][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for

user 'XXXXXX'.

ERROR: Error in the LIBNAME statement.

Could you confirm if the same entry format works for windows authentication?