BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sas_9
Obsidian | Level 7

Hi aLL,

need help plz

what is "active directory authentication to access SQL server data" ? How its work?

currently, i am connected to the different sql db server to the SAS by

- creating datasource using system dsn in database source (ODBC) from control panel.

- then creating gp definition in USER MANAGER (where i provided userid and psw for sql db and create authentication) in SAS MC

- then creating server definition in SERVER MANGER in SAS MC

- then creating ODBC library (predefined library) under SAS MC using authentication that i have given in gp definition in USER MANAGER in SAS MC

everything works great so far...

---------------

Question

now if i want to conenct sql db server by using active directory authentication then  How can i do that and how it works?

please advise....

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

We do it a little differently by defining everything in SAS MC for ODBC connections to SQL Server.

In the Connection properties for the ODBC Server definition under Server Manager in SAS MC under the Options tab we select connection string and add the following:

"server=MySQLServerName;DRIVER=SQL Server;Trusted Connection=yes" <=== Be careful with this as the string must not contain any more spaces than those shown! Replace MySQLServerName with your actual server name.

It's the Trusted Connection=yes that enables Windows Authentification. Also by defining your server here you avoid the ODBC Administrator data source definitions which to me is a major advantage.

The additional Qualfier = and database = parameters are added to the SAS MC data library definitions pointing to the ODBC SAS MC server.

View solution in original post

6 REPLIES 6
SASKiwi
PROC Star

We do it a little differently by defining everything in SAS MC for ODBC connections to SQL Server.

In the Connection properties for the ODBC Server definition under Server Manager in SAS MC under the Options tab we select connection string and add the following:

"server=MySQLServerName;DRIVER=SQL Server;Trusted Connection=yes" <=== Be careful with this as the string must not contain any more spaces than those shown! Replace MySQLServerName with your actual server name.

It's the Trusted Connection=yes that enables Windows Authentification. Also by defining your server here you avoid the ODBC Administrator data source definitions which to me is a major advantage.

The additional Qualfier = and database = parameters are added to the SAS MC data library definitions pointing to the ODBC SAS MC server.

sas_9
Obsidian | Level 7

@SASKiwi - Its not prompting me to server=/driver=/trusted connection= option.

this is what i have done so far...

- create a source name 'tets' under system dsn in the server and gave all details like db name, userid and psw.

- then trying to cretae server definition under SAS MC, but its not promting me for server= or any driver= options...

- i right click on server manager>new server>select - 'ODBC server'>give name 'test'>db source type='odbc - other db'>connection string='test', then don't know what goes to 'authentication type' and 'authentication domain'?

- also do i have to create gp definition under user manager in SASMC and then have to create server definition & library?

please advise....thanks.

SASKiwi
PROC Star

In SAS MC under Server Manager, right click, select New Server and ODBC Server. Then go through the wizard screens, the third of which should look like this:

screenshot.jpg

Select Connection String and paste in the text from my previous message. I am assuming you are using your personal Windows userid to connect to your SAS server from EG. It is this that will authenticate against SQL Server. You just need to ensure that your DBA has allowed you access to that server.

sas_9
Obsidian | Level 7

I got your point - Thanks a lot!

Patrick
Opal | Level 21

You might want to have a read of some of the Admin Guides SAS Intelligence Platform, especially the Security Admin Guide and the Data Admin Guide.

As I understand this your metadata user autenticates agains the SAS metadata server. You need to sync the active directory with SAS metadata (that's described in the Security Admin Guide). You then could define your SQL connection as SASKiwi describes it and eventually access the data with a trusted user account (key word: authentication domain). Your metadata identity would then need to be a member of the metadata group which has been defined for accessing the db via a trusted user.

sas_9
Obsidian | Level 7

Thanks for your feedback SASKIWI and Patrick. I will do same and mention in detail that how i get connected.


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 9188 views
  • 0 likes
  • 3 in conversation