Desktop productivity for business analysts and programmers

how to use Active directory authentication to access SQL Server data

Accepted Solution Solved
Reply
Regular Contributor
Posts: 220
Accepted Solution

how to use Active directory authentication to access SQL Server data

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


Accepted Solutions
Solution
‎01-29-2013 10:06 PM
Respected Advisor
Posts: 3,059

Re: how to use Active directory authentication to access SQL Server data

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


All Replies
Solution
‎01-29-2013 10:06 PM
Respected Advisor
Posts: 3,059

Re: how to use Active directory authentication to access SQL Server data

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.

Regular Contributor
Posts: 220

Re: how to use Active directory authentication to access SQL Server data

@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.

Respected Advisor
Posts: 3,059

Re: how to use Active directory authentication to access SQL Server data

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.

Regular Contributor
Posts: 220

Re: how to use Active directory authentication to access SQL Server data

I got your point - Thanks a lot!

Respected Advisor
Posts: 3,823

Re: how to use Active directory authentication to access SQL Server data

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.

Regular Contributor
Posts: 220

Re: how to use Active directory authentication to access SQL Server data

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


☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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