Tuesday, August 14, 2012

System.Data.SqlClient.SqlException: Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances

I. When I try to run ASP.net application with SQL Server database an error shown as below:


Server Error in '/' Application.
--------------------------------------------------------------------------------
Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.

Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

II. When I try to connect to a SQL Server database from visual studio server explorer an error shown as below:  
---------------------------
Microsoft Visual Studio
---------------------------
Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.
---------------------------
OK
---------------------------

Solution
This error is due to SQL Server Express has disabled user instance genereation. Simply apply the below steps to enable user instance generation.

1. Open SQL Server 2005 Management Studio Express.

2. Connect to the default instance of that server (eg: Computer Name\SQLEXPRESS)

3. Open a new query window and use master database

4. Execute sp_configure as below:
exec sp_configure 'user instances enabled', 1

--Configuration option 'user instances enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

5. Execute RECONFIGURE as:
RECONFIGURE

--Command(s) completed successfully.

6. Go to services and restart the SQL Server Instance currently running.



Note: Sample connection string format in web.config should be as below :

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=

|DataDirectory|\DBName.mdf;Integrated Security=True;User Instance=True;Initial Catalog=DBName;"

User Instances
User instance is a new feature supported by Microsoft SQL Server 2005 Express Edition, which is only available when using the .NET Framework Data Provider for SQL Server (SqlClient). A user instance is a separate instance of the SQL Server Express Database Engine that is generated by a parent instance. User instances allow users who are not administrators on their local computers to attach and connect to SQL Server Express databases. To generate user instances, a parent instance of SQL Server Express must be running. User instances are enabled by default when SQL Server Express is installed, and they can be explicitly enabled or disabled by a system administrator executing the sp_configure system stored procedure on the parent instance.

-- Enable user instances.
sp_configure 'user instances enabled', '1'

-- Disable user instances.
sp_configure 'user instances enabled', '0'