jdbc connector receives TCP/IP error against the warehouse

[benor1470]

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

[benor1470]

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?

[benor23]

anyone knows? is this thing supported? am I asking in the right place?

[v-linyulu-msft]

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]

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

[v-linyulu-msft]

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

[benor1470]

the data warehouse was created from
https://app.fabric.microsoft.com/groups/me/quickcreate?experience=power-bi
and selecting sample data, so it should be the most standart and avaiable item

I'm not under any firewall, so the allow list isn't related, as far as I see the block is coming from fabric site

[mdrakiburrahman]

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]

should I open a feature request or a bug somewhere? currently is shows in the docs like jdbc is a available
https://learn.microsoft.com/en-us/fabric/data-warehouse/connectivity#connect-using-jdbc

[mdrakiburrahman]

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.

[mdrakiburrahman]

@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:

[benor1470]

Thank you but anfortunatly it's not this
I'm using https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc/12.8.1.jre11 and https://mvnrepository.com/artifact/com.microsoft.azure/msal4j/1.17.0 and I don't have a any shaded jars as I load my drives using extral class loader and I don't use spark or Synapse

I can create a new project that just shows this issue, could you look at it?