Connecting to SQL Express User Instances in Management Studio

re: Connecting to SQL Express User Instances in Management Studio

Ross Shen — Tue, 28 Nov 2006 19:06:37 GMT

When I use the following connection string without "User Instance=TRUE", I noticed my database is attached to the SQLEXPRESS instance itself and is operable. Why I need to bother adding "User Instance=TRUE" and following your steps.

connectionString="Data Source=.\SQLEXPRESS;AttachDBFilename=|DataDirectory|Northwind.mdf;Integrated Security=True;

Second question, it seems that once the user instance goes alive, it will not go back to dead even though I restart the SQL service. Can you explain this?

Thanks,

Ross

re: Connecting to SQL Express User Instances in Management Studio

sqlexpress — Tue, 28 Nov 2006 21:10:53 GMT

Here is an answer for Ross' question:

User Instances were designed to allow non-administrative users to be able to attach a database "on the fly" during applicaiton runtime. (This explains the name of the feature, which is Run As Normal User.) This is in order to support ClickOnce deployment of single user, Windows application that use locally stored data.

Two things have to be true for AttachDBFilename to work:

1. The user has to have CREATE privledges on SQL Server.

2. SQL Server has to have access to the path represented by |DataDirectory|

Under default conditions, non-Administrator users will not have CREATE privledge, so the command line you specify would fail for them. Additionally, the default service account used by SQL Express do not have permissions for any of directories in a user profile, such a My Documents, which is the default location for databases created by VS. In your case, either you have given the Network Service account permissions for your user profile directories, or you are running SQL Express under a different account that does have permissions for those directories. (Or |DataDirectory| is pointed to somewhere that is not in a user profile.)

As an interesting side note, the connection string you reference will probably fail when run on Vista with User Account Control turned on because SQL Express will no longer percieve you as an Administrator on the computer. UAC automatically removes your Admin Tokens and treats you as a normal user until you do something to elevate the privledges of the application you are running. There are post else where in this blog discussing Vista and SQL Express.

- Mike

This would be a great question to ask on the SQL Express forum as well. (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=385&SiteID=1)

re: Connecting to SQL Express User Instances in Management Studio

sqlexpress — Tue, 28 Nov 2006 21:15:57 GMT

Opps, forgot the second question...

You're right. A User Instances is a completely separate process from the parent instance of SQL Express. Once it has been started, it is completely autonomous from the parent, shutting down the parent will have no impact on the User Instance. A User Instance will automatically time out after 60 minutes, and it will get shut down and not come back if you shut down your computer.

You can see the User Instance process in the Windows Task Manager, do something that starts a User Instance and then take a look at the Processes tab. You should see two occurances of sqlservr.exe, one will be running as the SQL Express service account (Network Service by default) and the second will be running under your own user account. It is interesting to note that a User Instances runs as a Process, not as a Service as the parent instance does.

- Mike

how to connect to SQL Express User Instances

Shervin Shakibi's Blog — Fri, 27 Apr 2007 13:26:18 GMT

how to connect to SQL Express User Instances

re: Connecting to SQL Express User Instances in Management Studio

hbkelkar — Wed, 16 May 2007 12:36:53 GMT

I am writing applications in VB6 and using SQL Express as a backend.

My client network is working on some Windows98 machines and some on Win XP.

I can easily connect to the database (SQL Express) from winXP client machines using SQL Native Client,SQLNCLI

I need to know.. whether i can connect to the Database from Windows98 OS.?

If it is possible, how to establish a connection string to work on both win98 and Win XP machines.

re: Connecting to SQL Express User Instances in Management Studio

aslanozcakir@hotmail.com — Mon, 11 Jun 2007 13:36:06 GMT

We need to connect to SQL Express instance remotely from another server.

We used an alias to map the remote server and sql express server but we can not connect.

We are getting the default cannot connect error message.

First of all, we need to know whether we can connect to SQL Express Server remotely because we are confused with the following statement:

"By Default, SQL Express, Evaluation and Developer editions allow local client connections only"

this is written at : SQL Server 2005 Surface Area Configuration's dialog box.

Please advise.

Thanks,

Aslan Ozcakir

re: Connecting to SQL Express User Instances in Management Studio

sqlexpress — Thu, 05 Jul 2007 19:53:52 GMT

Response for Aslan:

Hi Aslan,

User Instances do not support remote connections at all. You cannot connect to them in the manner you are trying.

Mike

re: Connecting to SQL Express User Instances in Management Studio

RWGAU — Mon, 25 Feb 2008 20:33:04 GMT

My problem is that when I log into my development PC as workstation only/local machine, everything works. However, when I log in to the domain account I get a message stating that User does not have permission to run the action. (or something like that) I've tried everything I can think of.

re: Connecting to SQL Express User Instances in Management Studio

derekgreer — Mon, 17 Mar 2008 05:24:17 GMT

I know this problem has been reported all over the place, but none of the threads have helped so far.

I'm running SQL Express on Vista. I have a database file set up for ASP.Net Authentication. I can connect to this database through Visual Studio, IIS 7.0 User Administration, and SSEUtil. However, I cannot connect to the database when accessing it from a Web request. Upon attempting to access the database, the web site returns the error:

"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."

I'm running SQLExpress as "Network Service" as intended. Any ideas what could be wrong?

Derek

re: Connecting to SQL Express User Instances in Management Studio

sqlexpress — Mon, 17 Mar 2008 19:48:55 GMT

Response for Derek:

