Sql Express Vista trouble #2

AlexBB

Actually I should have said #22 but this is beyond the point. It seems the errors pop up and then go away for no reason, then appear again.

I had Sql Server Express with Advanced Features set up all right as far as I can see. It is the connection issue that is causing the headache.

Currently when I try to make a connection with an Sql database I get an error in Visual Studio:

Generating user instance in Sql Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.

So I go to Sql Server, log in, open a new query and type:

sp_configure 'user instances enabled'

GO

I get a table where I can see that

name minimum maximum config_value run_value

user instances enabled 0 1 1 1

Are they enabled I think, yes. I go back to Vsisual Studio and get the same error. It is possible that I will have to log out in order for all this security garbage to take effect and I will try it but in the meantime, how can I set more than one instance.

I want to complain that these new security provisions that came apparently with SP2 and Vista are totally unnesessary for my applications. I do not have any high security info to worry about, e.g. social security numbers, etc. I would not mind to turn all this stuff off if it is possible at all.

So, how can I set up 3 user instances

Thanks.

Suprotim Agarwal

Dear AlexBB,

Do this :

sp_configure 'user instances enabled', 1; RECONFIGURE

and then restart Sql Server.

http://msdn2.microsoft.com/en-us/library/ms254504(VS.80).aspx

HTH,
Suprotim Agarwal

-----
http://www.dotnetcurry.com
-----

AlexBB

Suprotim Agarwal wrote:

Dear AlexBB, Do this : sp_configure 'user instances enabled', 1; RECONFIGURE and then restart Sql Server. http://msdn2.microsoft.com/en-us/library/ms254504(VS.80).aspx HTH, Suprotim Agarwal ----- http://www.dotnetcurry.com -----

Hi Suprotim, many many thanks, but the road to complete comprehension is still very very long.

I ran your statement in a query and got this result:

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

Two questions:

(1) What does it mean that I have to run a "RECONFIGURE to Install " Install what

To reconfigure what I think the statement I ran was supposed to reconfigure everything.

(2) Why was I getting an error in my Visual Development Studio that the user instances were not enabled sincee the result of the query seems to indicate that they have been enabled all along as I suspected

I have another question, number 3: I actually somehow configured my connections after all that bedlam in such a way that user instance = false was the preferred option. On the surface this problem has taken care of itself in a way. But want to undertstand any possible implications of my changing the user instances from 1 to 3 (provided you will explain to me how I can "install" something after that). I've read on user instances in MSDN and without claiming that I understood everything I got an idea that it is a preferred (security wise) use of Sql Server instances.

My thinking is that one of the user instances will be the connection in VS, another one is my singing in as a user into SSMS if I want to make any visual inspection of databases during execution of my C# app and one instance will be spare.

Thanks.

Hemantgiri S. Goswami

Hi,

sp_configure is system stored procedure, this procedure is used to modify server wide settings ,when you run

sp_configure ''user instances enabled' ,1

go

reconfigure

go

this will enabled user instance in your server,

1) Nothing will install, but yes server wide setting can be changed using sp_configure statement, refer

"User Instances for Non-Administrators" in Books On Line (BOL)

2) As you have posted it seems enabled,

3) As I said earlier nothing will be installed as it's SP to modify server wide settings.

Would you please post the exact error messgae so that we may help you better.

HTH

Mike Wachal - MSFT

Hi Alex,

A User Instance is a completely separate instance of SQL Server from the parent instance. When you specify User Instance=False, you are connecting to the parent instance. Visual Studio uses User Instanse to allow users who are not Administrators on their computers to be able to use a SQL database as part of a Windows Forms application without requring the developer to do extra work to set up permissions within SQL Server for the user. User Instances also allow you to deploy your database file as part of your applicaton rather than having to manually deploy the database.

You application will likely not be deployable and your end-users will likely not be able to use the database if you set User Instance=False.

My guess as to the cause of your problem is that you have more than one instance of SQL Server installed on your computer. The easiest way to check this is to open the SQL Configuraton Manager, select 'SQL Server 2005 Services' and look to see how many times you see 'SQL Server (instance name)' listed. If there is more than one, you are probably querying the feature in a different instance than the one being used by VS.

