Community Tip - You can change your system assigned username to something more personal in your community settings. X
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.
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
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.
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.
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.
Here are some of the errors that I encountered while setting up the ADO service for this blog:
Note: Logged in DotNetSDK-X-X-X.log
- 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
Note: Logged in Application.log from ThingWorx platform
- 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
Note: Logged in Application.log from ThingWorx platform while testing/executing the SQL service created in the ThingWorx Composer
- 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
Note: Logged in DotNetSDK-X-X-X.log
- 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
Note: Logged in DotNetSDK-X-X-X.log
- 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
Example for connect Excel 2016
"rows": [
{
"ConnectionType": "OleDb",
"ConnectionString": "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\EXCEL\\Book1.xlsx;Extended Properties=\"Excel 12.0 xml;HDR=YES;IMEX=1\"",
"AlwaysConnected": false,
"QueryEnabled": true,
"CommandEnabled": true,
"CommandTimeout": 60
}
]
Thanks for adding this Chikashi Hiwatashi It's quite helpful.