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.
Pre-requisite
1. Download and install Microsoft SQL Server Express or Enterprise edition, for testing I worked with Express edition : https://www.microsoft.com/en-us/sql-server/sql-server-editions-express
2. Once installed, it's imperative that the TCP/IP Protocol is enabled in the SQL Server Configuration Manager for the SQL Server
3. Download ThingWorx Edge ADO Service from PTC Software download page
What is ThingWorx ADO Service?
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)
"rows": [
{
"Address": "localhost",
"Port": 8080,
"Resource": "/Thingworx/WS",
"IsSecure": false,
"ThingName": "AdoThing",
"AppKey": "f7e230ac-3ce9-4d91-8560-ad035b09fc70",
"AllowSelfSignedCertificates": false,
"DisableCertValidation": true,
"DisableEncryption": true
}
]
8. Configure the connection string for the SQL Server in following section, in the same file opened above
"rows": [
{
"ConnectionType": "OleDb",
"ConnectionString": "Provider=SQLNCLI11;Server=localhosts\\SQLEXPRESS;Database=TWXDB;Uid=sa;Pwd=login123;",
"AlwaysConnected": true,
"QueryEnabled": true,
"CommandEnabled": true,
"CommandTimeout": 60
}
]
9. Just to highlight what's what in ConnectionString above:
"ConnectionString": "Provider=SQLNCLI11;Server=<Machine/ClientName>\\SQLServerInstanceName;Database=<databaseName>;Uid=<userName>;Pwd=<password>;"
10. To get correct connection string syntax for different source refer to the ConnectionStrings.com
11. Save the file
12. Navigate to the windows services by opening Windows Start > Run > services.msc
13. Check for the service ThingWorx .NET ADO Client as you'll have to start it if it's set to Manual, like so in my case
Following message will be logged on successful connection in the
DotNETSDK -X-X-X.log : [Critical] twWs_Connect: Websocket connected!
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:
Error 1: com.thingworx.ado.AdoThing Cannot connect to database. : System.Data.OleDb.OleDbException: Login timeout expired
Note: Logged in DotNetSDK-X-X-X.log
Cause & Resolution:
- Service is not able to successfully reach or authenticate against the SQL Server Express DB instance
- Ensure that the TCP/IP is enabled for the Protocols for the SQL Express, as I have shared in the screenshot above
- Make sure that the username / password used for authenticating with the database is correctly provided while configuring the settings for the OLEDB section in AdoThing.config
Error 2: com.thingworx.ado.AdoThing GetTables OleDbException error : System.Data.OleDb.OleDbException
Note: Logged in Application.log from ThingWorx platform
Cause & Resolution
- This exception is thrown when user attempts to check for the available tables, while creating the service in the ThingWorx Composer
- Resolution to this is similar to that mentioned above for Error 1
Error 3: [U: SYSTEM] [O: com.thingworx.ado.AdoThing] OleDbException [code = -2147217865, message = Invalid object name 'TWXDB.DemoTable'.] executing SQL query
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[0].ConnectionString', line 656, position 71.
Note: Logged in DotNetSDK-X-X-X.log
Cause & Resolution
- This is caused due to the
"ConnectionString": "Provider=SQLNCLI11;Server=<machineNameOrIP>\SQLEXPRESS;Database=TWXDB;Uid=sa;Pwd=login123;",
- Json requires this to be escaped thus switching to
"ConnectionString": "Provider=SQLNCLI11;Server=<machineNameOrIP>\\SQLEXPRESS;Database=TWXDB;Uid=sa;Pwd=login123;", resolved the issue
- Among many other, https://jsonformatter.curiousconcept.com/ is quite helpful in weeding out the issues from the JSON syntax
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