You should check out the blog post at http://blogs.msdn.com/sqlexpress/archive/2008/02/22/sql-server-express-and-hosting.aspx. In short, User Instances provide some great developer productivity features, but are not an appropriate technology for Hosted databases for exactly the types of issues you are experiencing.

In order to correctly access the database correctly through the web site, you should move the database to be connected to the parent instances and change your connection string. There are links in the blog post that show how to use the Database Publishing Wizard to do this, even to the point of combining your Membership database with your data database.

You can use SQL Express as the server to host your application databases, but be aware that the physical limitations on SQL Express may impact your performance as the load on your web site increases.

Hope this helps,

Mike

re: Connecting to SQL Express User Instances in Management Studio

derekgreer — Tue, 18 Mar 2008 04:14:38 GMT

Mike,

I appreciate the recommendation, but I'm interested in solving this problem more for academic reasons than anything else. I'm not running any sort of major Web site.

I recently ran into some installation problems with SQL Standard Edition on Vista which required me to reinstall SQL Express. Prior to this reinstall, I had a Web site configured to use ASP.Net authentication with SQL Server Express which was working fine. After reinstalling SQL Express, I started getting the "cannot create user instance" error message. My previous installation was the result of a VS2005 install. This process must have installed SQL Server Express differently than I did, because everything was working just fine with user instances.

That said and recommendations for not using user instances duly noted, I would like to determine what is wrong with my present configuration which would cause this problem. I'm not interested in solving the problem by avoiding the use of SQL Express or avoiding the use of user instances as the purpose of my quest is to determine what went wrong and how this works.

Here is a list of information if you think you can help with this problem:

* Running on Vista Ultimate

* Running SQL Server Express 2005

* Connection string: data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

* Was working before uninstalling and reinstalling SQL Server Express 2005

* SQLExpress service is run as user “Network Service”

* "Network Service" has full permissions on App_Data, MDF, and LDF

* “Network Service” has execute permissions on “sqlservr.exe”.

* All IIS 7.0 Application Pools are running as “Network Service”

* No other processes currently have the files locked

* User instance creation works fine when accessed through IIS 7.0 User administration, SSEUtil, and VS2005

* User instance cannot be created when accessed via Web request

* Event Viewer reports: “Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. [CLIENT: <local machine>]” for user “Network Service”

Thanks,

Derek

re: Connecting to SQL Express User Instances in Management Studio

sqlexpress — Tue, 18 Mar 2008 21:18:23 GMT

Response to Derek -

The short story is that I don't know what is wrong with your system. We've not been able to track down the root cause of this error. I do know that it has nothing to do with the way you installed SQL Express or the source of your SQL Express installation.

This particular error is returned when SQL Express sends a request to Windows to have a new process started and Windows returns a NULL rather than starting the process. We haven't been able to determine why Windows can't start the process.

There are a couple conditions that may be impacting your specific case:

- User Instances only work for interactive users because system files need to be copied into folders in the user profile. I notice you are running your Application Pool as Network Service and I don't believe there is a profile for Network Service, so there is nowhere to copy the system files. You could try changing your Application Pool to run as an interactive user for which a set of profile folders exists.

- If there is a profile for Network Services, you can try deleting the user instance folder so that it is recreated the next time you start the user instance. This frequently works for normal user accounts. The user instance folder is located at C:\Users\<username>\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

Again, user instances are not appropriate for hosted applications. I know you're on a mission to figure out why something is failing, but does it make sense to spend your time on this given that there is a better solution available to you? Just a thought.

Mike

re: Connecting to SQL Express User Instances in Management Studio

derekgreer — Wed, 19 Mar 2008 04:32:14 GMT

To answer your question about whether it makes sense to spend time trying to solve this problem, I would say the answer to this question depends on one's perspective. It's a bit of a "Why climb the mountain" issue in my case. I can't say its always been easy to have the type of personality that drives me to figure out stuff like this, but overall its certainly benefited me professionally.

Concerning your statement about user instances only working with interactive user accounts, if this is true then I'm really baffled. My Web site was working with user instances just a few days ago and I know for certain I haven't changed the account my Application Pool runs under or the Application Pool associated with my site.

Concerning there being a profile for Network Services, I know that there isn't currently a C:\Users\Network Service folder so I assume it wasn't there in the past either.

If only I had a time machine ...

- Derek

Connecting to SQL Server express user instances in management studio

DotNetKicks.com — Wed, 27 Aug 2008 09:13:17 GMT

You've been kicked (a good thing) - Trackback from DotNetKicks.com

FAQ: Error creating a Service-based database in Visual Studio 2008 SP1

SQL Server Express WebLog — Fri, 03 Oct 2008 03:07:45 GMT

A few of you have run into problems when trying to add a new Service-based database to your project in

re: Connecting to SQL Express User Instances in Management Studio

kasun087 — Tue, 24 Nov 2009 18:08:04 GMT

I found this very much helping and interesting. I am having a similar problem related to this thread. I have created a small business application by visual c sharp 2005.

After reading this thread i happened to know that i have created an user instance. I installed sql management studio 2005 express manually and even thought my application is not running the heat beat still shows it s alive.(But i created an exe file to deploy the application in client's machine)

So far everything is fine.But i need to do some backup taking at the client machine. So when i tried to connect to the database which i have deployed into my application, i could not find that database from the management studio. Is there any possible solution you have with this issue Mike? It would be really helpful if you can suggest me something on this matter

Thank you.