The login failed

[Nycolas]

I recently converted an MS Access database to Sql Server 2008 using the MSSMA
tool, and i am currently still trying to connect two of my web apps to this
database. One of them is an asp.Net web application and the other is a
classic asp web app.

I'm using Windows Integrated Security in combination with the NETWORK
SERVICE account. Connection to the asp.Net app is behaving normally. I use
the following connection string within the web.config file :

Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=myDBName;
Trusted_Connection=yes;

But when i try to connect my classic asp web app, i systematically get the
following error :

Cannot open database "myDBName" requested by the login. The login failed.

I tried the following different connection strings without any success :

Provider=SQLNCLI10;Server=WPC1480\SQLEXPRESS;Database=myDBName;Integrated
Security=SSPI;

PROVIDER=MSDASQL;DRIVER={SQL
SERVER};SERVER=WPC1480\SQLEXPRESS;DATABASE=myDBName;Integrated Security=SSPI;

Provider=SQLOLEDB;Data Source=C:\Program Files\Microsoft SQL
Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\;Initial
Catalog=myDBName;Network=DBMSSOCN;Integrated Security=SSPI;

provider=SQLOLEDB;Data Source=WPC1480\SQLEXPRESS;Initial
Catalog=myDBName;Integrated Security=SSPI;

Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=myDBName;Data
Source=WPC1480\SQLEXPRESS;

I also checked that the NETWORK SERVICE account is declared both at the sql
server level and at the database level.

Any suggestions on why i am getting this error ?

Tks

[Erland Sommarskog]

Nycolas (Nyc...@discussions.microsoft.com) writes:
> I recently converted an MS Access database to Sql Server 2008 using the
> MSSMA tool, and i am currently still trying to connect two of my web
> apps to this database. One of them is an asp.Net web application and the
> other is a classic asp web app.
>
> I'm using Windows Integrated Security in combination with the NETWORK
> SERVICE account. Connection to the asp.Net app is behaving normally. I use
> the following connection string within the web.config file :
>
> Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=myDBName;
> Trusted_Connection=yes;
>
> But when i try to connect my classic asp web app, i systematically get the
> following error :
>
> Cannot open database "myDBName" requested by the login. The login failed.

Apparently the login you are using is not able to access the database.

I suggest that you try this:

Change the connection string to read

Database=master

Then change the application so that the first command is

"USE myDBname"

The error message you get, may give you more information.

It may also be worth to run this SELECT as a debug:

SELECT system_user, user

(Run this while you are still in master.)

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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Nycolas]

Actually, by adding the "Built-In\Users" account to the "security" tab of my
database, the connection problem seems to be solved. Is there anything wrong
with this solution from a security point of view ?

Thanks
Nicolas

"Erland Sommarskog" wrote:

> .
>

[Nycolas]

Thank you Erland,

SELECT system_user, user gives me the following (1 line) result :

myServerName\myAdminAccountName on the first column

and

dbo on the second column

Is this correct or are there other accounts missing ?

Otherwise, where do i change the application to add: USE myDBname ?

Thanks
Nicolas

[Erland Sommarskog]

Nycolas (Nyc...@discussions.microsoft.com) writes:
> Thank you Erland,
>
> SELECT system_user, user gives me the following (1 line) result :
>
> myServerName\myAdminAccountName on the first column
>
> and
>
> dbo on the second column
>
> Is this correct or are there other accounts missing ?

I can't say whether this is correct or not - it's not up to me to decide
what accounts you should use.

But apparently then myServerName\myAdminAccountName should be added
as a user to the database.

> Actually, by adding the "Built-In\Users" account to the "security" tab
> of my database, the connection problem seems to be solved. Is there
> anything wrong with this solution from a security point of view ?

When it comes to security there is always a problem if you don't know
what you are doing. :-)

Builtin\users means that all users on the machine has access to the
database. Which may or may not be OK depending on what other people
who can log in.

[Nycolas]

Ok thanks,

the sql server manager (R2 version) is not letting me add the IUSR account
to the database, it says this account does not exist, although i do know that
it exists. That's why i had to add the Builtin\users account. So is it me who
does not know what to do or is it the software ? ;-)

Thanks a lot anyway, i will consider that this problem is solved.

btw. this newsgroups reader software (in the browser), that i got to from
the WEBSITESPARK area (oh la la !) is behaving very weard, it shows the
messages once upon a time. Is it my eyes that do not know what they do ? :-)

Cheers
Nicolas

"Erland Sommarskog" wrote:

> .
>

[Erland Sommarskog]

Nycolas (Nyc...@discussions.microsoft.com) writes:
> the sql server manager (R2 version) is not letting me add the IUSR
> account to the database, it says this account does not exist, although i
> do know that it exists. That's why i had to add the Builtin\users
> account. So is it me who does not know what to do or is it the software
> ? ;-)

I don't know which IUSR you are talking about. In your previous post
you said it was myServerName\myAdminAccountName, no IUSR there what I
can see.

> btw. this newsgroups reader software (in the browser), that i got to from
> the WEBSITESPARK area (oh la la !) is behaving very weard, it shows the
> messages once upon a time. Is it my eyes that do not know what they do ?

Well, that software is not the topic for this newsgroup.

[Mary Chipman [MSFT]]

In addition to what Erland has advised you to do, you might want to
review the Patterns & Practices security guidance for applications:
http://msdn.microsoft.com/en-us/library/ff650760.aspx. Although your
immediate problem may be solved, you'll want to make sure that you're
not leaving youself open to possible attack due to having left
something out. An Access to SQL Server migration can be full of hidden
pitfalls when it comes to security.

Mary

[Nycolas]

Thank you Mary for providing me with this link.

One of the topics that i may have found useful is the one entitled "Guides"
/ "Building Secure ASP.Net applications".
http://msdn.microsoft.com/en-us/library/cc394781.aspx

But unfortunately, while i'm writing this message, it still returns a broken
link showing a "Page not found" alert.

In a way, this has been useful, since it has taught me that the best way to
make your database content more secure is to avoid putting data in it :-)

Thanks
Nicolas

[Mary Chipman [MSFT]]

The site might have been down when you visited, I'd recommend trying
again. Security is always a tradeoff between ease of access and
security -- too much, and it's hard to use, too little, and you are
vulnerable to attack. ASP.NET in particular is designed to prevent
attacks by having the default account have very few priviledges, so
you need to understand the mechanics of how it accesses the server.
There are two layers your client application must pass in order to
access the data -- security on the web server, and security on your
SQL Server. If you are connecting as an administrator, which many
developers tend to do, then your experience will be quite different
than that of an end user who has fewer permissions. You need to
understand two security systems to grant users the access to only the
data they need without jeopardizing the server and the database by
granting too many permissions.

--Mary