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.