CODEWITHSUNDEEP
sql-serversasodbc
Steve Riesenberg
Steve Riesenberg

Reputation: 21

Connecting SAS to SQL Server Database ODBC with Microsoft NT authentication with SAS on remote server

I'm trying to connect SAS to a SQL Server Database like in this post or this guide

I went through the set up of the Microsoft ODBC Data Sources DSN and am able to connect to my database through the SAS Enterprise Guide GUI with the following steps:

enter image description here

Clicking on ODBC

enter image description here

And then clicking on Machine Data Source and selecting the database I set up with windows from the list.

However, I get a performance warning when connecting in this way, and I'd like to have a pure code soluion anyway. When I try to connect to the same data source using something like:

libname mySasLib odbc datasrc='myUserDSN';

I get the following error:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver 
       specified

or when I try a SQL pass through:

proc sql;
connect to ODBC as mycon (datasrc='myUserDSN');
create view one as
select colA, colB from connection to mycon
(select colA, colB from tableInDataSrc order by colA);
disconnect from mycon;
quit;

I get a similar error:

ERROR: A Connection to the DATABASENAME DBMS is not currently supported, or is not installed at your site.

I am using SAS through a server, SAS is not installed on my local machine. My hunch is that I need to do the Microsoft ODBC Data Sources set up on the server to set up the DSN there. Is this correct? It seemed odd to me that I could connect to the data source through the Enterprise Guide GUI though, does that make a different type of connection that doesn't need to be set up on the SAS server?

It may be difficult to do the required set up on the SAS server, are there other options for connecting?

Upvotes: 0

Views: 4854

Answers (1)

AlanC
AlanC

Reputation: 584

If SAS is installed on the server, that is where the data access layer resides. See if you can connect using a simple SAS program via EG:

libname AUDIT ODBC NOPROMPT="server=SERVER01;driver=ODBC Driver 13 for SQL Server;uid=UID;pwd=****;Database=SASAR;" STRINGDATES=NO IGNORE_READ_ONLY_COLUMNS=YES SCHEMA=DBO;

Fix the ODBC driver so it matches what is on the server

Upvotes: 1

Related Questions