使用PowerShell触发器连接Azure函数应用与Azure VM中SQL Server数据库

问题说明 #

已在Azure VM部署SQL Server数据库，创建带PowerShell TimerTrigger的Azure函数应用，目标是调用API并将结果存入数据库。目前API调用和数据读取正常，但写入数据库时出现连接失败问题。

错误详情 #

ERROR: Exception calling "Open" with "0" argument(s): "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

相关代码片段 #

# 定义连接参数
$serverName = "XXX"
$databaseName = "XXX"
$userId = "XXX"
$password = "XXX"
$connectionString = "Server=$serverName;Initial Catalog=$databaseName;User Id=$userId;Password=$password;"

# 打开SQL Server连接
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $connectionString
$connection.Open()

# 定义插入数据的SQL查询
$query = "TRUNCATE TABLE [dbo].[Table_1]; INSERT INTO [dbo].[Table_1] ([Test]) VALUES ('" + $accessTokenTrimmed + "');"

# 执行查询
$command = $connection.CreateCommand()
$command.CommandText = $query
$command.ExecuteNonQuery()
$connection.Close()

排查与解决步骤 #

• 验证实例名称与端口格式：确认$serverName格式正确，默认实例用VM公共IP,1433或VM的FQDN,1433；命名实例用VM公共IP\实例名,端口号。同时检查SQL Server配置管理器，确保TCP/IP协议已启用且监听端口正确。
• 检查网络连通性：在函数应用的Kudu控制台执行Test-NetConnection -ComputerName <VM公共IP> -Port 1433测试端口连通性；确保Azure VM的NSG允许1433端口的入站流量（针对函数应用的出站IP或AzureCloud服务标签）；确认VM的Windows防火墙已开放对应端口。
• 确认远程连接配置：在SSMS中连接VM上的数据库，右键实例→属性→连接，勾选允许远程连接到此服务器；检查SQL Server身份验证模式，确保启用SQL Server和Windows身份验证模式，且$userId对应的SQL登录名拥有目标数据库的读写权限。
• 优化连接字符串：显式指定端口，避免默认解析问题，示例：Server=XXX,1433;Initial Catalog=XXX;User Id=XXX;Password=XXX;；若使用VM私有IP，需让函数应用与VM在同一VNet，或通过VNet集成连接到VM所在网络。
• 修复代码安全隐患：当前代码存在SQL注入风险，改用参数化查询：

$query = "TRUNCATE TABLE [dbo].[Table_1]; INSERT INTO [dbo].[Table_1] ([Test]) VALUES (@AccessToken);"
$command = $connection.CreateCommand()
$command.CommandText = $query
$param = $command.Parameters.Add("@AccessToken", [System.Data.SqlDbType]::NVarChar)
$param.Value = $accessTokenTrimmed
$command.ExecuteNonQuery()

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