Connecting to SQL Server from a Web Service: login failed.

[BLUE]

I'm using SQL Server 2005 Developer Edition on Windows XP SP2 with this
setting:

<add name="SqlServerTrustedConn"
providerName="System.Data.SqlClient"
connectionString="Data Source=localhost;Initial
Catalog=GTSDB;Integrated Security=SSPI;" />


All worked great with my windows application.

Now I've copied the DAL of the windows application into a Web Service
project and when I open a connection I get the following exception:

Cannot open database "GTSDB" requested by the login. The login failed.
Login failed for user 'PCNAME\ASPNET'.


Since remote connections are disabled by default I've used the Surface Area
Configuration tool to enable them.

Then I've created a SQL login for the ASPNET account:
CREATE LOGIN [PCNAME\ASPNET] FROM WINDOWS

Since it didn't work I deleted the SQL login for the ASPNET account:
DROP LOGIN [PCNAME\ASPNET]

I've determined that PCNAME\WINDOWSUSER is the database owner:
select suser_sname(owner_sid) from sys.databases where name = 'GTSDB'

I've verified that 'sa' is the database owner according to the information
stored in the database itself:
select suser_sname(sid) from sysusers where uid = user_id('dbo')

I've found that the PCNAME\ASPNET login is not mapped to any user in the
database (the below query did not return a row):
(select * from sysusers where sid = suser_sid('PCNAME\ASPNET'))

I've done the mapping:
CREATE USER [PCNAME\ASPNET] (DROP USER [PCNAME\ASPNET] to reset to
initial state)

I've verified that the user had permission to connect to the database:
select * from sys.database_permissions where grantee_principal_id =
user_id('user_name')


I've tried changing localhost to 192.168.x.x receiving the following
exception even if I enable mixed mode:
Login failed for user ''. The user is not associated with a trusted SQL
Server connection.


What can I do???


Thanks,
Luigi.

 

[Nicholas Paldino [.NET/C# MVP]]

Have you considered running the ASP.NET application under a user account
which has access to the database? Giving anything access to the ASP.NET
account is generally a bad idea. Create an account specifically for your
application and then configure the app to run under that account. Then give
that account the appropriate persmissions in the database.

 

[Erland Sommarskog]

I'm using SQL Server 2005 Developer Edition on Windows XP SP2 with this
setting:

<add name="SqlServerTrustedConn"
providerName="System.Data.SqlClient"
connectionString="Data Source=localhost;Initial
Catalog=GTSDB;Integrated Security=SSPI;" />


All worked great with my windows application.

Now I've copied the DAL of the windows application into a Web Service
project and when I open a connection I get the following exception:

And does the web server run on the same machine as the SQL Server? Well,
apparently there is an SQL Server instance on the web server, since you
get an error message like:

Cannot open database "GTSDB" requested by the login. The login failed.
Login failed for user 'PCNAME\ASPNET'.

But is it the right instance? That is, the one with the GTSDB database?

I ask this, because you later say:

I've tried changing localhost to 192.168.x.x receiving the following
exception even if I enable mixed mode:
Login failed for user ''. The user is not associated with a trusted SQL
Server connection.

That indicates that you now specify a different server? Or is
192.168.x.x the address of your machine?

--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Mr. Arnold]

What can I do???

<add name="SqlServerTrustedConn"
providerName="System.Data.SqlClient"
connectionString="Data Source=localhost;Initial
Catalog=GTSDB;Integrated Security=SSPI;" />

You need to take out the Integrated Security and use a generic user-id and
psw.

The user-id and psw needs to be created for the SQL Server database with the
appropriate access permissions to access the database.

 

[BLUE]

Sorry, I've forgot saying that localhost, 127.0.0.1, 192.168.x.x all refers
to the same machine, that is my only one pc on wich I have XP SP2, IIS and
SQL Server 2005 Developer edition.

I'm not an expert administrator so I simply installed IIS and SQL Server
without creating multiple instances or somethin strange.

I only know that with my windows app and the same connection string all
works and I think this means the instance is only one and with the GTSDB
inside.

Have you considered running the ASP.NET application under a user account
which has access to the database?
Create an account specifically for your application and then configure the
app to run under that account.
Then give that account the appropriate persmissions in the database.

Sorry for my "newbieness" but I do not now how to do the things you have
suggested me :-(


Thanks,
Luigi.

 

[Erland Sommarskog]

You need to take out the Integrated Security and use a generic user-id and
psw.

The user-id and psw needs to be created for the SQL Server database with
the appropriate access permissions to access the database.

Since "BLUE" did not seem to know this, here are the steps:

First make sure SQL Server runs in mixed mode. (You seemed to know how to do
that).

Then:

CREATE LOGIN mygenericuser WITH PASSWORD='VeRy Str8Ng P@ßwrd'

and in the target database:

CREATE USER mygenericuser

In the connection string, replace "Integrated Securuty=SSPI" with
"User ID=MyGenericUser;Password={VeRy Str8Ng P@ßwrd}".

Now I have a question for the ASP .Net folks: why is the build in
ASPNET login to good to use?


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 

[Mr. Arnold]

Now I have a question for the ASP .Net folks: why is the build in
ASPNET login to good to use?

That's a good question. I'll be interested in seeing an answer.

 

[BLUE]

You have saved me!


Thank you very much,
Luigi.

 

[Hans Kesting]

Now I have a question for the ASP .Net folks: why is the build in

ASPNET login to good to use?

I don't think it's "to good" to use, but generally the database server
is different from the webserver. The ASPNET account (or NETWORK SERVICES)
is a local account (local to the webserver that is), and is not know
on the db-server. So either you use a domain account or integrated security.

Hans Kesting

 

[BLUE]

It was with integrated security that didn't work for me.
I'm curious to know a working integrated security connection string or way
to use integrated security from ASP.NET web service.


Bye,
Luigi.

 

[Erland Sommarskog]

I don't think it's "to good" to use, but generally the database server
is different from the webserver. The ASPNET account (or NETWORK
SERVICES) is a local account (local to the webserver that is), and is
not know on the db-server. So either you use a domain account or
integrated security.

Sorry, I meant to say "...login not good to use".

Thanks for the information about ASPNET being a local account, and thus
will not work when the database server is on a different box. That does
not seem to be the case for BLUE - but that might only be as long as he
is developing. The day he deploys it, they may be on different boxes,
and then integrated security is not going to work then.


--
Erland Sommarskog, SQL Server MVP, (e-mail address removed)

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx