Attach Database problem

I am trying to attach a database as part of my overall application distribution.

After installing Express when my .NET app first starts it atempts to attach the database using EXEC sp_attach_db etc...

This used to work fine with MSDE 2000 but now the database is being attached as read-only. If I then use Management Studio to manually detach and attach the database it is fine.

Any ideas why this is happening please?

Hi,

is the file propably readonly (on system level) ? Do you use the same permission / login in your application as in SQL Server Managment studio ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens

The .mdf and .ldf files are not set to read only.

This is happening on Windows XP.

When I run the attach command from within my code I am connected to the master db as the sa user. I am logged into Windows as an administrator user.

I have made sure that the database has been detached properly on my developement machine and the database is installed to the same path on the target machine.

Are you aware of any other reasons that could cause a db to be attached as read-only?

Thanks

Ade

|||

Odds are this is a result of the fact that SQL Express runs under Network Service rather than Local Service as was the case in MSDE. Check to see what permissions the Network Service account has on the directory where your database is located.

Mike

|||

Hi Mike

Thanks for your reply.

You were right - when I change SQL Express to run under the Local Service the problem goes away.

I can override the new default in the command line by setting the SQLACCOUNT="NT AUTHORITY\SYSTEM" parameter.

What I need to know now is why the default account has changed (and am I creating a problem by overriding it)?

The alternative is to leave the account as the Network Service but I don't know how then to set the relevant folder permissions from within my Instalshield setup (Windows XP Home and Professional)

Does anyone have any more comments on this?

Thanks again Mike

|||

Wow!

Thank you all!

I was trying to migrate a medical records system database that uses MSDE sql2000 over to SQL Express. I kept getting the "read only" errors when I tried to attach the databases to SQL Express. I was ripping my hair out!

Actually, I got the same error selecting 'local service', but selecting 'local system' WORKED!

David

at a time more than 8 users executing sp_xml_preparedocument what

we r using xmlconcept , sp_xml_preparedocument taking 1/8 of the total memor
y
but our front end tool is PB .often our application close cause of locking
at a time more than 8 users executing sp_xml_preparedocument what will
happen , i need urgent soultionMake sure you are using sp_xml_removedocument
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Subramaniyan Ramesh" <SubramaniyanRamesh@.discussions.microsoft.com> wrote
in message news:29D5FD50-CA2B-4090-B589-0749BC460200@.microsoft.com...
> we r using xmlconcept , sp_xml_preparedocument taking 1/8 of the total
> memory
> but our front end tool is PB .often our application close cause of locking
> at a time more than 8 users executing sp_xml_preparedocument what will
> happen , i need urgent soultion

asynchronous select statements

We have an application deployed on two seperate machines both reading from
the same database. The applications poll the database every few seconds and
find the first record in a table that has a bit field set to 0x0. The
application then updates the field to a value of 0x1 and then do some
processing based on the contents of the record. The problem we have is that
it is currently possible for each application to get the same record because
application 2 might select the record just before application 2 updates the
bit flag. we currently use two sql calls (in stored procs) such as:
select top 1 * from table1 where processed = 0x0
update table1 set processed = 0x1 where recid = @.ID
How can we avoid both apps getting the same recordHave the app call a single SP.
This SP updates the record, and then returns it to the client application.
This is in one transaction, so the other application can not get to the same
record.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:#GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
> We have an application deployed on two seperate machines both reading from
> the same database. The applications poll the database every few seconds
and
> find the first record in a table that has a bit field set to 0x0. The
> application then updates the field to a value of 0x1 and then do some
> processing based on the contents of the record. The problem we have is
that
> it is currently possible for each application to get the same record
because
> application 2 might select the record just before application 2 updates
the
> bit flag. we currently use two sql calls (in stored procs) such as:
> select top 1 * from table1 where processed = 0x0
> update table1 set processed = 0x1 where recid = @.ID
>
> How can we avoid both apps getting the same record
>|||Jeremy,
This is how you would do it; put the desired row into lock until the update
is done. For rowlock to be effective, you need a primary key on the table!
ReadPast hint will allow you to bypass the locked row and process the next
available one. Without it, your #2 connection will have to wait until #1 is
done. You can try both scenarios out to gain some deeper insight.
e.g.
/*
--sample tb
create table t1(i int primary key, b bit)
insert t1 values(1,0)
insert t1 values(2,0)
insert t1 values(3,0)
*/
-- drop table t1
go
--on connection #1
--this will lock i=1
declare @.i int
begin tran
select top 1 @.i=i
from t1 with (rowlock,readpast)
where b=0
update t1
set b=1
where i=@.i
select @.i as [i]
-- commit
-- rollback
go
--on connnection #2
--this will lock i=2
declare @.i int
begin tran
select top 1 @.i=i
from t1 with (rowlock,readpast)
where b=0
update t1
set b=1
where i=@.i
select @.i as [i]
-- commit
-- rollback
go
-oj
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
> We have an application deployed on two seperate machines both reading from
> the same database. The applications poll the database every few seconds
> and
> find the first record in a table that has a bit field set to 0x0. The
> application then updates the field to a value of 0x1 and then do some
> processing based on the contents of the record. The problem we have is
> that
> it is currently possible for each application to get the same record
> because
> application 2 might select the record just before application 2 updates
> the
> bit flag. we currently use two sql calls (in stored procs) such as:
> select top 1 * from table1 where processed = 0x0
> update table1 set processed = 0x1 where recid = @.ID
>
> How can we avoid both apps getting the same record
>|||Magnificent! Thanks.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Jeremy,
> This is how you would do it; put the desired row into lock until the
update
> is done. For rowlock to be effective, you need a primary key on the table!
> ReadPast hint will allow you to bypass the locked row and process the next
> available one. Without it, your #2 connection will have to wait until #1
is
> done. You can try both scenarios out to gain some deeper insight.
> e.g.
> /*
> --sample tb
> create table t1(i int primary key, b bit)
> insert t1 values(1,0)
> insert t1 values(2,0)
> insert t1 values(3,0)
> */
> -- drop table t1
> go
> --on connection #1
> --this will lock i=1
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
> --on connnection #2
> --this will lock i=2
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
>
> --
> -oj
>
> "Jeremy Chapman" <NoSpam@.Please.com> wrote in message
> news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
from
>|||Actually, testing discovered that this might not work, because if the sql
gets run at the same time, the select statements could select the same
record, because nothing is locked at that point.
"oj" <nospam_ojngo@.home.com> wrote in message
news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> Jeremy,
> This is how you would do it; put the desired row into lock until the
update
> is done. For rowlock to be effective, you need a primary key on the table!
> ReadPast hint will allow you to bypass the locked row and process the next
> available one. Without it, your #2 connection will have to wait until #1
is
> done. You can try both scenarios out to gain some deeper insight.
> e.g.
> /*
> --sample tb
> create table t1(i int primary key, b bit)
> insert t1 values(1,0)
> insert t1 values(2,0)
> insert t1 values(3,0)
> */
> -- drop table t1
> go
> --on connection #1
> --this will lock i=1
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
> --on connnection #2
> --this will lock i=2
> declare @.i int
> begin tran
> select top 1 @.i=i
> from t1 with (rowlock,readpast)
> where b=0
> update t1
> set b=1
> where i=@.i
> select @.i as [i]
> -- commit
> -- rollback
> go
>
> --
> -oj
>
> "Jeremy Chapman" <NoSpam@.Please.com> wrote in message
> news:%23GXM3G7EFHA.1084@.tk2msftngp13.phx.gbl...
from
>|||You could add another hint to the select to exclusively hold the lock. As
soon as the row is read, it's locked until you invoke commit/rollback.
e.g.
select *
from tb with (rowlock,xlock,readpast)
where pkid=@.para
-oj
"Jeremy Chapman" <NoSpam@.Please.com> wrote in message
news:uLpwUeIFFHA.464@.TK2MSFTNGP09.phx.gbl...
> Actually, testing discovered that this might not work, because if the sql
> gets run at the same time, the select statements could select the same
> record, because nothing is locked at that point.
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:e$coyd7EFHA.3664@.TK2MSFTNGP15.phx.gbl...
> update
> is
> from
>

