topic Re: Accessing a Microsoft SQL Server Database from SAS using SQL Server Authentication in SAS Programming

Accessing a Microsoft SQL Server Database from SAS using SQL Server Authentication

JNPLNCE — Thu, 27 Oct 2016 20:45:23 GMT

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!

Re: Accessing a Microsoft SQL Server Database from SAS using SQL Server Authentication

SASKiwi — Thu, 27 Oct 2016 21:44:43 GMT

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;

Re: Accessing a Microsoft SQL Server Database from SAS using SQL Server Authentication

JNPLNCE — Thu, 27 Oct 2016 21:53:24 GMT

My user account is stored separately in SQL Server. I've attached a screenshot of the sql log in. Thank you!

Re: Accessing a Microsoft SQL Server Database from SAS using SQL Server Authentication

Reeza — Thu, 27 Oct 2016 22:01:45 GMT

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;

Re: Accessing a Microsoft SQL Server Database from SAS using SQL Server Authentication

SASKiwi — Thu, 27 Oct 2016 23:22:32 GMT

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;";