SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Hi All,

 

    We have SAS server under UNIX, if we access Microsoft SQL data via SAS/Access to Microsoft SQL data, is there a way does not need to edit ODBC.ini file?

  

     I googled and found the following link, it seems we definitely still need to edit the ODBC.ini file even via SAS/Access to Microsoft SQL data (instead of the SAS/Access to ODBC)

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a001405609.htm

    

Please advise!

 

Thanks,

Jade

 


Accepted Solutions
Solution
‎01-31-2017 03:56 PM
Super User
Posts: 3,102

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Here is a typical connection string we use for SAS under Windows and it completely defines the connection to SQL Server:

 

noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"

 

We don't have Unix SAS so I can't try this for Unix. But if you removed Trusted Connection=yes and added uid= and password = you might get something close to what might work under Unix. I'm keen to know how you get on.

View solution in original post


All Replies
Super User
Posts: 3,102

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

It is definitely possible for Windows SAS and this reference suggests it could be possible for Unix:

 

https://communities.sas.com/t5/SAS-Communities-Library/How-to-configure-SAS-ACCESS-Interface-to-ODBC...

 

That is assuming SAS/ACCESS to SQL Server uses ODBC underneath.

 

You would usually use the NOPROMPT option say on a LIBNAME statement to specify the connection string however this reference says it is not supported under Unix:

 

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0gz66qe8msnkyn1jg...

 

I'd try this anyway just to confirm if it is correct or not. In my experience specifying database connections completely in a LIBNAME or CONNECT TO statement is way better than having to define DSNs elsewhere.  

Frequent Contributor
Posts: 111

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Thank you SASKiwi!

 

But the example in the reference using NOPROMPT still has DSN which I think should be defined in the ODBC.ini file, right?

libname mydblib sqlsvr noprompt="uid=myusr1;
   pwd=mypwd1; dsn=sqlservr;" stringdates=yes;

So I think there is no way to avoid configuring ODBC.ini file, please correct me if I am wrong.

 

Thanks,

Jade

Solution
‎01-31-2017 03:56 PM
Super User
Posts: 3,102

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Here is a typical connection string we use for SAS under Windows and it completely defines the connection to SQL Server:

 

noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"

 

We don't have Unix SAS so I can't try this for Unix. But if you removed Trusted Connection=yes and added uid= and password = you might get something close to what might work under Unix. I'm keen to know how you get on.

Frequent Contributor
Posts: 111

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Thanks SASKiwi!

So the server name here is the real server name, not the name that set up in the ODBC file?

noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;"

 

We have SAS ODBC to SQL data installed in UNIX, now thinking add on SAS/Access to Micosoft SQL data to reduce the work load of admin ( if no need to edit the ODBC.ini file in UNIX, we have a lot servers and databases, really a lot to manage in the ODBC.ini file for admin)

 

Thanks,

Jade

Super User
Posts: 3,102

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Yes it is the "real" server name. If you use SQL Server Studio then it is the server name you enter to connect to your required databases.

Frequent Contributor
Posts: 111

Re: If access MS SQL data via SAS/Access to MS SQL , still need to edit ODBC.ini file in UNIX?

Thank you, this is really helpful!

Jade

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 385 views
  • 0 likes
  • 2 in conversation