Login failed for user token-identified principal when connecting via sql_driver_manager.getConnection

Question

I am trying to connect to Azure SQL using Service Principle to create views, but it says com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user ClientConnectionId: XXXXX-XXXX-XXXX

However, with the same SPN I was able to connect and create tables, read tables.

import adal

resource_app_id_url = "https://database.windows.net/"
service_principal_id = dbutils.secrets.get(scope = "XX", key = "XXX")
service_principal_secret = dbutils.secrets.get(scope = "XX", key = "spn-XXXX")
tenant_id = dbutils.secrets.get(scope = "XX", key = "xxId")
authority = "https://login.windows.net/" + tenant_id
azure_sql_url = "jdbc:sqlserver://xxxxxxx.windows.net"
database_name = "testDatabase"
encrypt = "true"
host_name_in_certificate = "*.database.windows.net"

context = adal.AuthenticationContext(authority)
token = context.acquire_token_with_client_credentials(resource_app_id_url, service_principal_id, service_principal_secret)
access_token = token["accessToken"]

using above code I am able to create and read tables. There is a requirement to create views so I am using sql_driver_manager to connect to Azure SQL

properties = spark._sc._gateway.jvm.java.util.Properties()
properties.setProperty("accessToken", access_token)


sql_driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager
sql_con = sql_driver_manager.getConnection(azure_sql_url, properties)

query = """
create or alter view test_view as select * from dbo.test_table

"""
stmt = sql_con.createStatement()
stmt.executeUpdate(query)
stmt.close()

this is resulting in an error:

Py4JJavaError: An error occurred while calling z:java.sql.DriverManager.getConnection. : com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user token-identified principal. ClientConnectionId:

If I try the same with username and password instead of token, it works but I just need to use spn token for authenticating.

Working code:

sql_driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager
sql_con = sql_driver_manager.getConnection(azure_sql_url, username, password)

query = """
create or alter view test_view as select * from dbo.test_table

"""
stmt = sql_con.createStatement()
stmt.executeUpdate(query)
stmt.close()

What is that I am missing, can someone help me understand the issue. Thanks.

Answer 1

You can not use that method since it was built to execute an update statement and return indexes.

See documentation. Use the prepare() and execute() methods.

https://learn.microsoft.com/mt-mt/sql/connect/jdbc/reference/executeupdate-method-java-lang-string?view=azuresqldb-current

#
#  5 - Upsert from stage to active table.
#      Grab driver manager conn, exec sp
#

driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager
connection = driver_manager.getConnection(url, user_name, password)
connection.prepareCall("exec [stage].[UpsertFactInternetSales]").execute()
connection.close()

This is sample code from an article I wrote. It calls a stored procedure to execute an UPSERT. However, any DML or DDL will work as long as it does not return a result set.