设置账号 CAM 验证

{
    "statement": [
        {
            "action": [
                "cam:BuildDataFlowAuthToken"
            ],
            "effect": "allow",
            "resource": [
                "qcs::cam::uin/<用户 uin>:resourceUser/<实例 ID>/<账号名>",
            ]
        }
    ],
    "version": "2.0"
}

String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=<数据库名>;user=<账号名>;password=<密码>;encrypt=false;";  
Connection con = DriverManager.getConnection(connectionUrl);

import logging
import os
import time

import pymssql
from dbauth.db_authentication import DBAuthentication
from dbauth.model.generate_authentication_token_request import GenerateAuthenticationTokenRequest
from tencentcloud.common import credential
from tencentcloud.common.exception.tencent_cloud_sdk_exception import TencentCloudSDKException
from tencentcloud.common.profile.client_profile import ClientProfile
from tencentcloud.common.profile.http_profile import HttpProfile

# 配置root logger
logging.basicConfig(
    level=logging.INFO,
    format='[%(asctime)s] - [%(threadName)s] - {%(module)s:%(funcName)s:%(lineno)d} %(levelname)s - %(message)s',
    datefmt='%Y-%m-%d %H:%M:%S'
)
log = logging.getLogger(__name__)


def main():
    region = "ap-guangzhou"
    instance_id = "mssql-123456"
    user_name = "camtest"
    host = "gz-mssql-123456.sql.tencentcdb.com"
    port = 24398
    db_name = "test"
    secret_id = os.environ['AK']
    secret_key = os.environ['SK']

    connection = None
    try:
        # 获取连接
        connection = get_db_connection_using_cam(secret_id, secret_key, region,
                                                 instance_id, user_name, host, port, db_name)

        # 验证连接是否成功
        with connection.cursor() as cursor:
            cursor.execute("SELECT 'Success!';")
            result = cursor.fetchone()
            log.info(result[0])  # 应该打印 "Success!"
    except Exception as e:
        log.error(f"An error occurred: {e}")
    finally:
        if connection:
            connection.close()


def get_db_connection_using_cam(secret_id, secret_key, region, instance_id, user_name, host, port, db_name):
    cred = credential.Credential(secret_id, secret_key)

    max_attempts = 3
    last_exception = None
    for attempt in range(1, max_attempts + 1):
        try:
            auth_token = get_auth_token(region, instance_id, user_name, cred)

            connection = pymssql.connect(
                host=host,
                port=port,
                user=user_name,
                password=auth_token,
                database=db_name
            )
            return connection
        except Exception as e:
            last_exception = e
            log.info(f"Attempt {attempt} failed.")
            time.sleep(5)

    log.error(f"All attempts failed. error: {last_exception}")
    raise last_exception


def get_auth_token(region, instance_id, user_name, cred):
    try:
        # 实例化一个 http 选项，可选的，没有特殊需求可以跳过
        http_profile = HttpProfile()
        http_profile.endpoint = "cam.tencentcloudapi.com"

        # 实例化一个 client 选项，可选的，没有特殊需求可以跳过
        client_profile = ClientProfile()
        client_profile.httpProfile = http_profile

        request = GenerateAuthenticationTokenRequest(
            region=region,
            instance_id=instance_id,
            user_name=user_name,
            credential=cred,
            client_profile=client_profile,  # 可选
        )
        return DBAuthentication.generate_authentication_token(request)
    except TencentCloudSDKException as err:
        log.error(err)
        raise


if __name__ == "__main__":
    main()

composer require tencentcloud/dbauth-sdk-php

<?php
/**
 * Copyright (c) 2024 Tencent Cloud
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

// Disable deprecation warnings (PHP 8.5 compatibility)
// TencentCloud SDK's AbstractModel uses deprecated ReflectionProperty::setAccessible()
// This does not affect functionality but will produce warning messages
error_reporting(E_ALL & ~E_DEPRECATED & ~E_USER_DEPRECATED);

require_once __DIR__ . '/../vendor/autoload.php';

use TencentCloud\\Common\\Credential;
use TencentCloud\\Common\\Profile\\ClientProfile;
use TencentCloud\\Common\\Profile\\HttpProfile;
use TencentCloud\\DBAuth\\DBAuthentication;
use TencentCloud\\DBAuth\\Model\\GenerateAuthenticationTokenRequest;
use TencentCloud\\DBAuth\\Internal\\Logger;

// Configure logging
date_default_timezone_set('Asia/Shanghai');

// Set DBAuth log level to ERROR (only show errors, hide INFO logs)
Logger::setLogLevel(Logger::ERROR);

function logger($level, $message) {
    $timestamp = date('Y-m-d H:i:s');
    echo "[{$timestamp}] [{$level}] {$message}\\n";
}

/**
 * Get database connection (using CAM authentication)
 */
