BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
geoffu8
Calcite | Level 5

Is anyone familiar with configuring a SQL Pass-through connection in SAS to a Sybase database where password encryption is enabled?

 

We are using the sample connection:

connect to sybase as link
(server="dbserver" database="dbname" schema="link" user="userid"
password="password");


This is encountering the below error.

ERROR: Connection failed. Error Code: -1640 Adaptive Server requires encryption of the login password on the network.

 

Normally, we would pass (encryptPassword=2) on the connection string. But I'm not familiar with how to pass this value to SAS connection string.

 

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
BruceT
Calcite | Level 5

This issue is caused by PasswordEncryption option on the Sybase server is turned on. I am not sure if there is another option on the Sybase connection string in SAS. However, you can fix this from the Sybase driver level.

 

On your local (or SAS server), you should have Sybase driver installed. In the driver directory, find the OCS-<XX>..\ini subdir. Locate the ocs.cfg file in the directory and add the folloing line to enable password encryption from this machine.

 

[SAS ACCESS]
CS_SEC_ENCRYPTION= CS_TRUE

 

If the ocs.cfg file does not exist, you can create this file. Additionally, you can add the default setting to turn on password encryption on the client side by adding this section in the ocs.cfg file:

 

[DEFAULT]
CS_SEC_ENCRYPTION = CS_TRUE

 

 

View solution in original post

10 REPLIES 10
SASKiwi
PROC Star

A connection string can be defined in SAS like this:

connect to sybase (noprompt = "Put your Sybase connection details here");

Inside the quotes you can specify a user/password string in the form that Sybase would understand. 

geoffu8
Calcite | Level 5

Can you be more specific? How do I define the connection string? I tried this way :

 

connect to sybase as link
  (noprompt = "Data Source=ServerName;Port=14112;Database=dbname;Uid=userid;Pwd=password;EncryptPassword=2;");

 

 

That didn't seem to work. How do I define the connection string, like an ODBC connection ?

An example of the connection string would be useful.

 

Thanks,

SASKiwi
PROC Star

Sorry I don't use Sybase but this link may help guide you:

 

http://www.connectionstrings.com/sybase-adaptive/

 

You seem to be on the right track based on this link.

patelm
Calcite | Level 5
I'm also trying to use the Sybase connection string in SAS Enterprise guide, using above code to guide me. I receive an error "Invalid option name noprompt". should I be using another option?
SASKiwi
PROC Star

Please post your code giving the error.

patelm
Calcite | Level 5

proc sql;
   connect to sybase
   (noprompt = "Data Source=xx.xx.xx.xx;Port=5000;Database=xxxx;Uid=xxx;
Pwd=xxx;");

create table xxx as
select * from connection to sybase (select  xxx from xxx);
run;

 

 

 

results in following error:

 

ERROR: Invalid option name noprompt.

SASKiwi
PROC Star

I did some research and I'm wondering if NOPROMPT is valid with Sybase as it is with ODBC, DB2, Oracle.

 

Try this anyway:

 

proc sql;
   connect to sybase
   noprompt = "Data Source=xx.xx.xx.xx;Port=5000;Database=xxxx;Uid=xxx;
Pwd=xxx;";
patelm
Calcite | Level 5

I appreciate your research.  I tried that and got these errors:

 

ERROR 22-322: Syntax error, expecting one of the following: ;, (, AS.  

ERROR 202-322: The option or parameter is not recognized and will be ignored.

 

 

with Oracle, I've successfully entered connection string data with "path="; there must be an equivalent for Sybase...

SASKiwi
PROC Star

Sorry yes I think PATH works for Oracle, NOPROMPT works with ODBC, SQL Server and DB2.

 

With Sybase I think you need to use an interface file as documented here with the INTERFACE option:

 

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#n0xcqueed8s23cn14m...

 

And here from Sybase:

 

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc35823.1572/doc/html/san13...

 

I suggest you talk to your Sybase DBA as he/she will be much more familiar with this. 

BruceT
Calcite | Level 5

This issue is caused by PasswordEncryption option on the Sybase server is turned on. I am not sure if there is another option on the Sybase connection string in SAS. However, you can fix this from the Sybase driver level.

 

On your local (or SAS server), you should have Sybase driver installed. In the driver directory, find the OCS-<XX>..\ini subdir. Locate the ocs.cfg file in the directory and add the folloing line to enable password encryption from this machine.

 

[SAS ACCESS]
CS_SEC_ENCRYPTION= CS_TRUE

 

If the ocs.cfg file does not exist, you can create this file. Additionally, you can add the default setting to turn on password encryption on the client side by adding this section in the ocs.cfg file:

 

[DEFAULT]
CS_SEC_ENCRYPTION = CS_TRUE

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 10 replies
  • 13384 views
  • 0 likes
  • 4 in conversation