Persistent "Login Failed" using PySpark JDBC + Token Auth for Fabric SQL Database

[ackerman_chris]

Hi everyone,

I'm currently writing a notebook in PySpark that is attempting to write data over to a new Microsoft Fabric SQL Database. I’m trying to keep my ingestion logic strictly within Fabric Notebooks (PySpark), but I’ve hit a wall with the JDBC writer that I can't seem to get past.

The Goal

I am pulling data from a REST API into a Spark DataFrame and attempting to write it directly to a Fabric SQL Database. Using mode("append") so that Spark handles the CREATE TABLE DDL on the first run.

The Setup

• Identity: Running the notebook manually under my Entra ID (for now).

• Permissions: I have verified my user exists in the SQL Database (sys.database_principals) and has been granted db_datawriter, db_datareader, and db_ddladmin roles.

• Authentication: I am fetching an access token via notebookutils and passing it into the JDBC properties.

• Notebook Properties: The notebook is using my user account as the Run As and is the owner as well.

The Problem

Despite having the correct permissions and a valid token, the write operation consistently fails with:

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server "<REDACTED>" requested by the login. The login failed.

I have tried multiple token audiences, including "pbi" and "https://database.windows.net/", but the result is the same. The connection works fine for a spark.read, but the .write (which should trigger a DDL operation if the table is missing) triggers this login failure.

# Write data to Fabric SQL Database ─────────────────────────

if 'df' in locals() and not df.isEmpty():
    logger.info(f"Initiating write sequence for {schema}.{table_name}...")

    # 1. Fetch the SQL-specific token
    sql_token = notebookutils.credentials.getToken("pbi")
    
    write_props = {
        "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
        "accessToken": sql_token
    }
    
    jdbc_url = f"jdbc:sqlserver://{server_name}:1433;databaseName={database_id};"

    try:
        # 2. THE MAGIC LINE:
        # mode("append") checks if the table exists. 
        df.write.jdbc(
            url=jdbc_url,
            table=f"{schema}.{table_name}",
            mode="append", 
            properties=write_props
        )
        logger.info("Successfully wrote data. Table was created or appended automatically.")

    except Exception as e:
        # 3. Catching the 'First Run' Gremlin
        if "is not a valid schema" in str(e) or "Schema does not exist" in str(e):
            logger.error(f"FAIL: The schema '{schema}' doesn't exist.")
            logger.info(f"ACTION: Go to the SQL DB UI and run: CREATE SCHEMA {schema};")
        else:
            logger.error(f"Write failed: {str(e)}")
            raise e
else:
    logger.warning("No data retrieved from API. Skipping write.")

 

What I've Checked

1. Verified the Server Endpoint and Database GUID are correct.

2. Confirmed the Schema (traffic) was pre-created in the SQL DB.

3. Tried the standard .format("com.microsoft.sqlserver.jdbc.spark") which resulted in an AccessToken property conflict.

4. Verified that my user account can connect and query via the SQL Database portal UI.

I found this Stack Overflow posting that had a similar issue and I wasn't able to resolve it from here python - How to connect to Fabric SQL database from Notebook? - Stack Overflow

Is there a specific driver property or a different token required for the Fabric SQL DB engine specifically when performing DDL/Write operations to a Fabric SQL Database?

Any insight from someone who has this working in production would be much appreciated!

[v-nmadadi-msft]

Hi @ackerman_chris 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @ackerman_chris 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[deborshi_nag · Accepted solution]

Hello @ackerman_chris 

The best option is to use the Spark connector for SQL databases in Fabric - details in the Microsoft doc below:

Spark connector for SQL databases - Microsoft Fabric | Microsoft Learn

Please bear in mind this is still in Preview. 

For getting the right connection string for your database, go to the Fabric SQL database in question, Settings > Connection Strings > JDBC. Remove "authentication=ActiveDirectoryInteractive" from the connection string. It should end with a ";".

Here's a sample code:

import com.microsoft.sqlserver.jdbc.spark


data = [("Alice", 1), ("Bob", 2), ("Charlie", 3)]
cols = ["Name", "Age"]
df = spark.createDataFrame(data, cols)

# 2) JDBC URL for your Fabric SQL database
url = "jdbc:sqlserver://blah-blah.database.fabric.microsoft.com:1433;database={blah};encrypt=true;trustServerCertificate=false;" 


(df.write
   .mode("overwrite")                 # overwrite | append | errorifexists | ignore
   .option("url", url)
   .mssql("dbo.publicExample"))

#  3) Read it back to verify
spark.read.option("url", url).mssql("dbo.publicExample").show()