Use Credential Manager to host SQL authentication

Hi Mathieu!
Some months back I was asking Aveva about this, unfortunately I was not able to find a working solution, so I'm very interested in engaging in this discussion. We have several customers that wants to move away from SQL Server login credentials, and use dedicated SQL AD accounts instead.
But the connection string used to connect and execute a query/stored procedure will involve using username / password in clear text in script or storing this in CustomProperties/Object Attributes witch is not ideal.

Only way around this is to use integrated security but this will use the Windows account starting InTouch, but that will not allow for separating logged in user that starts InTouch and the user connecting to SQL.

I was exploring the possibility to create a custom DLL for storing credentials (encrypted), but did not reach a conclusion on what would be the best "future proof" solution.

The Credential Manager seems to be the best viable option if it was possible to call upon it in a scenario as you describe.

Hi Mathieu VANTARD ,

I agreed with Richard Köhlerstrand .

You should always use Windows Integrated Security for SQL Server connections. Building a SQL connection string with Windows Integrated Security is straightforward.

• Create a blank text file.
• Check the file extension from "txt" to "udl" (User Data Link). You should turn off "Hide extensions for known file types" first before changing the file extension.
• Double click on the created file, which will automatically launch the Data Link Properties form.
• Select a SQL driver on the Provider tab. 
• On Connection tab
  1. Enter your SQL server name.
  2. Select Windows Integrated Security.
  3. Select the database.
  4. Test "Connection".

• Click OK
• Use Notepad to open this file.

Below is a sample of the SQL connection string built with an UDL file. You can dynamically parse in the database name.

Provider=SQLNCLI11.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=A2ALMDB;Data Source=[Your SQL Server Name];Initial File Name="";Server SPN=""

You need to configure your SQL Server's Security with a domain user group and grant the user group to access databases used at runtime. The logon user of Windows on each node should be in this user group if the node needs to access the SQL databases in your system.

Hi Jacky!

Thanks for joining the discussion.

Using the UDL approach is indeed a good way to build your connection string, and even verifying connections from a computer without the need of deploying application/Objects to perform the test.

And yes, you should always aspire to move away from SQL User Login.

But I believe our core issue remains.

In the scenario of a Client Computer (InTouch/OMI)

• If you use Integrated security, it will verify using the user that is logged in to windows.
  We have customers that have dedicated operating stations that are logged in 24/7 and the Operator logs in to InTouch/OMI only.

So there is no way of separating the security context between the Windows users by logging in.

An option here is to move the security layer to the animations, using the available security features, ($AccessLevel, IsAssignedRole etc..)

• If you want to use a different login to SQL other than the active windows user, then you have to store the username and password somewhere to call upon it in your connection string.

Some IT departments have prompted me to use a different AD User login for each database, this will then be a challenge to manage.

In the scenario of SQL Access from Objects.

If you use Integrated security, it will verify using the user that configured as AVEVA “Network Account”, and this account only, cross the entire galaxy. (The user running the aaEngine process).

The same challenge applies here, there is no way of using another account except specifying this account in clear-text somewhere.

Another common scenario here is that if you end up the need if specifying a user and password in multiple places, (different scripts), then you want to avoid the need of going in to all these places, if your account needs to be updated or the Galaxy objects are moved to a different system, that uses a different account, (Ie. Dev/Test to Prod).

I have seen solutions for this using string attributes, such as MyPlatform.SQLConnectionstring, MyCommonObject.DatabaseA.SQLConnectionsting, MyCommonObject.DatabaseB.SQLConnectionsting and so on.

But all requires the username and password to be stored in a clear text (available in Development), and of course needs to be deployed to be called upon, (Available in Runtime if you have access to Object Viewer).

I am aware of that it is exactly this what makes it such a bad practice, but then you have to revert to One account for SQL Connections when using objects, and a SQL Group for all clients, (adding this groups to ALL possible databases).

I believe this is why I (and Mathieu?) thought it was so intriguing when the Credential Manager was introduced, at first glance I thought it was a way to remove this problem, having a possibility of storing username and password in a more secure manner.

 Mathieu VANTARD  has to correct me if I'm presuming to much. 

But at least from my tests it can not be used in this way yet, but it is a good start.

You might get away with using a Windows ODBC connection and store connection properties here, calling upon the DSN, but this was more common in stand-alone InTouch solutions, and I must admit that I believe this is a legacy way of making a SQL connection, with some performance issues among other things. And it does not allow for storing Windows Authentication credentials, so you end up with SQL Accounts in the end.. 

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.