Skip to main content

Integrated authentication for SQL queries on AIMMS PRO with WebUI

  • 6 January 2020
  • 7 replies
  • 446 views

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.

7 replies

Arthur
Userlevel 4
Badge +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

Arthur
Sree
Userlevel 2
Badge +3

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.

 

Arthur
Userlevel 4
Badge +1

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 !

Arthur
Andrew

Hi @Sree 

Was Arthur’s answer sufficient in answering your question?

Thanks

Andrew | User Support Engineer
Sree
Userlevel 2
Badge +3

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

Sree
Userlevel 2
Badge +3

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
[Microsoft][ODBC 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
[Microsoft][ODBC 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:

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

 

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:

SQLCreateConnectionString(
    DatabaseInterface              :  'ODBC', 
    DriverName                     :  "ODBC Driver 17 for SQL Server", 
    ServerName                     :  sp_serverName(mssql::db), 
    DatabaseName                   :  sp_databaseName(mssql::db),
    AdditionalConnectionParameters :  "Trusted_connection=yes")

 

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.

Mstislav Simonenkov
Rank
Userlevel 3
Badge +1

Good afternoon!

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

Mstislav Simonenkov PhD, Head of Applied science NTS Business Service Ltd.

Reply


Terms & Conditions

Didn't find what you were looking for? Try searching on our documentation pages:

AIMMS Developer & PRO | AIMMS How-To | AIMMS SC Navigator