November 29, 2023
This document offers tips for connecting to SQL server from SAS in the Windows Enclave Virtual Desktop using ODBC. Note that this solution has been validated using the Windows 10 Enclave Virtual Desktop connection to a MS Windows SQL Server with an ODBC driver.
The Microsoft ODBC Drivers for SQL Server are stand-alone ODBC drivers which provide an application programming interface (API) implementing the standard ODBC interfaces to Microsoft SQL Server.
Requirements or Prerequisites
- Access to Enclave Platform should already be granted and your workspace provisioned. If you do not have the approval to access the Enclave Platform, complete and submit the Research Access Request form.
- You should be logged on to your Enclave Windows Virtual Desktop. You cannot access the Enclave Platform resources from outside the platform.
Step 1. Create a DSN
The simplest way to make a connection from SAS is using a Data Source Name (DSN). You will need to create a DSN for the data source you are trying to connect to (i.e. the Complete PDSR Curated Data Set, COVID19 Mart, etc.)
Such connections, are created using the user's MGB credentials, and are normally used throughout an active session, as shown in the example below. Please, follow the step-by-step instructions in the document How to Create a Connection to a Data Source in Windows Enclave VDI, to create a DSN for your application.
Step 2. Creating a Database Connection from SAS Programs
Example 1. Create a Library By Submitting a LIBNAME Statement with the ODBC Engine
- Invoke SAS.
- Submit a LIBNAME statement that contains the ODBC engine in the SAS Enhanced Editor. The LIBNAME statement should be similar to the following:
Make sure you replace <DSN_Name> and <Schema> with the actual name of the Data Source Name (DSN) from Step 1 (i.e. "PDSR_Monthly") and database schema (i.e. "Analytics")
libname PDSR odbc noprompt="dsn=<DSN_Name>" schema=<Schema>; proc datasets lib=PDSR; Quit; - The previous steps confirm that the ODBC data source was created correctly and that you can access the tables in the Microsoft SQL Server.
- After you submit this LIBNAME statement, you can see your SQL Server tables in the SAS Explorer window.
- You can also submit a DATASETS procedure, as shown above, to display the tables in the SAS log.
Example 2. Run SQL queries using SQL Pass-Through for ODBC
- This next example sends an SQL query to the database server, configured under the data source name (DSN) PDSR_Monthly, for processing. The results from the query serve as a virtual table for the PROC SQL FROM clause.
-
proc sql; connect to odbc as pdsr (required="dsn=PDSR_Monthly"); select * from connection to pdsr (select getdate()); disconnect from pdsr; quit;
Limitations
Note: The above code was tested to run successfully under SAS 9.4 M6
Related References
- Connect to an ODBC Data Source (SQL Server Import and Export Wizard) - SQL Server Integration Services (SSIS) | Microsoft Docs
- Establishing Connectivity to an ODBC Database Using Microsoft Windows NT Authentication | SAS Documentation
- Working with a JDBC connection - JDBC Driver for SQL Server | Microsoft Docs
- Using the SAS ODBC Driver
- SAS® 9.4 Drivers for ODBC: User’s Guide