09-18-2015 10:55 AM
I am trying to create a new library to connect to an existing MS SQL server. I think it needs to use a generic userid rather than my personal userid like below which I am not able to make it do so.
1 LIBNAME BLBFLMS ODBC NOPROMPT=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX SCHEMA=dbo USER=saspert
1 ! PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;
ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver
ERROR: Error in the LIBNAME statement.
Major version number:
Minor version number:
Data Source Type:
ODBC - Other Database
Assigned to SAS Servers:
Database Schema Name:
I have attached the note that SAS shows when I finished the library installation.
09-18-2015 08:39 PM
The error message is pretty clear. SAS can't find the ODBC data source name SAS_BB. Have you defined it? Check in the ODBC Administrator if this is happening under Windows. Note this is the ODBC Administrator on the SASApp server. If your server runs on Unix then the DSN needs to be defined in ODBC.ini.
Since the DSN can't be found. the driver associated with it can't be found hence the driver error.
09-20-2015 03:33 AM
SASkiwi, I am wondering why there is a DSN resource created as all needed parameters for the connection can be specfied within SAS so avoiding the need to add the burdern of an segregated ODBCADM administraiton.
I know some guy from NZ that explained me the trick some day
Having a libname statement being genereated for some libref to connect to an exterenal RDBMs is not the solution for authentication.
- The libname/libref is the technical way how to get connected to data.
- the user/passwrd (when needed) is who has the grants for accessing those data
Commonly it is bad idea to use shared access (group accounts) for external sensitive data. With shared access you will lossed in the situation of lack of auditability traceability. Only in play-toy areas to prove the functionality of the technical connection something like that is acceptable. Doing things wrong wiht these concepts from start can cause a lot of political aversions.
09-21-2015 04:09 AM
Jaap, yes I agree, and that is how I do it. Avoiding the ODBC Administrator is to be recommended as it removes another point of maintenance.
In the case of this post I was just trying to explain the errors not necessarily provide a better solution - which can come later.
09-22-2015 06:00 PM
Hi @SASKiwi Thanks for the suggestion. If I expand sasapp server, I do not see the odbc list. Am I looking at the right place? See screenshot which is attached hopefully.
09-23-2015 03:38 AM
If you are looking for the ODBC Administrator then you will find it in the Windows Control Panel not in SMC at all. If you are going to use DSNs then they need to be defined there. This explains more:
However I suggest you don't bother with that and just start by defining a simple libname that does everything to connect to SQL Server:
See this example from the link:
libname mylib odbc noprompt="driver=SQL Server Native Client 10.0;server=server-name;database=SQL-database;Trusted_Connection=yes"
If you can get the LIBNAME working, then the next step might be to define all this in SMC but thats a bit harder to do. Maybe a lesson for another day.