Skip to main content
M
MA873117416-Pearl
16-Pearl
December 12, 2024
Solved

How to configure windows native authentication that uses AD Domain to authenticate user for MSSQL DB

  • December 12, 2024
  • 3 replies
  • 3349 views

hello community,

  We dont have any username or password for our ms  sql company database, we use windows native authentication which uses Active Directory Domain to authenticate the user. How can i connect our microsoft SQL database ? I am unable to connect as i have nothing to write in database username and password. Please have a look on the picture below and let me know please how can i connect my sql database with thingworxs.....

 

MA8731174_0-1734002692827.png

 

Best answer by Rocko

You could use an condition on your qualityCheckTimestamp to prefilter and reduce the amount of rows that need to be accessed, e.g. if you know the last 100 rows are always in the last 24h you could add that condition to the query. I agree with @VladimirRosu_116627, you need a second table for that. With the current way, you are putting a huge load on the db again and again. Whenever you enter something into the large table, a trigger could enter the same data into a second small table which you monitor to have only 100 rows.

But this is beyond any relation to TWX, you might get more specific advice in a db/mssql forum.

3 replies

M
MA873117416-PearlAuthor
16-Pearl
December 12, 2024

Hi @Surya_Tiwari do you have any idea?

C
Constantine18-Opal
18-Opal
December 12, 2024
Hello, try to add “ ;integratedSecurity=true” to your connection string.

Regards,
Constantine
    M
    MA873117416-PearlAuthor
    16-Pearl
    December 12, 2024

    thats already added in the end of connection string...

    Rocko
    Rocko19-Tanzanite
    19-Tanzanite
    December 12, 2024

    Please also check https://www.ptc.com/en/support/article/CS433191 and the used version of the JDBC driver, there was a breaking change.

      M
      MA873117416-PearlAuthor
      16-Pearl
      December 13, 2024

      @Constantine and @Rocko  Thanks for your replies. I ll try it out and update you whats a response. At the meantime i have one technical question in this usecase so i have 10 customers but the schema of data is same but due to privacy every customer who comes to mashup can only see his own data so there is a drop down with customers list and every customer who comes to mashup see his own name in the drop down and see his entries on the mashup he can also search the entry by putting serial number and search the entry.

      He can also select the time duration and see the entries created during this time.

       

      Now question is as i have connected sql with thingworx for these customers because in one day they create around 200 entreis so its a massive data and when they come to mashup they see entries which are latest 100 entries i stored in thingworx datatable and with timer event i delete from it old entries and always maintain 100 latest entries so that when customer comes on mashp it will load fast and he can see entries ..... on the sql side i have just one table which stored the entries of all customers. i have indexed field also doing some purposes now question is that is it a good approach? because i was also thinking that i should only use SQL even to load latest 100 entries but its not possible why? because of the big data in the table and when i fetch latest 100 entreis its fast but order by takes a lot of time to load the 100 entries like almost 15 seconds because i did test and created 500K entries for each customer in the database... so total 5 million entries for 10 customers.... 

       

      what do you guys think is a best approach here which i am already doing with latest 100 entries on thingworx side or how?. because even i create a table for each customer on the SQL database still each table will reach to 500K entries in a year so a lot of data and as i mentioned showing latest 100 entries on the mashup is not fast.... please guide me in this.....

      Rocko
      Rocko19-Tanzanite
      19-Tanzanite
      December 16, 2024

      My understanding is you are using a DataTable to cache the last 100 entries from the SQL database table, because running the query directly against the database takes you 15 seconds.

      You can do that, but I think it shouldn't be necessary. You should try to optimize query runtime by creating an index for the SQL table using customer id and date. Create the query and check in the execution plan that the index is used. It should be much faster that 15 seconds.

        Terms & ConditionsCookie settingsAccessibility statement