ASYNC_NETWORK_IO

Hello, When I execute a SQL SERVER 2005 stored procedure from Visual Basic 6 Application, I get my process locked with ASYNC_NETWORK_IO wait type.

When I Execute the same procedure from sql server console, It doesn't occur...

This is the visual basic code:

With objcommand

.ActiveConnection = CnnConexionPpalDb
.CommandTimeout = 0
.CommandText = "EJECUTIN"
.CommandType = adCmdStoredProc

'- Parametros del Command

'.Parameters("@.StrOrdenEstablecido").Value = StrOrdenEstablecido

End With
Set gTbl.VisOrdenamientoDatosFinalCiudades = objcommand.Execute

With gTbl.VisOrdenamientoDatosFinalCiudades

This is SQL SERVER CODE:

ALTER PROCEDURE [dbo].PCN_CONSULTAR_VISORDENAMIENTODATOSFINALCIUDADES_ORDENADO] @.StrOrdenEstablecido varchar(200)
as
BEGIN TRAN
DECLARE @.lvaSentencia NVARCHAR(4000)

SELECT dbo.Tbl_DatosSubidos.Direccion, dbo.Tbl_DatosSubidos.Ciudad, dbo.Tbl_DatosSubidos.Departamento,
dbo.Tbl_DatosSubidos.Inserto, dbo.Tbl_DatosSubidos.IndiceOLlaveUnica, dbo.Tbl_DatosSubidos.Identificacion,
dbo.Tbl_DetalleCiudadesBuenas.IdArchivo, dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytes,
dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytesFinalDato, dbo.Tbl_DetalleCiudadesBuenas.Ciu_Codigo,
dbo.Tbl_DetalleCiudadesBuenas.Ciu_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.Dep_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Dep_Descripcion,
dbo.Tbl_DetalleCiudadesBuenas.DatoIngresado, dbo.Tbl_DetalleCiudadesBuenas.Dis_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Dis_Descripcion,
dbo.Tbl_DetalleCiudadesBuenas.Doc_Codigo, dbo.Tbl_DetalleCiudadesBuenas.Doc_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.Cli_Codigo,
dbo.Tbl_DetalleCiudadesBuenas.Cli_Descripcion, dbo.Tbl_DetalleCiudadesBuenas.DCB_Novedad,
dbo.Tbl_DetalleCiudadesBuenas.Ciu_Georreferenciada, dbo.Tbl_DetalleCiudadesBuenas.Ciu_Prioridad,
dbo.Tbl_DetalleCiudadesBuenas.Geo_CodigoZona, dbo.Tbl_DetalleCiudadesBuenas.DatoCiudadCompleta,
dbo.Tbl_DetalleCiudadesBuenas.Tipo_Distribucion, dbo.Tbl_DatosSubidos.Factura, dbo.Tbl_DatosSubidos.Nombre,
dbo.Tbl_ArchivosSubidos.Ars_NombreArchivoSubido AS NombreArchivo
FROM dbo.Tbl_DatosSubidos WITH (NOLOCK) INNER JOIN
dbo.Tbl_ArchivosSubidos WITH (NOLOCK) ON dbo.Tbl_DatosSubidos.IdArchivo = dbo.Tbl_ArchivosSubidos.Ars_Codigo INNER JOIN
dbo.Tbl_DetalleCiudadesBuenas WITH (NOLOCK) ON dbo.Tbl_DatosSubidos.IdArchivo = dbo.Tbl_DetalleCiudadesBuenas.IdArchivo AND
dbo.Tbl_DatosSubidos.PosicionEnBytes = dbo.Tbl_DetalleCiudadesBuenas.PosicionEnBytes