Let us know what you find and if there is more than one I'll help you sort out which one VS is using and how to make sure that instance is the one you're actually configuring.

Mike

Suprotim Agarwal

Dear Alex,

When you run the command sp_configure ''user instances enabled' ,1, all it does is just control the enabling or disabling of user instance. So I am not very sure what do you mean when you say "I think the statement I ran was supposed to reconfigure everything."

You should read the link that Hemantgiri pointed you to, to understand this better.

Out of curiosity, do you have Sp1 of SQL Express installed. How many SQL instances do you have on your machine

HTH,
Suprotim Agarwal

-----
http://www.dotnetcurry.com
-----

AlexBB

Hemantgiri S. Goswami wrote:

Hi, sp_configure is system stored procedure, this procedure is used to modify server wide settings ,when you run sp_configure ''user instances enabled' ,1 go reconfigure go this will enabled user instance in your server, 1) Nothing will install, but yes server wide setting can be changed using sp_configure statement, refer "User Instances for Non-Administrators" in Books On Line (BOL) 2) As you have posted it seems enabled, 3) As I said earlier nothing will be installed as it's SP to modify server wide settings. Would you please post the exact error messgae so that we may help you better. HTH

I do not quite understand what error message are you referring When I ran the sp_configure, no error message came up, the message was the one that I posted previously.

It all began when I install a Vista and moved my very well debugged and long running C# code from XP to the Vista partition. All hell broke lose, nothing worked. I also donwloaded and installed new Visual Studio 2005 and new SSMS with all the newst SPs. Perhaps that was the cause. None of my connection strings worked. I never used user isntances before, never. I was advised by a moderator in C# to change my connection strings and I did but his suggestion that I should use user instance= true did not work. I was getting persistent error message that user instances were disabled at SS Express. The error message (iin VS 200) suggested that I should run sp_configure 'user instances enabled.' When I did (in SSMS) I kept getting answers that they were enabled. This is why I came here.

In the end I changed user isntances=false in my conn strings and was able to make all connections. The moderator said that it was perhaps my DBs are in SS (full version) folder set up by default when you install VS 2005. Those are priviledged folders and cannot be accessed by a person running under user instance since it is a LUA account. It is not an admin account over the system (SS).

My questions were (which you did not answer): Can I set up user instances = 3 for instance What would be the implications

As you are saying that nothing is installed, the result message is misleading.

Suprotim Agarwal

Dear Alex,

AFAIK, you can set a maximum of 1 user instance[a one-instance-per-user basis] and no more than that on Sql Express. And seeing your earlier posts, it appears that your user instance is already set up.

HTH,
Suprotim Agarwal

-----
http://www.dotnetcurry.com
-----

AlexBB

Suprotim Agarwal wrote:

Dear Alex, AFAIK, you can set a maximum of 1 user instance[a one-instance-per-user basis] and no more than that on Sql Express. And seeing your earlier posts, it appears that your user instance is already set up. HTH, Suprotim Agarwal ----- http://www.dotnetcurry.com -----

Thank you. It means that all that semantic garbage about maximum user instances is misleading.

Mike Wachal - MSFT

Hi Alex,

I'm not sure what "semantic garbage" you're talking about here, but that isn't really the point I guess; the point is to get your application running.

It's not clear from you post what exactly you are trying to accomplish and how that relates to User Instances. One of your posts seems to indicate that you were not using User Instances in your application; if that is the case, then the solution to change your connection string to User Instance=False is the right one. You should only specify User Instance=True if you actually need User Instances.

Let me know if this is the case and if changing your connection string has solved your issue.

Mike

PS: FWIW - The sp_configure statement being discussed throughout this thread either enables (1) or disables (0) user instances, it doesn't set the number of user instances. User Instances are create per user, so there is only one user instance for any given user on a computer, but if you have multiple people sharing a computer, each one of them would have a separate user instance when they were logged in and running an app that uses them. This happens automatically and you don't/can't configure the number of user instances.