BookmarkSubscribeRSS Feed
tuckeraw
Obsidian | Level 7

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;

 

5 REPLIES 5
tuckeraw
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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?

 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 616 views
  • 1 like
  • 3 in conversation