ORDER BY 1
COMMIT

This wait type means the server is busy sending rows to the client fo processing. The best way to fix it is to reduce the number of rows returned. Alternatively, client-side processing should be accelerated. Or both.

-Ryan / Kardax

Asymmetric communication from ms-sql-m protocol from an SQL Cluster

We are installing an application that requires access to the ms-sql-m
protocol (UDP/1434) as well as the data port (TCP/1433). The SQL Server
we are using is part of an N+1 cluster. The issue is that when we try
to communicate to the node instance xxx.xxx.123.226 recieve the
ms-sql-m response from the physical device ip xxx.xxx.123.222 causing
an asymmetric IP communication and the response appears to be dropped
by the request as one might expect. This causing our installation to
fail.
Has anyone run into this issue before, no of a common misconfiguration
in clustering services that leads to this, or aware of any documented
bug?
Thanks in advance for you posts.
Actually, this is typical of MS cluster applications. The response comes
back from the underlying NIC address, not the cluster virtual address. It
ain't a bug, it's a feature. Or at least it has always operated this way
and could therefore be considered a standard.
Sorry this isn't the answer you were looking for, but it is the way the
system actually works.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<ddnash@.gmail.com> wrote in message
news:1165524543.657106.172130@.79g2000cws.googlegro ups.com...
> We are installing an application that requires access to the ms-sql-m
> protocol (UDP/1434) as well as the data port (TCP/1433). The SQL Server
> we are using is part of an N+1 cluster. The issue is that when we try
> to communicate to the node instance xxx.xxx.123.226 recieve the
> ms-sql-m response from the physical device ip xxx.xxx.123.222 causing
> an asymmetric IP communication and the response appears to be dropped
> by the request as one might expect. This causing our installation to
> fail.
> Has anyone run into this issue before, no of a common misconfiguration
> in clustering services that leads to this, or aware of any documented
> bug?
>
> Thanks in advance for you posts.
>
|||I found the following article that does acknowledge the issue and
states that MS has chosen not to address it at this point, but there
are a couple of workarounds.
http://blogs.msdn.com/sql_protocols/archive/2006/02/27/539706.aspx
Thanks for the post.
Geoff N. Hiten wrote:[vbcol=seagreen]
> Actually, this is typical of MS cluster applications. The response comes
> back from the underlying NIC address, not the cluster virtual address. It
> ain't a bug, it's a feature. Or at least it has always operated this way
> and could therefore be considered a standard.
> Sorry this isn't the answer you were looking for, but it is the way the
> system actually works.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <ddnash@.gmail.com> wrote in message
> news:1165524543.657106.172130@.79g2000cws.googlegro ups.com...
sql

Asymmetric communication from ms-sql-m protocol from an SQL Cluster

We are installing an application that requires access to the ms-sql-m
protocol (UDP/1434) as well as the data port (TCP/1433). The SQL Server
we are using is part of an N+1 cluster. The issue is that when we try
to communicate to the node instance xxx.xxx.123.226 recieve the
ms-sql-m response from the physical device ip xxx.xxx.123.222 causing
an asymmetric IP communication and the response appears to be dropped
by the request as one might expect. This causing our installation to
fail.
Has anyone run into this issue before, no of a common misconfiguration
in clustering services that leads to this, or aware of any documented
bug?
Thanks in advance for you posts.Actually, this is typical of MS cluster applications. The response comes
back from the underlying NIC address, not the cluster virtual address. It
ain't a bug, it's a feature. :) Or at least it has always operated this way
and could therefore be considered a standard.
Sorry this isn't the answer you were looking for, but it is the way the
system actually works.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<ddnash@.gmail.com> wrote in message
news:1165524543.657106.172130@.79g2000cws.googlegroups.com...
> We are installing an application that requires access to the ms-sql-m
> protocol (UDP/1434) as well as the data port (TCP/1433). The SQL Server
> we are using is part of an N+1 cluster. The issue is that when we try
> to communicate to the node instance xxx.xxx.123.226 recieve the
> ms-sql-m response from the physical device ip xxx.xxx.123.222 causing
> an asymmetric IP communication and the response appears to be dropped
> by the request as one might expect. This causing our installation to
> fail.
> Has anyone run into this issue before, no of a common misconfiguration
> in clustering services that leads to this, or aware of any documented
> bug?
>
> Thanks in advance for you posts.
>|||I found the following article that does acknowledge the issue and
states that MS has chosen not to address it at this point, but there
are a couple of workarounds.
http://blogs.msdn.com/sql_protocols/archive/2006/02/27/539706.aspx
Thanks for the post.
Geoff N. Hiten wrote:
> Actually, this is typical of MS cluster applications. The response comes
> back from the underlying NIC address, not the cluster virtual address. It
> ain't a bug, it's a feature. :) Or at least it has always operated this way
> and could therefore be considered a standard.
> Sorry this isn't the answer you were looking for, but it is the way the
> system actually works.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <ddnash@.gmail.com> wrote in message
> news:1165524543.657106.172130@.79g2000cws.googlegroups.com...
> > We are installing an application that requires access to the ms-sql-m
> > protocol (UDP/1434) as well as the data port (TCP/1433). The SQL Server
> >
> > we are using is part of an N+1 cluster. The issue is that when we try
> > to communicate to the node instance xxx.xxx.123.226 recieve the
> > ms-sql-m response from the physical device ip xxx.xxx.123.222 causing
> > an asymmetric IP communication and the response appears to be dropped
> > by the request as one might expect. This causing our installation to
> > fail.
> >
> > Has anyone run into this issue before, no of a common misconfiguration
> > in clustering services that leads to this, or aware of any documented
> > bug?
> >
> >
> > Thanks in advance for you posts.
> >

Asymmetric communication from ms-sql-m protocol from an SQL Cluster

