Liquibase Enterprise was formerly known as Datical DB.

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »

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 Azure SQL Managed Instance and using username and password.

If you are a server admin, you can authenticate to any database on the server or instance as the database owner and create additional SQL logins and users, which enable users to connect using username and password.

To connect to Azure SQL Managed Instance with the SQL Authentication (security) from Datical DB, enter the following information:

  • Username

  • Password

  • Hostname

  • Port

  • Database name

  • Instance name

Azure Active Directory Authentication

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

Prerequisites

To use the Azure Active Directory authentication mode, you need to сonfigure Azure AD with Azure Managed Instance.

Active Directory Integrated Authentication

Active Directory Integrated Authentication is a mechanism of connecting to Azure SQL Managed Instance by using the Azure Directory integrated mode.

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

For more information about requirements, see Configure your client computers.

  • Check whether you can see the adal.dll file in the C:\Windows\System32 and C:\Windows\SysWOW64 folders, which is needed for the Active Directory Integrated connection. The adal.dll file will be automatically added to these folders after you install the OLE DB driver.

In case you have issues with the adal.dll file, see Troubleshooting Issues with Active Directory Authentication.

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

  • Make the connection from a domain-joined machine that is connected with Azure Active Directory. Additionally, a database user representing your Azure Active Directory principal, or one of the groups to which the user belongs, needs to exist in the database and have the CONNECT permission.

You can access Azure SQL Managed Instance without entering credentials when you are logged in to a domain-joined machine.

3. Run the following scripts:

CREATE LOGIN [testuser@onmicrosoft.com] FROM EXTERNAL PROVIDER;

CREATE USER [testuser@onmicrosoft.com] FOR LOGIN [testuser@onmicrosoft.com];

ALTER ROLE [db_datareader] ADD MEMBER [testuser@onmicrosoft.com];

ALTER ROLE [db_datawriter] ADD MEMBER [testuser@onmicrosoft.com];

ALTER ROLE [db_ddladmin] ADD MEMBER [testuser@onmicrosoft.com];

Ensure that the SQL Server JDBC Driver Authentication Library is in C:\Windows\System32. The file name
is mssql-jdbc_auth-<version>-<arch>.dll, where version is a version number for the file.

Example: mssql-jdbc_auth-8.4.1.x64.dll.

  • Obtain the library from the following file after you install the SQL Server JDBC Driver: <datical-install>\plugins\com.datical.db.drivers.mssql_<version>.jar if this library is not in C:\Windows\System32.

  • Open or extract the jar file using an archive utility to access its contents. The DLL is located in the following archive location: com.datical.db.drivers.mssql_1.0.24.jar\auth\<arch>\mssql-jdbc_auth-<version>.<arch>.dll.

  • Put the DLL in C:\Windows\System32.

For more information about the configuration of the ActiveDirectoryIntegrated authentication, see Connecting using ActiveDirectoryIntegrated authentication.

To create a connection with the Active Directory Integrated mode in Datical DB, select the following:

  • Connection Type – Azure SQL Managed Instance

  • Security – Active Directory Integrated Security

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

Active Directory Password Authentication

As Azure Active Directory Password Authentication is a mechanism for connecting to Azure SQL Managed Instance by using identities in Azure Active Directory, you can connect to applications by using an Azure Active Directory username and password.

To create a connection with the Active Directory Password mode in Datical DB, select the following:

  • Connection Type - Azure SQL Managed Instance

  • Security - Active Directory Password Authentication

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

For more information about the configuration of the ActiveDirectoryPassword authentication, see Connecting using ActiveDirectoryPassword authentication mode.

Active Directory MSI Authentication

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

To use the Active Directory MSI Authentication, you need a contained database user representing your Azure Resource's System Assigned Managed Identity or User Assigned Managed Identity, or one of the groups to which your Managed Identity belongs, which must exist in the target database and have the CONNECT permission.

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.

For more information about the configuration of the ActiveDirectoryMSI authentication, see Connecting using ActiveDirectoryMSI authentication mode.

To create a connection with the Active Directory MSI mode in Datical DB, select the following:

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

  2. Make the connection from a domain-joined machine that is connected with Azure Active Directory. Additionally, a database user representing your Azure Active Directory principal, or one of the groups to which the user belongs, needs to exist in the database and have the CONNECT permission.

You can access Azure SQL Managed Instance without entering credentials when you are logged in to a domain-joined machine.

3. Run the following scripts:

CREATE LOGIN [testuser@onmicrosoft.com] FROM EXTERNAL PROVIDER;

CREATE USER [testuser@onmicrosoft.com] FOR LOGIN [testuser@onmicrosoft.com];

ALTER ROLE [db_datareader] ADD MEMBER [testuser@onmicrosoft.com];

ALTER ROLE [db_datawriter] ADD MEMBER [testuser@onmicrosoft.com];

ALTER ROLE [db_ddladmin] ADD MEMBER [testuser@onmicrosoft.com];

To create a connection with the Active Directory MSI mode in Datical DB, select the following:

  • Connection Type - Azure SQL Managed Instance

  • Security - Active Directory MSI Authentication

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

The appdba:sqlcmd change type is not supported when using MSI Authentication.  In Packager these change types are produced by the DIRECT package method. Use the sqlfile package method with MSI Authentication instead.

  • No labels