Deploying SQL Express with ASP.Net Application

I have a small ASP.Net application developed in VS 2005 that I would like to deploy with SQL Express.

I have included the MDF file in the Application and added SQL Express as a pre-requisite for the Setup and Deployment project.

It installs SQL Express but when it tries to connect I receive:
"This is not a valid login Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITY\SYSTEM'."

I don't seem to have any options using the "prerequisite options in VS 2005 in terms of command line deployment parameters.

Likewise I would prefer to attach the database to the instance (because I need to write a connection string from a PDA using RDA and I have zero clue how to do RDA directly back to the file).

Thanks for any help!
If you do not impersinate the user within your Asp application, the service account which the ASP Service is running with is used to connect to the SQL Server. If this account is not priviledged to access the server, you will get the mentioned error message. I don′t know how your application design is, but if you want to leave it as it is, you will have to grant access for the SYSTEM account to the server.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Deploying a database witch uses the membership tables

I have a website that uses an SQL 2005 Express database, with the added aspnet_users, aspnet_roles tables etc' inside it.
(That is not an .mdf file, but in the databse itself)

Are there any guidelines for moving this kind of database to the deployment server, which also has SQL Express ?

Is it just a case of detach + attach ?

Hi,

Detach + Attach will ship the whole database (tables, sp, users....)

Regards

deploy sql server express to ISP

To deploy sql server express to an ISP that supports Sql Server, I backed up the .mdf and .ldf files.

I can connect to the ISP database with Sql Server Mangement Studio Express. That connection requires a password.

When I try to execute 'restore', I get an error for no permission.

Any ideas why I have no permission after a password has already been entered?

I sure appreciate any help.

You will need to contact your ISP to discover what permissions you have on thier server. If you're getting a no permission error, it means that you don't have permission to perform a RESTORE on the server. SQL security is not all or nothing, you can be assigned a whole range of permissions that each imply certain rights.

Is RESTORE the way your ISP recomends putting databases on thier system? I'd actually be kind of surprised if it was, most ISP do not give this level of permissions to thier users.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Finally --

I found a solution.

The problem was the ISP. They were not helpful at all. I quit and went to discountasp.net (yea) -- they have a tool to convert sql server express over to sql server 2005 and they have answered all of my questions within just an hour or so.

Thanks for your help Mike.

|||

Glad that it worked out for you CK. I'm also glad the discountasp.net has a tool to upload your database easily.

I'll make one minor nit just so it's mentioned in the thread. I think it's important for folks to understand that SQL Express is SQL Server 2005. There is not difference between the database files, all the same data types are supported, etc. SQL Express has fewer functions, but that's all. This is pretty normal, think about all the applications that offer a "lite" or "personal" edition with reduced functionaltiy, and then when you pay, you get additional functionality, same thing here.

You don't need to convert the SQL Express database, it's already exactly the same. The confusion stems from two issues:

Some Hosters allow you to move your database to the site using the Import/Export wizard, which isn't currently in SQL Express. This makes it hard to get your database on the server and we're working with Hosters to get the whole upload story working for SQL Express users.|||

Hi Mike, very pleased to find your post. I have a website built in VWD express, where deployment to an isp fails with this:

... (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) ...

After a rather frustrating 2 days I've arrived at the same conclusion as you suggest, that isp's do not really support sql express, and they annoyingly avoid saying so in their blurb. Yet the VWD Express deployment went just fine, so I just have to fix the SQL stuff.

From your post are you saying that SQL Express is Enterprise ManagerLite, but under the hood, its still system tables like sysobjects and so on, just inaccessible. Or is SQL Express reduced in actual scope and reach, regardless of accessibilty. I cannot find a SQL express query window on its own, it seems that the only interface is from within VWD which is sufficient, although much reduced from Enterprise Manager. This must be Lite.

Reason I ask is so that I can understand better what happens when i transfer my database (at least its contents) from my local machine to my isp. Following your logic, do my SQL Express .mdf (with or without .ldf) files attach/detach seamlessly between SQL Express and SQL 2005 and back. In which case I just have to find an isp who will allow attach/detach. Then I alter the connection string in web.config to use the server/database on the isp's machine as per MSDN. Then it should work?

Hope this brightens your day, then maybe you can shed some light ...

Charlie

|||

Charlie,

I'm sorry about your 2 days of frustration. I know exactly what you are going through. The ISP that caused me 3 weeks of frustration (1) told me on the phone before I signed up that VWD/SQL Server Express would work fine (2) sent me solutions from multiple techs that required Enterprise Manager - which is NOT available from SQL Express and (3) would not admit they didn't know.

Of course I bought into the idea that VWD is for hobbyists and lite-weights, forcing me to pass my frustrations on to this forum(!), but now that is behind me and I am a happy camper.

Here's an article that helped me: http://aspnet.4guysfromrolla.com/articles/040506-1.aspx

deploy SQL Server Express

Problem: (asp.net 2.0)

I x-copy from my development server a database named C:\site_test\site.mdf to C:\site. When I attach the database with Server Management Studio Express, it list the name as: "C:\site_test\site.mdf" eventhough on the live server the database is in the C:\site directory and my connection string in my web.config is "C:\site\site.mdf".

But everything works until a week later when I need to make a field change to a table on the live site: I'm forced to re-attach the database, but this time it list the database as:C:\site\site.mdf". When I re-attach I get a NT AUTHORITY\SERVICE does not have permission. So I give NT AUTHORITY\SERVICE permission on the directory and files and ownership with Server Management Stuidio Express, but I still get the error.

What am I'm doing wrong? Thanks for the help

--Dietrich

Hi Dietrich,

If you have attached the .mdf file to database manually, please check if you have set the connection string accordingly.

It has to be "Data Source=ServerName\Instancename; Initial Catelog=DatabaseName"

Deploy .MDF to a web host

Hi all,

I'm new to SQLServer (Express edition) so I was wondering: if the web
host supports SQLServer 2005 do I just need to move the .MDF file to my
directory on the web host to be able to use it?

Thanks,
LorenzoIf you have rights attaching a user database to a SQL Server and the
instance has access to your upload directory you can do this.

HTH, Jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||Jens wrote:
> If you have rights attaching a user database to a SQL Server and the
> instance has access to your upload directory you can do this.

Thanks for the answer, Jens

Lorenzo|||lbolognini@.gmail.com (lbolognini@.gmail.com) writes:
> I'm new to SQLServer (Express edition) so I was wondering: if the web
> host supports SQLServer 2005 do I just need to move the .MDF file to my
> directory on the web host to be able to use it?

You should probably talk to your web host about this. Jens suggested that
you may rights to attach databases. I say that the likelyhood for this is
nil, as this requires hefty rights on the server.

A little more likely is that they create an empty database for you,
and then you may be permitted to overwrite that database with a backup
of your database.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx