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

[MA8731174]

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]

Hi @Surya_Tiwari do you have any idea?

[Constantine]

Hello, try to add “ ;integratedSecurity=true” to your connection string.

Regards,

Constantine

[MA8731174]

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

[MA8731174]

Connection String      :        jdbc:sqlserver:jamal.com;Database=cvs_absorber_test;integratedSecurity=true;

[Constantine]

Hmm, this seems correct. What error do you get? In my understanding, in integratedSecurity mode MS SQL JDBC driver will try to authenticate the user who runs Tomcat process, so you need to ensure the it can access the database. If you run it as a Windows service, make sure this service executes under your "real" user, or just run it "manually".

You might also want to try removing username and password, just to see if that helps.

Finally, make sure sqljdbc_auth.dll is somewhere on the PATH, which Tomcat uses.

[Rocko]

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

[MA8731174]

@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]

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.