Hi All,
I am trying to access a Microsoft SQL Server Database from SAS using SQL Server Authentication. I have tried this syntax below with no luck. I was wondering if there is a different sytanx to use?
/* SQL Server Authentication reference: " https://support.sas.com/techsup/technote/ts765.pdf" */
LIBNAME SQL ODBC noprompt= "dsn=sqlsrv; uid=; pwd=; wsid=d17117";
Thanks!
JP
Is your user account stored separately in SQL Server or are you authenticating with your OS account - usually called Windows Authentication if your SAS server runs on Windows? If it is the latter then something like this should work:
proc sql;
connect to odbc (noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;");
create table Want as
select * from connection to odbc
(SELECT *
FROM [MyDatabase].[MySchema1].[MyTable]
)
;
disconnect from odbc;
quit;
My user account is stored separately in SQL Server. I've attached a screenshot of the sql log in. Thank you!
If you still prefer to define everything in code and avoid the ODBC Adminstrator then this should work:
libname sqlsrvr odbc noprompt = "server=MyServerName;DRIVER=SQL Server;uid=myusr1;pwd=mypwd1;";
Set it up in your DSN set up (Control Panel/ODBC Connections) rather than SAS and then use a 'plain' connection string.
I find this method easier to manage and then my password and uid are not stored in code anywhere.
LIBNAME SQL ODBC DSN=’myserver’ schema=dbo;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.