0

We have certain security requirements in order for our app to go live within our orgainisation.

We are using the Microsoft azure platform to host the application along with a Azure SQL server and database. To meet these security requirements, we need to configure settings on the server/database.

However we are running into issues using the default azure SQL server/database. Here is an example. We need to "Disable 'clr enabled' option". We have tried the following:

EXECUTE sp_configure 'show advanced options', 1;
RECONFIGURE;
EXECUTE sp_configure 'clr enabled', 0;
RECONFIGURE;
GO
EXECUTE sp_configure 'show advanced options', 0;
RECONFIGURE;

We run this in the T-SQL editor on the Azure platform, and receive the following:

Failed to execute query. Error: Statement 'CONFIG' is not supported in this version of SQL Server.

When we run the following, we see that is enabled.

SELECT name,
CAST(value as int) as value_configured,
CAST(value_in_use as int) as value_in_use
FROM sys.configurations
WHERE name = 'clr enabled';

enter image description here

How to we update these settings?

thanks.

Conor8630
  • 345
  • 1
  • 17
  • The error is telling you the problem here... I assume you are using an Azure SQL Database, rather than SQL Server hosted in Azure. Though Azure SQL Database's don't support OLE Automation Procedures as far as I am aware. (Certainly my instance doesn't have the objects). – Thom A Jan 12 '21 at 13:48
  • I think CLR is a better example. It is enabled currently and we are failing to see how to disable it. I'll update my question with that example – Conor8630 Jan 12 '21 at 13:55
  • CLR is also, not supported. [Resolving Transact-SQL differences during migration to SQL Database](https://learn.microsoft.com/en-gb/azure/azure-sql/database/transact-sql-tsql-differences-sql-server): *"**Transact-SQL syntax not supported in Azure SQL Database**: .NET Framework: CLR integration with SQL Server."* – Thom A Jan 12 '21 at 13:57
  • That document I linked also states (under the same heading): *"`sp_configure` options and `RECONFIGURE`. Some options are available using [ALTER DATABASE SCOPED CONFIGURATION](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql)."* – Thom A Jan 12 '21 at 13:58
  • My instance, too, returns `1`, but that doesn't change the fact it is *not* supported. – Thom A Jan 12 '21 at 14:01
  • Perfect! Thank you for clarifying. – Conor8630 Jan 12 '21 at 14:02

1 Answers1

1

sp_configure (Transact-SQL) is not supported in Azure SQL database: enter image description here

We can not run the sp_configure statements. But sys.configurations (Transact-SQL) table is supported. enter image description here

We can see the default value is 1 for clr enabled.

And like @Larnu said, CLR is also not supported: Resolving Transact-SQL differences during migration to SQL Database.

Ref this question: Does or does not SQL Azure support CLR assemblies?

Just for now, we can not change this settings in Azure SQL database.

HTH.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23