Connecting to SQL Server from SAS EG running on unix server

[mnjtrana]

Hi guys,

I am using windows SAS Enterprise guide 6.1 , running on Linux server x86_64.

We wanted to access the SQL server from SAS EG itself, however we dont think we have the odbc connection setup for it.

Connection string:

[ABC_DRI]

Driver=/sso/faw/dbclinet/lib/S0451.so

Description=DataDirect 7.1.5 SQL Server Wire Protocol

AuthenticationMethod=1

BulkLoadBatchSize=1

database=abc_dri_reporting

EnableQuotedIdentifier=0

EnableBulkLoad=1

Hostname=dri02stub_abc

Portnumber=1726

libname sqlsvr1 sqlsvr noprompt="

Driver=/sso/faw/dbclinet/lib/S0451.so;

/*Description=DataDirect 7.1.5 SQL Server Wire Protocol;*/

AuthenticationMethod=1;

BulkLoadBatchSize=1;

database=abc_dri_reporting;

EnableQuotedIdentifier=0;

EnableBulkLoad=1;

Hostname=dri02stub_abc;

Portnumber=1726;

UID= fv4578;

PWD=abc_def_124;

";

Do you know if we can use the connection as DSNLESS using the username and password that we are using to login to sql server management studio on windows.

I tried using it but it didnt execute. Below is the errro :CLI Datadirect ODBC lib data source  name not found.

Also I don't have the user privileges to change odbc.ini on linux server

am i using the correct format and what could i be doing wrong? TIA

[RW9]

You need a driver of some sort, if you want to connect via odbc, then you need an odbc driver.  Login details is just part of the login process, it does not pass information back and forth, this is a driver task.

[mnjtrana]

hi @RW9

thanks for the reply. I have the driver path, please see below:

Driver=/sso/faw/dbclinet/lib/S0451.so

[RW9]

"however we dont think we have the odbc connection setup for it." - speak with your IT, you need to have the driver setup to work correctly with the database.

[mnjtrana]

This driver setup is already working for the other SQL server prod environment, we just wanted to make it work for UAT environment.

It's just the server name the database is different for UAT.

[LinusH]

Just to be clear, we are talking about connecting from the SAS server, not the EG client right (since libname is executed on the server)?

Verify that you have the SAS/ACCESS to ODBC license. And you have that module installed.

Then follow the constructions for post install configurations.

[saliyakumaw]

We have this problem of connecting SQL Server database from HP Unix server using SAS EG 7.15. We purchased Easysoft Unix ODBC SQL Server driver and installed  and configured it in HP Unix sever. Then I was able to connect to SQL Server database. You may use Easysoft Unix ODBC driver free of charge for a shorter period of time. This driver is available for both unix and Windows server. 

In order to install this driver in SAS unix server you must be a root user or have unix system administrator right  

[SASKiwi]

Why don't you use the SAS install account, often called sasinstall? It surely has the right permissions for server software installs.

[Quentin]

Suggest looking at the solution in this thread about DSNLESS connections. https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Li...

[Sajid01]

Hello @mnjtrana 
As is clear form the error message the it appears that the dsn has not been setup.
You appear to have the data direct odbc driver installed. Try with the following libname. Hopefully it should work if there are no other issues. I have taken the user,password, dsn and database values from your post. Make sure you have the actual values

libname sqlsrvr1 ODBC dsn=ABC_DRI user=fv4578; password="abc_def_124" database=abc_dri_reporting;

 If this does not work with sqlserver instead of odbc.
About dsnless connection please look at this form post

https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Li...