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

[Purushotham11]

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?

[VDD]

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.

[SASKiwi]

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

[Purushotham11]

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?

 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

[SASKiwi]

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

[Purushotham11]

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?