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

[lfabbri]

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!

[kiran_mk]

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.

[lfabbri]

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 ;

[kiran_mk]

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

[lfabbri]

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?

[kiran_mk]

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

[kjohnsonm]

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  😎

[lfabbri]

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

[SASKiwi]

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

[kjohnsonm]

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;

or

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

[lfabbri]

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 Administrati.... 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!

[lfabbri]

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!

[kiran_mk]

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

[dobby]

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?

[AnandVyas]

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/