How to run SQL-Server on a Webserver!

Hi!

Today I decided to finally install MS SQL 2005 Express on my VPS. For a long time I tried to work with MS Access what definitely brings a lot of complications.

I already installed SQL05Exp on my local machine and everything works great here. Because I'm quite new to these things I simply installed SQL05Exp on the Server the same way like locally. I just clicked through the installation with the predefined settings and everything installed without troubles.

When I uploaded my first Application and tried to run it, I got the following error-message:

Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.

I then read somewhere that I could change the web.config connectionstring to "User Instance=False" what leads to the following:

CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:\Inetpub\vhosts\gsp-peru.com\subdomains\muestra\httpdocs\App_Data\Database.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

I read a lot of documents about how to install, etc. but it couldn't really use it for me, surely for a lake of basics. When I understand the problem correctly, the user running the app on the server and trying to attach the mdf-database to the sql-server has not the rights to do that. However I don't know what to do against that.

What I would really love is a simple solution that allows me to connect to the sql-server from whatever domain on the server, just by copying the mdf. It also would be nice to use some kind of password in the connectionstring to assure that just my apps can connect to my databases.

I would be very thankful if someone could give me some good basic explanation what to do and maybe also give me a reference to some nice and understandable informations on this.

Best regards,

Markus

In most cases, the error is exactly what the error message says, but most people ignore what it's saying, so I'll break it down:

Error Description
An attempt to attach an auto-named database for file C:\Inetpub\vhosts\gsp-peru.com\subdomains\muestra\httpdocs\App_Data\Database.mdf failed.

Possible causes
1)database with the same name exists
2)specified file cannot be opened,
3)or it is located on UNC share

Fixing #1 Seeing as the you named the mdf "Database", it's quite possible that you have a database called "database" already. Rename the file to something meaningful, like...gsp-peru.mdf

Fixing #2 Who is your SQL Server running as, and do they have read/write permissions to the app_data folder?

Fixing #3 Move the file, heh. This is rarely the cause, as most people don't do this.

|||

Hi Motley, thanks for your help.

Fixing #1 Seeing as the you named the mdf "Database", it's quite possible that you have a database called "database" already. Rename the file to something meaningful, like...gsp-peru.mdf

There is not even one database installed until now, however I tried to change the name but everything is the same.

Fixing #2 Who is your SQL Server running as, and do they have read/write permissions to the app_data folder?

It was running as Network-Service, I also tried as Local Service and as Local System, always the same result.

Fixing #3 Move the file, heh. This is rarely the cause, as most people don't do this.

It is located within the App_Data folder, everything is on the same machine.

What else could it be?

Markus

|||

Did you check the second half of Fix#2? Most likely you need to make sure the user(Network Service) has write/read permissions to the folder (or to the database directly, if you assign permission database by database).

If you use window authentication, you may need assign window login user with write/read permission to this folder too.

|||

Now I tried to assign the user everyone with all possible rights to both the master database located in the installation folder of sql server and I did the same on both databases of my app.

Everything stays the same. What else could cause this errors?

Markus

|||

I didn't mean the user everyone. Try this one (yourMachinesName\theLoginUser) and Network Service (win2k3 IIS 6.0).

to App_Data with write/read permissions.

Here is a link you can check the basics.

Tell you the truth, I went through this one alittle while ago. What I can remember is the security settings. Since you mention this problem, I am curious that how did you access your application? through VWD on the server or through a virtual directory under IIS 6.0? It may help us to understand why the problem didn't go away.

If you are using IIS to access your application, there are some other settings to adjust like Application should run on asp.NET 2.0 , connction string in your web.config modification ...

Hope we can get the application run soon.

|||

Dear limno, thanks for your help.

Yesterday I assigned the user everyone because what I know this is like setting access for all the existing users. It is a quick way to test if it has something to do with rights, after that I can search for the right user which to give access to.

However, I did exactly like you told me, I gave the network service user and the user administrator which i us to log on full rights but the problem stays the same.

Not sure if I understand your second question. Locally I'm using VWD with SQL Express and it works fine. But on my VPS it's not working. Means if I call my website through the browser I get this error-message.

