Hi, after successfully connection using oauth flow and reciveing a token for scope
https://analysis.windows.net/powerbi/api/Warehouse.ReadWrite.All
using 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 side
once 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=true
and 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
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 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
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 | Micr...
Fabric doesn't have a solution yet, unless someone can comment on this thread.
@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:
