Failed to connect to database when lightweight pooling is enabled

Cause

To enable lightweight pooling, Common Language Runtime (CLR) must be disabled in SQL Server. When CLR is disabled, XML cannot be successfully queried. XML support is required for geodatabases at ArcGIS 10 and later releases. ArcSDE is unable to query the GDB schema tables in the geodatabase, as these tables have XML columns.

Solution or Workaround

To enable Common Language Runtime (CLR), it is necessary that lightweight pooling be disabled in SQL Server, as they are mutually exclusive. This allows connection to the geodatabase hosted on SQL Server.

On the SQL Server instance, run a script similar to the following:

EXEC sp_configure 'show advanced options' , '1';
GO
RECONFIGURE;
GO
-- Disable Lightweight Pooling
exec sp_configure 'lightweight pooling', 0;
GO
RECONFIGURE;
GO
-- Enable CLR
EXEC sp_configure 'clr enabled' , '1'
GO
RECONFIGURE;
GO