CODEWITHSUNDEEP
ado.netssissybase
Dave Sexton
Dave Sexton

Reputation: 11188

Sybase Authentication Issue When Updating Via SSIS

In an SSIS package I am using an ADO.Net connection with a Sybase v9 data provider to make a series of updates.

The package will fail randomly with an error message of 'Authentication violation', it is never on the same record. After a bit of research I have found out that Sybase requiers a connection_authentication string in the following format:

SET TEMPORARY OPTION connection_authentication='Company=CompanyName Plc;Application=AppName;Signature=000fa55157ed..etc'

I know the correct string for my database because I ran the following:

CALL sa_conn_properties( );

So I know the correct string to use but how do I incorporate it into SSIS. In theory it can be called as a SQL statement like so: 

EXEC SQL SET TEMPORARY OPTION connection_authentication='Company=CompanyName Plc;Application=AppName;Signature=000fa55157ed..etc'

But when used in a SQL Task this just gives an error message 'connection_authentication cannot be set from within a procedure'. Ideally I would like to incorporate the string into the connection rather than change every SQL statement.

Upvotes: 1

Views: 675

Answers (1)

Dave Sexton
Dave Sexton

Reputation: 11188

Found the answer:

In SSIS create a new ADO.Net connection manager.

For the provider select '.Net Providers\Odbc Data Provider'.

Select the radio button for Use connection string.

Enter a connection string in following format (but omitting the extra white space that I have added just for clarity):

Driver={Adaptive Server Anywhere 9.0};
uid=MyName;
compress=No;
commlinks=SharedMemory,TCPIP{Host=10.10.10.10};
disablemultirowfetch=Yes;
debug=No;
integrated=No;
autostop=No;
enginename=MyServer;
initstring=SET TEMPORARY OPTION CONNECTION_AUTHENTICATION='
    Company=MyComapny;
    application=MyApplication;
    signature=000fa552

The key part on the connection string is the initstring section where the authentication data is added.

I found the answer from the following page:

http://dcx.sybase.com/1201/en/dbadmin/running-s-3417964.html

Upvotes: 1

Related Questions