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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

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

6 REPLIES 6
SASKiwi
PROC Star

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
Pyrite | Level 9

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
PROC Star

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
Pyrite | Level 9

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
PROC Star

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.

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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