CODEWITHSUNDEEP
pythonazure-functionsazure-sql-database
Art Vandelay_
Art Vandelay_

Reputation: 101

Azure Function (Python) & Azure SQL DB - Error services aren't available right now -

I'm getting an odd error, when trying to create a connection to a azure sql database, through my azure function (Python & consumption). It all works locally, but no once deployed.

The error:

<!DOCTYPE html PUBLIC '-//W3C//DTD XHTML 1.0 Transitional//EN' 'http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd'><html xmlns='http://www.w3.org/1999/xhtml'><head><meta content='text/html; charset=utf-8' http-equiv='content-type'/><style type='text/css'>body {font-family:Arial; margin-left:40px; }img  { border:0 none; }#content { margin-left: auto; margin-right: auto }#message h2 { font-size: 20px; font-weight: normal; color: #000000; margin: 34px 0px 0px 0px }#message p  { font-size: 13px; color: #000000; margin: 7px 0px 0px0px}#errorref { font-size: 11px; color: #737373; margin-top: 41px }</style><title>Service unavailable</title></head><body><div id='content'><div id='message'><h2>Our services aren't available right now</h2><p>We're working to restore all services as soon as possible. Please check back soon.</p></div><div id='errorref'><span>[Some Long Code]=</span></div></div></body></html>

The Code:

import logging
import pyodbc
import azure.functions as func


def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')


    error = ''

    try:
        BB_connection = 'Driver={ODBC Driver 17 for SQL Server};Server=[server]\[instance];Database=[database];Trusted_Connection=yes;'

        cnxn = pyodbc.connect(BB_connection)        
    except Exception as e:
        error = str(e)

    

    return func.HttpResponse(f"Error: {error}")

in requirements.txt I have added pyodbc==4.0.31 I've tried looking around, but have not been able to find, a question where someone else, gets the same output.

Is there some link between my azure sql data base and the azure function, that has to be established? I'm just wondering why, it works locally, but not once deployed.

Upvotes: 0

Views: 1629

Answers (2)

Robert Dourado
Robert Dourado

Reputation: 1

My problem was the need of Azure Function IP permission in Azure SQL Database. After adding a range of IPs, I was able to access the database using the pyodbc library through the Azure Function itself.

First, I figured out the list of possible IPs for my Azure Function:

AzureFunction Settings

AzureFunction Outbound addresses

Then I sorted the IPs and added the range in the AzureSQL firewall settings:

Firewall AzureSQL

Addresses Allowed

Furthermore, even though the AzureSQL Database connection string indicates the Driver 'ODBC Driver 13 for SQL Server, I was only able to run the script when I set it to 'ODBC Driver 17 for SQL Server.

Connection string AzureSQL

Upvotes: 0

Delliganesh Sevanesan
Delliganesh Sevanesan

Reputation: 4796

Our services aren't available right now We're working to restore all services as soon as possible. Please check back soon.

The Error is not related to connecting azure SQL with the Azure function. It related to SSL/TLS certification validation error or your app/resource using CDN that access was not able to access.

I am following the below code to connect Azure SQL using the Azure function.

Make sure you are using the correct connection string for ODBC and use it in the below code

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')
        error = ''
        try:
            with pyodbc.connect( 'Driver={ODBC Driver 17 for SQL Server};''Server=tcp:<Yoursqlservername>.database.windows.net,1433;''Database=<databaseName>;Uid=<Your userID>;Pwd=<YOUR PWD>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')as conn:
                with conn.cursor() as cursor:
                cursor.execute("INSERT INTO [ApplicationLogs] ([LogMessage],[CreateDate]) VALUES('This is a test log message 1.', GETDATE())")
                cursor.execute("SELECT * FROM [ApplicationLogs]")
                row = cursor.fetchall()
        except Exception as e:
        error = str(e)
    return func.HttpResponse(f"row fetch: {row}")

You need to allow the firewall access of your Azure Function IP address from where you are running your azure function.

Here I have added the IP which I was accessing the azure SQL server using the Azure Function

enter image description here

And if you are using any certificate while accessing the SQL make sure you need to use that.

enter image description here

Here workaround

enter image description here

Browser response for connecting Azure SQL and getting values

enter image description here

Upvotes: 1

Related Questions