Integrated authentication for SQL queries on AIMMS PRO with WebUI

When querying data from a SQL Server using an ODBC driver (using SQLCreateConnectionString() and ‘trusted_connection=yes’ for the AdditionalConnectionParameters argument), is the authentication carried out for the service account running the AIMMS PRO service on the server or for the user logged into the PRO portal on an AD-linked environment? Is there additional setup required for double-hopping? Or does it depend on whether the AIMMS PRO service account is registered on AD?

I am looking for support to build solutions where the database serving the AIMMS application is restricted for use by specific individuals and not service accounts.

Page 1 / 1

Hi @Sree ,

If you are using the WebUI, The authentication is carried out from the service account running the AIMMS PRO service, thus from the server.

If you are using WinUI, the database ODBC driver is located (by default) on your end user computer, thus following the AD environment.

In WebUI, you may restrict the access to the database within the AIMMS application, because you can access the login User info (containing the AD info) as described in the following article:

https://how-to.aimms.com/Articles/120/120-pro-user-groups.html#granting-access-to-data-within-your-app

Hi @Arthur, thanks for the info. As I understand, the restriction of access via user info obtained from PRO library is within AIMMS. I am looking for a different use case, which I did not elaborate sufficiently above, I think.

Let’s say there are some databases that are authorized to be accessed only by employees and not services like AIMMS (or server running AIMMS) for audit purposes (and other legal reasons). I was wondering if there is a mechanism to pass on the authenticated AD user credentials to the SQL server to query the data.

Hi @Sree ,

Indeed, I never used any mechanism to emulate a certain AD user from a service like AIMMS PRO when connecting to a database. That would be a Database and ODBC driver feature that would allow to connect to a database with a certain filled AD name, independently from the service/computer/account itself.

There is maybe something to consider from the following new feature:

https://docs.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver15

To make it work, you will need to store some “ActiveDirectoryPassword” in PRO itself matching the AD user connecting to the App.

Hope it helps !

Hi @Sree

Was Arthur’s answer sufficient in answering your question?

Thanks

@Arthur thanks for the pointer to the Microsoft documentation. I will test the ActiveDirectoryIntegrated authentication option sometime this week. I wonder if it applies only to Azure SQL or SQL Server on-premises. I will keep this thread updated.

The suggested authentication method in the documentation - https://docs.microsoft.com/en-us/sql/connect/odbc/using-azure-active-directory?view=sql-server-ver15 did not work. I tried this locally on my workstation (developer environment).

Attempt 1 with the following connection string:

SQLCreateConnectionString(
   DatabaseInterface : 'ODBC',
   DriverName : "ODBC Driver 17 for SQL Server",
   ServerName : sp_serverName(mssql::db),
   DatabaseName : sp_databaseName(mssql::db),
   AdditionalConnectionParameters : "UID=<userID>;Authentication=ActiveDirectoryInteractive")

A dialog window titled ‘Microsoft SQL Server Login’ pops up with the following two error messages and an OK button.

Connection Failed:
SQLState: ‘08001’
SQL Server Error: -2146893019
rMicrosoft]tODBC Driver 17 for SQL Server]SSL Provider: The certificate chain was issued by an authority that is not trusted

Connection Failed:
SQLState: ‘08001’
SQL Server Error: -2146893019
dMicrosoft]LODBC Driver 17 for SQL Server]Client unable to establish connection

Upon clicking OK, another dialog window titled ‘SQL Server Login’ pops up with modifiable parameters for the connection string. No modification here seems to resolve the connection issue. AIMMS hangs upon clicking ‘Cancel’ in this dialog.

Attempt 2 with the following connection string:

No error messages. But TestDataSource(sp_SQLconnectionStrings(testdb)) fails.

I also tried variations of the above attempts (with “TrustServerCertificate=True” and “Encrypt=True”) without any luck.

Attempt 3 with the following connection string:

Above works fine. So it is probably not a driver issue (‘ODBC Driver 17 for SQL Server’).

I am not sure if something needs to be configured on the server side. May be it does not apply to on-premises SQL server.

Good afternoon!

I would like to recommend a site with Connection Strings to a variety of databases:
https://www.connectionstrings.com/

Reply

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded