ODBC Connection String

Replies to This Discussion

Permalink Reply by Martin Wasznicky on May 16, 2017 at 1:52pm

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

• ▶ Reply

Permalink Reply by Moody Saint Louis on May 16, 2017 at 2:06pm

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

Attachments:

• screenshot error.docx, 160 KB

• ▶ Reply

Permalink Reply by Martin Wasznicky on May 16, 2017 at 2:24pm

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. 

• ▶ Reply

Permalink Reply by Moody Saint Louis on May 16, 2017 at 3:06pm

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.

• ▶ Reply

Permalink Reply by Martin Wasznicky on May 16, 2017 at 3:09pm

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

• ▶ Reply

Permalink Reply by Moody Saint Louis on May 16, 2017 at 3:12pm

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

• ▶ Reply

Permalink Reply by Martin Wasznicky on May 16, 2017 at 3:17pm

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. 

• ▶ Reply

Permalink Reply by Moody Saint Louis on May 16, 2017 at 3:26pm

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?

• ▶ Reply

Permalink Reply by Martin Wasznicky on May 16, 2017 at 3:40pm

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...

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

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.....

• ▶ Reply

Permalink Reply by Moody Saint Louis on May 17, 2017 at 9:27am

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