Neuron ESB User Network

The Service Bus for the Connected Business

Greetings! 

I have a quick question regarding setting up an ODBC connection string to connect to a SQL server. I am trying to set up a process that will poll a SQL database every 1 min. I have filled out all the necessary properties to publish results to a topic, and have set the ODBC connection adapter to Publish Mode.

Currently I have tried using your Build Connection String functionality in Neuron, and get either of the following 2 errors when running this through my local machine as server. 

1) 2017-05-16 15:13:22.412-04:00 [30] ERROR - A general publishing error occurred in the ODBC Adapter.

THE ADAPTER WILL NOW BE DISCONNECTED.

System.Data.Odbc.OdbcException (0x80131937): ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'FORTUNESOCIETY\A2IT201137$'.
ERROR [28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'FORTUNESOCIETY\A2IT201137$'. 

It should be trying to use my Windows Authentication to log in, not sure why it's set to that user.

2)

017-05-10 12:26:13.403-04:00 [29] ERROR - A general publishing error occurred in the ODBC Adapter.

THE ADAPTER WILL NOW BE DISCONNECTED.

System.Data.Odbc.OdbcException (0x80131937): ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

 

This is when I select to put in my server credentials for UID/PWD or put in a connnection string through the Adapter Bindings with my credentials.

Can you please help direct the step-by-step way to resolve this?  Thank you.

Moody 

Views: 597

Reply to This

Replies to This Discussion

can you send us a screen shot of what you have entered in the odbc connection builder UI

Here's an attached file of the diff ways I've set up the odbc connection builder UI

Attachments:

ok...first rule of thumb, use this resoruce for building connection strings: :) https://www.connectionstrings.com/

second, always use integrated auth....trusted_connection = yes.  Usually with all connection strings, you have to specify the driver, database and auth info. integrated auth is ALWAYS more secure than passing in credentials.

third, the keywords for the connection string will often vary between drivers...so you may have to look them up. that's why the connectionstrings web site is handy..

forth, if in doubt...create a system level DSN...and use that for the ODBC Connection string. if you click the "Create" button at the top of the builder, it will launch the "ODBC data source administrator" where you can create a system dsn. then you  can use that in the connection string....like so:

 Driver = ODBC Driver 11 for SQL Server

 DSN = myDsn

If I just want to specify everything, I would use the following keywords:

Driver = ODBC Driver 11 for SQL Server
server = localhost
trusted_connection = Yes
database = SQLAdapterServiceHowTo

Where "SQLAdapterServiceHowTo" is the name of my database I need to connect to. The server keyword is the name of the sql server.

in both cases I have integrated auth.  Now...if I want to run the adapter endpoint under a different credential than that of the esb service, I can go into the Neuron ESB explorer, Security section and create a new Windows Credential. After its created, i can go to the adapter endpoint and assign the Credential to the Run as property.  

Save everything...and now the adapter should be running under the context of the credentials.  if its now, check the event log.  The user the esb service is running under should be configured with delegation rights...so it can impersonate. 

Hey Marty,

Thanks for your help. Unfortunately, I've already done alot of the steps you've specified.

I have been using that site to build the connection strings.  And in most cases I have been using trust_connection = yes. When I would Test Connection, Neuron would indicate that the connection was successful connection, however, it would not be running under my credentials. All of my tests were using integrated auth instead of passing in my credentials, but because of the error, I figured that the bus would need me to pass my credentials manually.

I've tried creating a system level DSN a number of times and would get the second error I mentioned, so I think what i'm missing here is how to create/assign a new Windows Credential in Neuron ESB explorer.  I'm going to try that and see if it will take.  

Thanks.

ok.. what service account are you running the ESB service under? 

Local System currently, and my ESB Server configuration is set to a local folder.

try to run the esb service using a domain user account with local admin rights and then see if that works. if that works, in production you don't need local admin privileges...you can look in the readme for the details on what permissions you  need to run under a general user account.  the key is the service account needs impersonation rights. 

I see what you mean;

Thank you for clarifying that point.  It appears that creating the new Windows Credentials has done the trick! Message went through and the connection is up and running. Will be keeping that key point about the service acct needing impersonation rights in mind.

While I have you here, I am curious about a step after connecting to SQL: Once I've polled a data set from a table in SQL, is there a method in Neuron that allows me to iterate and push each row within that dataset through a process/program one row at a time? 

Let's say I'd want to pull the first row of data from SQL, push it to a C# program, return a message back before moving on to the next row of data, what functionality would you recommend I focus on to set that up in Neuron?

Yes....we have a Split Process Step.

I just finished writing some new docs on this.  Here are a few older links:

http://www.neuronesb.com/neuron/Help3/Development/Samples_and_Walkt...

if you promise not to forward, I'll privately send you my first draft of the new Business Process Development documentation.....

Hey Marty,

Thank you for that info. I promise not to forward! :)

I really appreciate your help. I'm going through that documentation now.

Thanks again!

Best regards,

Moody 

RSS

Neuron ESB Product Support Forums and Communities

Latest Activity

Anupama Nair posted a discussion

Marketo Adapter Invalid Token

Hi,We are using the Marketo adapter to push account updates to Marketo. It works well for some time then starts failing with Invalid Token unless restarted. Is there a configuration that can be done so it can auto refresh the token when required?Thanks!See More
Nov 6, 2023
Sayantini Basak posted a discussion

Maximum payload size(REST API) for requests interfacing to NeuronESB

I am new to Neuron ESB and in our current scenario,We need to process batch transactions comprising of ~1000 records and send them to Neuron ESB for further processing. I would like to understand what is the maximum size of payload that can be transferred using REST interface to Neuron ESB.See More
Jul 22, 2022
Profile IconRobert E Dunie and Sayantini Basak joined Neuron ESB User Network
Apr 28, 2022
Profile IconDayanand, Frederic C, Steffen Greve-Oksfeldt and 1 more joined Neuron ESB User Network
Mar 16, 2022
Profile IconCam Vong and Mitja Luznar joined Neuron ESB User Network
Jan 27, 2022
Profile IconWill Hitzges, Chad Parsons, michael larsen and 4 more joined Neuron ESB User Network
Jun 11, 2021
Anupama Nair posted a discussion

ODBC stored proc polling with temporary tables

We have set up an ODBC adapter to poll a stored proc.We found that if the stored proc has a temporary table defined the rows returned are always 0.Any idea why this would be and what we can do to get around it?See More
Dec 14, 2020
Prasanth Kharade is now a member of Neuron ESB User Network
Dec 30, 2019

Badge

Loading…

© 2024   Created by Neuron Admin.   Powered by

Badges  |  Report an Issue  |  Terms of Service