10-27-2016 04:45 PM
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";
10-27-2016 05:44 PM
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;
10-27-2016 05:53 PM
My user account is stored separately in SQL Server. I've attached a screenshot of the sql log in. Thank you!
10-27-2016 07:22 PM
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;";
10-27-2016 06:01 PM
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;