topic Re: New SAS Library Errors in Administration and Deployment

New SAS Library Errors

saspert — Fri, 18 Sep 2015 14:55:16 GMT

Hi,

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
specified
ERROR: Error in the LIBNAME statement.

Server:
BB
Major version number:
0
Minor version number:
0
Data Source Type:
ODBC - Other Database
Associated Machine:
server.client.com

Connection:
Connection: BLUEBUFFALO
Authentication type:
User/Password
Connection String:
NOPROMPT="dsn=SAS_BB;Trusted_Connection=yes"
Authentication domain:
DefaultAuth

Library:
BBMS
Location:
/HMI/SourceData/BB
Assigned to SAS Servers:
SASApp
Libref:
BLBFLMS
Engine:
ODBC

Database Server:
BBB
Database Schema Name:
dbo
Connection:
Connection: BB
Default Login:
client\blueskydataaimports (DefaultAuth)

I have attached the note that SAS shows when I finished the library installation.

Re: New SAS Library Errors

Sue_SAS — Fri, 18 Sep 2015 16:01:18 GMT

I'll check into this and get back with you.

Re: New SAS Library Errors

Sue_SAS — Fri, 18 Sep 2015 17:18:01 GMT

I contacted Tech Support and they told me to have you open a SAS Tech Support Track. They can help solve your problem.

Re: New SAS Library Errors

saspert — Fri, 18 Sep 2015 20:56:32 GMT

Thanks!

Re: New SAS Library Errors

SASKiwi — Sat, 19 Sep 2015 00:39:50 GMT

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.

Re: New SAS Library Errors

jakarman — Sun, 20 Sep 2015 07:33:44 GMT

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.

Re: New SAS Library Errors

SASKiwi — Mon, 21 Sep 2015 08:09:57 GMT

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.

Re: New SAS Library Errors

saspert — Tue, 22 Sep 2015 22:00:07 GMT

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.

Re: New SAS Library Errors

saspert — Tue, 22 Sep 2015 22:01:32 GMT

@Sue_SAS I think my supervisor has teh solution to this. I may not need a ticket at this time. Many thanks.

Re: New SAS Library Errors

SASKiwi — Wed, 23 Sep 2015 07:38:41 GMT

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:

http://support.sas.com/documentation/cdl/en/odbcdref/65036/HTML/default/viewer.htm#n1huqo74tuh90wn1vooxwjrqilgb.htm

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:

http://support.sas.com/kb/52/777.html

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"
 schema=schema-name;

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.