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
Tags:
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
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...
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.....
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
Neuron ESB Product Support Forums and Communities
© 2024 Created by Neuron Admin. Powered by