- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.)
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content