Merge pull request #2699 from Infisical/misc/address-remaining-ui-ux-issues-audit

misc: address other ui/ux issues with audit logs

.env.example

@@ -78,3 +78,5 @@ PLAIN_API_KEY=
 PLAIN_WISH_LABEL_IDS=
 
 SSL_CLIENT_CERTIFICATE_HEADER_KEY=
+
+ENABLE_MSSQL_SECRET_ROTATION_ENCRYPT=

backend/src/db/migrations/20241014084900_identity-multiple-auth-methods.ts

@@ -72,5 +72,5 @@ export async function down(knex: Knex): Promise<void> {
   }
 }
 
-const config = {transaction: false};
+const config = { transaction: false };
 export { config };

backend/src/ee/services/secret-approval-request/secret-approval-request-service.ts

@@ -267,7 +267,8 @@ export const secretApprovalRequestServiceFactory = ({
                 : "",
               secretComment: el.secretVersion.encryptedComment
                 ? secretManagerDecryptor({ cipherTextBlob: el.secretVersion.encryptedComment }).toString()
-                : ""
+                : "",
+              tags: el.secretVersion.tags
             }
           : undefined
       }));
@@ -571,7 +572,7 @@ export const secretApprovalRequestServiceFactory = ({
                     reminderNote: el.reminderNote,
                     skipMultilineEncoding: el.skipMultilineEncoding,
                     key: el.key,
-                    tagIds: el?.tags.map(({ id }) => id),
+                    tags: el?.tags.map(({ id }) => id),
                     ...encryptedValue
                   }
                 };

backend/src/ee/services/secret-rotation/secret-rotation-queue/secret-rotation-queue-fn.ts

@@ -85,7 +85,8 @@ export const secretRotationDbFn = async ({
   password,
   username,
   client,
-  variables
+  variables,
+  options
 }: TSecretRotationDbFn) => {
   const appCfg = getConfig();
 
@@ -117,7 +118,8 @@ export const secretRotationDbFn = async ({
       password,
       connectionTimeoutMillis: EXTERNAL_REQUEST_TIMEOUT,
       ssl,
-      pool: { min: 0, max: 1 }
+      pool: { min: 0, max: 1 },
+      options
     }
   });
   const data = await db.raw(query, variables);
@@ -153,6 +155,14 @@ export const getDbSetQuery = (db: TDbProviderClients, variables: { username: str
       variables: [variables.username]
     };
   }
+
+  if (db === TDbProviderClients.MsSqlServer) {
+    return {
+      query: `ALTER LOGIN ?? WITH PASSWORD = '${variables.password}'`,
+      variables: [variables.username]
+    };
+  }
+
   // add more based on client
   return {
     query: `ALTER USER ?? IDENTIFIED BY '${variables.password}'`,

backend/src/ee/services/secret-rotation/secret-rotation-queue/secret-rotation-queue-types.ts

@@ -24,4 +24,5 @@ export type TSecretRotationDbFn = {
   query: string;
   variables: unknown[];
   ca?: string;
+  options?: Record<string, unknown>;
 };

backend/src/ee/services/secret-rotation/secret-rotation-queue/secret-rotation-queue.ts

@@ -94,7 +94,9 @@ export const secretRotationQueueFactory = ({
           // on prod it this will be in days, in development this will be second
           every: appCfg.NODE_ENV === "development" ? secondsToMillis(interval) : daysToMillisecond(interval),
           immediately: true
-        }
+        },
+        removeOnComplete: true,
+        removeOnFail: true
       }
     );
   };
