Re: Connection issues between .Net framwok and SQl Express 2005

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Nov 29, 1:13 pm, "Norman Yuan" <NoAddr...@xxxxxxxxxxxx> wrote:
You have re-configure the website to use SQL Server Express as ASPNETDB.
here is excert from someone else reply to similar question that might help.

<Quote>

As for configuring ASP.NET 2.0 membership to connect SQL Server 2000, you
need to do the following things:

** use aspnet_regsql.exe tool to setup a database in sqlserver 2000 db

** add a new membership provider in your ASP.NET 2.0 application which use
a connectionstring pointed to your SQL Server 2000 database

Here are two web articles provided detailed steps:

#Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL
Server 2005http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

#Using ASP.NET 2.0 Membership API with SQL Server 2000http://www.vikramlakhotia.com/Using_ASPNET_20_Membership_API_with_SQL...
_2000.aspx

</Quote>

"RGF" <Raf.Figue...@xxxxxxxxx> wrote in message

news:547fd478-447c-4f6c-ac60-b4057b9d101c@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Nov 20, 11:12 pm, "Cowboy \(Gregory A. Beamer\)"
<NoSpamMgbwo...@xxxxxxxxxxxxxxxxxx> wrote:
Look up sp_attachdb in the books online with SQL Server Express. That
will
show you how to attach the database to the server rather than have it as
a
user instance. The connection string will then look something like:

server=.\SQLExpress;database={YourdatabaseName};Integrated Security=SSPI

You will no longer need the database MDF file name in the connection
string.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

*************************************************
| Think outside the box!
|
*************************************************"RGF"
<Raf.Figue...@xxxxxxxxx> wrote in message

news:fc7bc83d-d90a-4563-98ef-693322dd93df@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Nov 20, 9:18 am, "Norman Yuan" <NoAddr...@xxxxxxxxxxxx> wrote:
OK, it is, as I expected, because of using SQL Server Express USER
INSTANCE.

To solve your problem, the simplest way is to not use USER INSTANCE.
You
simply attach the ASPNETDB.mdf to SQL Server permenantly, and change
the
connection slightly (i.e. remove "AttachDBFile=..." and "User
Instance=True"
from the ConnectionString, and use SSMS to attach the ASPNETDB.mdf to
the
SQL Server/Express, if it has been detached previously, automatically
or
not.

Study on SQL Server Epxress USER INSTANCE is always good if you are to
use
it (but as I said in previous post, it does not have much chance in
web
application).

"RGF" <Raf.Figue...@xxxxxxxxx> wrote in message

news:2c627856-c3bd-413f-aadc-11d4d953282a@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

On Nov 19, 3:42 pm, "Norman Yuan" <NoAddr...@xxxxxxxxxxxx> wrote:
Since you use SQL Server Express USER INSTANCE (hope you know what
does
that
mean. Unfortunately, ASP.NET management wizard create ASPNETDB
automatically
as USER INSTANCE on existing SQL Server Express, assuming the
developer
knows USER INSTANCE. The reality is most newbie have no idea on
USER
INSTANCE at all and most ASP.NET book do not explain that), your
call
to
the
"sqlcmd" (i do not know why you need to run that) only runs when
the
calling
process is under the same user account as the ASP.NET application,
because
USER INSTANCE only allow single user access (not neccesarily single
connection, though), which is the user account used to run your
ASP.NET
application, by default, it is ASPNET or Network Service, or any
other
account you may have impersonated to.

If your application will be deployed to a host service provider's
web
server, the host provider would most likely not support SQL Server
Express
at all, so no USER INSTANCE will be available. If you know USER
INSTANCE
well, and have no difficulty understanding how to change ASPNETDB
to a
full
SQL Server, that would be fine. If you don't, better spend some
time
on
SQL
Server, and its Express USER INSTANCE. You'd discover there is most
likely
no need to use USER INSTANCE for a web application/website.

"RGF" <Raf.Figue...@xxxxxxxxx> wrote in message

news:c3b107dc-5dee-464b-88d6-dedd6a1ac528@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Background:

I wrote a simple .bat file which executes the following sqlcmd
call:

"sqlcmd -S BLUEBOX\SQLEXPRESS -d C:\App_Data\ASPNETDB.MDF -U
MyUserCount -P MyUserPassword -Q "Exec SendMessage" -o
C:\Vocalico
\SendMailQuePump\Undelievered.txt"

the above command works fine, as long as no other application is
accessing the db.

Also, using VS2005 I wrote a simple web based calendar scheduling
application which I am testing by launching it via Visual Studio
2005
(using the built in ASP.Net Development Server), the web
application
seems to work correctly as well.

The Problem Found:

When I run the VS2005 web application the sqlcmd fails to login
(note
that the web application and the sqlcmd access the same db
(C:\App_Data
\ASPNETDB.MDF)). The following is the error I get form SQL 2005
Express when running the sqlcmd while the web application is
running:

Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
Unable to open the physical file "C:\App_Data\aspnetdb.mdf".
Operating
system error 32: "32(The process cannot access the file because
it
is
being used by another process.)".
Msg 5120, Level 16, State 101, Server BLUEBOX\SQLEXPRESS, Line 1
Unable to open the physical file "C:\App_Data\aspnetdb_log.ldf".
Operating system error 32: "32(The process cannot access the file
because it is being used by another process.)".
File activation failure. The physical file name "C:\App_Data
\aspnetdb_log.ldf" may be incorrect.
Msg 4060, Level 11, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
Cannot open database "C:\App_Data\ASPNETDB.MDF" requested by the
login. The login failed.
Msg 18456, Level 14, State 1, Server BLUEBOX\SQLEXPRESS, Line 1
Login failed for user 'MyUserCount'.

Questions:

Shouldn't SQL 2005 Express allow more than one user to connect
and
authenticate to the db by default? Are there any settings I need
to
modify in Visual Studio 2005 or SQL Express 2005 in order to
allow
my
web app and the sqlcmd command to co-exist and execute without
interfering with each other?

My Setup:

VS 2005
.Net Framework 2.0
XP Professional SP2
SQL 2005 Express
web.config connection string looks like this:
<add name="ASPNETDBConnectionString" connectionString="Data
Source=.
\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated
Security=SSPI;User Instance=True"
providerName="System.Data.SqlClient"/

I have been going around in circles on this issue, any help would
be
greatly appreciated!

Sincerely,
-ralph

Norman,
If you've been working in the tech field long enough you should know
by now that as technology evolves you become a newbie again, yup I
am
a newbie to the VS2005 and SQL 2005 Express beasts. I did not
encounter user instance issues in previous projects I did (older
gen.
of MS technology).

You are correct in that, I used the helpful wizard, I mean, that is
what is there for.. and as far as I can remember, it did not give me
an option as to the USER INSTANCE option. Just to clarify, the
account used (impersonation) to run the ASP.Net page is different
than
the account used by the sqlcmd call, I would presume that the
separate
accounts would spawn their own access and process threads .. If I
understood your comment correctly "USER INSTANCE only allow single
user access" this would mean that only one account (or one user)
would
be able to access and lock everyone out from a site that was created
using the connecting wizard in VS2005, right? that would strike me
as
odd, but I will to some googling on "USER INSTANCE"...thanks for the
lead.

Also, with regards to the usage of sqlcmd. I resolved to use
"sqlcmd"
because as you are probably are aware SQL 2005 Express does not come
with SSIS, and DTS (SQL 2000) refused to run against my instance of
SQL 2005 Express. So in order to schedule a SQL task to run
periodically, it is valid to batch out a sqlcmd call, it beats
executing the a store proc manually...:)

Thanks for your feedback Norman..

I read a bit more regarding "User Instance", the notion that it only
works on SQL 2005 Express and it is not supported on other versions of
SQL 2005, is enough of an incentive not to use UI. I am hopeful the
tool I am developing grows beyond the 4GB cap placed on SQL 2K5
Express, at which point I will need to get a full SQL 2K5 version and
I rather not have to revisit the connection

...

read more >>

Norman, thanks for the fast response...the links you forwarded
provided helped!!

I ran aspnet_regsql.exe as per the steps provided in the links you
forwarded, I also modified the "LocalSqlServer" connection string
inside the machine.config file, and everything (roles / membership and
no collision issues ) worked like a charm!!

I really appreciate your (and Gregory's) guidance, clear advice and
your patience on this issue, you guys rock!

Cheers,
Ralph




.

Quantcast