The user instance

Question

Hello,
I have a problem with setting up a connection in visual studio 2005 professional.
Sqlserver express is installed on the machine so I try to set up a connection by means of a microsoft sql server databasefile. When I click ok I  get the error :
The user instance login flag is not supported on this version of sql server.
Can someone help me to resolve this problem.
                                     Thanks in advance Ilja zwolsman
 

Answer 1

hi,

User Instances are only supported on SQLExpress edition and only if the relative feature has been enabled..

http://msdn2.microsoft.com/en-us/library/ms254504.aspx

regards

Answer 2

Hello, Thanks for the reply.
I used the stored procedure sp_configure in sqlexpress :
sp_configure 'user instance enabled', '1'
Reconfigure;
Go
Then I get the message:
The configuration option 'user instance enabled' does not exist, or it may be an advanced option.
Sqlexpress was installed during installation of vs2005 professional.
I wonder if  I am using the good sqlexpress edition.
                                                      Best regards, Ilja zwolsman
 

Answer 3

hi,

'user instances enabled' is not an advanced option requiring 'advanced options' to be set like

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
...

if you try to enable user instances on an edition other than SQLExpress you'll be prompted with the exception you reported..

on my Developer Edition:

 

EXEC sp_configure 'user instances enabled','1' ;
RECONFIGURE;
GO
SELECT @@VERSION;
--<----------------
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'user instances enabled' does not exist, or it may be an advanced option.

-------------------------------------------------------
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
	Apr 14 2006 01:12:25 
	Copyright (c) 1988-2005 Microsoft Corporation
	Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

as expected.. check the SQL Server edition you are connected to..

regards

Answer 4

Hello,
You are right. I have two instances installed : sqlexpress edition and sql developer edition .
But it is a little strange, as far as I now I connected to "machineName\sqlexpress" and executed the stored procedure.
Anyway I uninstalled both and installed sqlexpress from the second vs2005 cd and now it works
fine!!
                                Thanks for your help, Ilja Zwolsman

Answer 5

OK!

I know why the User Instances are giving you trouble.

You need them for ASP.NET authentication since the maching.config requires it in LocalSqlServer connection string.

Resolution is as follows:

First run aspnet_regsql.exe command-line tool (in c:\windows\Microsoft.Net\Framework\v2.0.50727) and it will create a database on your MSSQL (developer, standard... edition) server. The name of database is aspnetdb.

Next, change the connection string 'LocalSqlServer' in your c:\windows\Microsoft.Net\Framework\v2.0.50727\maching.config to:

<add name="LocalSqlServer" connectionString="Data Source=MYSERVERNAME;Initial Catalog=aspnetdb;Integrated Security=True" providerName="System.Data.SqlClient" />

Do not forget to change MYSERVERNAME in connection string

Answer 6

I have also faced the same problem. And when I searched I have found these solutions.
There would be two solution.

• if you have installed two instances of database like developer edition of sqlserver2005 and SqlserverExpress.then remove carefully with to whom ,you think appropriate.
  

• Another is might be your machine config file's connection string have changed so plz check it and changed with your appropriate SqlServer instance.

In my case my machine.config file was changed due to windows updates.then I changed my connection string with my previous connection string.It works fine.!!







Changed due to some windows updates.

<!--<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>-->

then I replaced with my existing connection string

    <add name="LocalSqlServer" connectionString="Data Source=192.168.0.62\xxxxxxxx;Database=xyz;User ID=xyz;Password=xyz;"/>

Answer 7

 

if you have sql server2008 developer edition :

 

1) create "aspnetdb" database with "aspnet_regsql" in vs command prompt.

2) find location of " aspnetdb.mdf" and " aspnetdb_log.ldf " files with right click on database name in mss management studio.

3) stop sqlserver service.

4) copy two files .mdf and .ldf in App_Data folder of your site.

5) start sqlserver service.

6) attach aspnetdb database from files to sqlserver.

6) chang in machine.config file instance to false:instance=false.

7) press asp.net configuration in your project.

8) define your users and roles.

 

make sure to create ur website project in "C:\Documents and Settings\username\My Documents\Visual Studio 2008"

 

roozbeh noroozy GIS Developer in IRAN -Tehran: [e-mail]

 

with thanks from:

sanjitpatel :  http://forums.asp.net/p/1090491/2802641.aspx#2802641

and

thomassamson :  http://forums.asp.net/t/913172.aspx?PageIndex=2

Answer 8

It worked fine after this procedure. THANKS

Answer 9

Perfect solution, THANKS