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.
ODBC.INI:
[mfbs_db]
Description = SQL Server Database
Driver = ODBC Driver 11 for SQL Server
Servername = MFBSSQLP01/XRM
Port = 59714
Trace = Yes
Database = MFBS_MSCRM
ODBCINST.INI:
[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
$LD_LIBRARY_PATH:
/opt/odbc_unix:/opt/odbc_unix/lib:/opt/odbc_unix/lib64:/opt/odbc_mssql/msodbcsql-11.0.2270.0/lib:/opt/odbc_mssql/msodbcsql-11.0.2270.0/lib64:/o
pt/sas/SASHome/Secure/sasexe:/opt/sas/SASHome/SASPrivateJavaRuntimeEnvironment/9.4/jre/lib/amd64/server:/opt/sas/SASHome/SASPrivateJavaRuntimeE
nvironment/9.4/jre/lib/amd64:/opt/sas/SASHome/SASFoundation/9.4/sasexe
$ODBCINI:
/etc/odbc.ini
$ODBCSYSINI:
/etc
libname-statement:
libname mydblib sqlsvr
noprompt="uid=<user>;
pwd={SAS002}F560034D1111E5B200B1C5553A6A67744BE8CBB1;
dsn=mfbs_db;"
stringdates=yes;
Error-Message:
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?
Regards,
Christoph
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.
Troubleshooting:
- 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.
See: https://communities.sas.com/thread/61088
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).
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.
I've always had to set ODBCHOME as well.
http://support.sas.com/documentation/installcenter/en/ikfdtnunxcg/66380/PDF/default/config.pdf
And to verify SAS always pick it up (for all users), set it - and all the other required variables in !SASROOT/bin/sasenv_local
What version of SAS? We had to update SAS 9.4 to M2 to get the ODBC & SQLSVR to work together.
SAS 9.4 M1
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.
regards,
christoph
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.
Find more tutorials on the SAS Users YouTube channel.