Tables not shown up

[Analysts12]

odbc drivers are installed, as all other SQL libraries are running fine.

We got Active directory domain user Service account created for this new library and Database team updated details, now if we try to create library on Management console, tables are still not visible.

[Sajid01]

Then you must check the odbc.ini file.
Looks like you have Data Direct drivers. Please have a look in the documentation.

Prima facie SAS and the database are not able to communicate.
Typically it is the odbc configuration one has to look into.

[Sajid01]

Also please make sure you have updated the sasenv_local file with appropriate paths

[Analysts12]

Hi All,

so we checked the odbc.ini file and entry was no created for this SQL Library.
We updated all the details.
Still tables are not visible and we are receiving the same error : "ERROR: CLI error trying to establish connection: [SAS][ODBC SQL Server Wire Protocol driver]Connection refused. Verify Host Name
and Port Number."

[Sajid01]

As I pointed earlier sasenv_local also needs to be updated.
Once done I would restart SAS.

[Analysts12]

Hi Everyone,

So we tried all the steps:
Issue: SQL Library creation
Error: CLI Error

Steps performed:
> we got windows service account created
>DB Team updated the same on SQL
>we created the library on console, updated odbc.ini entry and updated sasenv_local.
>re-started SAS Services.

still same error: ERROR: CLI error trying to establish connection: [SAS][ODBC SQL Server Wire Protocol driver]Connection refused. Verify Host Name
and Port Number.

[Sajid01]

Sorry to hear that inspite of so much effort you are not able to connect.
There is something wrong some where and that is going undetected.
I would recommend the following

1.From a desktop, using SQL Server Management Studio try to connect to the database using the same credentials that you are using in SAS.

2. If you are able to connect successfully, then use the same information (hostname, port number etc). in your odbc configuration. If sql server is connecting through a non default port, then port number may be needed.
Look for details in the vendor documentation
3.Try establishing a connection using libname statement and minimal set of options for example

LIBNAME mylibref ODBC DSN=server_name USER=myuser PASSWORD=mypassword SCHEMA=syourschema;

4.If you are able to connect in step(1) but not is step (3) then I would prefer to reinstall the driver.

[Analysts12]

regarding step 1, SQL Team is unable to login to a service account. they only have permission to login to SQL authentication.

Our account was created on Active directory
any other way if we can check from database level?

[Sajid01]

Good.
If SQL team is able to connect to the SQL server database  using SQL account, then use this account information in the libname statement and try creating a library..
Please make sure that hostname information is correct and if you are using a non default port then that information should be there in the odbc configuration.

If possible test your connection from the Linux command line line using isql .
If things don't work and you want help on this community please let us know your operating system and the odbc driver you are using

[SASKiwi]

The "Connection Refused" error indicates that you might have a server firewall rule blocking SAS to database server traffic. Check with your IT network people to see if this is the problem. 

[shparber_m]

I have similar issue:

I ran the following code without errors:

libname sqltmp odbc
complete="DSN=MyODBCName;UID=username;PWD=password;DATABASE=SQL_DB;SCHEMA=dbo;BULKLOAD=YES"
;

I do see the library under SASApp server, but i am not been able to expand it and see the tables.

When I specify it under Output in a Query - it is succeeding to write to it (I see the table with data in SQL Server),

but the output table is not shown in the SAS EG process flow.

Maybe something is missing in my libname statement?

Please help

[Sajid01]

Hello @shparber_m  
I would recommend installing SQL Server Management Studio on you client machine (on the machine where you have SAS EG) and test if you are able to connect and see all your table. This step will validate credentials, authorizations etc.,

Typically, in a corporate environment resolving this type of issues is a team effort. One would need to involve the SAS Admin,  DBA,  OS Admin and Networking (for firewall) and of course desktop support (for SSMS). 

[ballardw]

You really should start your own thread. Your use case has, at best, little similarity to that of the original poster in this thread. His throws errors and involves use of SMC to connect, relates to a different ODBC connection with DATADIRECT. Also it is not clear whether the OP was using EG.

You have checked that your ODBC connection is set for your EG server to see the data haven't you? A connection on your local machine could quite likely not be configured on the EG server or has different connection properties.

[SASKiwi]

What does PROC DATASETS report, if anything?

proc datasets library = SQLTMP;
run;
quit;

If this lists all the tables in your SQL Server schema, then the problem is with the EG Server list. Note you may have to refresh the server list after assigning it for tables to show.

[Nigel_Pain]

It would be worth trying an ODBC special catalog query:

proc sql;
connect to odbc as MyODBCName (dsn="MyODBCName" user="username" password="password");
select * from connection to MyODBCName (
ODBC::SQLTables(,"dbo")
)
;
disconnect from MyODBCName ;
quit;

It should produce a list of the tables in the dbo schema. If you want the tables from all schemas then remove the contents of the brackets in the ODBC::SQLTables statement. I've found this an invaluable tool for investigating connection problems to ODBC data sources. More information in the documentation here:  

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1f29m86u65hken1deqcybowtgma.htm