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. :)

InTouch offers two options:

1. Using the same SQL Script Library as used in System Platform. This requires that the script is placed in an Industrial Grahphics symbol. This is my favorite approach since I find it more flexible than option 2 and it has better support for stored procedures as well as a secure management of parameters for parameterized queries.
2. InTouch also has the "SQL Access Manager" which facilitates binding tags to record retrieved from databases. This feature is described in the "InTouch HMI Management Gudie". This option has partial support for stored procedures.

Thanks a lot rickard.norin 

If the stored procedure sends back a list of responses like an array, how do we achieve this into Intouch ? In System Platform we can directly put into an array by defining an attribute, in intouch i dont see array as a variable

Depends on what the acquired data will be used for.

You are correct that InTouch doesn't have array-typed tags, but there's several things one may do with an array, data set, image, XML document or any other thing that may come out of a database. 

• Populate a listbox, datagrid, or chart control with the data
• Extract bits a pieces of the data and use for visualization and animation
• Parse individual scalar items from the data, then assign these to scalar value tags.

Hi Rickard, in the above 3 instances that you have mentioned, how can we achieve this with Intouch ? I think the 3 you mentioned are the most popular things I do from SQL. 

Hi Rickard, in the above 3 instances that you have mentioned, how can we achieve this with Intouch ? I think the 3 you mentioned are the most popular things I do from SQL. 

If you put the logic for connecting to a SQL Server in a Industrial Graphic Symbol, then you can use the above mentioned SQL Script Library, there you can manage the result and use it as seen proper. 

This requires the symbol to be open to function.

If it is background functionality or that you can not use a Industrial Graphic Symbol then you need to use native InTouch SQL Function to connect and use tags for storing the data.

So in some cases a workaround would be to have 'Empty' a symbol in the header that is always open, that manages all connections and result.

Best solution for you is depending on what you are trying to achieve.

But if the target is to populate a user interface with data, such a listbox, datagrid, other controls etc. then you can for sure use the SQL Script library in your symbol, 

Thanks a lot Richard Köhlerstrand , you're a diamond 

Here is an example to start with, but as mentioned earlier, if your query takes time to finalize, then you need to start manage the connection Async, (let the script poll for completion and capture the result once it is completed) and don't forget your Command.Dispose(); !

doing so will generate a memory leak over time.

dim Connection 	as aaDBClient.aaDBConnection;
dim Command    	as aaDBClient.aaDBCommand;
'dim Transaction as aaDBClient.aaDBTransaction;
'Create a connection object with the connection string.
LogMessage("Creating connection");
'Connection = aaDBAccess.CreateConnection("Data Source=localhost;Initial Catalog=AdventureWorks;Integrated Security=true");
Connection = aaDBAccess.GetConnection("server=localhost;database=LABDB;Trusted_Connection=yes", aaDBConnectionType.Sql);
'Create a command object for the transaction object, with a SQL statement or Stored Procedure.
LogMessage("Creating a command object");
'Command = Connection.CreateCommand("Select Column1 from ...", aaDBCommandType.SqlStatement, TRUE);
Command = Connection.CreateCommand("GetData", aaDBCommandType.StoredProcedure, TRUE);
Command.SetIntParameterByName("@wait", QueryTime, 	aaDBParameterDirection.Input);
Command.SetIntParameterByName("@rc", RowCount, 	aaDBParameterDirection.Input);
Command.SetBitParameterByName("@DoError", DOError, 	aaDBParameterDirection.Input);

'Everything is ready, let's execute the transaction sync.
LogMessage("Executing transaction sync");
dim ResultCode as integer;
ResultCode = Command.ExecuteSync();
If ResultCode <> 0 Then  
	'Failed to execute transaction sync, report the reason.
	LogMessage("Got error " + ResultCode + " executing transaction sync");
Else
	If Command.ExecutionState == aaDBTransactionState.Failed Then
			Catcherror = Command.LastExecutionError;
			LogWarning("Execution error: "+Command.LastExecutionError);
			'Possibility to retry or manage error here
	elseif Command.ExecutionState == aaDBTransactionState.Completed Then
		dim Rows as integer;
		Rows = Command.RowCount;
		LogMessage("Row count returned from command is " + Rows);
		'Use other methods of script library to read data and assign to UDAs, etc.
		if Rows > 0 then
			'Manage result from query, (single row)
			dim Column1 as string; 
			Column1 = Command.GetCurrentRowColumnByName("Column1"); 
			LogMessage("Got data "+Column1);
			Sync_Column1 = Column1;
		endif;
		if Rows > 0 then
			'Manage result from query, (loop rows), 
			'in this case the SP returns two datasets, and the second one (1) has to be selected
			Command.SelectTable(1);
			'dim dt as System.Data.DataTable;
			'DataGridView1.Datasource = Command.GetDataset().Tables(1);
			LogMessage("rows found: "+Command.RowCount());
			dim j as integer;
			for j = 0 to Command.RowCount-1
				Command.SelectRow(j);
				ComboBox1.AddItem(Command.GetCurrentRowColumnByName("Rows"));
			next;
		endif;
	EndIf;
EndIf;

Command.Dispose();

Sync_GetData = false;

Thanks a lot ! I would like to see as a feature the Credentials Manager be able to use for scripting such as web browser authentication. Also Richard, I'm planning to create an Intouch backup where I would implement a Gold Class Standard of everything ( graphics, scripting, efficient methods ) from an old Intouch project upgrade. Would you by any chance have a public project that I can use in Intouch ?