Markus

|||Change the anonymous user accout for the application to you. You can't log in as any of the accounts you mentioned, so they won't have a user directory (C:\Documents and Settings\{User Name}).|||

The Anonymous user of the application is gsp-peru and it also has a user directory. I tried to change it to administrator which changed nothing. Hope I understood your idea.

Markus

|||

If there are any more hints or ideas I would be happy to hear about them.

Limno, you wrote above about a link which seems that you have not set, maybe you could post it again.

Markus

|||

Hello:

Here is the link from the tutorial.

http://www.asp.net/QuickStart/aspnet/doc/data/default.aspx

Please read the Deploy your local database section.

You mentioned you had problem to access the site from the browser.

Which one IIS or the built-in web browser? what is your url looks like?

Also, I think the user we are talking about isyourmachinename\ausername.

If you cannot figure it out, I will start over on my machine to figure it out.

Good luck(sorry for the delay, I was locked out for a while on this site.)

|||

This thread has an answer.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125227&SiteID=1

|||

Hi limno,

Many thanks for all your replies. I finally got it running and the problem was where we searched. In IIS I chose the application-pool of my domain and assigned as identity "local system". After that I also had to change sql-server to run under local system. Now it is working, however I'm not quite sure if I will use that because of Security reasons. If I understand correctly, it now would be possible to get access to the whole file-system through script and probably more. Beside all users/domains will be able to access all databases within the instance of sql server.

So the next big step would be to get sql server running under the standard IWPD user of the domain and also to include some username/password security through the connection-string.

Is that possible?

Markus

|||

Hello:

FYI:

<connectionStrings>

<add name="conString1" connectionString="Data Source=yourServer;Integrated Security=false;Initial Catalog=YourDB;User ID=yourDBUser;Password=thePassword" providerName="System.Data.SqlClient" />

<add name="conString2" connectionString="Data Source=yourServer;Initial Catalog=yourDB;Integrated Security=True" providerName="System.Data.SqlClient" />
<remove name="LocalSqlServer" />

</connectionStrings>

<roleManager enabled="true"
defaultProvider="CustomizedRoleProvider">
<providers>
<clear />
<add name="CustomizedRoleProvider"
type="System.Web.Security.SqlRoleProvider"
connectionStringName="conString1"
applicationName="/"
/>
</providers>
</roleManager>

<membership defaultProvider="CustomizedMembershipProvider">
<providers>
<clear />
<add name="CustomizedMembershipProvider"
type="System.Web.Security.SqlMembershipProvider"
connectionStringName="conString2"
applicationName="/"
requiresUniqueEmail="false"
minRequiredPasswordLength="1"
minRequiredNonalphanumericCharacters="0"
passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"

/>
</providers>
</membership>

you need create your database user in your own database first.

Assume you merged the ASPNETDB.MDF into yourDB and you are using customized prociders which point back to the default ones for membership and roles.

The applicationName is important. You can type your application name in it. You can search for explainations.

Hope this can save you a little bit time.

How to revoke Rights in Mixed Mode Authentication

