Configuring the ADO Service for SQL Server DB connection For ThingWorx
This post will cover the challenges I've had while going through the setup of .NET SDK based ADO Service for SQL Server DB Connection. I'll be starting from the scratch on setting up the service for this to present full picture on the setup.
An ActiveX Data Object service allowing connection to a Microsoft database source e.g. MS SQL Server, MS Excel or MS .NET application to the ThingWorx platform. It is based on the ThingWorx .NET SDK.
Installing ADO Service
Let me begin by saying this is just a summary, in a crude way of course, of ThingWorx Edge ADO Service Configuration Guide. So when in doubt it's strongly recommended to go through the guide,also provided together with the downloaded package.
I'll be using the ThingWorx ADO Service v5.6.1, most recent release, for the purpose of this blog. Depending if you are on x86 or x64 Windows navigate to the C:\Windows\Microsoft.NET for accessing the InstallUtil.exe
You'll find the above specified file under following two locations, use the one that applies to your use case.
i) For x64 : C:\Windows\Microsoft.NET\Framework64\v4.0.30319
ii) For x86 : C:\Windows\Microsoft.NET\Framework\v4.0.30319
1. Copy over the desired InstallUtil.exe to the location where you have unzipped the ADO Service package, the one downloaded above. e.g. I've put mine at C:\Software\ThingWorxSoftware\ADOService\
2. Start a command prompt (Windows Start Menu > Command Prompt) and execute the InstallUtil.exe ThingWorxADOService.exe
3. This should create a service and some additional info in the \\ADOService folder in the form of InstallUtil.InstallLog
4. Check the log for confirmation, you should see something similar
Running a transacted installation.
The Commit phase completed successfully.
The transacted install has completed.
5. In Windows Explorer navigate to the folder containing all the unzipped files, and edit the AdoThing.config
6. For this blog I've security disabled, though obviously in production you'd definitely want to enable it
7. Configure the ConnectionSettings as per your requirement (refer to the guide for more detail on settings), below I'm noting the settings that will require configuration in its most minimum form (I've also attached my complete AdoThing.config file for reference)
8. Configure the connection string for the SQL Server in following section, in the same file opened above
At the end of the blog I'll share some of the errors that I came across while working on this and how to go about addressing them.
Creating and connecting to Remote Database Thing
Now, let's navigate to the ThingWorx Composer and create a Thing with RemoteDatabase Template to consume the resource created above in the form of ADO Service.
I've named my thing as AdoThing while creating it in ThingWorx Composer, which matches with the ThingName used in the AdoThing.json file. If everything went through as needed you should see the isConnected = true in the AdoThing's Properties section.
Since, this is a Database thing I can now go about creating all the required services concerning the Create, Update, Delete (CRUD) operations, just like for any database for created using the RDBMS Connector.
Handling errors while setting up the ADO Service
Here are some of the errors that I encountered while setting up the ADO service for this blog:
Note: Logged in Application.log from ThingWorx platform while testing/executing the SQL service created in the ThingWorx Composer
Cause & Resolution
- The error is due to the usage of DB name in front of the table name, it's not required since the DB name is already selected in the connection String
Error 4: [O: com.thingworx.Configuration] Could not read configuration file. : Newtonsoft.Json.JsonReaderException: Bad JSON escape sequence: \S. Path 'Settings.rows.ConnectionString', line 656, position 71.
Error 4:[O: com.thingworx.ado.AdoClient] Error while initializing new AdoThing, or opening connection to Platform. : System.AccessViolationException: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. at com.thingworx.communications.client.TwApiWrapper.twApi_Connect(UInt32 timeout, Int32 retries) at com.thingworx.communications.client.TwApiWrapper.Connect(UInt32 timeout, Int16 retries) at com.thingworx.communications.client.BaseClient.start() at com.thingworx.ado.AdoClient.run()
Note: Logged in DotNetSDK-X-X-X.log
Cause & Resolution
- This error is observed when using FIPS version of the ADO Service, esp. when downloaded from the ThingWorx Marketplace
- Make sure to recheck the SSL configuration
- When not using SSL check that the x64 and x86 directories only contain twApi.dll as by default FIPS version contain two additional dlls i.e. libeay32.dll & ssleay32.dll in both x64 & x86 directories