The SAS libname statement below shows you how to make an ODBC-connection to an SQL-server through ODBC without first having to create the ODBC-connection in the OS.
libname <Name of SAS-libname> ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<Name of server>; DATABASE=<Name of database on server>; TRUSTED_CONNECTION=yes" schema=<Name of schema in database> access=readonly;
‘TRUSTED_CONNECTION=yes‘ tells SAS, that you want to use the AD to provide the password. Remember to grant the AD-user the correct right to the data.
‘access=readonly‘ is done for precaution. If the user is only suppose to read the data then this should be set on the datasource – not on the libname.
It’s also possible to use a username and password instead of ‘TRUSTED_CONNECTION=yes‘ and AD-user priviliges.
libname <Name of SAS-libname> ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<Name of server>; DATABASE=<Name of database on server>; UID=<name of user>; PWD=<Password for user>" schema=<Name of schema in database> access=readonly;
It’s also possible to make SQL pass-through without having an ODBC-connection in the operating system (OS).
proc sql noprint;
connect to odbc (NOPROMPT="DRIVER=SQL Server; SERVER=<Your server>; DATABASE=<Your DB>; TRUSTED_CONNECTION=yes");
execute ( <Your SQL sentence> ) by odbc;
disconnect from odbc;
quit;
‘TRUSTED_CONNECTION=yes‘ tells SAS, that you want to use the AD to provide the password. Remember to grant the AD-user the correct right to the data.
It’s also possible to use a username and password instead of ‘TRUSTED_CONNECTION=yes‘ and AD-user priviliges.
proc sql noprint;
connect to odbc (NOPROMPT="DRIVER=SQL Server; SERVER=<Your server>; DATABASE=<Your DB>; UID=<Your username>; PWD=<Your password>");
execute ( <Your SQL sentence> ) by odbc;
disconnect from odbc;
quit;
It’s also possible to make a pass-through connection using an DSN created eg in the ‘Data Sources (ODBC)’ in Windows.
proc sql noprint;
connect to odbc (DSN=<Your DSN name> USER=<Your username>; Password=<Your password>);
execute (
<Your SQL sentence>
) by odbc;
disconnect from odbc;
quit;
Below is a link to an article from SAS explaining different ways of using SAS ODBC-connection directly in SAS . It stats that NOPROMT can’t be used with the SQL Server ODBC Driver, but I haven’t experienced that.
Usage Note 52777: Examples of SAS/ACCESS® Interface to ODBC LIBNAME code used to access a Microsoft SQL Server database without configuring an ODBC data source name
http://support.sas.com/kb/52/777.html