Hi all,
we are using SQL Server 2000 Standard Edition on Win2K with Mixed Mode
Authentication.
Revoking rights to database users using SQL-Server logins does not seem to
work properly.
When I log in as TinyTim with SQL-Server authentification I can happily
create views on the database (TinyTim is assigned to), select, insert and
update in ForbiddenTable, ...
Output of sp_helpuser 'TinyTim'
UserName GroupName LoginName DefDBMame UserID
----
--
TinyTim Auswerter NULL NULL 5
Output of sp_helprotect, entries related to user TinyTim
Owner Object Grantee Granto ProtectType Action Column
----
--
dbo ForbiddenTable Auswerter dbo Deny Insert .
dbo ForbiddenTable Auswerter dbo Deny Update (All+New)
dbo ForbiddenTable TinyTim dbo Deny Delete .
dbo ForbiddenTable TinyTim dbo Deny Insert .
dbo ForbiddenTable TinyTim dbo Deny References(All+New)
dbo ForbiddenTable TinyTim dbo Deny Select (All+New)
dbo ForbiddenTable TinyTim dbo Deny Update (All+New)
. . public dbo Deny Backup Database .
. . public dbo Deny Backup Transaction .
. . public dbo Deny Create Default .
. . public dbo Deny Create Function .
. . public dbo Deny Create Procedure .
. . public dbo Deny Create Rule .
. . public dbo Deny Create Table .
. . public dbo Deny Create View .
Thanks in advance
Regards,
ArneArne,
Is TinyTim a member of the sysadmin role? You can't deny anyhing to
sysadmins.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:3DEB6B87-3D28-40D8-9262-AB527E486F47@.microsoft.com...
> Hi all,
> we are using SQL Server 2000 Standard Edition on Win2K with Mixed Mode
> Authentication.
> Revoking rights to database users using SQL-Server logins does not seem to
> work properly.
> When I log in as TinyTim with SQL-Server authentification I can happily
> create views on the database (TinyTim is assigned to), select, insert and
> update in ForbiddenTable, ...
>
> Output of sp_helpuser 'TinyTim'
> UserName GroupName LoginName DefDBMame UserID
> ----
--
> TinyTim Auswerter NULL NULL 5
> Output of sp_helprotect, entries related to user TinyTim
> Owner Object Grantee Granto ProtectType Action Column
> ----
--
> dbo ForbiddenTable Auswerter dbo Deny Insert .
> dbo ForbiddenTable Auswerter dbo Deny Update (All+New)
> dbo ForbiddenTable TinyTim dbo Deny Delete .
> dbo ForbiddenTable TinyTim dbo Deny Insert .
> dbo ForbiddenTable TinyTim dbo Deny References(All+New)
> dbo ForbiddenTable TinyTim dbo Deny Select (All+New)
> dbo ForbiddenTable TinyTim dbo Deny Update (All+New)
> . . public dbo Deny Backup Database .
> . . public dbo Deny Backup Transaction .
> . . public dbo Deny Create Default .
> . . public dbo Deny Create Function .
> . . public dbo Deny Create Procedure .
> . . public dbo Deny Create Rule .
> . . public dbo Deny Create Table .
> . . public dbo Deny Create View .
>
> Thanks in advance
> Regards,
> Arne
>|||"Dejan Sarka" wrote:

