10-06-2014 02:59 AM
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/libmsodbcsql-11.0.so.2270.0
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?
10-06-2014 06:42 AM
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 (http://technet.microsoft.com/en-us/library/hh568449(v=sql.110).aspxMicrosoft ODBC Driver for SQL Server as of MS)
The message is telling you the name mfbs_db is not found.
- unixodbc manual: http://www.unixodbc.org/doc/UserManual/ 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).
10-06-2014 08:37 AM
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.
10-06-2014 10:23 AM
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
10-14-2014 10:09 AM
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.