Architecting, installing and maintaining your SAS environment

New SAS Library Errors

Reply
Super Contributor
Posts: 268

New SAS Library Errors

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. 

SAS Employee
Posts: 8

Re: New SAS Library Errors

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

SAS Employee
Posts: 8

Re: New SAS Library Errors

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

Super Contributor
Posts: 268

Re: New SAS Library Errors

Thanks!

Super Contributor
Posts: 268

Re: New SAS Library Errors

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

Super User
Posts: 3,111

Re: New SAS Library Errors

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. 

Valued Guide
Posts: 3,208

Re: New SAS Library Errors

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 Smiley Happy

 

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.
    

---->-- ja karman --<-----
Super User
Posts: 3,111

Re: New SAS Library Errors

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. 

Super Contributor
Posts: 268

Re: New SAS Library Errors

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. 


listofsasservers.gif
Super User
Posts: 3,111

Re: New SAS Library Errors

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#n1huqo74tuh90wn1v...

 

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.

 

 

Ask a Question
Discussion stats
  • 9 replies
  • 693 views
  • 0 likes
  • 4 in conversation