Microsoft SQL Server (MSSQL) or Azure SQL¶
Pre-requisites¶
To create an Instance Secret for securing MSSQL or Azure SQL credentials, you must have the following information:
Access Permissions in DataOS¶
To create an Instance Secret in DataOS, at least one of the following role tags must be assigned:
-
roles:id:data-dev -
roles:id:system-dev -
roles:id:user
Checking Assigned Roles
Use the following command to verify assigned roles:
If any required roles are missing, contact a DataOS Operator or submit a Grant Request for role assignment.
Alternatively, if access is managed through use cases, ensure the following use case is assigned:
-
Manage All Instance-level Resources of DataOS in User Layer
To validate assigned use cases, refer to the Bifrost Application Use Cases section
Bifrost Governance
Source System Requirements¶
-
Username: The MSSQL username used to authenticate and access your MSSQL database. You can retrieve this from the MsSQL database admin by asking them to provide the username for your account.
-
Password: The password associated with the MSSQL username for authentication. You can obtain this from the MSSQL database admin, or if the password is already set, you will need to securely retrieve it.
Ensure you have these credentials ready before proceeding with the Instance Secret creation process.
Create an Instance Secret for securing MSSQL or Azure SQL credentials¶
Microsoft SQL Server (MSSQL) is a SQL database. Azure SQL, the cloud-based managed version of MSSQL, offers similar functionality but is optimized for cloud scalability, automated maintenance, and high availability. While MSSQL requires manual setup and management, Azure SQL provides a fully managed service, reducing administrative overhead.
To create an MSSQL or Azure SQL Instance Secret in DataOS, ensure you have access to the DataOS Command Line Interface (CLI) and the required permissions. Follow the steps below to complete the creation process efficiently and securely.
Step 1: Create a manifest file¶
Begin by creating a manifest file to hold the configuration details for your MSSQL Instance Secret. Depending on your access needs (read-only or read-write), start with the corresponding YAML template provided below.
name: ${mssql-depot-name}-r # Unique identifier for Resource, replace ${mssql-depot-name} with depot name
version: v1 # Manifest version
type: instance-secret # Type of the Resource
description: ${description} # Purpose of the Instance-secret
layer: user # DataOS layer
instance-secret:
type: key-value-properties # Secret type
acl: r # Access control: 'r' for read-only
data:
username: ${username} # replace with MSSQL or Azure SQL username
password: ${password} # replace with MSSQL or Azure SQL password
name: ${mssql-depot-name}-rw # Unique identifier for Resource, replace ${mssql-depot-name} with depot name
version: v1 # Manifest version
type: instance-secret # Type of the Resource
description: ${description} # Purpose of the Instance-secret
layer: user # DataOS layer
instance-secret:
type: key-value-properties # Secret type
acl: rw # Access control: 'rw' for read-write
data:
username: ${username} # replace with MSSQL or Azure SQL username
password: ${password} # replace with MSSQL or Azure SQL password
Resource meta section
The Instance Secret manifest includes a Resource meta section with essential metadata attributes common to all resource types. Some attributes in this section are optional, while others are mandatory. For more details, refer to the configurations section.
Instance-secret specific section
This section focuses on attributes specific to MSSQL or Azure SQL Instance Secrets. It includes details like:
-
type: Specifies the Instance Secret type (key-value-properties). -
acl: Access control level (read-only or read-write). -
data: Contains sensitive information such as Azure endpoint suffix, storage account key, and storage account name.
For more information, refer to the configurations section.
Step 2: Apply the manifest¶
To create the MSSQL Instance Secret within DataOS, use the apply command. Since Instance Secrets are Instance-level resources, do not specify a workspace while applying the manifest.
Step 3: Validate the Instance Secret¶
To validate the proper creation of the Instance Secret in DataOS, use the get command.
To get the list of all the Instance Secret within the Dataos environment execute the following command.
dataos-ctl resource get -t instance-secret -a
INFO[0000] 🔍 get...
INFO[0000] 🔍 get...complete
NAME | VERSION | TYPE | WORKSPACE | STATUS | RUNTIME | OWNER
-----------------------------|---------|-----------------|-----------|--------|---------|------------------------
abfssv2alpha-r | v1 | instance-secret | | active | | iamgroot
abfssv2alpha-rw | v1 | instance-secret | | active | | iamgroot
abfsswithoutmetastore-r | v1 | instance-secret | | active | | thisisthor
abfsswithoutmetastore-rw | v1 | instance-secret | | active | | thisisthor
Alternatively, you can also check on Metis UI by searching the Instance Secret by name.
Delete the Instance Secret¶
To delete an Instance Secret, use one of the following methods:
Method 1¶
Specify the Resource type and Instance Secret name in the delete command.
Method 2¶
Copy the Instance Secret name, version, and Resource-type from the output of the get command separated by '|' enclosed within quotes and use it as a string in the delete command.
Method 3¶
Specify the path of the manifest file and use the delete command.