Azure PowerShell函数连接SAP Datasphere遇ODBC驱动管理器错误

问题概述 #

尝试在Azure Function App中通过PowerShell脚本连接SAP Datasphere数据库时，因环境无法直接安装所需的ODBC驱动，运行脚本触发以下错误：

ERROR: Exception calling "Open" with "0" argument(s): "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

原脚本如下：

using namespace System.Net

# Input bindings are passed in via param block.
param($Request, $TriggerMetadata)

# Write to the Azure Functions log stream.
Write-Host "PowerShell HTTP trigger function processed a request."

$connectionString = @"
Driver={HDBODBC};ServerNode=e510.hanacloud.ondemand.com:443;UID=PROFICO;PWD=8,*SZ_U_s:WvW;
"@


$con = New-Object System.Data.Odbc.OdbcConnection $connectionString
$con.Open()
$sql = "SELECT top 10 MATNR as MaterialNumber, TO_DATE(ERSDA)  as CreatedOn, WERKS as Plant, MAKTX as Description, LVORM as DeletionFlag, MTART as MaterialType, BESKZ as ProcurmentType FROM PROFITABILITY_SP.VIEW_MATERIAL_MASTER"
$cmd = New-Object System.Data.Odbc.OdbcCommand $sql, $con
$rdr = $cmd.ExecuteReader()
while ($rdr.Read())
{
    Write ($rdr["MaterialNumber"], $rdr["Description"])
}
$rdr.Close()
$con.Close()


# Associate values to output bindings by calling 'Push-OutputBinding'.
Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = [HttpStatusCode]::OK
    Body = $errorMessage
})

可行解决方案 #

1. 自定义部署安装ODBC驱动 #

Azure Function的沙箱环境支持通过Kudu工具手动部署驱动：

• 下载与Function运行时架构匹配的SAP HANA ODBC驱动（优先x64版本）
• 登录Function App的高级工具(Kudu)，将驱动文件上传至D:\home\site\tools目录
• 修改连接字符串，指定驱动的绝对路径，同时添加加密参数确保安全：

$connectionString = @"
Driver={D:\home\site\tools\hdbodbc.dll};ServerNode=e510.hanacloud.ondemand.com:443;UID=PROFICO;PWD=8,*SZ_U_s:WvW;Encrypt=yes;
"@

2. 改用SAP HANA .NET Core客户端 #

SAP提供的.NET Core客户端无需依赖ODBC驱动，可直接在PowerShell中使用：

• 将Sap.Data.Hana.Core.dll上传至函数目录（如D:\home\site\wwwroot\YourFunctionName\）
• 修改脚本加载程序集并使用HANA专属连接对象：

using namespace System.Net

param($Request, $TriggerMetadata)

Write-Host "PowerShell HTTP trigger function processed a request."

# 加载SAP HANA .NET客户端程序集
Add-Type -Path "D:\home\site\wwwroot\YourFunctionName\Sap.Data.Hana.Core.dll"

$connectionString = "Server=e510.hanacloud.ondemand.com:443;User ID=PROFICO;Password=8,*SZ_U_s:WvW;Encrypt=true"
$con = New-Object Sap.Data.Hana.HanaConnection $connectionString
$con.Open()

$sql = "SELECT top 10 MATNR as MaterialNumber, TO_DATE(ERSDA)  as CreatedOn, WERKS as Plant, MAKTX as Description, LVORM as DeletionFlag, MTART as MaterialType, BESKZ as ProcurmentType FROM PROFITABILITY_SP.VIEW_MATERIAL_MASTER"
$cmd = New-Object Sap.Data.Hana.HanaCommand $sql, $con
$rdr = $cmd.ExecuteReader()

$result = @()
while ($rdr.Read())
{
    $row = [PSCustomObject]@{
        MaterialNumber = $rdr["MaterialNumber"]
        Description = $rdr["Description"]
        CreatedOn = $rdr["CreatedOn"]
        Plant = $rdr["Plant"]
        DeletionFlag = $rdr["DeletionFlag"]
        MaterialType = $rdr["MaterialType"]
        ProcurmentType = $rdr["ProcurmentType"]
    }
    $result += $row
    Write ($rdr["MaterialNumber"], $rdr["Description"])
}

$rdr.Close()
$con.Close()

Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = [HttpStatusCode]::OK
    Body = $result
})

3. 借助Azure Logic Apps中转 #

若上述方法受限于沙箱权限，可通过Azure Logic Apps连接SAP Datasphere，再让Azure Function调用Logic Apps的API，绕过驱动安装问题。

注意事项 #

• 确保SAP Datasphere的防火墙规则允许Azure Function的出站IP地址访问
• 禁止在脚本中硬编码密码，改用Function App的应用设置存储敏感信息，示例：$pwd = $env:SAP_DSP_PASSWORD

内容的提问来源于stack exchange，提问作者Omid Erfanmanesh