We are installing an application that requires access to the ms-sql-m
protocol (UDP/1434) as well as the data port (TCP/1433). The SQL Server
we are using is part of an N+1 cluster. The issue is that when we try
to communicate to the node instance xxx.xxx.123.226 recieve the
ms-sql-m response from the physical device ip xxx.xxx.123.222 causing
an asymmetric IP communication and the response appears to be dropped
by the request as one might expect. This causing our installation to
fail.
Has anyone run into this issue before, no of a common misconfiguration
in clustering services that leads to this, or aware of any documented
bug?
Thanks in advance for you posts.Actually, this is typical of MS cluster applications. The response comes
back from the underlying NIC address, not the cluster virtual address. It
ain't a bug, it's a feature. Or at least it has always operated this way
and could therefore be considered a standard.
Sorry this isn't the answer you were looking for, but it is the way the
system actually works.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<ddnash@.gmail.com> wrote in message
news:1165524543.657106.172130@.79g2000cws.googlegroups.com...
> We are installing an application that requires access to the ms-sql-m
> protocol (UDP/1434) as well as the data port (TCP/1433). The SQL Server
> we are using is part of an N+1 cluster. The issue is that when we try
> to communicate to the node instance xxx.xxx.123.226 recieve the
> ms-sql-m response from the physical device ip xxx.xxx.123.222 causing
> an asymmetric IP communication and the response appears to be dropped
> by the request as one might expect. This causing our installation to
> fail.
> Has anyone run into this issue before, no of a common misconfiguration
> in clustering services that leads to this, or aware of any documented
> bug?
>
> Thanks in advance for you posts.
>|||I found the following article that does acknowledge the issue and
states that MS has chosen not to address it at this point, but there
are a couple of workarounds.
http://blogs.msdn.com/sql_protocols.../27/539706.aspx
Thanks for the post.
Geoff N. Hiten wrote:[vbcol=seagreen]
> Actually, this is typical of MS cluster applications. The response comes
> back from the underlying NIC address, not the cluster virtual address. It
> ain't a bug, it's a feature. Or at least it has always operated this w
ay
> and could therefore be considered a standard.
> Sorry this isn't the answer you were looking for, but it is the way the
> system actually works.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> <ddnash@.gmail.com> wrote in message
> news:1165524543.657106.172130@.79g2000cws.googlegroups.com...

Assigning User to a Database

Hi

I am using SQL SERVER 2000. Until now my application used the default user "sa", but now the illigal access to my database make me move to a more secure login.

i am new to this concept.

i need to create a login, which i am successful in creating, but my problem is

I need to allow only this user to access my database and no other user should login my database.

please can any one explain how to do this.

its very urgent.

regards

James Alvin

Noone is granted access to a database unless you allow it, so go ahead and create a user and give him the appropiate permissions on the database. Make in addition sure, that if you want to restrict the sysadmin users (which is by default the sa and the members of the sysadmin group, e.g. the local administrators) you will have to remove them from the groups / disable the sa account.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

hi

how can we disable sa account in sql server 2000

|||

You can't disable sa on a SQL Server 2000 instance. Not in any way that would be supported. Some time ago, a few people hacked at the system tables and eventually removed sa but then they had continual problems, couldn't apply service packs and were on an unsupported system. So not in any way that would be supported or stable.

Another option, depending on your application, would be to use just Windows Authentication and use impersonation in your application to log in with just the one windows account you add to the users for this database.

Sysadmins will still be able to access that database though. If it's that critical to lock out everyone, you would want to look at auditing as well as explore third party options for encryption.

-Sue

.

|||

Yes, I ment dismanteling instead of disabling. What I do in reality is to give the sa a cryptic (long and non-guessable) password and lock this in the (virtual) safe. Noone should use that account beside emergencies (like locked accounts etc.) Then I create a new login which has the same rights but a non well-known name (like sa, everyone know that this is the system adminstrator and therefore you only have to guess the password as you already know the name). This account is then mainly used for tasks that cannot be done with the Windows authentication (like users which are not present in the AD). If you are able only use Windows authentication you can even discard this task.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Assign name to the DB users.

I'm developing a vb 2005 application and I’m creating the users directly to the database. I want to assign them names.

I want to do something like this:

CREATE TABLE admin.db_users (

id INT CONSTRAINT db_user_pk PRIMARY KEY,

[name] VARCHAR(50) CONSTRAINT db_user_name_nn NOT NULL,

authentication VARCHAR(25) CONSTRAINT db_user_authentication_nn NOT NULL,

CONSTRAINT db_user_fk FOREIGN KEY(id)

REFERENCES sys.database_principals (principal_id)

ON UPDATE CASCADE

ON DELETE CASCADE

);

GO

This is the error that i'm getting:

Msg 1767, Level 16, State 0, Line 1

Foreign key 'db_user_fk' references invalid table 'sys.database_principals'.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

How do I solve this problem or how can I do something similar.

You cannot create foreign key references to views. A different way to get functionality close to what you need would be to use insert/update trigger on the db_users table where the trigger action verifies the existance of the user in sys.database_principals. Then define a DDL trigger on the database for the DDL_USER_EVENTS (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/fb2a7bd0-2347-488c-bb75-734098050c7c.htm) to catch DROP/ALTER USER events and do the appropriate delete/cascade action.|||

Hi,

I’m new in these if you or some body could to help me I will appreciated. How can I create that trigger?

This is the script of the table that I want to use if this is possible:

CREATE TABLE admin.db_users (

idINT CONSTRAINT user_pk PRIMARY KEY,

[name]VARCHAR(50) CONSTRAINT user_name_nn NOT NULL,

authenticationVARCHAR(25) CONSTRAINT user_authentication_nn NOT NULL,

activeBIT

);

|||

Here's a sample that will give you a sense of what can be done and get you started. You will need to modify/build on the sample to achieve what you need. Please check the "CREATE TRIGGER" topic in Books Online for more detailed information on using triggers.

-- DML Trigger to verify users against sys.database_principals
--
create trigger check_users on db_users
for insert, update
as
-- Collect inserted users that don't exist in sys.database_principals
declare @.invalid_users table([name] sysname)
insert into @.invalid_users
select convert(sysname, name) from inserted except select [name] from sys.database_principals

-- If invalid users are found, rollback transaction
if exists (select [name] from @.invalid_users)
begin
print 'Operation was aborted because following users are invalid'
select [name] as invalid_users from @.invalid_users
if @.@.trancount > 0 rollback tran
end
go

-- DDL Trigger to catch create/drop/alter of new database principals
--
create trigger user_ddl_trig
on database
for ddl_user_events
as
declare @.user_name sysname,
@.event_type sysname

select @.event_type = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','sysname')
select @.user_name = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname')

-- Do your cascased action
if @.event_type = 'DROP_USER'
begin
print 'dropping user ' + @.user_name
delete from db_users where [name] = @.user_name
-- Or check against db_users and rollback this transaction, etc
end
--else if @.event_type = 'ALTER_USER'...
go

|||Thanks a lot.

AspNetSessionExpiredException - Your results may vary?

I have a custom reporting application that displays Sql Server Reporting Services reports on an ASP.NET page using Microsoft's report viewer control. The SSRS reports are local reports (they have an rdlc extension).

When a report is left inactive for a lengthy period of time it generates anAspNetSessionExpiredException when the user attempts to move to a new page. The thing that is surprising is that the formatted exception display is contained within the report viewer. The report document map remains visible as does the report toolbar. In the right pane where the report normally appears I see a standard, raw, ASP.NET exception display.

I'm surprised that the report viewer is "catching" the exception and displaying it within the report viewer. Standard events in the page that hosts the report viewer (PreInit, Init, PageLoad) are never invoked.

I have graceful error handling defined in a base page class but it does me no good because the exception never reaches the page code-behind. Puzzling.

Do any of you ASP.NET developers know if it is possible to catch theAspNetSessionExpiredExceptionso that I can handle it in my application code rather than having SSRS display a very ugly, very raw exception message.

I've included the exception message below.

Thanks.

BlackCatBone

...

Server Error in '/templatedemo' Application.

------------------------

ASP.NET session has expired

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Microsoft.Reporting.WebForms.AspNetSessionExpiredException: ASP.NET session has expired

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[AspNetSessionExpiredException: ASP.NET session has expired]

Microsoft.Reporting.WebForms.ReportDataOperation..ctor() +683

Microsoft.Reporting.WebForms.HttpHandler.GetHandler() +553

Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +10

System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154

System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64

------------------------

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42

I am having exactly the same problem. If anyone knows the solution to this it would be fabulous to here from you.

|||

I had the same problem and I found the solution in this posthttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=170875&SiteID=1. See Brian Hartman's post regarding sessionState. I used his suggestion of using the out-of-process session state as StateServer mode and it fixed the issue for me. Good luck.

ASPNETDB.MDF in Visual Studio 2005 > ISP-hosted MS SQL

I am using (and making some enhancements to) the auto-generated ASPNETDB database in Visual Studio 2005. Now, I would like to deploy the application I have built (together with the data it contains and uses) to my ISP's ASP.NET 2.0 server and my freshly-purchased MS SQL database.

I haven't a clue how to do the above. Some other posts mention Entreprise Manager and Data Transformation Tools, but I really don't know where to start. I wonder if someone would be kind enough to go through each step in detail? Don't worry about sounding condescending... I really am a novice!

I apologise for the naivety of this message...!

Any and all help hugely appreciated!

Josh Ord-Hume

ANy response to your problem with ASPNETDB. I've got a similar problem and at this time it appears that you can't use the login controls in a remote-hosted environment. I'm starting to wish I had used ColdFusion.

Thanks,

Will

|||

In general, you can deploy the database by simply copying the mdf and ldf files. Shut down SQL Server and copy out the files.

Then the ISP can "attach" these to a new instance of SQL Server. This works as long as both versions of SQL Server are the same version.

Another common alternative is to backup the SQL Server database and then give the backup to the ISP and they can restore it to a new database on the SQL Server. To backup the database you can use Enterprise Manager GUI to do this, read the help topics on backup. You can use the BACKUP TSQL command to automate this as well (single TSQL command will create a backup of your database to a file). If you want to automate this and you do not care to understand the TSQL, you can turn on SQL Server Profiler, then backup the database using the GUI, then in SQL Profiler the TSQL command to backup the database will be logged and you can copy and paste this to backup the database again later using TSQL.

Most ISPs who host SQL Server will know how to restore a database from backup.

The only other issue you will have is setting up the proper connection string in your ASP.NET code. Ensure you use a configuration file for the connection string and this way the ISP can easily point the connection string to the proper SQL Server database.

ASPNETDB.MDF in Visual Studio 2005 > ISP-hosted MS SQL

I am using (and making some enhancements to) the auto-generated ASPNETDB database in Visual Studio 2005. Now, I would like to deploy the application I have built (together with the data it contains and uses) to my ISP's ASP.NET 2.0 server and my freshly-purchased MS SQL database.

I haven't a clue how to do the above. Some other posts mention Entreprise Manager and Data Transformation Tools, but I really don't know where to start. I wonder if someone would be kind enough to go through each step in detail? Don't worry about sounding condescending... I really am a novice!

I apologise for the naivety of this message...!

Any and all help hugely appreciated!

Josh Ord-Hume

ANy response to your problem with ASPNETDB. I've got a similar problem and at this time it appears that you can't use the login controls in a remote-hosted environment. I'm starting to wish I had used ColdFusion.

Thanks,

Will

|||

In general, you can deploy the database by simply copying the mdf and ldf files. Shut down SQL Server and copy out the files.

Then the ISP can "attach" these to a new instance of SQL Server. This works as long as both versions of SQL Server are the same version.

Another common alternative is to backup the SQL Server database and then give the backup to the ISP and they can restore it to a new database on the SQL Server. To backup the database you can use Enterprise Manager GUI to do this, read the help topics on backup. You can use the BACKUP TSQL command to automate this as well (single TSQL command will create a backup of your database to a file). If you want to automate this and you do not care to understand the TSQL, you can turn on SQL Server Profiler, then backup the database using the GUI, then in SQL Profiler the TSQL command to backup the database will be logged and you can copy and paste this to backup the database again later using TSQL.

Most ISPs who host SQL Server will know how to restore a database from backup.

The only other issue you will have is setting up the proper connection string in your ASP.NET code. Ensure you use a configuration file for the connection string and this way the ISP can easily point the connection string to the proper SQL Server database.

aspnet_wp.exe could not be started.

When I launch the reporting services report server on my sql server, I
recieve the following error:
"Server Application Unavailable
The web application you are attempting to access on this web server is
currently unavailable. Please hit the "Refresh" button in your web browser
to retry your request.
Administrator Note: An error message detailing the cause of this specific
request failure can be found in the application event log of the web server.
Please review this log entry to discover what caused this error to occur."
In the event log, the details are...
"aspnet_wp.exe could not be started. The error code for the failure is
80004005. This error can be caused when the worker process account has
insufficient rights to read the .NET Framework files. Please ensure that the
.NET Framework is correctly installed and that the ACLs on the installation
directory allow access to the configured account. "
I have validated that ASPNET user account does have the appropriate
permissions.
I have ran "aspnet_regiis -i".
I have validated that ASPNET user account is not locked out.
Are there any other solutions that I can try to get my report server
functioning?Hi Rhonda,
Make sure 'ASP.Net Machine Account' has Full Control permissions to the
following folder:
"C:\WINNT\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET Files"
Eshoo
"Helpme Rhonda" <Helpme Rhonda@.discussions.microsoft.com> wrote in message
news:7AA4DA9E-232E-420D-A5A3-47F213B54975@.microsoft.com...
> When I launch the reporting services report server on my sql server, I
> recieve the following error:
> "Server Application Unavailable
> The web application you are attempting to access on this web server is
> currently unavailable. Please hit the "Refresh" button in your web
browser
> to retry your request.
> Administrator Note: An error message detailing the cause of this specific
> request failure can be found in the application event log of the web
server.
> Please review this log entry to discover what caused this error to occur."
> In the event log, the details are...
> "aspnet_wp.exe could not be started. The error code for the failure is
> 80004005. This error can be caused when the worker process account has
> insufficient rights to read the .NET Framework files. Please ensure that
the
> .NET Framework is correctly installed and that the ACLs on the
installation
> directory allow access to the configured account. "
> I have validated that ASPNET user account does have the appropriate
> permissions.
> I have ran "aspnet_regiis -i".
> I have validated that ASPNET user account is not locked out.
> Are there any other solutions that I can try to get my report server
> functioning?|||My 'ASP.Net Machine Account' does have full control of the directory
mentioned below. I also have a folder of
"C:\WINNT\Microsoft.NET\Framework\v1.0.3705\" from the original installation.
Could there be some conflict with that being there?
If not, are there any other suggestions? Thank you in advance.
"Eshoo" wrote:
> Hi Rhonda,
> Make sure 'ASP.Net Machine Account' has Full Control permissions to the
> following folder:
> "C:\WINNT\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET Files"
>
> Eshoo
> "Helpme Rhonda" <Helpme Rhonda@.discussions.microsoft.com> wrote in message
> news:7AA4DA9E-232E-420D-A5A3-47F213B54975@.microsoft.com...
> > When I launch the reporting services report server on my sql server, I
> > recieve the following error:
> >
> > "Server Application Unavailable
> > The web application you are attempting to access on this web server is
> > currently unavailable. Please hit the "Refresh" button in your web
> browser
> > to retry your request.
> >
> > Administrator Note: An error message detailing the cause of this specific
> > request failure can be found in the application event log of the web
> server.
> > Please review this log entry to discover what caused this error to occur."
> >
> > In the event log, the details are...
> >
> > "aspnet_wp.exe could not be started. The error code for the failure is
> > 80004005. This error can be caused when the worker process account has
> > insufficient rights to read the .NET Framework files. Please ensure that
> the
> > .NET Framework is correctly installed and that the ACLs on the
> installation
> > directory allow access to the configured account. "
> >
> > I have validated that ASPNET user account does have the appropriate
> > permissions.
> > I have ran "aspnet_regiis -i".
> > I have validated that ASPNET user account is not locked out.
> >
> > Are there any other solutions that I can try to get my report server
> > functioning?
>
>|||My agency is have this problem as well... I am exhaused from troubleshooting
SQL Reporting Services. Here are details of the problem:
I've been having a little trouble with an installation of SQL Reporting
Services. The installation is truly vanilla, but I can't seem to find a
resolution for the below noted problem. I have tried all of the fixes I can
find on MSDN and TechNet in the Knowledge Base that address the error
message. If you can help at all, I would greatly appreciate.
From Browser Requesting Reporting Services (http://{hostname}/Reports/ or
http://{hostname}/reportserver):
Server Application Unavailable
The web application you are attempting to access on this web server is
currently unavailable. Please hit the "Refresh" button in your web browser to
retry your request.
Administrator Note: An error message detailing the cause of this specific
request failure can be found in the application event log of the web server.
Please review this log entry to discover what caused this error to occur.
Event Log Detail:
aspnet_wp.exe could not be started. The error code for the failure is
80070545. This error can be caused when the worker process account has
insufficient rights to read the .NET Framework files. Please ensure that the
.NET Framework is correctly installed and that the ACLs on the installation
directory allow access to the configured account.
Knowledge Base/MSDN/TechNet Attempted Fixes:
317012, 315158, 323292, 833444, 811320
In one situation there were errors in the security log regarding access,
this was resolved on that installation and now works. The only errors
produced in the event log is the one noted above.
"Eshoo" wrote:
> Hi Rhonda,
> Make sure 'ASP.Net Machine Account' has Full Control permissions to the
> following folder:
> "C:\WINNT\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET Files"
>
> Eshoo
> "Helpme Rhonda" <Helpme Rhonda@.discussions.microsoft.com> wrote in message
> news:7AA4DA9E-232E-420D-A5A3-47F213B54975@.microsoft.com...
> > When I launch the reporting services report server on my sql server, I
> > recieve the following error:
> >
> > "Server Application Unavailable
> > The web application you are attempting to access on this web server is
> > currently unavailable. Please hit the "Refresh" button in your web
> browser
> > to retry your request.
> >
> > Administrator Note: An error message detailing the cause of this specific
> > request failure can be found in the application event log of the web
> server.
> > Please review this log entry to discover what caused this error to occur."
> >
> > In the event log, the details are...
> >
> > "aspnet_wp.exe could not be started. The error code for the failure is
> > 80004005. This error can be caused when the worker process account has
> > insufficient rights to read the .NET Framework files. Please ensure that
> the
> > .NET Framework is correctly installed and that the ACLs on the
> installation
> > directory allow access to the configured account. "
> >
> > I have validated that ASPNET user account does have the appropriate
> > permissions.
> > I have ran "aspnet_regiis -i".
> > I have validated that ASPNET user account is not locked out.
> >
> > Are there any other solutions that I can try to get my report server
> > functioning?
>
>|||Me too. This is frustrating. Is this a bug? I had a perfectly operational
install of SQLRS SP1 until I installed SP2. Anyone with ideas?
"Helpme Rhonda" wrote:
> When I launch the reporting services report server on my sql server, I
> recieve the following error:
> "Server Application Unavailable
> The web application you are attempting to access on this web server is
> currently unavailable. Please hit the "Refresh" button in your web browser
> to retry your request.
> Administrator Note: An error message detailing the cause of this specific
> request failure can be found in the application event log of the web server.
> Please review this log entry to discover what caused this error to occur."
> In the event log, the details are...
> "aspnet_wp.exe could not be started. The error code for the failure is
> 80004005. This error can be caused when the worker process account has
> insufficient rights to read the .NET Framework files. Please ensure that the
> .NET Framework is correctly installed and that the ACLs on the installation
> directory allow access to the configured account. "
> I have validated that ASPNET user account does have the appropriate
> permissions.
> I have ran "aspnet_regiis -i".
> I have validated that ASPNET user account is not locked out.
> Are there any other solutions that I can try to get my report server
> functioning?|||Just as an FYI, as a work around, I added the ASP.NET user account to the
local server's Administrators group and it started working again:
NOTE: This completely defeats the purpose of having the ASP.NET account so
we still need to find a solution to why this "just happens".
"Helpme Rhonda" wrote:
> When I launch the reporting services report server on my sql server, I
> recieve the following error:
> "Server Application Unavailable
> The web application you are attempting to access on this web server is
> currently unavailable. Please hit the "Refresh" button in your web browser
> to retry your request.
> Administrator Note: An error message detailing the cause of this specific
> request failure can be found in the application event log of the web server.
> Please review this log entry to discover what caused this error to occur."
> In the event log, the details are...
> "aspnet_wp.exe could not be started. The error code for the failure is
> 80004005. This error can be caused when the worker process account has
> insufficient rights to read the .NET Framework files. Please ensure that the
> .NET Framework is correctly installed and that the ACLs on the installation
> directory allow access to the configured account. "
> I have validated that ASPNET user account does have the appropriate
> permissions.
> I have ran "aspnet_regiis -i".
> I have validated that ASPNET user account is not locked out.
> Are there any other solutions that I can try to get my report server
> functioning?

aspnet_regsql.exe

hi...

I want to Create an Application Service Database for SQL Server,SqlWebEventProvider, and i am trying to write in command line:

c:\>C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -U user -P pass -S sqlserver\sqltest -A w

i get the following error:

The argument 'sqlserver\sqltest' is invalid.

why is that?

do i have to use the following flags:

-ssadd -sstype t ?

and, how is the right way to do this...?

thank you...

Try to use one of this options:

c:\>C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -U user -P pass -S sqlserver -A w

c:\>C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -U user -P pass -S sqltest -A w

c:\>C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe -U user -P pass -S localhost -A w

aspnet_regsql

Where's the sql script to generate "aspnet_regsql" application services database stored? Thanks.

We use ASP.NET SQL Server Registration tool (aspnet_regsql.exe) to create a Microsoft SQL Server database for use by the SQL Server providers in ASP.NET, and I haven't heard the corresponding SQL script. Are you sure there is a SQL script used to created database objects for use by application services?

For more information about the tool, you can refer to:

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

|||I read an article saying DBA can use a script provided by MS to generate the application services database on SQL server. Thanks.

aspnet_regsql

Hi,

It seems everytime I create a new project, I have to run aspnet_regsql to map the database to my application, otherwise, there'll be errors when trying to connect to SQL in my application. My question is, is this a norm? To be executing this procedure everytime I create a web application?

bump, anyone?

|||

Hi,

In your project, if you want to create a Microsoft SQL Server database for use by the SQL Server providers in ASP.NET, you have to use that tool to access that database in your SqlServer. My suggestion is once you use that tool, make a copy of the database which built by that tool as a sample database and stored in your SqlServer. In the furture if you want to get a new database for use by SqlServer providers in ASP.NET. Just create a new empty database and import the structure from the sample one.

Thanks.

ASPNET worker process account can't login to Database

Hi,
I'm using .Net Remoting and IIS (5.1) to pull data from a (local for now)
SQL Server 2005 database. My application is failing at the point at which a
connection to the database is being opened.
Using cn as New SqlConnection(ConnectString)
cn.Open()
Causes an SQLException to be thrown. The message is...
Cannot open database "AppData" requested by the login. The login failed.
"Login failed for user '<MachineName>\ASPNET'
This is the ASP.Net worker process user. I have had some problems with this
User Id on this machine - it was originally absent from the machine. I got
it back by re-installing ASP.Net (ver 2.0). Now all my remoting stuff is
working, but I cannot get to the data.
So my question is, what configuration items / permissions in SQL Server
should I set up to give the ASPNet worker process access to my data?.
For now the application, SQL Server Instance, and IIS are all on the same
machine. Everything seems to work except for the login call to the Database
.
Thanks for your help.
BBMHello BBM,
You could add the <Machine Name>\ASPNET user in the SQL Login. After that,
you could grant the proper permission for the account to access the sql
database.
Here is an article you could refer:
Building Secure ASP.NET Applications: Authentication, Authorization, and
Secure Communication
http://msdn2.microsoft.com/en-us/library/aa302387.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Wei Lu,
Thanks for your help. I knew this had to be covered someplace.
BTW, Searching Help in SQL Server, or in Visual Studio on this topic, the
ASP "worker process" account, returns nothing remotely useful.
Thanks again.
BBM
"Wei Lu [MSFT]" wrote:

> Hello BBM,
> You could add the <Machine Name>\ASPNET user in the SQL Login. After that,
> you could grant the proper permission for the account to access the sql
> database.
> Here is an article you could refer:
> Building Secure ASP.NET Applications: Authentication, Authorization, and
> Secure Communication
> http://msdn2.microsoft.com/en-us/library/aa302387.aspx
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ========================================
==========
> Get notification to my posts through email? Please refer to
> l]
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> [url]http://msdn.microsoft.com/subscriptions/support/default.aspx." target="_blank">http://msdn.microsoft.com/subscript...t/default.aspx.
> ========================================
==========
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello BBM,
You could use the MSDN search in the web directly. And you could search for
ASPNET and SQL Server as the key words.
Thanks!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscript...t/default.aspx.
========================================
==========
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

ASPNET user with network resources elsewhere on a domain

I've been trying to workout how, without impersonation, I can allow an application running under the default ASPNET user to access a network resource (SQL Server/ADAM) not on the same physical machine.

It seems that because the ASPNET user is a local user not a domain user I can't setup windows authentication on the network resource. That seems to leave impersonation which MS et al say is unwise.

Have I mised something here?

Regards,

Steve.Yes.

You can change the user running the asp.net process. ONly the STANDARD user is a local user. It CAN also be a domain user, per your choosing.

ASPNET user in MSDE

Hello.

I am using MSDE in an ASP .NET application using forms authentication in order to the user can visit my website anonymously but have to authenticate in some pages.

Web server and MSDE server are in the same computer.

I would like to know how role I should set for the ASPNET sql server user. I am using Microsoft SQL Web Data Administrator and when I am creating the ASPNET user, then a page with some roles is shown.

Roles like public (default), db_owner, db_accessadmin, db_datareader, db_datawriter...

I had applied db_datareader and db_datawriter but now, when I use store procedures in my code, an error is made so I have applied db_owner role for the ASPNET user.

Is this correct or db_owner is a excessive privileged role for the ASPNET user?

Thank you and sorry for my English.DONT EVER GIVE THAT USER DB_OWNER!! You're setting yourself up for sql inject attacks.

The best thing to do is create a role named WebUser and add the asp.net user to that role. Then grant execute permissions to the user.

Here's the script that you need:

exec sp_addrole 'WebUser'
go
exec sp_addrolemember 'WebUser', 'MACHINENAME\ASPNET'
go
grant execute on PROCNAME to WebUser

aspnet user can not access sql server

We have an ASP.net application that currently sits on a server that runs IIS and sql server. We are spliting IIS and SQL server into 2 seperate machines. I believe I have the connection string okay as I can see the entries in the security log on the sql server machine however it keeps saying aspnet user invalid user or invalid password. What gives with this. We put .net on the sql server however this just added the aspnet user to the machine. Do I need to create an aspnet user inside the sql server and give it control of the dbases as well? I am running this using the personal web server not IIS on a server. I have a project on my desktop and am using the "local" IIS or personal web server that comes with visual studio out of the box. What kills me is that when I put the project on a real IIS server that has sql server on the same machine I have no issues. However when I split the dbase and the IIS apart onto 2 servers I get this aspnet invalid user or invalid password.
HELPtry giving database permission to the aspnet user on the other machine (IIS). ex. IISMachine\ASPNET
cheers
Shane Sukul
|Bsc|Mcsd.Net|Mcsd|Mcad|
|||

In SQL Server you can use Windows Authentication or SQL Server Authentication. If in connection string you don't spec. which user and password to use it tries to use Windows Authentication and the user which ASP.NET process runs should have permissions in the SQL Machine.
I normally use SQL Server authentication, put in my web.config (with only permission to the ASP.NET process) the ConnectionString with user and password.
HH
NeuralC

|||

neuralc wrote:

In SQL Server you can use Windows Authentication or SQL Server Authentication. If in connection string you don't spec. which user and password to use it tries to use Windows Authentication and the user which ASP.NET process runs should have permissions in the SQL Machine.
I normally use SQL Server authentication, put in my web.config (with only permission to the ASP.NET process) the ConnectionString with user and password.
HH
NeuralC

Of course you wouldn't actually put that in clear text!
|||

None of this works. The application currently sits on my desktop which is running xp professional. The web server is local to my machine whatever ships with visual studio. The dbase is on a server that has the .net environment running on it. Why can I not see the dbase information when I have the correct connection string.

If I take the same application on my machine and go and hit a server that has IIS adn sql server installed on the same machine it works fine
What gives

|||Also have a look at your firewall settings on the server that hosts your database.