BookmarkSubscribeRSS Feed
saspert
Pyrite | Level 9

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. 

9 REPLIES 9
Sue_SAS
SAS Employee

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

Sue_SAS
SAS Employee

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

saspert
Pyrite | Level 9

Thanks!

saspert
Pyrite | Level 9

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

SASKiwi
PROC Star

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. 

jakarman
Barite | Level 11

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.
    

---->-- ja karman --<-----
SASKiwi
PROC Star

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. 

saspert
Pyrite | Level 9

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
SASKiwi
PROC Star

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.

 

 

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

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.

Discussion stats
  • 9 replies
  • 1938 views
  • 0 likes
  • 4 in conversation