Architecting, installing and maintaining your SAS environment

SAS Interface/ACCESS to MS SQL Server

Occasional Contributor
Posts: 10

SAS Interface/ACCESS to MS SQL Server

Hi all,

I have installed the unixODBC 2.3.0 and MS SQL Server ODBC Driver msodbcsql-11.0.2270.0. Then I have installed SAS Interface/ACCESS to MS SQL Server.



Description = SQL Server Database

Driver = ODBC Driver 11 for SQL Server

Servername = MFBSSQLP01/XRM

Port = 59714

Trace = Yes

Database = MFBS_MSCRM


[ODBC Driver 11 for SQL Server]

Description = Microsoft ODBC Driver 11 for SQL Server

Driver = /opt/odbc_mssql/msodbcsql-11.0.2270.0/lib64/

Threading = 1










libname mydblib sqlsvr






ERROR: CLI error trying to establish connection: [unixODBC][Driver Manager]Data source name not found, and no default driver specified

ERROR: Error in the LIBNAME statement.

Can anyone help me with this issue? What I am doing wrong in configuration?



Valued Guide
Posts: 3,208

Re: SAS Interface/ACCESS to MS SQL Server

You  have a lot of settings mentioned. What is not mentioned:

Are these settings active in the SAS session that is trying to connect?

As you are mentioning a Unix approach you are most likely using Eguide and a WS-server.

These settings should get activated using SAS configuration files. It does not help when you are doing that in your personal Unix profile settings

When you are using the system profiles for modifications than those can be revoked and reset by your OS system administrator.

You probably are following: Installing the Driver Manager ( ODBC Driver for SQL Server as of MS)

The message is telling you the name mfbs_db is not found.



- unixodbc manual: is using a graphical interface. You should be able to use a X-server terminal on your desktop.

  When the datamanager is working proceed to your SAS interface  

Having installed the ODBC driver you should check the functionality of that one outside of SAS.

Do not trust the pwencode approach. Your userid is not secret you could have left to the real one in your SAS example code.


Having a demo/check account for just verifying the technical connections is very sensible advice. You can validate that always without worrying to do something with real possible sensitive business production information (data).  

---->-- ja karman --<-----
Super Contributor
Posts: 358

Re: SAS Interface/ACCESS to MS SQL Server

Also - you need to check that the port number is set on the ODBC as defined in your code.  We found that even with the port

number in the code, it still had to be set manually.  Also - make sure you have the appropriate authority to define the ODBC.

SAS Employee
Posts: 101

Re: SAS Interface/ACCESS to MS SQL Server

I've always had to set ODBCHOME as well.

And to verify SAS always pick it up (for all users), set it - and all the other required variables in !SASROOT/bin/sasenv_local

Trusted Advisor
Posts: 2,113

Re: SAS Interface/ACCESS to MS SQL Server

What version of SAS?  We had to update SAS 9.4 to M2 to get the ODBC & SQLSVR to work together.

Occasional Contributor
Posts: 10

Re: SAS Interface/ACCESS to MS SQL Server

SAS 9.4 M1

Occasional Contributor
Posts: 10

Re: SAS Interface/ACCESS to MS SQL Server

it is working now with sql server authentification. now the last task is to get this running with kerberos and windows authentification via AD.

thanks for your help and support.



Ask a Question
Discussion stats
  • 6 replies
  • 1 like
  • 5 in conversation