SAS Studio to MSSQL Database connection issue via ODBC driver

[kumarsandip975]

Hi,
Could you please help us to understand the root cause with the below error,

Please note, we have this issues when we enabled Kerberos authentication to SAS Studio environment(running on windows with SAS 9.4 M8). It was working fine before Kerberos implementation.

73         libname SQLXXX01 SQLSVR datasrc=SQLXXX01 schema=abc;

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'NT

        AUTHORITY\ANONYMOUS LOGON'. : [Microsoft][ODBC Driver 17 for SQL Server]Invalid connection string attribute

ERROR: Error in the LIBNAME statement.

[Eddie305]

Please check that your SQL Server is using NT authentication or SA authentication.

Where do you provide the DB user account and password to set up the library before the Kerberos implementation?

[kumarsandip975]

I guess this is because we have changed to run object spawner services running to one of service account, earlier it was running with local system . I am not quite sure how it was working before juts defining libname statement, could be SAS was using windows authentication ? Do I need to consider Kerberos delegation between the SAS Workspace Server and SQL Server?

[gwootton · Accepted solution]

If the Object Spawner and SQL Server are running using service accounts on different hosts, you may need to perform the steps discussed here to provide those service accounts with SPNs.

Manually Registering Object Spawner SPNs
https://go.documentation.sas.com/doc/en/bicdc/9.4/bisecag/n1d1zo1jsf2o0en1ehu4c4simfky.htm#p0okm1tr2...

Manually Registering SQL SPNs
https://go.documentation.sas.com/doc/en/bicdc/9.4/bisecag/n1d1zo1jsf2o0en1ehu4c4simfky.htm#n0f8oogy4...

If you are using constrained delegation, you would also need to allow the SAS SPN to delegate to the SQL Server SPN:

Configure Kerberos
https://go.documentation.sas.com/doc/en/bicdc/9.4/bisecag/p1jg2eif6qym5qn1co79sdclcyt7.htm#n1t1pem89...

and also be sure to set the SAS_CONSTRAINED_DELEG_ENABLED environment variable:

Kerberos on Windows
https://go.documentation.sas.com/doc/en/bicdc/9.4/bisecag/p1jg2eif6qym5qn1co79sdclcyt7.htm#p187xli70...

[kumarsandip975]

Thanks, that makes sense. Our SAS application server and SQL Server are indeed hosted on different machines, so I’ve requested the AD team to delegate the required SPNs to the service account running the Object Spawner. I’ll share an update once I hear back from them.

Everything else seems to be in place, since Kerberos authentication is already working successfully from SAS Studio and the backend SAS services. It looks like only the database side was missing the required configuration.

I do have one additional question: If you can assist me, we also connect to other databases such as Oracle and PostgreSQL through ODBC drivers on different servers. Should we expect a similar Kerberos delegation requirement for those, or is this issue specific to SQL Server? So far, no one has reported authentication problems with Oracle or PostgreSQL, so I’m not entirely sure whether Kerberos is involved for those connections in our SAS environment.

[gwootton]

If those data sources are configured for Kerberos authentication you would similarly need to be allowed to delegate to the SPNs they use. It sounds like these are not configured for Kerberos authentication.

[kumarsandip975]

This got fix as you suggested, thanks for help.