topic Re: Steps to register Microsoft SQL Server Library (no ODBC library) in SAS 9.4 in Administration and Deployment

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

lfabbri — Wed, 28 Nov 2018 15:15:46 GMT

Hello everyone,

I have this system with SAS 9.4 installed. I need to register a library for connection to a Microsoft Sql Server database to use in SAS Data Integration.

The system administrators have created an ODBC System DSN, which is configured to use Sql Server authentication.

I cannot use an ODBC library because the ODBC engine for SAS has not been installed or licensed.

In SAS Management Console I see it is possible to register a database server of type "Microsoft SQL Server" and a library of type "Microsoft SQL Server Library", as shown below

Following the documentation LIBNAME Statement Specifics for Microsoft SQL Server, I've registered a libref using the following command:

libname mssqllib sqlsvr
   noprompt="uid=MY_USER;
   pwd=MY_PASSWORD;
   dsn=MY_DSN_NAME;"
   stringdates=yes schema=DBO;

Then I am a bit lost. I've tried to create in SMC a database server of type "Microsoft SQL Server" specifying "MY_DSN_NAME" as Datasrc, then I've created a Microsoft SQL Server Library using this server and "mssqllib" as libref, but when I've tried to register some tables on that library I got Sql Server authentication error in the log:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 
       'sasadm@saspw'

It seems it's using the user logged to the Management Console instead of the user specified in the LIBNAME statement (MY_USER, MY_PASSWORD).

Can someone provides the required steps to properly register a Microsoft SQL Server Library on SAS? In the documentation I've found only the steps to register an ODBC library, but I cannot follow that way since the ODBC engine was not installed/licensed in this SAS installation.

Thanks for any help!

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

kiran_mk — Wed, 28 Nov 2018 15:25:56 GMT

You have the answer in the error. Login failed for the user sasadm@saspw to SQL.

Enter SQL credentials and it should work. before that clear the credentials cache in SAS Management console.

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

lfabbri — Wed, 28 Nov 2018 16:02:20 GMT

Hi, thanks for the reply.

It does not provide any prompt where to enter credentials, thus even if I clear the credentials cache I got the same error, it still executes the following statement (from the log) when I try to register tables:

1          LIBNAME mssqllib SQLSVR  Datasrc=MY_DSN_NAME SCHEMA=DBO  USER="sasadm@saspw"
1        ! PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;

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

kiran_mk — Wed, 28 Nov 2018 16:25:57 GMT

Right Click on the library you have created for SQL and select Display libname Statement,

If the libname statement is displayed as below then clear the credentials cache in Management Console.

LIBNAME mssqllib SQLSVR  Datasrc=MY_DSN_NAME SCHEMA=DBO  USER="sasadm@saspw"
1        ! PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;

Then you will be able to see an empty box for credentials, here enter your SQL login credentials, which are used for creating DSN.

Display the libname statement after you put in your credentials. Refresh the libraries in SMC and run the libname statement in SAS EG/ Base SAS it should work.....!!!

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

lfabbri — Wed, 28 Nov 2018 16:36:32 GMT

I've tried as you suggested, but even after I clear the credentials cache the Display Libname Statement command shows the same result

LIBNAME mssqllib SQLSVR  Datasrc=MY_DSN_NAME SCHEMA=DBO  USER="sasadm@saspw"
1        ! PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ;

Do I need to define the server/library in some other way?

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

kiran_mk — Wed, 28 Nov 2018 16:58:28 GMT

Execute proc setinit;run; and check if SAS/ACCESS Interface to ODBC product is included in your license.

If, Yes create ODBC server and ODBC library instead Microsoft SQL Server , Microsoft SQL Server Library.

No, you need to reach out to your SAS rep to find out license info.

--Thanks

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

kjohnsonm — Wed, 28 Nov 2018 17:01:29 GMT

Hello,
When I use this form it works for me when I have an ODBC configuration already setup and lic-ed etc. (this is all on Windows 10, with SAS 9.4 desktop loaded)