@@ -114,6 +116,7 @@ export const secretRotationQueueFactory = ({
 
   queue.start(QueueName.SecretRotation, async (job) => {
     const { rotationId } = job.data;
+    const appCfg = getConfig();
     logger.info(`secretRotationQueue.process: [rotationDocument=${rotationId}]`);
     const secretRotation = await secretRotationDAL.findById(rotationId);
     const rotationProvider = rotationTemplates.find(({ name }) => name === secretRotation?.provider);
@@ -172,6 +175,15 @@ export const secretRotationQueueFactory = ({
         // set a random value for new password
         newCredential.internal.rotated_password = alphaNumericNanoId(32);
         const { admin_username: username, admin_password: password, host, database, port, ca } = newCredential.inputs;
+
+        const options =
+          provider.template.client === TDbProviderClients.MsSqlServer
+            ? ({
+                encrypt: appCfg.ENABLE_MSSQL_SECRET_ROTATION_ENCRYPT,
+                cryptoCredentialsDetails: ca ? { ca } : {}
+              } as Record<string, unknown>)
+            : undefined;
+
         const dbFunctionArg = {
           username,
           password,
@@ -179,8 +191,10 @@ export const secretRotationQueueFactory = ({
           database,
           port,
           ca: ca as string,
-          client: provider.template.client === TDbProviderClients.MySql ? "mysql2" : provider.template.client
+          client: provider.template.client === TDbProviderClients.MySql ? "mysql2" : provider.template.client,
+          options
         } as TSecretRotationDbFn;
+
         // set function
         await secretRotationDbFn({
           ...dbFunctionArg,
@@ -189,12 +203,17 @@ export const secretRotationQueueFactory = ({
             username: newCredential.internal.username as string
           })
         });
+
         // test function
+        const testQuery =
+          provider.template.client === TDbProviderClients.MsSqlServer ? "SELECT GETDATE()" : "SELECT NOW()";
+
         await secretRotationDbFn({
           ...dbFunctionArg,
-          query: "SELECT NOW()",
+          query: testQuery,
           variables: []
         });
+
         newCredential.outputs.db_username = newCredential.internal.username;
         newCredential.outputs.db_password = newCredential.internal.rotated_password;
         // clean up

backend/src/ee/services/secret-rotation/templates/index.ts

@@ -1,4 +1,5 @@
 import { AWS_IAM_TEMPLATE } from "./aws-iam";
+import { MSSQL_TEMPLATE } from "./mssql";
 import { MYSQL_TEMPLATE } from "./mysql";
 import { POSTGRES_TEMPLATE } from "./postgres";
 import { SENDGRID_TEMPLATE } from "./sendgrid";
@@ -26,6 +27,13 @@ export const rotationTemplates: TSecretRotationProviderTemplate[] = [
     description: "Rotate MySQL@7/MariaDB user credentials",
     template: MYSQL_TEMPLATE
   },
+  {
+    name: "mssql",
+    title: "Microsoft SQL Server",
+    image: "mssqlserver.png",
+    description: "Rotate Microsoft SQL server user credentials",
+    template: MSSQL_TEMPLATE
+  },
   {
     name: "aws-iam",
     title: "AWS IAM",

backend/src/ee/services/secret-rotation/templates/mssql.ts

@@ -0,0 +1,33 @@
+import { TDbProviderClients, TProviderFunctionTypes } from "./types";
+
+export const MSSQL_TEMPLATE = {
+  type: TProviderFunctionTypes.DB as const,
+  client: TDbProviderClients.MsSqlServer,
+  inputs: {
+    type: "object" as const,
+    properties: {
+      admin_username: { type: "string" as const },
+      admin_password: { type: "string" as const },
+      host: { type: "string" as const },
+      database: { type: "string" as const, default: "master" },
+      port: { type: "integer" as const, default: "1433" },
+      username1: {
+        type: "string",
+        default: "infisical-sql-user1",
+        desc: "SQL Server login name that must be created at server level with a matching database user"
+      },
+      username2: {
+        type: "string",
+        default: "infisical-sql-user2",
+        desc: "SQL Server login name that must be created at server level with a matching database user"
+      },
+      ca: { type: "string", desc: "SSL certificate for db auth(string)" }
+    },
+    required: ["admin_username", "admin_password", "host", "database", "username1", "username2", "port"],
+    additionalProperties: false
+  },
+  outputs: {
+    db_username: { type: "string" },
+    db_password: { type: "string" }
+  }
+};

backend/src/ee/services/secret-rotation/templates/types.ts

@@ -8,7 +8,9 @@ export enum TDbProviderClients {
   // postgres, cockroack db, amazon red shift
   Pg = "pg",
   // mysql and maria db
-  MySql = "mysql"
+  MySql = "mysql",
+
+  MsSqlServer = "mssql"
 }
 
 export enum TAwsProviderSystems {

backend/src/lib/config/env.ts

@@ -162,7 +162,8 @@ const envSchema = z
     DISABLE_AUDIT_LOG_GENERATION: zodStrBool.default("false"),
     SSL_CLIENT_CERTIFICATE_HEADER_KEY: zpStr(z.string().optional()).default("x-ssl-client-cert"),
     WORKFLOW_SLACK_CLIENT_ID: zpStr(z.string().optional()),
-    WORKFLOW_SLACK_CLIENT_SECRET: zpStr(z.string().optional())
+    WORKFLOW_SLACK_CLIENT_SECRET: zpStr(z.string().optional()),
+    ENABLE_MSSQL_SECRET_ROTATION_ENCRYPT: zodStrBool.default("true")
   })
   .transform((data) => ({
     ...data,

backend/src/services/integration-auth/integration-sync-secret.ts

@@ -2540,7 +2540,7 @@ const syncSecretsAzureDevops = async ({
 
   const { groupId, groupName } = await getEnvGroupId(integration.app, integration.appId, integration.environment.name);
 
-  const variables: Record<string, { value: string, isSecret: boolean }> = {};
+  const variables: Record<string, { value: string; isSecret: boolean }> = {};
   for (const key of Object.keys(secrets)) {
     variables[key] = { value: secrets[key].value, isSecret: true };
   }

docs/documentation/platform/secret-rotation/mssql.mdx

@@ -0,0 +1,139 @@
+---
+title: "Microsoft SQL Server"
+description: "Learn how to automatically rotate Microsoft SQL Server user passwords."
+---
+
+The Infisical SQL Server secret rotation allows you to automatically rotate your database users' passwords at a predefined interval.
+
+## Prerequisites
+
+1. Create two SQL Server logins and database users with the required permissions. We'll refer to them as `user-a` and `user-b`.
+2. Create another SQL Server login with permissions to alter logins for `user-a` and `user-b`. We'll refer to this as the `admin` login.
+
+Here's how to set up the prerequisites:
+
+```sql
+-- Create the logins (at server level)
+CREATE LOGIN [user-a] WITH PASSWORD = 'ComplexPassword1';
+CREATE LOGIN [user-b] WITH PASSWORD = 'ComplexPassword2';
+
+-- Create database users for the logins (in your specific database)
+USE [YourDatabase];
+CREATE USER [user-a] FOR LOGIN [user-a];
+CREATE USER [user-b] FOR LOGIN [user-b];
+
+-- Grant necessary permissions to the users
+GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO [user-a];
+GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO [user-b];
+
+-- Create admin login with permission to alter other logins
+CREATE LOGIN [admin] WITH PASSWORD = 'AdminComplexPassword';
+CREATE USER [admin] FOR LOGIN [admin];
+
+-- Grant permission to alter any login
+GRANT ALTER ANY LOGIN TO [admin];
+```
+
+To learn more about SQL Server's permission system, please visit this [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/getting-started-with-database-engine-permissions).
+
+## How it works
+
+1. Infisical connects to your database using the provided `admin` login credentials.
+2. A random value is generated and the password for `user-a` is updated with the new value.
+3. The new password is then tested by logging into the database.
+4. If test is successful, it's saved to the output secret mappings so that rest of the system gets the newly rotated value(s).
+5. The process is then repeated for `user-b` on the next rotation.
+6. The cycle repeats until secret rotation is deleted/stopped.
+
+## Rotation Configuration
+
+<Steps>
+  <Step title="Open Secret Rotation Page">
+	Head over to Secret Rotation configuration page of your project by clicking on `Secret Rotation` in the left side bar 
+  </Step>
+  <Step title="Click on Microsoft SQL Server card" />
+  <Step title="Provide the inputs">
+	<ParamField path="Admin Username" type="string" required>
+		SQL Server admin username
+	</ParamField>
+
+    <ParamField path="Admin password" type="string" required>
+    	SQL Server admin password
+    </ParamField>
+
+    <ParamField path="Host" type="string" required>
+    	SQL Server host url (e.g., your-server.database.windows.net)
+    </ParamField>
+
+    <ParamField path="Port" type="number" required>
+    	Database port number (default: 1433)
+    </ParamField>
+
+    <ParamField path="Database" type="string" required>
+    	Database name (default: master)
+    </ParamField>
+
+    <ParamField path="Username1" type="string" required>
+    	The first login name to rotate - `user-a`
+    </ParamField>
+
+    <ParamField path="Username2" type="string" required>
+    	The second login name to rotate - `user-b`
+    </ParamField>
+
+    <ParamField path="CA" type="string">
+    	Optional database certificate to connect with database
+    </ParamField>
+
+  </Step>
+  <Step title="Configure the output secret mapping">
+  	When a secret rotation is successful, the updated values needs to be saved to an existing key(s) in your project.
+
+    <ParamField path="Environment" type="string" required>
+    	The environment where the rotated credentials should be mapped to.
+    </ParamField>
+
+    <ParamField path="Secret Path" type="string" required>
+    	The secret path where the rotated credentials should be mapped to.
+    </ParamField>
+
+    <ParamField path="Interval" type="number" required>
+    	What interval should the credentials be rotated in days.
+    </ParamField>
+
+    <ParamField path="DB USERNAME" type="string" required>
+    	Select an existing secret key where the rotated database username value should be saved to.
+    </ParamField>
+
+    <ParamField path="DB PASSWORD" type="string" required>
+    	Select an existing select key where the rotated database password value should be saved to.
+    </ParamField>
+
+  </Step>
+</Steps>
+
+## FAQ
+
+<AccordionGroup>
+ <Accordion title="Why can't we delete the other user when rotating?">
+ 	When a system has multiple nodes by horizontal scaling, redeployment doesn't happen instantly.
+
+    This means that when the secrets are rotated, and the redeployment is triggered, the existing system will still be using the old credentials until the change rolls out.
+
+    To avoid causing failure for them, the old credentials are not removed. Instead, in the next rotation, the previous user's credentials are updated.
+
+ </Accordion>
+ <Accordion title="Why do you need an admin account?">
+    The admin account is used by Infisical to update the credentials for `user-a` and `user-b`.
+
+    You don't need to grant all permissions for your admin account but rather just the permission to alter logins (ALTER ANY LOGIN).
+
+ </Accordion>
+ <Accordion title="How does this work with Azure SQL Database?">
+   When using Azure SQL Database, you'll need to:
+   
+   1. Use the full server name as your host (e.g., your-server.database.windows.net)
+   2. Ensure your admin account is either the Azure SQL Server admin or an Azure AD account with appropriate permissions
+   3. Configure your Azure SQL Server firewall rules to allow connections from Infisical's IP addresses
+ </Accordion>
+</AccordionGroup>

docs/mint.json

@@ -165,6 +165,7 @@
             "documentation/platform/secret-rotation/sendgrid",
             "documentation/platform/secret-rotation/postgres",
             "documentation/platform/secret-rotation/mysql",
+            "documentation/platform/secret-rotation/mssql",
             "documentation/platform/secret-rotation/aws-iam"
           ]
         },

frontend/src/views/SecretApprovalPage/components/SecretApprovalRequest/components/SecretApprovalRequestChangeItem.tsx

@@ -96,7 +96,7 @@ export const SecretApprovalRequestChangeItem = ({
                   <SecretInput isReadOnly value={secretVersion?.secretValue} />
                 </Td>
                 <Td>{secretVersion?.secretComment}</Td>
-                <Td>
+                <Td className="flex flex-wrap gap-2">
                   {secretVersion?.tags?.map(({ slug, id: tagId, color }) => (
                     <Tag
                       className="flex w-min items-center space-x-2"
@@ -118,7 +118,7 @@ export const SecretApprovalRequestChangeItem = ({
                   <SecretInput isReadOnly value={newVersion?.secretValue} />
                 </Td>
                 <Td>{newVersion?.secretComment}</Td>
-                <Td>
+                <Td className="flex flex-wrap gap-2">
                   {newVersion?.tags?.map(({ slug, id: tagId, color }) => (
                     <Tag
                       className="flex w-min items-center space-x-2"