SAS author's tip: SAS/ACCESS interface to Microsoft SQL server

This week's SAS tip is from Frederick Pratter and his book Web Development with SAS by Example, Third Edition. The many real-world examples in the book guide lend it to being frequently excerpted on this blog! Previously featured tips include: Formatting XML documents, Configuring TCP security, and SQL pass-through.

The following excerpt is from SAS Press author Frederick Pratter and his book "Web Development with SAS by Example, Third Edition". Copyright © 2011, SAS Institute Inc., Cary, North Carolina, USA. ALL RIGHTS RESERVED. (please note that results may vary depending on your version of SAS software)

SAS/ACCESS Interface to Microsoft SQL Server

There are SAS/ACCESS interfaces for most vendor-supported databases, such as Oracle, DB2, and MySQL. For Microsoft SQL Server, however, it is necessary to use ODBC or OLE DB to connect. For ODBC, the procedure is the same as for Oracle. Using the ODBC administrator, create a System DSN using the SQL Native Client. If your server is configured correctly to allow TCP/IP remote access, you should be able to test the connection successfully. The code is exactly the same as in Example 6.6 except that you change the ODBC DSN to the data set name that you used when creating the data source, and of course provide the correct UID and password.

For OLE DB, it gets a little more interesting. There are examples of OLE DB connection strings at Usage Note 12117, “Example LIBNAME statement for accessing SQL Server data using Enterprise Guide”; see http://support.sas.com/kb/12/117.html. The problem is that unless you are running SAS on the same machine as the SQL server, your server administrator has to enable remote TCP/IP access to the database. The best advice at this point is talk to your DBA to see if they will open a TCP port to connect remotely and supply the required connection string for your site.

There are two different formats for the connection string, depending on whether you are using Windows authentication (connect to the database with the same credentials as your login) or SQL server authentication. In the former case, you don’t have to specify a user name and password, as shown next. This example uses SQL Express 2008 and the default data set is set to master: