topic Re: Connecting to SQL Server from SAS EG running on unix server in SAS Enterprise Guide

Connecting to SQL Server from SAS EG running on unix server

mnjtrana — Tue, 10 Apr 2018 08:49:15 GMT

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

Re: Connecting to SQL Server from SAS EG running on unix server

RW9 — Tue, 10 Apr 2018 08:00:17 GMT

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.

Re: Connecting to SQL Server from SAS EG running on unix server

mnjtrana — Tue, 10 Apr 2018 08:08:16 GMT

hi @RW9

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

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

Re: Connecting to SQL Server from SAS EG running on unix server

RW9 — Tue, 10 Apr 2018 08:14:33 GMT

"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.

Re: Connecting to SQL Server from SAS EG running on unix server

mnjtrana — Tue, 10 Apr 2018 08:16:54 GMT

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.

Re: Connecting to SQL Server from SAS EG running on unix server

LinusH — Tue, 10 Apr 2018 18:09:50 GMT

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.

Re: Connecting to SQL Server from SAS EG running on unix server

saliyakumaw — Sun, 21 Aug 2022 01:19:52 GMT

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

Re: Connecting to SQL Server from SAS EG running on unix server

SASKiwi — Sun, 21 Aug 2022 02:37:33 GMT

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

Re: Connecting to SQL Server from SAS EG running on unix server

Quentin — Sun, 21 Aug 2022 02:46:35 GMT

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-Linux/m-p/349245#M10386

Re: Connecting to SQL Server from SAS EG running on unix server

Sajid01 — Sun, 21 Aug 2022 13:37:01 GMT

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-Linux/td-p/349245