cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Want the oppurtunity to discuss enhancements to PTC products? Join a working group! X

Issue while Connecting MS SQL Server with Thingworx

Harsha616
9-Granite

Issue while Connecting MS SQL Server with Thingworx

I am trying to connect SQL Server (Installed on my laptop) with ThingWorx (manufacturing APP). I am facing this error when trying to execute the service. I am using Windows authentication to log into the SQL Server so I am not setting any Username and Password in the Configuration of Thing.

14 REPLIES 14
slangley
23-Emerald II
(To:Harsha616)

Hi @Harsha616.

 

When you say you are trying to connect SQL Server with ThingWorx, are you saying you are trying to configure SQL Server as a data provider?  If so, take a look at the Relational Database Connectors available on PTC Marketplace.

 

You also may need to run SQL Server in mixed authentication mode, and set up a SQL Server user for accessing your data.

 

Regards.

 

--Sharon

Hi slangley,

Thanks for your reply, I have downloaded the MSSQL and JDBC connectors from the PTC Marketplace and imported them using composer. I also tried creating a new user in MS SQL Server still getting the same error. Sharing the configuration details below for your reference.

I also tried changing the authentication mode to "SQL Server Authentication" but still getting error.

 

JDBC Driver Class Name: com.microsoft.sqlserver.jdbc.SQLServerDriver

JDBC Connection String: jdbc:sqlserver://localhost;databaseName=Coffee

connectionValidationString: SELECT GETDATE()

Database User Name: TWAdmin   ---(Created a user in SQL Server)

slangley
23-Emerald II
(To:Harsha616)

Hi @Harsha616.

 

Your connection string is missing the port.  The default for SQL Server is 1433 but, of course, this can be changed so you will need to validate for your implementation.

 

Regards.

 

--Sharon

Hi Sharon,

 

I tried the way as suggested but no luck.

 

JDBC Connection String: jdbc:sqlserver://localhost:1433;databaseName=Coffee

 

Was there any limitation with Manufacturing App to connect with local Databases (SQL Server) or do I need to check on any permissions/authentications?

slangley
23-Emerald II
(To:Harsha616)

Hi @Harsha616.

 

Once you added the port, did the error message change?  If so, please provide the error or attach the log.

 

I would recommend testing with the Administrator account to rule out a permission issue.

 

Regards.

 

--Sharon

Hi Sharon,

 

I added the port and still facing with same error which I shared earlier, sharing the Log Message for your reference.

 

I logged in as Administrator only.

 

Thanks

Harsha

Hi Sharon,

 

I added the port and still facing with same error which I shared earlier, sharing the Log Message for your reference.

 

I logged in as Administrator only.

 

Thanks

Harsha

slangley
23-Emerald II
(To:Harsha616)

Hi @Harsha616.

 

Did you notice this message in the logs:

 

Error Getting Tables : Login failed for user '',

 

It looks like there is an issue with the credentials you are using for logging in to the database.

 

Regards.

 

--Sharon

slangley
23-Emerald II
(To:slangley)

Hi @Harsha616.

 

Have you found a solution to your issue?  If so, and one of the previous posts helped to resolve it, please mark the appropriate one as the Accepted Solution.  If you found a solution not posted here, please post it and mark it as the Accepted Solution for the benefit of others with the same issue.

 

Thank you for using our Community!

 

Regards.

 

--Sharon

Hi Sharon, Sorry for the late reply.

 

I checked with my user credentials and I confirm I don't have any trouble logging in and I am not sure why is that giving that error. As I already mentioned earlier that I am working with trial version of ThingWorx (Manufacturing App) I think ThingWorx is limiting it from connecting to the databases due to some limitations.

 

slangley
23-Emerald II
(To:Harsha616)

Hi @Harsha616.

 

We tested this and encountered the same issue you are experiencing.  It's my understanding that connectivity to external sources should be supported even under the trial versions.  Therefore, I am opening an internal case with our R&D team and will post an update here once we have more information.

 

Regards.

 

--Sharon

slangley
23-Emerald II
(To:slangley)

Hi @Harsha616.

 

After further testing, we discovered an error in our connect string.  Once corrected the connectivity was successful, therefore I have closed the internal ticket.  Please provide the additional artifacts requested by @thingw0rxgenie so we can investigate further.

 

Regards.

 

--Sharon

I tried the test that you're doing, and I'm able to connect. Is it possible that your problem is the Service you created to test?

 

Are you able to show your configuration screen and the JavaScript/code used to connect?

Adding screen shots of my configuration and Service configuration for those coming to this topic. I created the thingworx database and the testdata table for testing purposes.

 

You can use SELECT 1 for MS SQL as a query alternate without having to create any databases/tables (there should be a default database created when you created the server. this can be used as the connection configuration).

 

Note: The return type of the Services are InfoTables without DataShapes.

Top Tags