Page Comparison

There are the following types of connection and authentication (security) options available in Datical DB:

...

• Microsoft SQL Server with the SQL Authentication and Integrated Security

• Azure SQL Database and Azure SQL Managed Instance with:

  • SQL Authentication

  • Active Directory Integrated Authentication

  • Active Directory Password Authentication

  • Active Directory MSI Authentication

SQL Authentication

SQL Authentication refers to the authentication of a user when connecting to an Azure SQL Database and using username and password.

...

Azure Active Directory Authentication

The Azure Active Directory authentication refers to the authentication of a user when connecting to an Azure SQL Database and using identities in Azure Active Directory.

Prerequisites

To use the Azure Active Directory authentication mode, you need to:

...

Active Directory Integrated Authentication

The Active Directory Integrated Authentication is a mechanism of connecting to an Azure SQL Database by using an integrated mode with Azure Active Directory.

To use the Active Directory Integrated Security authentication option, follow these steps:

1. Log in with the admin user and create USER [username] from EXTERNAL PROVIDER; on the database you use.

2. Add ddladmin, datawriter, and datareader roles for checking the status and Deploy Packager processes:
   EXEC sp_addrolemember 'db_ddladmin', 'datical_user';
EXEC sp_addrolemember 'db_datawriter', 'datical_user';
EXEC sp_addrolemember 'db_datareader', 'datical_user';

3. Make sure that you federated the on-premise Active Directory Federation Services (AD FS) with the Azure Active Directory in the cloud.

4. Make the connection from a domain-joined machine that is federated with Azure Active Directory. You can access an Azure SQL Database without entering credentials when you're logged in to a domain-joined machine. Additionally, a database user representing your Azure Active Directory principal, or one of the groups to which the user belongs to, needs to exist in the database and have the CONNECT permission.

5. Install the OLE DB driver from this link:

   https://docs.microsoft.com/en-us/sql/connect/oledb/download-oledb-driver-for-sql-server?view=sql-server-ver15

   Driver. This msi installer should add adal.dll to System32 and Syswow64 folders.

6. Install the ODBC Driver from this link:
   https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver15

7. Ensure that the SQL Server JDBC Driver Authentication Library is in \Windows\System32. The file is named mssql-jdbc_auth-<version>.x64.dll where <version> is a version number for the file.

   1. If the mssql-jdbc_auth-<version>.x64.dll library is not already in \Windows\System32, it can be extracted from the following file (if you have installed the SQL Server JDBC Driver for Liquibase Enterprise/Datical): <datical-install>\plugins\com.datical.db.drivers.mssql_<version>.jar

   2. Using an archive utility, open or extract the com.datical.db.drivers.mssql_<version>.jar file to access its contents.

   3. The DLL is located in the following archive location: com.datical.db.drivers.mssql_1.0.24.jar\auth\x64\mssql-jdbc_auth-<version>.x64.dll

   4. Put the mssql-jdbc_auth-<version>.x64.dll file in \Windows\System32

8. Make sure sqlcmd (version 13.1 or higher) is installed and on your PATH. You can install it from the following link:https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15To install sqlcmd, go to the sqlcmd Utility page.

8. To create a connection with the Active Directory Integrated mode in the Liquibase Enterprise/Datical DB GUI, select the following:

• Connection Type - Azure SQL Database

• Security – Active Directory Integrated Security

Also, enter your hostname, port, application name, database name, and instance name.

9. You can test the connection either by using the “Test Connection” Test Connection button in the GUI or by running “hammer
hammer testConnect <dbDef>” <dbDef> from the command line. Troubleshooting for connection errors:

• If you get the error “Login failed to authenticate the user in Active Directory (Authentication=ActiveDirectoryIntegrated)”, you may need to add a mssql-jdbc_auth-<version>.x64.dllentry in the Windows registry:

  1. Enter Registry Editor in the Windows search box on the taskbar and open the Registry Editor app. If you are prompted by User Account Control, select Yes.

  2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSJDBCAUTHDLL, double-click the TargetDir file, and then add the following path:

     Code Block
     C:\\WINDOWS\\System32\\mssql-jdbc_auth-<version>.x64.dll

  3. Select OK to save the changes.

• If you receive an "Unable to load adalsql.dll" error message after downloading the adal.dll file and copying it to C:\Windows\System32, you will need to add an entry for adal.dll in the Windows registry.

To add the adal.dll entry in the Windows registry:

1. Enter Registry Editor in the Windows search box on the taskbar and open the Registry Editor app. If you are prompted by User Account Control, select Yes.

2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSADALSQL, double-click the TargetDir file, and then add the following path:

C:\\WINDOWS\\System32\\adal.dll

...

1. Select OK to save the changes.

Active Directory Password Authentication

As the Azure Active Directory Password Authentication is a mechanism of connecting to an Azure SQL Database by using identities in Azure Active Directory, you can connect to applications by using an Azure Active Directory user name and password.

To connect using the Active Directory Password authentication, follow these steps:

1. Log in with the admin user and create USER [username] from EXTERNAL PROVIDER; on the database you use.

2. Add ddladmin, datawriter, and datareader roles for checking the status and Deploy Packager processes:
   EXEC sp_addrolemember 'db_ddladmin', 'datical_user';
EXEC sp_addrolemember 'db_datawriter', 'datical_user';
EXEC sp_addrolemember 'db_datareader', 'datical_user';

3. Install the SQL Server JDBC Driver Authentication Library - mssql-jdbc_auth-<version>-<arch>.dll file on your machine. The file is located in the <datical-install>\DaticalDB\plugins\ directory.

   1. Copy com.datical.db.drivers.mssql_1.x.x.jar to a temporary directory and unzip it (<unzip directory>). 

   2. On Windows systems you can:

      o Use <java-install-path>\JAVA\jar.exe xf <name>.jar

      o Change the extension of the file from .jar to .zip and then unzip it.

   3. Go to <unzip directory>\auth\, then go to the folder for your version of the Java Virtual Machine (JVM). 

4. Put the mssql-jdbc_auth-<version>-<arch>.dll file in \Windows\System32. There are 32-bit and 64-bit versions of the .dll file included with the Microsoft SQL Server JDBC driver. For example: mssql-jdbc_auth-8.4.1.x64.dll.

...

Also, enter your hostname, port, application name, database name, instance name, username, and password.

Active Directory MSI Authentication

You can use the Active Directory MSI Authentication for connection from inside of an Azure Resource with the Identity feature.

...

Optionally, to acquire the accessToken for establishing the connection, you can specify msiClientId in the Connection or DataSource properties along with the Active Directory MSI Authentication mode, which must include the Client ID of a Managed Identity.

To create a connection with the Active Directory MSI mode in the Datical DB , select GUI, go to the Edit Connection screen. Select the following:

• Connection Type -Azure SQL Database

• Security -Active Directory MSI Authentication

• Also

...

• enter your hostname, port, application name, database name, instance name, and MSI Client ID.

Then define properties in your driver properties file:

• Go to your Liquibase Enterprise/Datical installation directory (such as C:\Apps\DaticalDB_7.13).

• Edit the file called mssql_driver.properties.

• Add these two lines to your mssql_driver.properties file:

trustServerCertificate=true

hostNameInCertificate=*.azure.<domain>

(Replace <domain> with your actual domain name. For example: hostNameInCertificate=*.azure.acme.com.)