questions about SAS connection to SQL server with ODBC

[Jade_SAS]

Hi All,

   We are using SAS in unix enviroments, in order to access SQL server data with ODBC, the  SQL server name, port#,databasename, user and password etc needs to define in the ODBC.ini file in the unix enviroments.

   My question is: Is the user authentication has to be SQL Server authentication? or Can the user use withdowns NT authentication with network login ID? Thank you!

Jade

[aj34321 · Accepted solution]

You can use both authentications..  

But when using Windows NT user account, ensure to make changes to ODBC file and add AuthenticationMethod=9 and Domain=<WIndows domain name>

[Jade_SAS]

Thank you!

If use Windows authentications, do I need to collect all the unique user ID and their windows password in the ODBC.ini file under unix environment? Sorry if it's a silly question, really a newbie with unix here:)

[JBailey]

Hi @Jade_SAS

You would not need to put userid/passwords in the odbc.ini file. You can specify them in your SAS LIBNAME statement. 

There is an SGF paper that may be able to provide some insights: An Insider's Guide to SAS/ACCESS Interface to ODBC

Best wishes,

Jeff

[Jade_SAS]

Thank you Jeff! Which one is the most general configuration in ODBC.ini file for SAS under Unix environment, SQL Server authentication or windows NT authentication? What's the advance and disadvantage of these two?

[JBailey]

I think SQL Server authentication is common, but I don't have a lot of field experience with customer setups. SQL Server authentication. Server authentication is the quickest way to try it out. 

[SASKiwi]

Windows Authentication is best because you don't need to supply a userid or password as SQL Server authenticates against the userid you logged onto Windows with.