function getDBConnectionUsingCAM($secretId, $secretKey, $region, $instanceId, $userName, $host, $port, $dbName) {
    $credential = new Credential($secretId, $secretKey);
    $maxAttempts = 3;
    $lastError = null;

    for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
        try {
            // Get authentication token
            $authToken = getAuthToken($region, $instanceId, $userName, $credential);

            // Connect to SQL Server using dblib driver, specify database in DSN
            $dsn = "dblib:host={$host}:{$port};dbname={$dbName};charset=UTF-8";

            $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES => false,
                PDO::ATTR_TIMEOUT => 10,
            ];

            // Create PDO connection, connect directly to specified database
            $pdo = new PDO($dsn, $userName, $authToken, $options);

            // Test connection
            $pdo->query("SELECT 'Success!'");

            return $pdo;

        } catch (Exception $e) {
            $lastError = $e;
            logger('WARN', "Connection attempt {$attempt} failed: " . $e->getMessage());

            if ($attempt < $maxAttempts) {
                logger('INFO', 'Retrying in 5 seconds...');
                sleep(5);
            }
        }
    }

    logger('ERROR', "All {$maxAttempts} attempts failed");
    throw new Exception('Failed to connect to database: ' . $lastError->getMessage());
}

/**
 * Get authentication token
 */
function getAuthToken($region, $instanceId, $userName, $credential) {
    // Configure HTTP Profile
    $httpProfile = new HttpProfile();
    $httpProfile->setEndpoint('cam.tencentcloudapi.com');
    $httpProfile->setReqTimeout(5);

    $clientProfile = new ClientProfile();
    $clientProfile->setHttpProfile($httpProfile);

    // Create request object
    $request = GenerateAuthenticationTokenRequest::builder()
        ->region($region)
        ->instanceId($instanceId)
        ->userName($userName)
        ->credential($credential)
        ->clientProfile($clientProfile)
        ->build();

    // Generate authentication token
    return DBAuthentication::generateAuthenticationToken($request);
}

try {
    // Get credentials from environment variables
    $secretId = getenv('TENCENTCLOUD_SECRET_ID');
    $secretKey = getenv('TENCENTCLOUD_SECRET_KEY');

    if (empty($secretId) || empty($secretKey)) {
        throw new Exception('Please set TENCENTCLOUD_SECRET_ID and TENCENTCLOUD_SECRET_KEY environment variables');
    }

    // Database configuration
    $region = 'ap-guangzhou';
    $instanceId = 'mssql-test123';
    $userName = 'test_db_1';
    $host = 'gz-mssql-test123.sql.tencentcdb.com';
    $port = 3306;
    $dbName = 'testdb';

    // Clean up shared memory
    DBAuthentication::clearCache();

    logger('INFO', '==========================================');
    logger('INFO', 'SQL Server Connection Example (CAM Auth)');
    logger('INFO', '==========================================');
    logger('INFO', "Region: {$region}");
    logger('INFO', "Instance ID: {$instanceId}");
    logger('INFO', "Host: {$host}:{$port}");
    logger('INFO', "Database: {$dbName}");
    logger('INFO', "User: {$userName}");
    logger('INFO', '==========================================');

    // Loop connection test (simulating actual usage scenario)
    for ($i = 1; $i <= 100; $i++) {
        logger('INFO', "Iteration {$i}/100");

        // Get database connection
        $connection = getDBConnectionUsingCAM(
            $secretId,
            $secretKey,
            $region,
            $instanceId,
            $userName,
            $host,
            $port,
            $dbName
        );

        // Execute query
        $stmt = $connection->query("SELECT 'Success!'");
        $result = $stmt->fetch();
        logger('INFO', "Query result: " . json_encode($result));

        // Close connection
        $connection = null;

        // Wait 1 second
        sleep(1);
    }

    logger('INFO', '==========================================');
    logger('INFO', 'All iterations completed successfully!');
    logger('INFO', '==========================================');

} catch (Exception $e) {
    logger('ERROR', 'Failed to connect to SQL Server: ' . $e->getMessage());
    logger('ERROR', 'Stack trace: ' . $e->getTraceAsString());
}

dotnet add package TencentCloudSDK --version 3.0.1374

using var loggerFactory = LoggerFactory.Create(builder =>
{
    builder.AddConsole().SetMinimumLevel(LogLevel.Information);
});

DBAuthentication.SetLoggerFactory(loggerFactory);

using System;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
using TencentCloud.Common;
using TencentCloud.DBAuth.SDK;
using TencentCloud.DBAuth.SDK.Model;

namespace SqlServerExample
{
    /// <summary>
    /// SQL Server example using CAM authentication
    /// </summary>
    public static class SqlServerExample
    {
        private static ILogger? _logger;

