473,326 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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.
Jun 10 '07 #1
10 5238
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.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"BLUE" <bluewrote in message news:Or**************@TK2MSFTNGP03.phx.gbl...
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.
Jun 10 '07 #2
BLUE (blue) writes:
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 10 '07 #3
>
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.

Jun 10 '07 #4
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.
Jun 10 '07 #5
Mr. Arnold (MR. Ar****@Arnold.com) writes:
>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.
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 10 '07 #6
>
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.
Jun 10 '07 #7
You have saved me!
Thank you very much,
Luigi.
Jun 11 '07 #8
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
Jun 11 '07 #9
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.
Jun 11 '07 #10
Hans Kesting (ne***********@spamgourmet.com) writes:
>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.
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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 11 '07 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Irishmaninusa | last post by:
I have a client who is trying to connect to a sql server database using asp.net and this is the error message that they get, any ideas on what is causing the error message and what the correction...
14
by: John Spiegel | last post by:
Hi all, I'm missing something, probably stupid, on connecting to a SQL Server database from an aspx file. I haven't really done much w/ SQL Server and suspect that it's a problem on that side. ...
4
by: Aaron Bellante | last post by:
I have been creating some pages that pull info from an SQL database located on my computer. I have an identical DB located on the production machine. The page runs fine on my PC (XP Pro), but...
3
by: GTDriver | last post by:
I'm trying to connect my application with a web service located on my own web server(localhost). I guess when the solution/proect is built it makes a file called 'Web...
10
by: MVChauhan | last post by:
Hi We are planning to move over to SQL Server 2005 in near future. At the moment Website is on a seperate server then the Database. OS for both the server is Window 2003 and currently our data...
1
by: Neo | last post by:
Hello All, I am trying to connect SQL Server 2005 from ASP.net. I am getting error Asp.Net login failed for user ''. I have seen Asp.Net "login failed for user 'NT Authority/Network...
3
by: Lee T. Hawkins | last post by:
I am having a number of problems over the last two full days trying to get an ASP.NET 2.0 application to connect to a SQL Server 2005 database... First off, I built this application w/ Visual...
2
by: ramaswamynanda | last post by:
Hello All, I have an application using ASP.NET. The database is SQLServer Express 2005. There is a table called ApartmentInfo in the database. All i am trying to do, is to "select * from...
3
by: =?Utf-8?B?QklKVQ==?= | last post by:
Hai, I am a new in ASP.NET. I tried to connect ASP.net(VB) with SQL server 2000. I got the connection failed message as Login Failed for user 'NT AUTHORITY\NETWORK SERVICE' " ie. System....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.