JDBC Library for Azure Sql Server created in SAS Management Console Register Tables returns no t

[rtynd4]

I will try it again and let you know what issues we have.

[SASKiwi]

The first thing to check are firewall rules for the port(s) the ODBC driver is using. Does your SAS server run on Linux or Windows? It's a lot easier to test this on Windows as you can do this all in the Windows ODBC Administrator tool.

[rtynd4]

The install is on Solaris.

I have tried to connect using 

SAS/ACCESS Interface to Microsoft SQL Server

Is there a product SAS/ACCESS Interface to ODBC?

I created a server and library using SQL Server and get errors when trying to register tables.

In the SMC I created a server and library choosing ODBC but when I try to register tables it says there is not ODBC drivers.

[SASKiwi]

Yes, there is a product called SAS/ACCESS Interface to ODBC. You can check if you have it installed and licensed by running this:

proc product_status;
run;
proc setinit;
run;

However SAS/ACCESS Interface to SQL Server should also work. Did the SQL Server LIBNAME assign without errors or not? Please post your SAS log.

[rtynd4]

Here is the latest on this issue.

Working with SAS it was determined that there seems to be a bug in JDBC driver connection to SQL Server in that it returns an empty set of tables.

They gave me a workaround to use SAS code to register tables. 

So, created the library with settings like this below. Also, it is best to have double quotes for connection below.

URL="jdbc:sqlserver://myserver:1433;Authentication=ActiveDirectoryServicePrincipal;databaseName=MyDb;schema=MySchema;user=myuserid;password=mypw;"

DriverClass="com.microsoft.sqlserver.jdbc.SQLServerDriver"

ClassPath="/usr/sas/sashome/AccessClients/9.4/DataDrivers/jdbc/..."

The above are the settings for the connection.

For the  library selected the JDBC server created in the SMC and set the schema names and the connection. 

After that used the code editor to execute this code:

proc metalib ;
omr (library="JDBC_LIBRARY_PREPROD");
select ("MyTable") ;
report ;
run;

SAS is going to provide information about generating a log file so that it can be sent to Microsoft. 

I originally tried to use SQL Server connection but the driver does not support the AuthenticationMethod=13 so this did not work.