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

Question

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.

Are you using Certificates in your azure function ?

Delliganesh Sevanesan
– Delliganesh Sevanesan

2022-01-21 04:45:22 +00:00
Commented Jan 21, 2022 at 4:45 — Delliganesh Sevanesan
– Delliganesh Sevanesan, Commented Jan 21, 2022 at 4:45

Delliganesh Sevanesan · Accepted Answer · 2022-01-21 07:01:52Z

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

Robert Dourado · Accepted Answer · 2022-02-11 04:14:03Z

0

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:

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

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.

edited Feb 11, 2022 at 4:14

anon

answered Feb 10, 2022 at 18:15

Robert Dourado

12 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related