Gen applications & Build Tool using SQL Server with Windows authentication

Products

• Gen
• Gen - Workstation Toolset

Issue/Introduction

Can Gen applications for C & C# (CLR/.NET)  connect to SQL Server using Windows authentication instead of SQL Server authentication?
Also is it possible for the Build Tool to use Windows authentication when creating a SQL Server database/installing DDL?

Environment

Release: 8.6
Component: Gen SQL Server applications

Resolution

PLEASE NOTE: The below information covers what has been successfully tested by the Gen support team but is not actually officially certified/supported, so is to be used at own risk.

APPLICATIONS
1. Windows C Applications:

Gen Windows C applications use ODBC to connect to SQL Server and therefore Windows authentication can be used for that connection.

The ODBC connection uses a defined Data Source Name (DSN) and the DSN can be configured to use Integrated Windows authentication which has no negative impact on the Gen runtimes or application execution.

For a Gen GUI application (always 32-bit) use C:\Windows\syswow64\odbcad32.exe to define a 32-bit DSN with the same name as the database name in the model and select Windows authentication followed by the default database to connect to e.g. for GENDB (from sample model):

For a Gen cooperative server which can be 32-bit or 64-bit define a similar database DSN using C:\Windows\syswow64\odbcad32.exe (3-bit) or C:\Windows\system32\odbcad32.exe (64-bit)

In the generated aeenv file used by the Transaction Enabler dummy userid and password values need to be added for the connection data to be processed correctly i.e.

CHANGE: flow P900 P900 GENDB 

TO:  flow P900 P900 userid password GENDB 

The userid/password values in the aeenv file are effectively ignored because the DSN configuration for Integrated Windows authentication is used.

 

2. CLR/.NET C# applications:
For .NET applications the connection string that needs to be entered on the assembly step can use either the .NET Framework Data Provider for SQL Server (System.Data.SqlClient) or .NET Framework Data Provider for ODBC ( System.Data.Odbc).

They both support the use of Windows authentication in the connection string parameters ("Integrated Security"/"Trusted_Connection).

More information can be found here:
Microsoft Learn > .NET > .NET Framework > ADO .Net > .NET Framework Data Providers
Microsoft Learn > .NET > .NET Framework> ADO .Net > Connection String Syntax

BUILD TOOL

1. MSSQL Technical Design
Out of the box it is not possible to force the Build Tool (BT) connection to SQL Server to use Windows authentication for the creation of the SQL Server database/DDL when using the MSSQL Technical Design. For example if the BT profile MSSQL property OPT.DBCONNECT is set to LOCAL and property OPT.DBUSER set to a domain user with SQL Server access the connection will fail:

===

TIS95DDL: Connection="", User="server_name\administrator", Password="*************" 

TIS95DDL: Error 1 connecting to the database server 
TIS95DDL: Microsoft.SqlServer.Management.Common.ConnectionFailureException 
Microsoft.SqlServer.ConnectionInfo 
Failed to connect to server . 
TIS95DDL: DDL Install Failed 

===

The directory %GEN86%\Gen\ddl contains source code for tis95ddl.cpp and SMOBridge.cpp which uses SMO (SQL Server Management Objects) to make the connection. SMOBridge.cpp can be customised to enable Windows Authentication by adding one line "LoginSecure = TRUE" for the connection. That will cause the usr & pwd values to be ignored and the logged on Windows user will be used instead i.e. 

        srvconn = gcnew ServerConnection(gcnew String(conn), gcnew String(usr), gcnew String(pwd));
        srvconn->LoginSecure  = TRUE;
        srvconn->ConnectTimeout = 30;
        ...

The 32-bit TIS95DDL.exe in directory %GEN86%\Gen then needs to be rebuilt, but before doing so please backup the complete %GEN86%\Gen directory as a fallback measure. Then follow these steps:

a. Create a command file run_makeddl.cmd in %GEN86%\Gen with this content:

REM NOTE: The exact directory name may differ but SQLSERVER_HOME should be the Microsoft SQL Server directory which contains SDK\Assemblies
set SQLSERVER_HOME=C:\Program Files (x86)\Microsoft SQL Server\120
set GEN_BITS_ENV=x86
set GEN_BITS_DIR=.
set GEN_BITS_MACHINE=X86
"%GEN86%\Gen\ddl\makeddl.bat" MSSQL %GEN_BITS_DIR%

b. From command prompt cd to %GEN86%\Gen, execute genenv.bat to set up Microsoft Visual Studio Paths for 32-bit (Testing and Running Applications for Windows IT >GENENV.BAT), and finally execute run_makeddl.cmd e.g. for Microsoft Visual Studio 2015:

cd %GEN86%\Gen
genenv.bat 32 2015  NOTE: Change 2015 to 2017 if using Microsoft Visual Studio 2017

run_makeddl.cmd

The old TIS95DDL.exe will be backed up to directory %GEN86%\Gen\saved_dll and a new tis95ddl.exe will be created which can be used to install the database with Windows authentication.

2. ODBC Technical Design
Alternatively if OCBC is used for the Technical Design instead of MSSQL, then it is possible to use Windows authentication to install the DDL after initial manual creation of an empty database as follows:

a. Manually create the new empty database in SQL Server.

b. Create a 32-bit DSN to connect to the new database with Integrated Windows authentication.

c. When installing the DDL select the new DSN when prompted by the Build Tool.