topic Re: Using SAS FEDSQL and DS2 against SAS ACCESS/ODBC connections in SAS Programming

Using SAS FEDSQL and DS2 against SAS ACCESS/ODBC connections

gra_in_aus — Mon, 22 Jan 2018 07:05:05 GMT

Hi All,

I am unable to use SAS FEDSQL and DS2 against SQL Appliances that have been connected to using SAS ACCESS/ODBC. I keep getting ERROR: TKTS initialization failed. We don't have the Federation Server licence, hoping it is not required.

Many thanks

Re: Using SAS FEDSQL and DS2 against SAS ACCESS/ODBC connections

Kurt_Bremser — Mon, 22 Jan 2018 13:14:41 GMT

Did you already do a google search for "ERROR: TKTS initialization failed"? If not, there might be hints in the quite numerous results.

Re: Using SAS FEDSQL and DS2 against SAS ACCESS/ODBC connections

gra_in_aus — Mon, 22 Jan 2018 22:22:44 GMT

Google doesn't provide any detailed information. The basic examples that I have used for FEDSQL and DS2 work when using SAS data on the server. However, it fail with the TKTS error. I have noticed that the TKTS error seems to be very general and not specific. I am so used to specific error messages in the SAS log. DS2 and FEDSQL error messaging needs to be improved.

Note: Using SAS 9.4m3 and SAS/ACCESS to ODBC to connect to a Postgresql Database.

Re: Using SAS FEDSQL and DS2 against SAS ACCESS/ODBC connections

ChrisBrooks — Tue, 23 Jan 2018 01:49:28 GMT

You shouldn't need a Federation Server licence for this - I suspect there may be something wrong with your ODBC connection.

Are you able to successfully connect and run some "old school" SAS i.e. not DS2 code against the data source using the same connection string?

Re: Using SAS FEDSQL and DS2 against SAS ACCESS/ODBC connections

gra_in_aus — Tue, 23 Jan 2018 07:21:40 GMT

Hi Chris,

I have successfully ran PROC SQL and DATA steps against the ODBC connection without issue, just the DS2 and FEDSQL giving problems. As mentioned I know the FEDSQL and DS2 code that I have ran is good as I changed the input and out put libraries to SAS locations and it works really well.

Forgot to mention that SAS is being ran on Red Hat Linux in AWS and using a AWS RDS Postgresql Database.

ODBC connection details I have set are:

[pos-server-name]
Driver = PostgreSQL
Description = PostgreSQL Data Source
Servername = xxxxxxxx-server-location-xxxxxxxx
Port = 5432
Protocol = 7.4
Database = riskindex_poc
SSLMode = require
SSLKeyFile = /apps/unixodbc/etc/rds-combined-ca-bundle.pem
MaxLongVarcharSize = 32767

Many thanks

Re: Using SAS FEDSQL and DS2 against SAS ACCESS/ODBC connections

ChrisBrooks — Tue, 23 Jan 2018 11:28:52 GMT

That seems odd - I think your best bet would be to raise a track with SAS Support

Re: Using SAS FEDSQL and DS2 against SAS ACCESS/ODBC connections

SylviaPowell — Wed, 14 Nov 2018 23:01:16 GMT

FedSQL and DS2 use a PostgreSQL TKTS driver to access PostgreSQL. This driver is available with SAS/ACCESS for PostgreSQL.

Re: Using SAS FEDSQL and DS2 against SAS ACCESS/ODBC connections

gra_in_aus — Thu, 15 Nov 2018 08:54:52 GMT

Unfortunately, not a solution unless SAS/ACCESS to Postgresql is actually licenced. There should be a way to utilise SAS/ACCESS to ODBC.

Re: Using SAS FEDSQL and DS2 against SAS ACCESS/ODBC connections

Kurt_Bremser — Thu, 15 Nov 2018 09:01:55 GMT

What did SAS technical support have to say about the issue?