We’re attempting to deploy a machine learning model to Azure SQL Database for in-database scoring, but consistently hitting permission denied errors during external script execution. Our model is trained using Python and we’re trying to enable ML Services for real-time predictions.
The error occurs when executing:
EXEC sp_execute_external_script
@language = N'Python',
@script = N'import pickle; model = pickle.loads(model_data)'
We’ve verified the database user has db_owner role, but external script execution still fails. The model needs to score transactions in real-time, so this is blocking our production deployment. Has anyone successfully configured role-based access control for external script execution in Azure SQL?
For 5000 predictions/hour, REST API calls are totally viable. That’s roughly 1.4 requests per second - well within Azure ML endpoint capabilities. Latency will be higher than in-database (50-200ms vs 5-10ms), but you can batch predictions to improve throughput. Consider using Azure ML managed online endpoints with autoscaling. The trade-off is acceptable for most use cases unless you need sub-50ms response times.
The Managed Instance migration sounds promising but might be overkill for our current scale. We’re processing about 5000 predictions per hour. Would the REST API approach with Azure ML endpoints handle that volume efficiently? Concerned about latency compared to in-database scoring.
Azure SQL Database doesn’t support ML Services with external script execution like on-premises SQL Server does. You have a few options: deploy your model as an Azure ML endpoint and call it via stored procedures using REST APIs, use Azure SQL Managed Instance which does support ML Services, or implement scoring logic using CLR assemblies if your model is simple enough. The REST API approach is most common - you can use sp_invoke_external_rest_endpoint if you’re on a compatible tier.
We had this exact issue last quarter. Ended up migrating to Azure SQL Managed Instance specifically for ML Services support. The migration was straightforward using Azure Database Migration Service. If you go this route, make sure to enable external scripts at the instance level and grant EXECUTE ANY EXTERNAL SCRIPT permission to your application service principal. The performance of in-database scoring is significantly better than calling external endpoints for high-volume scenarios.