SSO when using SQL Server Drive (SAS ACCESS to MS SQL Server) - Need to use Integrated Security=SSPI

[tuckeraw]

I need some assistance with driver settings to allow SSO integration for SQL Server and/or also allow the use of domains in our user ids. We have Viya 4.x on AWS and we are testing connecting to on prem SQL server instances.  Works great but we have to use authid without a domain. Preferred is to use SSO and pass our credentials which have the domain. We are trying to figure out how to add the integrated security=sspi setting on the driver we are using. Has anyone had these issues and if so what how was the driver set? Any documentation that was helpful or other SAS resources? We prefer not use ODBC but the actual SQL Server driver.

Does work using auth id without DOMAIN\

libname testsql sqlsvr
NOPROMPT="
Driver={SAS ACCESS to MS SQL Server}; 
HostName=SERVER_GOES_HERE; PortNumber=PORT_GOES_HERE; 
Database=DATABASE_GOES_HERE;
UID=ID_GOES_HERE; PWD=PASSWORD_GOES_HERE"
schema=dbo;

Does not work (using DOMAIN\)

libname testsql sqlsvr
NOPROMPT="
Driver={SAS ACCESS to MS SQL Server}; 
HostName=SERVER_GOES_HERE; PortNumber=PORT_GOES_HERE; 
Database=DATABASE_GOES_HERE;
UID=DOMAIN\ID_GOES_HERE;PWD=PASSWORD_GOES_HERE"
schema=dbo;

[tuckeraw]

Most likely SSO will not pass credentials (SAS is Azure AD and on prem used AD) but we may just need to change a setting to change the authentication to allow the use of a DOMAIN.

[SASKiwi]

Have you raised this issue with SAS Tech Support? Given the complexity of working between cloud and on-prem, they are the best way to progress your problem. We use SSO/IWA exclusively for SAS - SQL Server database connections, but that's all on-prem in the same domain so it's a lot easier.

[tuckeraw]

That is also in progress. Each client can be different in their AWS, Azure, AD etc... Just looking for others who may have attempted anything similar what their outcome may have been. 

[SASKiwi]

FYI, here is what our SQL Server string looks like:

libname SQLSRVR odbc noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Trusted Connection=yes" DATABASE = MyDatabase schema = dbo;

It's the Trusted Connection=yes option that enables SSO.

Edit: Have you seen this library article? https://communities.sas.com/t5/SAS-Communities-Library/SAS-Cloud-Data-Exchange-for-the-SAS-Viya-Plat...

[Morse_Bill]

Sorry, but it's been a long time for me, but are the quotes in the right place in this example provided ?   Quote after  DB name ends the string, right?