You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Dataflow迁移SQL Server到BigQuery遇连接超时及权限错误求助

avatar
算力阿峰
2026-6-21
解决Dataflow JDBC模板连接SQL Server超时问题

问题背景

我正在用Dataflow的SQL Server到BigQuery模板迁移AdventureWorks2019数据库的多张表，指定用SQL to BQ或JDBC to BQ模板，不用Cloud Shell或Python。但管道执行失败，报错TCP/IP连接超时。JDBC连接字符串为：sqlserver://xx.xx.xx.xxx:1433;databaseName=AdventureWorks2019;user=xxxxxxx;password=xxxxxxxx;。

已完成的排查步骤：

  • 配置SQL Server支持远程连接，启用TCP/IP并设置IP和端口
  • 配置Windows防火墙允许1433端口流量
  • 本地Java程序和同网络Mac设备能正常连接并查询数据库
  • GCP项目配置完成，服务账号有BigQuery Admin、Dataflow Worker等角色，Dataflow wordcount示例能正常运行

错误日志 

2024-06-29 22:12:13.949 CST
Error message from worker: org.apache.beam.sdk.util.UserCodeException: java.sql.SQLException: Cannot create PoolableConnectionFactory (The TCP/IP connection to the host xx.xx.xx.xxx, port 1433 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".)
    org.apache.beam.sdk.util.UserCodeException.wrap(UserCodeException.java:39)
    org.apache.beam.sdk.io.jdbc.JdbcIO$ReadFn$DoFnInvoker.invokeProcessElement(Unknown Source)
    org.apache.beam.fn.harness.FnApiDoFnRunner.processElementForParDo(FnApiDoFnRunner.java:803)
    org.apache.beam.fn.harness.data.PCollectionConsumerRegistry$MetricTrackingFnDataReceiver.accept(PCollectionConsumerRegistry.java:348)
    org.apache.beam.fn.harness.data.PCollectionConsumerRegistry$MetricTrackingFnDataReceiver.accept(PCollectionConsumerRegistry.java:275)
    org.apache.beam.fn.harness.FnApiDoFnRunner.outputTo(FnApiDoFnRunner.java:1792)
    org.apache.beam.fn.harness.FnApiDoFnRunner.access$3000(FnApiDoFnRunner.java:143)
    org.apache.beam.fn.harness.FnApiDoFnRunner$NonWindowObservingProcessBundleContext.output(FnApiDoFnRunner.java:2650)
    org.apache.beam.sdk.transforms.MapElements$2.processElement(MapElements.java:151)
    org.apache.beam.sdk.transforms.MapElements$2$DoFnInvoker.invokeProcessElement(Unknown Source)
    org.apache.beam.fn.harness.FnApiDoFnRunner.processElementForParDo(FnApiDoFnRunner.java:803)
    org.apache.beam.fn.harness.data.PCollectionConsumerRegistry$MetricTrackingFnDataReceiver.accept(PCollectionConsumerRegistry.java:348)
    org.apache.beam.fn.harness.data.PCollectionConsumerRegistry$MetricTrackingFnDataReceiver.accept(PCollectionConsumerRegistry.java:275)
    org.apache.beam.fn.harness.BeamFnDataReadRunner.forwardElementToConsumer(BeamFnDataReadRunner.java:213)
    org.apache.beam.sdk.fn.data.BeamFnDataInboundObserver.multiplexElements(BeamFnDataInboundObserver.java:158)
    org.apache.beam.fn.harness.control.ProcessBundleHandler.processBundle(ProcessBundleHandler.java:537)
    org.apache.beam.fn.harness.control.BeamFnControlClient.delegateOnInstructionRequestType(BeamFnControlClient.java:150)
    org.apache.beam.fn.harness.control.BeamFnControlClient$InboundObserver.lambda$onNext$0(BeamFnControlClient.java:115)
    java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    org.apache.beam.sdk.util.UnboundedScheduledExecutorService$ScheduledFutureTask.run(UnboundedScheduledExecutorService.java:163)
    java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    java.base/java.lang.Thread.run(Thread.java:829)
Caused by: java.sql.SQLException: Cannot create PoolableConnectionFactory (The TCP/IP connection to the host xx.xx.xx.xxx, port 1433 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".)
    org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:653)
    org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:531)
    org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:731)
    org.apache.beam.sdk.io.jdbc.JdbcIO$ReadFn.getConnection(JdbcIO.java:1526)
    org.apache.beam.sdk.io.jdbc.JdbcIO$ReadFn.processElement(JdbcIO.java:1539)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host xx.xx.xx.xxx, port 1433 has failed. Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
    com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:237)
    com.microsoft.sqlserver.jdbc.SQLServerException.convertConnectExceptionToSQLServerException(SQLServerException.java:288)
    com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2574)
    com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:715)
    com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3427)
    com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:3077)
    com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2919)
    com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1787)
    com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1229)
    org.apache.commons.dbcp2.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:52)
    org.apache.commons.dbcp2.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:374)
    org.apache.commons.dbcp2.BasicDataSource.validateConnectionFactory(BasicDataSource.java:106)
    org.apache.commons.dbcp2.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:649)
    org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:531)
    org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:731)
    org.apache.beam.sdk.io.jdbc.JdbcIO$ReadFn.getConnection(JdbcIO.java:1526)
    org.apache.beam.sdk.io.jdbc.JdbcIO$ReadFn.processElement(JdbcIO.java:1539)
    org.apache.beam.sdk.io.jdbc.JdbcIO$ReadFn$DoFnInvoker.invokeProcessElement(Unknown Source)
    org.apache.beam.fn.harness.FnApiDoFnRunner.processElementForParDo(FnApiDoFnRunner.java:803)
    org.apache.beam.fn.harness.data.PCollectionConsumerRegistry$MetricTrackingFnDataReceiver.accept(PCollectionConsumerRegistry.java:348)
    org.apache.beam.fn.harness.data.PCollectionConsumerRegistry$MetricTrackingFnDataReceiver.accept(PCollectionConsumerRegistry.java:275)
    org.apache.beam.fn.harness.FnApiDoFnRunner.outputTo(FnApiDoFnRunner.java:1792)
    org.apache.beam.fn.harness.FnApiDoFnRunner.access$3000(FnApiDoFnRunner.java:143)
    org.apache.beam.fn.harness.FnApiDoFnRunner$NonWindowObservingProcessBundleContext.output(FnApiDoFnRunner.java:2650)
    org.apache.beam.sdk.transforms.MapElements$2.processElement(MapElements.java:151)
    org.apache.beam.sdk.transforms.MapElements$2$DoFnInvoker.invokeProcessElement(Unknown Source)
    org.apache.beam.fn.harness.FnApiDoFnRunner.processElementForParDo(FnApiDoFnRunner.java:803)
    org.apache.beam.fn.harness.data.PCollectionConsumerRegistry$MetricTrackingFnDataReceiver.accept(PCollectionConsumerRegistry.java:348)
    org.apache.beam.fn.harness.data.PCollectionConsumerRegistry$MetricTrackingFnDataReceiver.accept(PCollectionConsumerRegistry.java:275)
    org.apache.beam.fn.harness.BeamFnDataReadRunner.forwardElementToConsumer(BeamFnDataReadRunner.java:213)
    org.apache.beam.sdk.fn.data.BeamFnDataInboundObserver.multiplexElements(BeamFnDataInboundObserver.java:158)
    org.apache.beam.fn.harness.control.ProcessBundleHandler.processBundle(ProcessBundleHandler.java:537)
    org.apache.beam.fn.harness.control.BeamFnControlClient.delegateOnInstructionRequestType(BeamFnControlClient.java:150)
    org.apache.beam.fn.harness.control.BeamFnControlClient$InboundObserver.lambda$onNext$0(BeamFnControlClient.java:115)
    java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
    java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
    org.apache.beam.sdk.util.UnboundedScheduledExecutorService$ScheduledFutureTask.run(UnboundedScheduledExecutorService.java:163)
    java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    java.base/java.lang.Thread.run(Thread.java:829)

