When I search for SAS Access to SQL Server I tend to find a lot of information on how to code my SAS script, what engine to use and some of the options available, but details on the configuration are few and far between. So, I thought I would write a blog about each one so the next time I search I’ll be able to find a reference.
The key file for SQL Server is odbc.ini.
The odbc.ini file can sit on SAN disk, so you may have to only update it once. If not, you will need to update it on all of your compute servers.
If using Linux or similar, be careful updating it using FTP (File Transfer Protocol) as this can corrupt the file, I adopted the VI Editor.
Now you know what to update and how, you just need to find it. You can search for the odbc.ini file, read your SAS installation documentation or ask your friendly server administrator. Try looking in the SAS binaries first.
When you find the file, a connection string will look similar to this. There are a few variations so don’t be concerned if yours is not the same.
Description=SAS Institute Inc. 7.0 SQL Server Wire Protocol