topic Re: SSO when using SQL Server Drive (SAS ACCESS to MS SQL Server) - Need to use Integrated Security= in Administration and Deployment

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

tuckeraw — Fri, 13 Oct 2023 14:10:41 GMT

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;

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

tuckeraw — Fri, 13 Oct 2023 14:17:57 GMT

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.

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

SASKiwi — Sat, 14 Oct 2023 21:57:38 GMT

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.

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

tuckeraw — Sun, 15 Oct 2023 10:55:20 GMT

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.

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

SASKiwi — Mon, 16 Oct 2023 00:04:23 GMT

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-Platform/ta-p/873764

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

Morse_Bill — Sun, 15 Oct 2023 23:10:50 GMT

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?