解决方案

1. 从GCP视角验证SQL Server连通性

本地能连接不代表GCP的Dataflow worker能连接。打开GCP Cloud Shell，执行以下命令测试：

nc -zv xx.xx.xx.xxx 1433

如果连接失败，说明你的SQL Server所在主机的公网防火墙/运营商端口拦截，需要开放1433端口的入站流量给GCP的IP段，或者配置VPC对等连接。

2. 配置Dataflow作业的网络参数

  • 如果SQL Server在私有网络，运行Dataflow作业时选择与SQL Server网络对等的GCP VPC，并指定对应的子网
  • 如果用公网连接，确保Dataflow作业启用Cloud NAT，或者子网有出站访问权限，没有被VPC防火墙规则拦截出站到1433端口的流量

3. 优化JDBC连接字符串

添加超时参数和SSL配置（如果未启用SSL）：

sqlserver://xx.xx.xx.xxx:1433;databaseName=AdventureWorks2019;user=xxxxxxx;password=xxxxxxxx;loginTimeout=30;connectTimeout=30;encrypt=false;
  • loginTimeout和connectTimeout设置为30秒，避免默认超时过短
  • encrypt=false关闭SSL（如果SQL Server未配置SSL证书，否则会导致握手失败）

4. 检查服务账号网络权限

确保Dataflow使用的服务账号拥有roles/compute.networkUser角色，允许它在指定VPC中创建和使用worker实例。

5. 确认SQL Server登录配置

  • 确保使用的是SQL Server身份验证账号，而非Windows身份验证（JDBC模板不支持Windows账号）
  • 验证该账号对AdventureWorks2019数据库及目标表有SELECT权限

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.21 15:04:57