        /// <summary>
        /// Main entry point for SQL Server example
        /// </summary>
        /// <param name="args">Command line arguments</param>
        public static void Main(string[] args)
        {
            _logger = LoggerFactory.Create(builder => builder.AddConsole())
                .CreateLogger(typeof(SqlServerExample));
            
            try
            {
                // SQL Server hardcoded parameters
                const string region = "ap-guangzhou";
                const string instanceId = "mssql-test123";
                const string user = "test_sqlserver";
                const string host = "gz-mssql-test123.sql.tencentcdb.com";
                const int port = 3306;
                const string database = "test0";
                const int times = 100;
                const int interval = 1000; // milliseconds
                
                _logger.LogInformation($"Sqlserver Example - Region: {region}, InstanceId: {instanceId}");
                _logger.LogInformation($"Host: {host}:{port}, Database: {database}, User: {user}");
                
                // Get credentials from environment variables and connect to database
                TestSqlServerConnection(region, instanceId, user, host, port, database, times, interval);
                
                _logger.LogInformation("SqlServer example completed successfully");
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "SqlServer example failed, error: {ex.Message}", ex.Message);
                Environment.Exit(1);
            }
        }

        /// <summary>
        /// Get auth token from environment variables
        /// </summary>
        private static string GetAuthTokenFromEnv(string region, string instanceId, string user)
        {
            var secretId = Environment.GetEnvironmentVariable("TENCENTCLOUD_SECRET_ID");
            var secretKey = Environment.GetEnvironmentVariable("TENCENTCLOUD_SECRET_KEY");

            if (string.IsNullOrEmpty(secretId) || string.IsNullOrEmpty(secretKey))
            {
                throw new InvalidOperationException("Environment variables TENCENTCLOUD_SECRET_ID and TENCENTCLOUD_SECRET_KEY must be set");
            }

            var credential = new Credential
            {
                SecretId = secretId,
                SecretKey = secretKey
            };

            return GetAuthToken(region, instanceId, user, credential);
        }

        /// <summary>
        /// Get database authentication token
        /// </summary>
        private static string GetAuthToken(string region, string instanceId, string userName, Credential credential)
        {
            try
            {
                var tokenRequest = GenerateAuthenticationTokenRequest.NewBuilder()
                    .Region(region)
                    .Credential(credential)
                    .UserName(userName)
                    .InstanceId(instanceId)
                    .Build();

                var authToken = DBAuthentication.GenerateAuthenticationToken(tokenRequest);
                _logger.LogInformation($"Generated auth token for instance {instanceId}, user {userName}");
                
                return authToken;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "Failed to generate auth token, error: {ex.Message}", ex.Message);
                return string.Empty;
            }
        }
        
        /// <summary>
        /// Test SQL Server connection
        /// </summary>
        private static void TestSqlServerConnection(string region, string instanceId, string user, 
            string host, int port, string database, int times, int interval)
        {
            const int maxAttempts = 3;
            
            for (int i = 0; i < times; i++)
            {
                Exception? lastError = null;
                bool success = false;
                
                // Retry mechanism
                for (int attempt = 1; attempt <= maxAttempts; attempt++)
                {
                    try
                    {
                        var authToken = GetAuthTokenFromEnv(region, instanceId, user);
                        
                        var result = TestConnectionSqlServer(
                            host,
                            port,
                            user,
                            database,
                            authToken
                        );
                        
                        if (result == null)
                        {
                            _logger.LogWarning($"Iteration {i} returned null result");
                        }else
                        {
                            _logger.LogInformation($"Iteration {i} succeeded, result: {result}");
                            success = true;
                            break;
                        }
                    }
                    catch (Exception ex)
                    {
                        lastError = ex;
                        
                        if (attempt < maxAttempts)
                        {
                            System.Threading.Thread.Sleep(5000);
                        }
                    }
                }
                
                if (!success)
                {
                    throw new Exception($"Failed to connect to database: {lastError?.Message}");
                }
                
                if (i < times - 1)
                {
                    System.Threading.Thread.Sleep(interval);
                }
            }
        }
        
        /// <summary>
        /// Test SQL Server database connection
        /// </summary>
        private static string TestConnectionSqlServer(string ip, int port, string user, string dbName, string authToken)
        {
            string output = "";
            try
            {
                var connectionString = $"Server={ip},{port};Database={dbName};User Id={user};Password={authToken};TrustServerCertificate=true;Connection Timeout=30;";

                using var connection = new SqlConnection(connectionString);
                connection.Open();

                const string sql = "SELECT 'Success'";
                
                using var command = connection.CreateCommand();
                command.CommandText = sql;
                using var reader = command.ExecuteReader();
                
                if (reader.Read())
                {
                    var result = reader.GetString(0);
                    output = $"Connection: {sql} Result: {result}";
                }
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "SqlServer connection failed,error: {ex.Message}", ex.Message);
                throw;
            }
            
            return output;
        }
    }
}