can't load data if I am not login into Azure SQL database

Question

can't load data if I am not login into Azure SQL database

Renan 60

I have created a job in Azure Databrick to transform data and load data into SQL database and schedule the time for it. If I am login into SQL database the code works fine and data is loaded into the database. However, if I am not login into SQL database I get this error:

“Py4JJavaError: An error occurred while calling o442.jdbc.

: com.microsoft.sqlserver.jdbc.SQLServerException: Database ' name of the database' on server 'name-of-server.database.windows.net' is not currently available. Please retry the connection later.”

The option “Allows Azure services and resources to access this server” is also ticked and saved.

Can you explain how to fix this problem? I need to be able to load the data into the database when I am not login. The code has all the credentials, but it only works when I actually manually has login into the SQL database and then run the code.

Confused...

Kind regards

Renan 60 Reputation points

2024-12-05T11:11:09.1233333+00:00

Also every time I login into the SQL database it asks me to add a new IP. How to fix this as well ?
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-12-06T19:02:58.59+00:00

@Renan We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Renan 60 Reputation points

2024-12-09T10:00:46.09+00:00

Can you explain in clear steps how to use this Service Principal Authentication. I have created the Service Principal Authentication, but how do I use it my Databrick notebook to connect to the azure SQL database? I tried to Google how to use it, but found nothing useful.

Can you also explain in clear steps how to use this VPN or Azure Bastion as well?
Renan 60 Reputation points

2024-12-09T10:01:56.33+00:00

Can you explain in clear steps how to use this Service Principal Authentication. I have created the Service Principal Authentication, but how do I use it my Databrick notebook to connect to the azure SQL database? I tried to Google how to use it, but found nothing useful.

Can you also explain in clear steps how to use this VPN or Azure Bastion as well?
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-12-10T18:54:17.9566667+00:00
@Renan

Please check below steps

Using Service Principal Authentication in Databricks

Create a Service Principal:

In the Azure portal, navigate to Azure Active Directory > App registrations > New registration.

Enter a name for your application and register it.

After registration, go to Certificates & secrets and create a new client secret. Note down the Client ID, Tenant ID, and Client Secret.

Assign Roles to the Service Principal:

Navigate to your Azure SQL Database.

Go to Access control (IAM) > Add role assignment.

Assign the Contributor role to your service principal.

Configure Databricks to Use the Service Principal:

In your Databricks workspace, create a secret scope to store your service principal credentials securely.

Use the following commands in a Databricks notebook to create the secret scope:Python
dbutils.secrets.createScope(scope = "myScope") dbutils.secrets.put(scope = "myScope", key = "clientId", stringValue = "<your-client-id>") dbutils.secrets.put(scope = "myScope", key = "clientSecret", stringValue = "<your-client-secret>") dbutils.secrets.put(scope = "myScope", key = "tenantId", stringValue = "<your-tenant-id>")

Connect to Azure SQL Database:

Use the following code in your Databricks notebook to connect to the Azure SQL Database:Python
import pyodbc import adal authority_url = 'https://login.microsoftonline.com/' + dbutils.secrets.get(scope = "myScope", key = "tenantId") resource_url = 'https://database.windows.net/' client_id = dbutils.secrets.get(scope = "myScope", key = "clientId") client_secret = dbutils.secrets.get(scope = "myScope", key = "clientSecret") context = adal.AuthenticationContext(authority_url) token = context.acquire_token_with_client_credentials(resource_url, client_id, client_secret) conn = pyodbc.connect( 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=<your-server-name>.database.windows.net;DATABASE=<your-database-name>;UID=<your-username>;PWD=' + token['accessToken'] ) cursor = conn.cursor() cursor.execute("SELECT * FROM your_table") rows = cursor.fetchall() for row in rows: print(row)

Using VPN

Create a Virtual Network Gateway:

In the Azure portal, navigate to Create a resource > Networking > Virtual network gateway.

Configure the settings and create the gateway.

Configure the VPN Client:

Download the VPN client configuration from the Azure portal.

Install the VPN client on your local machine and connect to the VPN.

Connect to Azure SQL Database:

Once connected to the VPN, you can access your Azure SQL Database using its private IP address.

Using Azure Bastion

Create a Bastion Host:

In the Azure portal, navigate to Create a resource > Networking > Bastion.

Configure the settings and create the Bastion host.

Connect to Azure SQL Database:

In the Azure portal, navigate to your virtual machine.

Click on Connect and select Bastion.

Use the Bastion host to securely connect to your virtual machine and access the Azure SQL Database.

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-12-12T18:18:39.6566667+00:00

@Renan We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

1 answer

Your answer

Renan 60 Reputation points

2024-12-05T11:11:09.1233333+00:00

Also every time I login into the SQL database it asks me to add a new IP. How to fix this as well ?
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-12-06T19:02:58.59+00:00

@Renan We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Renan 60 Reputation points

2024-12-09T10:00:46.09+00:00

Can you explain in clear steps how to use this Service Principal Authentication. I have created the Service Principal Authentication, but how do I use it my Databrick notebook to connect to the azure SQL database? I tried to Google how to use it, but found nothing useful.

Can you also explain in clear steps how to use this VPN or Azure Bastion as well?
Renan 60 Reputation points

2024-12-09T10:01:56.33+00:00

Can you explain in clear steps how to use this Service Principal Authentication. I have created the Service Principal Authentication, but how do I use it my Databrick notebook to connect to the azure SQL database? I tried to Google how to use it, but found nothing useful.

Can you also explain in clear steps how to use this VPN or Azure Bastion as well?
phemanth 15,755 Reputation points Microsoft External Staff Moderator

2024-12-12T18:18:39.6566667+00:00

@Renan We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Answer 1

Hi@Renan

Thanks for reaching out to Microsoft Q&A.

The error you’re seeing suggests that the database might not be accessible due to transient connectivity issues or configuration settings. Here are steps to troubleshoot and resolve this:

Check Firewall Rules: Ensure that your IP address is allowed to access the Azure SQL Database. You can add your IP address to the firewall rules in the Azure portal under the SQL Database settings.
Service Principal Authentication: Instead of using your personal login, consider using a service principal for authentication. This way, your Databricks job can authenticate without needing you to be logged in. You can set up a service principal in Azure Active Directory and grant it access to your SQL Database.
Retry Logic: Implement retry logic in your code to handle transient errors. Azure SQL Database can sometimes have brief periods of unavailability due to maintenance or load balancing. Adding retry logic can help your application handle these temporary issues gracefully.
Connection String: Ensure your connection string is correctly configured with all necessary parameters, including the correct server name, database name, and authentication details.

If you’re frequently prompted to add a new IP address, it might be due to dynamic IP changes from your ISP. Here are some solutions:

Use a Static IP: If possible, use a static IP address for your connection. This will prevent the need to update the firewall rules frequently.
VPN or Azure Bastion: Consider using a VPN or Azure Bastion to connect to your Azure SQL Database. This can provide a consistent IP address for your connections.
Allow Azure Services: Ensure that the option to allow Azure services and resources to access your server is enabled. This setting can help if your Databricks job is running within Azure.

Please check the below reference for more details

Troubleshoot common connection issues - Azure SQL Database & Azure SQL Managed Instance

I hope the above steps will resolve the issue, please do let us know if issue persists. Thank you

Share via

can't load data if I am not login into Azure SQL database

1 answer

Your answer