Use Credential Manager to host SQL authentication

Great discussion on a non-trivial topic. Here are my two cents.

I agree that Integrated Security is a great approach, and it is my personal preference. I also like to always use Stored Procedures instead of table-direct queries, since this allows a more operationally oriented role-based security model in the database instead of managing permissions in the storage schema. For instance, one may grant access to a procedure that updates only one column instead of granting full write access to the entire table. Stored Procedures are also parameterized by default, and parameterized queries is a good pattern for reducing the risk of SQL injection vulnerabilities.

There are, however, cases where Integrated Security cannot be used. In some scenarions the database may not be configured to support Windows or Active Directory authentication. It may also, in some cases, be desireable to access content with a different user account than the one executing the HMI/SCADA runtime.

To summarize, I prefer to use Integrated Security, but I agree that the SQL script library should also be able to work with the secure credentials store. I don't think that it does today, so I will have it confirmed and see if we can act upon it for a future version.

Hi rickard.norin  Can you please share how are you using the stored procedures to get values from SQL insted of queries to tables? I want to run scripts in system platform to connect to SQL stored procedures and get the results, but I can't find a way to do that.

In Application Server and Industrial Graphics, then the best way would be to use the SQLData Script Library. 

when setting up the command you can specify if you want to use an sql statement or use a stored procedure.

Command = Connection.CreateCommand("MySP", aaDBCommandType.storedProcedure, TRUE); 

and then set the parameters

Command.SetCharParameterByName("@TankName", Me.TagName, aaDBParameterDirection.Input, 100);

The documentation has examples to use, both sync and async execution.

SQLScript.pdf

C:\Program Files (x86)\ArchestrA\Framework\Docs\1033\SQLScript.pdf

Thanks Richard for answering.

That's how I would do it as well and I always use the ExecuteAsync() patterns. It may require a second script to check for the completion of the query and catching the result, but async invocation of SQL queries, OPC UA Methods, REST APIs, etc. has some nice benefits both in graphics scripting and in object scripting.

In graphics, async execution of the query facilitates implementation of a "please wait, loading..." message or an animated progress indicator to be displayed while the query is executing, instead of freezing the UI until the query has completed.

In objects, async invocation tends to be more efficient compared to making a synchronous invocation in an async script. This is because async scripts are constrained to a limited thread pool while async invocations lack that kind of limit and therefor scale better. 

Hi rickard.norin  and Richard Köhlerstrand  does stored procedures work with Intouch as well ? I found SQL query in Intouch quite difficult to do. Any easy way to SQL query to pass data into a tag or UDT ?

Thanks Richard and Rickard! these were both very insightful for me. I have been doing some really cool stuff with System Platform and it seems like there is so much more that can be done. :)

Thanks Richard and Rickard! these were both very insightful for me. I have been doing some really cool stuff with System Platform and it seems like there is so much more that can be done. :)