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

[Jade_SAS]

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

[SASKiwi]

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.  

[Jade_SAS]

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

[SASKiwi · Accepted solution]

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.

[Jade_SAS]

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

[SASKiwi]

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.

[Jade_SAS]

Thank you, this is really helpful!

Jade