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