topic Re: Connection String For SAS EG to SQL Server using Windows Auth in SAS Enterprise Guide

Connection String For SAS EG to SQL Server using Windows Auth

paulinalawson — Wed, 16 Aug 2023 17:41:47 GMT

Hi Everyone,

I'm trying to establish a connection to Microsoft SQL server from SAS EG using windows authentication.

Could anyone please let me know if that's possible, then how it can be achieved, like the connection string & backend config prerequisites.

SAS Management Console: 9.4 M2

SAS Enterprise Guide: 7.15

I have tried below, but receiving this ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Invalid string or buffer length

%let server = XYZ;
%let database = ABC;

proc sql;

connect to odbc as myconn
(dsn="DRIVER={SQL Server Native Client 11.0};SERVER=&server;DATABASE=&database;Trusted_Connection=yes");

create table work.mytable as
select *

from connection to myconn (

select *

from dbo.test);

disconnect from myconn;
quit;

Please let me know if needed more details.

Thanks,

Paulina

Re: Connection String For SAS EG to SQL Server using Windows Auth

Quentin — Wed, 16 Aug 2023 18:30:43 GMT

Typically when you use EG, your SAS code is running on a remote SAS server, not your local Windows PC.

That means the ODBC connection needs to be defined on the SAS server, and use ODBC drivers that are on server. Typically this would be configured by a SAS server administrator, not an individual programmer. So as a first step, you might want to ask your admin or other colleagues how they connect to SQL server databases from the SAS server.

Re: Connection String For SAS EG to SQL Server using Windows Auth

paulinalawson — Wed, 16 Aug 2023 18:55:04 GMT

Thank you for your response!
I'm also the admin for the SAS Server, I did make sure that the ODBC connection is defined on the server and the ODBC driver that's on the server is "SQL Server Native Client 11.0"

All of our users are able to connect to the SQL server using username and password on their local machines from SAS EG, no issues on that. We want to establish same connection using Windows Auth rather than providing sql credentials.

Re: Connection String For SAS EG to SQL Server using Windows Auth

Quentin — Wed, 16 Aug 2023 18:58:13 GMT

So your SAS server is a Windows server? And they authenticate to the SAS server using their Windows authentication?

(I'm not an admin, and I've only worked on linux servers, so won't be able to help much.)

Re: Connection String For SAS EG to SQL Server using Windows Auth

paulinalawson — Wed, 16 Aug 2023 19:11:42 GMT

Yes, SAS Server is installed on Windows Server. The users authenticate to the SAS server from their local (using EG) using windows auth.

Re: Connection String For SAS EG to SQL Server using Windows Auth

AlanC — Wed, 16 Aug 2023 19:12:32 GMT

Here is what I have used directly:

libname ref '\\ABC.corp\dfsroot\BSC\SAS';
libname SQLREF ODBC NOPROMPT="server=SVDSQLASASREFERENCED;driver=ODBC Driver 13 for SQL Server;database=DB;trusted_connection=yes" STRINGDATES=NO IGNORE_READ_ONLY_COLUMNS=YES SCHEMA=DBO;

data SQLREF.Factor;
set ref.Factor ;
run;

Re: Connection String For SAS EG to SQL Server using Windows Auth

AlanC — Wed, 16 Aug 2023 19:16:22 GMT

Also, if you are server admin, I would get it working on the server using DMS and then try and get it working via EG.

Re: Connection String For SAS EG to SQL Server using Windows Auth

SASKiwi — Wed, 16 Aug 2023 20:04:19 GMT

@paulinalawson - Try hard coded syntax first. This is how we do:

libname SQLSRVR odbc noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Trusted_Connection=yes" DATABASE = MyDatabase schema = dbo;

Connection strings are very sensitive to syntax so can be very fiddly to get right.

Re: Connection String For SAS EG to SQL Server using Windows Auth

paulinalawson — Wed, 16 Aug 2023 22:05:50 GMT

Thank you very much, Alan! That's helpful.

Re: Connection String For SAS EG to SQL Server using Windows Auth

paulinalawson — Wed, 16 Aug 2023 22:07:00 GMT

Thank you very much! creating the library first and then query from the library works.

Re: Connection String For SAS EG to SQL Server using Windows Auth

SASKiwi — Thu, 17 Aug 2023 02:12:39 GMT

You can also use a LIBNAME definition in a PROC SQL CONNECT statement:

libname sqlsrvr noprompt='<sql connection string>';

proc sql;
  connect using sqlsrvr;
  select * from ......;
quit;