Hi, after successfully connection using oauth flow and reciveing a token for scope https://analysis.windows.net/powerbi/api/Warehouse.ReadWrite.Allusing my access code I can get a refresh code and using that refresh code I can get multiple access codes so that session is working fine and the code is also working to connect to snowflake using azure so I don't think the error is on that sideonce I use the same connection for I get "The TCP/IP connection to the host pbipwus9-westus.pbidedicated.windows.net\<some id> (redirected from <same some id>.datawarehouse.fabric.microsoft.com), port 1433 has failed. Error: "null. 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."."the connection propertis are accessToken, encrypt=true trustServerCertificate=trueand connection string jdbc:sqlserver://<SQL connection string> taken from the UI the only guess I can find is that TCP/IP is turned off and we're using shared memory that is working for Sql Server but can't work for JDBC

I'm facing the same issue from Synapse Spark. The problem is due to Fabric's forced usage of Redirect connections for Azure hosted compute. Local Spark works fine, cloud does not.The error:2024-09-07 22:59:22,622 INFO ApplicationMaster [shutdown-hook-0]: Unregistering ApplicationMaster with FAILED (diag message: User class threw exception: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host ....pbidedicated.windows.net\...dw (redirected from ...-....msit-datawarehouse.fabric.microsoft.com), port 1433 has failed. Error: "null. 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.". at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:234) at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:285) at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2462) at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:668) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:2695) at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2362) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2213) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1276) at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:861) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:189) The code (scala):val server = driverOpts.FabricSqlKeepAliveDriver.Source.SqlServer.Server val domain = driverOpts.FabricSqlKeepAliveDriver.Source.SqlServer.Domain val port = driverOpts.FabricSqlKeepAliveDriver.Source.SqlServer.Port val database = driverOpts.FabricSqlKeepAliveDriver.Source.SqlServer.Database val tables = driverOpts.FabricSqlKeepAliveDriver.Source.SqlServer.Tables val jdbcUrl = s"jdbc:sqlserver://${server}.${domain}:${port};database=${database}" val connectionProperties = new java.util.Properties() connectionProperties.setProperty("accessToken", accessToken) connectionProperties.setProperty("encrypt", "true") connectionProperties.setProperty("trustServerCertificate", "false") connectionProperties.setProperty("hostNameInCertificate", s"*.${domain}") connectionProperties.setProperty("loginTimeout", "30") DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver()); // This will throw in Azure Synapse Spark val connection: Connection = DriverManager.getConnection(jdbcUrl, connectionProperties)Local:Azure:In regular Azure SQL, you can force Proxy connections to solve this problem:Azure SQL Database connectivity architecture - Azure SQL Database and Azure Synapse Analytics | Microsoft LearnFabric doesn't have a solution yet, unless someone can comment on this thread.

I'd open a support ticket and ask the support engineer to raise an "ICM" so he/she can get in touch with the Fabric Software Engineer that wrote this, my guess is, that Software Engineer didn't test in Azure Synapse or other Azure Compute.

benor1470 I was able to solve the problem. Old JDBC drivers don't work with Fabric because the Fabric Proxy forces Redirect mode, you need to use the latest JDC drivers wherever you are running your code. In case of Synapse, even though I was using the latest Driver, Synapse CLASSPATH overrides what the user passes in their JAR. Shading your JAR solves the problem and it works. Please use this version: Shade your spark-submit JAR: Works fine in Synapse:

Hi,benor1470 Regarding the issue you raised, my solution is as follows: 1.First of all, there is the "check if TCP/IP is enabled" you mentioned: make sure that SQL Server is configured with the TCP/IP protocol and is listening on port 1433. You can verify this in SQL Server Configuration Manager as follows: 2.Second, you can try using the telnet command to test the connection: What you may want to note is that you need to turn on the following settings in advance: Here is a screenshot of the official document: Troubleshooting connectivity - JDBC Driver for SQL Server | Microsoft Learn 3.Then, make sure your network configuration allows TCP/IP connections from the client to SQL Server. If you're using a VPN or proxy server, you may need to make configuration adjustments accordingly. 4.Finally, make sure that the SQL Server instance is running and can accept external connections. You can check the status of your instance through SQL Server Management Studio (SSMS). Of course, if you have any new ideas, you are welcome to contact us. Best Regards, Leroy Lu If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you but non of those helped1) first since it's a fabric database I can't access it's config to change TCP/IP configas far as I can see this tool can only be used to make local connections2/3) I can telnet to the public address, the issue isn't with the network connection on my side4) I managed to connect using SSMS

jdbc connector receives TCP/IP error against the warehouse

11 Replies

Anonymous
Not applicable
2 years ago
Hi,benor1470

Regarding the issue you raised, my solution is as follows:

1.First of all, there is the "check if TCP/IP is enabled" you mentioned: make sure that SQL Server is configured with the TCP/IP protocol and is listening on port 1433. You can verify this in SQL Server Configuration Manager as follows:

2.Second, you can try using the telnet command to test the connection:

What you may want to note is that you need to turn on the following settings in advance:

Here is a screenshot of the official document:

Troubleshooting connectivity - JDBC Driver for SQL Server | Microsoft Learn

3.Then, make sure your network configuration allows TCP/IP connections from the client to SQL Server. If you're using a VPN or proxy server, you may need to make configuration adjustments accordingly.

4.Finally, make sure that the SQL Server instance is running and can accept external connections. You can check the status of your instance through SQL Server Management Studio (SSMS).

Of course, if you have any new ideas, you are welcome to contact us.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- benor1470
  Frequent Visitor
  2 years ago
  Thank you but non of those helped
  1) first since it's a fabric database I can't access it's config to change TCP/IP config
  as far as I can see this tool can only be used to make local connections
  
  2/3) I can telnet to the public address, the issue isn't with the network connection on my side
  4) I managed to connect using SSMS
  - Anonymous
    Not applicable
    2 years ago
    Hi, benor1470
    
    Regarding the issue you raised, my solution is as follows:
    
    1.First of all, I observed that the database you checked is from warehouse, but the error message is from SQL server, so we want you to check if the data source that warehouse is connected to has this problem.
    
    2.Secondly, since the error is related to the port, we also hope that you can check whether your port is allowed or not, and the following documents hope to help you:
    Add Power BI URLs to allowlist - Microsoft Fabric | Microsoft Learn
    
    Of course, if you have any new ideas, you are welcome to contact us.
    
    Best Regards,
    
    Leroy Lu
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
benor23
New Member
2 years ago
anyone knows? is this thing supported? am I asking in the right place?
benor1470
Frequent Visitor
1 year ago
Hi, upgraded msal4j as I saw you had a new version and now the error is different
"Login failed due to invalid authentication methods."

not sure if it's because of the upgrade and I can't find any resources about this error message
maybe it's because of the scope, I tried
https://analysis.windows.net/powerbi/api/Warehouse.ReadWrite.All
https://analysis.windows.net/powerbi/api/Connection.Read.All
https://sql.azuresynapse-dogfood.net/user_impersonation
but non of them works, is there a different scope I should be using?

Forum Discussion

jdbc connector receives TCP/IP error against the warehouse

11 Replies

Related content

Synapse SQL connector for warehouse failed to write data.

Spark Connector for MS Fabric Warehouse

Delay in receiving alerts

Warehouse Scaling

warehouse vs lakehouse