Steps to register Microsoft SQL Server Library (no ODBC library) in SAS 9.4

[dobby]

@AnandVyas Thank you but I've tried those steps, making the adjustments for the MS SQL Server, and still no success. The odd thing is that there is no documentation that I have found that speaks specifically to MS SQL Server.

When I get to the connection properties page of the New Server Wizard, what would I input for the Datasrc field to reflect the MS SQL Server and the specific database on that server that I want to connect to?

[AnandVyas]

I think you will have to perform these steps in order to create DSN for MS SQL Server.

SAS/ACCESS Interface to Microsoft SQL Server
Starting with the third maintenance release for SAS 9.4, the installation of SAS/ACCESS Interface to
Microsoft SQL Server includes a Progress DataDirect Microsoft SQL Server Wire Protocol ODBC
driver component.
You can use the ODBC administrator to configure a Data Source Name (DSN) to access a Microsoft
SQL Server database. If multiple users may use the same workstation to access the Microsoft SQL
Server database, you can create it as a system DSN instead of a user DSN.
Take the following steps to create a data source for Microsoft SQL Server database access:
Configuration Guide for SAS 9.4 Foundation for Microsoft Windows for x64
17
1. Click Run Data Sources (ODBC) in the Administrative Tools section of the Control Panel.
2. Click the User DSN or System DSN tab, depending on the number of users.
3. Click Add, and select SAS ACCESS from the pop-up window.
4. Enter any name in the Data Source Name field. This name will be used by SAS to identify
this database connection.
5. Enter a Description for this connection.
6. Enter the host name or IP address of the server where Microsoft SQL Server resides.
7. Enter the Port Number where the host is listening for Microsoft SQL Server connections. The
default is 1433.
8. Enter the Database Name of the Microsoft SQL Server database on the server.
9. Click Test Connection and enter your user ID and password. A test connection is initiated.
10. Click OK to close the ODBC Administrator and save the new DSN entry.
If you are using a DSN to connect, be sure to add the option EnableScrollableCursors=3 in the
Extended Options box on the Advanced tab of the DSN, or include it in the string that you use to
make a COMPLETE, PROMPT, or NOPROMPT connection to SAS.
To enable bulk loading, check the Enable Bulk Load check box on the Bulk tab of the DSN, or you
can add EnableBulkLoad=1 to the COMPLETE, PROMPT, or NOPROMPT connection string if you are
using that method to connect.

http://support.sas.com/documentation/installcenter/en/ikfdtnwx6cg/66385/PDF/default/config.pdf

Pg:22 - SAS/ACCESS Interface to Microsoft SQL Server

[SASKiwi]

@dobby  - What OS does your SAS server run on? The setup is very different on Windows compared to Unix.

[dobby]

@SASKiwi My SAS is running from Windows Server 2016.

[SASKiwi]

@dobby  - I suggest you try getting a LIBNAME working first. I'd also suggest trying the DSN-less approach and put everything in your connection string. Here is an example of what we do:

libname sqlsrvr odbc noprompt = "server=MyServerName;DRIVER=SQL Server;Trusted Connection=yes;" qualifier = "MyDatabase" schema = "MySchema";

All you need to do is replace MyServerName, MyDatabase and MySchema with yours. Once you get the LIBNAME working, you copy and paste the connection string into the Management Console Server connection properties. Please note the use of Windows Authentication (Trusted Connection=yes) to avoid supplying a userid and password. I hope this is how your SQL Server is set up.