I’m currently trying and failing to connect get an Azure Function to work. I created a SQL Server with a database.
I also created an Azure Function, I configured cors to allow all origins, I have set up that the connection string of the database in my Azure function, I have all the environment variables like DB_USER, DB_PASSWORD, etc. all set up.
I added all of the out IP’s of the Function app in my SQL Server, I also enabled it so that all Azure resources can use it, I even added my Azure function as a Microsoft Entra admin.
I installed SQL Server using the kudu console.
Nothing works – I always seem to get a 500 error with no log.
This is the code for my HttpTrigger, I need to only use the Azure portal editor, I cannot create another project in VS Code to publish, what can I do?
const sql = require('mssql');
module.exports = async function (context, req) {
try {
// Log that the function has started
context.log('Function execution started.');
// Set up connection to Azure SQL Database
const config = {
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
server: process.env.DB_SERVER,
database: process.env.DB_DATABASE,
options: {
encrypt: true,
enableArithAbort: true
}
};
// Connect to SQL Server
await sql.connect(config);
context.log('Successfully connected to SQL Server.');
// Send a success message
context.res = {
status: 200,
body: "Successfully connected to the database!" // Success message
};
} catch (err) {
// Log the error for debugging
context.log.error('Error occurred:', err);
// Return a 500 error with a message
context.res = {
status: 500,
body: `Error connecting to the database: ${err.message}` // Error message
};
} finally {
context.log('Function execution completed.');
}
};
This is the error I get
Executing ‘Functions.GetGptwai’ (Reason=’This function was programmatically called via the host APIs.’, Id=)
Sending invocation id: ‘
Posting invocation id: on workerId:[Error] Executed ‘Functions.GetGptwai’ (Failed, Id=, Duration=1ms)
I tried to allow all origins in cors, setting my Azure function as admin, adding all ips to the whitelist
N4CH0L is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
Follow below steps to connect Azure function app to Azure SQL database, refer MSDOC for code.
- Create a SQL server and database.
- Deploy the function code to Azure functionapp.
My function code:
const { app, input } = require('@azure/functions');
const sqlInput = input.sql({
commandText: 'select [Id], [order], [title], [url], [completed] from dbo.ToDo',
commandType: 'Text',
connectionStringSetting: 'SqlConnectionString',
});
app.http('httpTrigger1', {
methods: ['GET'],
authLevel: 'anonymous',
extraInputs: [sqlInput],
handler: (request, context) => {
context.log('HTTP trigger and SQL input binding function processed a request.');
const toDoItems = context.extraInputs.get(sqlInput);
return {
jsonBody: toDoItems,
};
},
});
Add SQL server connection string in Function App=>Environment Variables=>App Settings:
"SqlConnectionString":"Server=tcp:servername.database.windows.net,1433;Initial Catalog=rkdb;Persist Security Info=False;User ID={Username};Password={Password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;"
Navigate to SQL server=>Access Control(IAM)=>Assign Sql Server contributor role to your UserID or service principal.
Run the deployed function:
Function App Logs:
2024-12-06T10:56:28Z [Verbose] AuthenticationScheme: WebJobsAuthLevel was successfully authenticated.
2024-12-06T10:56:28Z [Verbose] AuthenticationScheme: Bearer was not authenticated.
2024-12-06T10:56:28Z [Verbose] Authorization was successful.
2024-12-06T10:56:28Z [Information] Executing 'Functions.httpTrigger1' (Reason='This function was programmatically called via the host APIs.', Id=aa8cf697-fd96-499c-90a9-0c7cb310b76d)
2024-12-06T10:56:28Z [Verbose] Sending event Create
2024-12-06T10:56:30Z [Verbose] Sending event Convert
2024-12-06T10:56:30Z [Information] 1 row(s) queried from database: rkdb using Command: select [Id], [order], [title], [url], [completed] from dbo.ToDo
2024-12-06T10:56:30Z [Verbose] Sending invocation id: 'aa8cf697-XX499c-90a9-0c7cb310b76d
2024-12-06T10:56:30Z [Verbose] Posting invocation id:aa8cf697-fd9XX9-0c7cb310b76d on workerId:4b6649e1-XX-b666-0abca6973c96
2024-12-06T10:56:30Z [Information] HTTP trigger and SQL input binding function processed a request.
2024-12-06T10:56:30Z [Information] Executed 'Functions.httpTrigger1' (Succeeded, Id=aa8cf697-fd96-XXb310b76d, Duration=1628ms)
Response: