0

i am using databricks with pyspark, i am doing some test to connect to on-premise databases and i find a problem : i can do it with python but no with pyspark. for example this code with python and pymssql works and i can get data later :

import pymssql
try:
    conn = pymssql.connect(
        host=host,
        user=user,
        password=password,
        database=db
    )
except Exception as e:
 print("An error occurred while connecting to SQL Server:", e)

but this codes does not work

df = spark.read \
.format("jdbc") \
    .option("url", f"jdbc:sqlserver://{host}:1433;database={db};user={user};password={password}") \
    .option("dbtable", db) \
    .load()

the credential are the same , i have tried many ways, with many drivers, jars, etc. Has anyone had something similar happen? could it be a networking error? the error is always "com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ... " thanks

SOLUTION

integrated_security = 'true'
authentication_scheme = "NTLM"
url = f"jdbc:sqlserver://{host}:{port};database={nombre};integratedSecurity={integrated_security};authenticationScheme={authentication_scheme}"

query = f"(SELECT TOP 10 * FROM {table}) AS subquery"

jdbcDF = spark.read \
    .format("jdbc") \
    .option("url", url) \
    .option("dbtable", query) \
    .option("user", f'{user_solo}@{domain}') \
    .option("password", clave) \
    .load()
oviedoh7
  • 1
  • 1
  • Do any of these variables have special characters? – Zero Jul 14 '23 at 00:41
  • Or try [this](https://stackoverflow.com/a/12769241) or [this](https://stackoverflow.com/a/24320153) – Zero Jul 14 '23 at 00:46
  • no, they does not have special characters, only the user is like "Domain\User". i used the "integratedSecurity=true" but keeps returning the same error – oviedoh7 Jul 16 '23 at 00:27
  • Try using arguments from the other answers I shared above, and see if that works. – Zero Jul 16 '23 at 01:13
  • thanks, i can connect now, the error was raised because i was not using integrated_security and i was setting the user like {domain}\{user} insted of {user}@{domain} – oviedoh7 Jul 18 '23 at 14:52

0 Answers0