cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Connecting to a On Prem SQL Server Instance using JDBC

Nick_Pacey
New Contributor III

Hi,

We are trying to connect to an On-prem SQL Server instances using JDBC (we really want to use a Federated connection, testing JDBC first).  We have successfully done this for one of the SQL Servers we have, but cannot get it to work for the other.  We get an general error of "Connection refused".

Both SQL Servers are 2016.  The only difference between the configuration is that the SQL Server failing to work has an SQL instance setup i.e. the server host is hostname\instancename rather than just the hostname.

We've tried every variation of string we can think of (double slash, forward slashes, ASCII char), they either fail validation or get to the same error.  Networking confirms the traffic is hitting the SQL Server through our firewalls.

Anyone out there who is connecting to a SQL Server instance via Databricks JDBC / Lakehouse Federation who has any ideas what this might be?! 

Thanks, Nick.  

1 ACCEPTED SOLUTION

Accepted Solutions

Can you try with this additional config?

 

jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2};trustServerCertificate=true;loginTimeout=30;hostNameInCertificate=*.database.windows.net;".format(jdbcHostname,jdbcPort,jdbcDatabase)

 

View solution in original post

4 REPLIES 4

MadhuB
Contributor III

@Nick_Pacey Can you try the below approach and let me know how it goes. Make sure that the firewall on the SQL Server machine allows traffic on the dynamic port that the named instance is using. You can find the port being used by the named instance in SQL Server Configuration Manager under SQL Server Network Configuration.

%python
import pymssql

#jdbcHostname = "servername"
#jdbcPort = 1433
jdbcHostname = "servername\\instancename"
jdbcPort = 1433  # Typically, named instances use dynamic ports, so this might not be necessary.
jdbcDatabase = "databasename"
jdbcUsername = "username"
jdbcPassword = dbutils.secrets.get('vault-scope','pwd')


connectionProperties = {
    "user" : jdbcUsername,
    "password" : jdbcPassword,
    "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

jdbcUrl = f"jdbc:sqlserver://{jdbcHostname}:{jdbcPort};database={jdbcDatabase}"

conn = pymssql.connect(
            server=jdbcHostname, database=jdbcDatabase, user=jdbcUsername, password=jdbcPassword
        )

# Create a cursor object to execute SQL commands
cursor = conn.cursor()

# Execute Merge logic in SQL Server
merge_query = """
MERGE INTO employees AS target
USING new_employees AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.name = source.name, target.salary = source.salary
WHEN NOT MATCHED THEN
    INSERT (id, name, salary)
    VALUES (source.id, source.name, source.salary);
"""
# Execute the merge query
cursor.execute(merge_query)

#Commit the changes to the database
conn.commit()

# Provide a success message
print(f"Merge statement executed successfully.")

# Close the database connection
conn.close()

 

Nick_Pacey
New Contributor III

Thanks for coming back to me!

I'm using a different python SQL library, but my config is almost the same as your code above.  I'm having to use the IP address of the server rather than the name (does this matter?).  

This is the error I get..

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 192.168.xxx.xx, port 1433 has failed. Error: "Connection refused (Connection refused). 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."

I can see the traffic going through the firewall to the server.  In the Network configuration, I can see the IP address of the SQL Server but there is no port specifically defined.

Can you try with this additional config?

 

jdbcUrl = "jdbc:sqlserver://{0}:{1};database={2};trustServerCertificate=true;loginTimeout=30;hostNameInCertificate=*.database.windows.net;".format(jdbcHostname,jdbcPort,jdbcDatabase)

 

Nick_Pacey
New Contributor III

We've finally found and understood the issue.  It was down to a configuration on the SQL Server to use dynamic ports, but then define the port to listen on buried in the network configuration.  Once we had the right port to talk on, the JDBC connection was accepted and is working (very similar code to what you shared).

Thanks for your help!  Let's hope Lakehouse Federation will work with this configuration too!

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you won’t want to miss the chance to attend and share knowledge.

If there isn’t a group near you, start one and help create a community that brings people together.

Request a New Group