libname mydblib sqlsvr
   noprompt="uid=my_user_id;
   pwd=my_password;
   dsn=my_odbc_defined_db;"
   stringdates=yes schema=DBO;

However I see a line in the doc referenced:

DATAsrc=<'>SQL-Server-data-source<'>

specifies the Microsoft SQL Server data source to which you want to connect. For UNIX platforms, data sources must be configured by modifying the .ODBC.ini file. DSN= is an alias for this option that indicates that the connection is attempted using the ODBC SQLConnect API, which requires a data source name. You can also use a user ID and password with DSN=. This API is guaranteed to be present in all drivers.

when I try this:

libname mydblib sqlsvr
noprompt="uid=my_user_id;
pwd=my_password;

datasrc=my_domain\my_server;"
stringdates=yes schema=DBO;

libname mydblib sqlsvr
noprompt="uid=my_user_id;
pwd=my_password;

datasrc=my_domain\my_server;

dsn=my_db_needed;"
stringdates=yes schema=DBO;

or this

libname mydblib sqlsvr
noprompt="uid=my_user_id;
pwd=my_password;

datasrc=my_domain\my_server.my_db_needed;"

stringdates=yes schema=DBO;

They all fail with the same basic message:
ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
ERROR: Error in the LIBNAME statement.///////////////////////

Sorry not much help but I think you should not be trying to use the dsn option based on that doc reference. -KJ

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

kjohnsonm — Wed, 28 Nov 2018 17:50:05 GMT

If you can use the ODBC option directly, at least on win7/8/10 it’s very easy and stable, I highly recommend this rout if you can. However can see advantages if you can set it up in your code directly so you do not have to manage the ODBC per system. I do not know how at this time to automate loading these ODBC configurations on to new hardware, well without using OS imaging software that I do not have access too. -KJ 😎

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

SASKiwi — Wed, 28 Nov 2018 19:08:25 GMT

Have you been successful with just a LIBNAME statement? If not you should get this correct first before reproducing it in SAS metadata.

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

lfabbri — Thu, 29 Nov 2018 08:47:04 GMT

I don't have ODBC licensed, that's why I have to use the SAS/ACCESS interface to Microsoft Sql Server (database server of type "Microsoft SQL Server" and SAS library of type "Microsoft SQL Server Library"), but I cannot find any documentation about this type of configuration (how is this possible?), I can find only informations about ODBC or OLEDB configurations.

This is the output of proc init:

Original site validation data
Current version: 9.04.01M5P091317
Site name:    'XXX'.
Site number:  70244564.
Expiration:   15JAN2019.
Grace Period:  0 days (ending 15JAN2019).
Warning Period: 0 days (ending 15JAN2019).
System birthday:   12NOV2018.
Operating System:   WX64_SV .
Product expiration dates:
---Base SAS Software
  15JAN2019
---SAS/CONNECT
  15JAN2019
---SAS Integration Technologies
  15JAN2019
---SAS/Secure 168-bit
  15JAN2019
---SAS/Secure Windows
  15JAN2019
---SAS/ACCESS Interface to Microsoft SQL Server
  15JAN2019
---SAS Metadata Bridges for General Industry Standards
  15JAN2019
---SAS Workspace Server for Local Access
  15JAN2019
---SAS/ACCESS to Postgres
  15JAN2019

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

lfabbri — Thu, 29 Nov 2018 09:29:11 GMT

I believe I found an useful reference for registering a library using a SAS/ACCESS interface from here (section "Establishing Connectivity to an Oracle Database" page 80):

SAS® 9.4 Intelligence Platform: Data Administration Guide

This is for ACCESS/Oracle interface, but I think it applies also for ACCESS/SQLSERVER interface. I believe the key problem here is this part when creating the database server (page 82):

"Authentication domain OraAuth (You might need to create a new authentication domain. For more information, see How to Store Passwords for a Third-Party Server” in SAS Intelligence Platform: Security Administration Guide. Click New to access the New Authentication Domain dialog box. Then enter the appropriate value in the Name field and click OK to save the setting."

