DATA Step, Macro, Functions and more

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

Reply
New Contributor
Posts: 2

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

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

Super User
Posts: 3,252

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

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;
New Contributor
Posts: 2

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

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

 

 


SQL_Server_Authentication.PNG
Super User
Posts: 3,252

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

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

 

Super User
Posts: 19,772

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

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; 
Ask a Question
Discussion stats
  • 4 replies
  • 223 views
  • 0 likes
  • 3 in conversation