topic Re: SQL Server connectivity Issue in Administration and Deployment

SQL Server connectivity Issue

Key123 — Mon, 18 Nov 2024 18:46:26 GMT

I have a new M8 setup and we are connecting to SQL server using Access engine through provided Datadirect drivers

LIBNAME SQLSRVT SQLSVR Datasrc=MyDatasrc SCHEMA=MySchema authdomain="SQLSRV";

Authentication is successful but it does not list Schema tables in the library

I have some users connecting to SQL server using long Libname definitions:

LIBNAME SQLSRVT1 SQLSVR NOPROMPT="Driver=SAS Institute, Inc 8.2 SQL Server Wire Protocol;
AuthenticationMethod=9; Database=MYDB ; HostName= MyHost\SSINST; PortNumber=Myport;
Domain=MYDOMAIN; LogonID=[username]; Password=[password];" Schema=MySchema;

Library assignment is successful and it list tables. Can you suggest where this is going wrong?

Below are the ODBC.ini entries for libname using AuthDomain

[DataSRC]
Driver=/sashome/AccessClients/9.4/SQLServer/lib/S0sqls28.so
Description=SAS Institute, Inc 8.2 SQL Server Wire Protocol
AuthenticationMethod=9
Database=MyDB
HostName=MyHost\SSINST
PortNumber=MYPORT
EncryptionMethod=6
ValidateServerCertificate=0
Trusted_Connection=Yes
QuotedId=yes
IntegratedSecurity=false
TrustServerCertificate=yes
CryptoProtocolVersion=TLSv1.2, TLSv1.1,TLSv1, SSLv3, SSLv2
SSLLibName=/usr/lib64/libssl.so.1.1
CryptoLibName=/usr/lib64/libcrypto.so.1.1

Re: SQL Server connectivity Issue

SASKiwi — Mon, 18 Nov 2024 19:10:12 GMT

You can only list tables in an SQL Server database via a SAS LIBNAME if you provide correct database and schema names. Without these set the LIBNAME will assign correctly at the server level but can only be used in a limited way like with SQL Passthru for example. Here is one way:

libname sqlsrvr odbc noprompt = "server=MyServerName;DRIVER=ODBC Driver 18 for SQL Server;Trusted Connection=yes;";

proc sql;
 connect using sqlsrvr;
  create table Want  as 
  select * from connection to sqlsrvr
  (SELECT *
   FROM [MyDatabase].[MySchema1].[MyTable] A
   LEFT JOIN [MyDatabase2].[MySchema2].[MyTable2] B
   ON A.Key1 = B.Key1
   )
  ;
quit;

Re: SQL Server connectivity Issue

LinusH — Tue, 19 Nov 2024 11:21:29 GMT

I'm a little bit confused by these statements:

"... it does not list Schema tables...".

What do you mean by schema tables?

"... and it list tables...".

What tables gets listed?

Please provide examples of what you see in SAS, and what's available in in SQL Server.