Connection String For SAS EG to SQL Server using Windows Auth

[paulinalawson]

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

[Quentin]

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.

[paulinalawson]

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.

[Quentin]

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

[paulinalawson]

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

[SASKiwi · Accepted solution]

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

[paulinalawson]

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

[SASKiwi]

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;

[AlanC]

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;

[paulinalawson]

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

[AlanC]

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.