"Register the DBMS Server
To register a DBMS server, perform the following steps:
1 - Right-click Server Manager and select the New Server option to access the New Server wizard.
2 - Select the database server type from the Database Servers list. Click Next.
3 - Enter an appropriate server name in the Name field. Click Next.
4 - Accept the defaults for the server properties. Click Next.
5 - Specify the database vendor-specific values on the connection properties page. If the user credentials for the database are different from the credentials used to log on to SAS, then you must create an Authentication domain to store valid database credentials. For more information, see “How to Store Passwords for a Third-Party Server” in SAS Intelligence Platform: Security Administration Guide.
Click Next.
6 - Examine the final page of the wizard to ensure that the proper values have been entered. Click Finish to save the wizard settings."

I think I need to configure an Authentication Domain for SqlServer to be used in the definition of the database server in SAS.

I am trying to follow this guide How to Store Passwords for a Third-Party Server but I am stuck at point 1.b, there is no authentication domain assignment in the options tab, as shown below:

Does anyone know how to correctly configure an Authentication Domain to use for an ACCESS/SQLSERVER server/library in SAS?

Thank you for any information!

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

lfabbri — Thu, 29 Nov 2018 09:36:32 GMT

I believe I found an useful reference for registering a library using a SAS/ACCESS interface from this guide (section "Establishing Connectivity to an Oracle Database" page 80):

SAS® 9.4 Intelligence Platform: Data Administration Guide

This is for ACCESS/Oracle interface, but I think it applies also for ACCESS/SQLSERVER interface. I believe the key problem here is this part:

"Register the DBMS Server
To register a DBMS server, perform the following steps:
1 - Right-click Server Manager and select the New Server option to access the New Server wizard.
2 -Select the database server type from the Database Servers list. Click Next.
3 - Enter an appropriate server name in the Name field. Click Next.
4 - Accept the defaults for the server properties. Click Next.
5 - Specify the database vendor-specific values on the connection properties page. If the user credentials for the database are different from the credentials used to log on to SAS, then you must create an Authentication domain to store valid database credentials. For more information, see “How to Store Passwords for a Third-Party Server” in SAS Intelligence Platform: Security Administration Guide.
Click Next.
6 - Examine the final page of the wizard to ensure that the proper values have been entered. Click Finish to save the wizard settings."

I think I need to configure an Authentication Domain for SqlServer to be used in the definition of the database server in SAS.

I'm trying to follow this guide How to Store Passwords for a Third-Party Server but I am stuck at point 1.b, there is no "authentication domain assignment" in the options tab, as shown below:

Does anyone know how to configure an Authentication Domain to be used for login using ACCESS/SQLSERVER interface?

Thank you for any information!

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

kiran_mk — Thu, 29 Nov 2018 14:42:31 GMT

Here is the document on Defining Oracle library in SAS Management Console, you can use this as a reference to create a

library for SQL.

http://support.sas.com/techsup/technote/ts771.pdf

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

dobby — Thu, 27 Feb 2020 05:10:18 GMT

Did you get this resolved for MS Sql Server? I tried the steps but my library either does not show up in SAS EG or there are no tables for the ones that show up. Do you have screen shots of how you configured the Database Server and the Library from the Management Console that you could share?

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

AnandVyas — Thu, 27 Feb 2020 05:38:13 GMT

This blog has step by step instructions along with snaps on how to do that. It uses ODBC type connection, just change it to MS SQL Server type if you want for that.
https://blogs.sas.com/content/sgf/2013/02/13/registering-dbms-data-in-sas-management-console/

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

SASKiwi — Thu, 27 Feb 2020 06:24:17 GMT

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

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

dobby — Thu, 27 Feb 2020 12:45:07 GMT

@SASKiwi My SAS is running from Windows Server 2016.

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

dobby — Thu, 27 Feb 2020 12:50:41 GMT

@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?

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

AnandVyas — Thu, 27 Feb 2020 14:28:19 GMT

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

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

SASKiwi — Thu, 27 Feb 2020 20:14:39 GMT

@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.