> Arne,
> Is TinyTim a member of the sysadmin role? You can't deny anyhing to
> sysadmins.
Hi Dejan,
promising shot but: TinyTim is not a member of any server role
Arne|||Arne,
I can't reproduce this. Can you generate T-SQL script for the table,
database users, logins and permissons? It can be done quickly in EM, just
right-click on the table and select All Tasks -> Generate SQLScripts. Then
in Options menu select optins for logins, users and permissions. Post the
script here, so we can check it.
Thanks,
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:861933B6-FB6E-44C5-8674-470764833EE7@.microsoft.com...
> "Dejan Sarka" wrote:
>
> Hi Dejan,
> promising shot but: TinyTim is not a member of any server role
> Arne|||> Arne,
> I can't reproduce this. Can you generate T-SQL script for the table,
> database users, logins and permissons? It can be done quickly in EM, just
Hi Dejan,
the generated script does not contain the settings for the 'public' role -
(just did not come out by the described procedure) the properties of the
database ProsimTest show red X's in each checkbox - hope the rest of it
helps.
In case it is relevant: we are using the standard edition of SQL Server.
Regards,
Arne
if not exists (select * from master.dbo.syslogins where loginname =
N'TinyTim')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
N'ProsimTest', @.loginlang = N'Deutsch'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases
where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages
where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'TinyTim', null, @.logindb, @.loginlang
END
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', sysadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', securityadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', serveradmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', setupadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', processadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', diskadmin
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', dbcreator
GO
exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', bulkadmin
GO
if not exists (select * from dbo.sysusers where name = N'Any' and uid < 1638
2)
EXEC sp_grantdbaccess N'VORDEFINIERT\Administratoren', N'Any'
GO
if not exists (select * from dbo.sysusers where name = N'TinyTim' and uid <
16382)
EXEC sp_grantdbaccess N'TinyTim'
GO
DENY CREATE FUNCTION , CREATE TABLE , CREATE VIEW , CREATE PROCEDURE ,
DUMP DATABASE , CREATE DEFAULT , DUMP TRANSACTION , CREATE RULE TO
[TinyTim]
GO
if not exists (select * from dbo.sysusers where name = N'Auswerter' and uid
> 16399)
EXEC sp_addrole N'Auswerter'
GO
exec sp_addrolemember N'Auswerter', N'TinyTim'
GO
CREATE TABLE [dbo].[ForbiddenTable] (
[ID] [int] NOT NULL ,
[Name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
DENY SELECT ON [dbo].[ForbiddenTable] TO [public] CASCADE
GO
DENY UPDATE , INSERT ON [dbo].[ForbiddenTable] TO [Auswerter
] CASCADE
GO
DENY REFERENCES , SELECT , UPDATE , INSERT , DELETE ON
[dbo].[ForbiddenTable] TO [TinyTim] CASCADE
GO|||Arne,
I run the script on my SQL Server and I still can't reprduce the problem.
Here TinyTim is denied everything. I use SQL 2k Dev edtion with SP3a. Maybe
you should contact support?
Sorry I couldn't help more.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:81A8E360-A236-44CB-A66C-7C5E43BD2BB6@.microsoft.com...
just[vbcol=seagreen]
> Hi Dejan,
> the generated script does not contain the settings for the 'public' role -
> (just did not come out by the described procedure) the properties of the
> database ProsimTest show red X's in each checkbox - hope the rest of it
> helps.
> In case it is relevant: we are using the standard edition of SQL Server.
>
> Regards,
> Arne
>
> if not exists (select * from master.dbo.syslogins where loginname =
> N'TinyTim')
> BEGIN
> declare @.logindb nvarchar(132), @.loginlang nvarchar(132) select @.logindb =
> N'ProsimTest', @.loginlang = N'Deutsch'
> if @.logindb is null or not exists (select * from master.dbo.sysdatabases
> where name = @.logindb)
> select @.logindb = N'master'
> if @.loginlang is null or (not exists (select * from
master.dbo.syslanguages
> where name = @.loginlang) and @.loginlang <> N'us_english')
> select @.loginlang = @.@.language
> exec sp_addlogin N'TinyTim', null, @.logindb, @.loginlang
> END
> GO
>
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', sysadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', securityadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', serveradmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', setupadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', processadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', diskadmin
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', dbcreator
> GO
> exec sp_addsrvrolemember N'VORDEFINIERT\Administratoren', bulkadmin
> GO
>
> if not exists (select * from dbo.sysusers where name = N'Any' and uid <
16382)
> EXEC sp_grantdbaccess N'VORDEFINIERT\Administratoren', N'Any'
> GO
> if not exists (select * from dbo.sysusers where name = N'TinyTim' and uid
<
> 16382)
> EXEC sp_grantdbaccess N'TinyTim'
> GO
> DENY CREATE FUNCTION , CREATE TABLE , CREATE VIEW , CREATE PROCEDURE ,
> DUMP DATABASE , CREATE DEFAULT , DUMP TRANSACTION , CREATE RULE TO
> [TinyTim]
> GO
> if not exists (select * from dbo.sysusers where name = N'Auswerter' and
uid
> EXEC sp_addrole N'Auswerter'
> GO
> exec sp_addrolemember N'Auswerter', N'TinyTim'
> GO
> CREATE TABLE [dbo].[ForbiddenTable] (
> [ID] [int] NOT NULL ,
> [Name] [varchar] (100) COLLATE Latin1_General_CI_AS NULL
> ) ON [PRIMARY]
> GO
> DENY SELECT ON [dbo].[ForbiddenTable] TO [public] CASCADE
> GO
> DENY UPDATE , INSERT ON [dbo].[ForbiddenTable] TO [Auswert
er] CASCADE
> GO
> DENY REFERENCES , SELECT , UPDATE , INSERT , DELETE ON
> [dbo].[ForbiddenTable] TO [TinyTim] CASCADE
> GO
>