从Web服务连接到SQL Server：登录失败。 [英] Connecting to SQL Server from a Web Service: login failed.
问题描述
我在Windows XP SP2上使用SQL Server 2005 Developer Edition，并设置了这个
设置：
< add name =" SqlServerTrustedConn"
providerName =" System.Data.SqlClient"
connectionString =" Data Source = localhost; Initial
Catalog = GTSDB; Integrated Security = SSPI;" />
我的Windows应用程序都运行良好。
现在我已将Windows应用程序的DAL复制到Web服务中了>
项目，当我打开连接时，我得到以下异常：
无法打开数据库GTSDB登录请求。登录失败。
用户PCNAME \ ASPNET登录失败。
由于默认禁用远程连接，我使用了Surface Area
启用它们的配置工具。
然后我为ASPNET帐户创建了一个SQL登录名：
CREATE LOGIN [PCNAME \ _ASPNET]来自WINDOWS
由于它无法正常工作，我删除了ASPNET帐户的SQL登录名：
DROP LOGIN [PCNAME\ASPNET ]
我已经确定PCNAME \ WINDOWSUSER是数据库所有者：
从sys.databases中选择suser_sname（owner_sid），其中name ='' GTSDB''
我已根据数据库中存储的信息确认'sa'是数据库所有者：
从sysusers中选择suser_sname（sid），其中uid = user_id（''dbo''）
我发现PCNAME \ ASPNET登录不是映射到任何我们在
数据库中（下面的查询没有返回一行）：
（从sysusers中选择*，其中sid = suser_sid（'''PCNAME \ ASPNET''） ）
我已经完成了映射：
创建用户[PCNAME \ASPNET]（DROP USER [PCNAME\ASPNET]重置为< br $>
初始状态）
我已经验证用户有权连接数据库：
select * from sys .database_permissions where grantee_principal_id =
user_id（''user_name''）
我尝试将localhost更改为192.168.xx，收到以下内容
即使我启用混合模式也是例外：
用户''''登录失败。用户没有与受信任的SQL
服务器连接相关联。
我该怎么办？
谢谢，
Luigi。
您是否考虑过在用户帐户下运行ASP.NET应用程序
有权访问到数据库？任何访问ASP.NET
帐户的权限通常都是一个坏主意。专门为您的
应用程序创建一个帐户，然后将该应用程序配置为在该帐户下运行。然后给出
来计算数据库中相应的持久性。
-
- Nicholas Paldino [.NET / C＃MVP]
- mv*@spam.guard.caspershouse.com
蓝色 < bluewrote in message news：或************** @ TK2MSFTNGP03.phx.gbl ...
我''使用SQL Server 2005 Developer Edition在Windows XP SP2上使用此
设置：
< add name =" SqlServerTrustedConn"
providerName =" System.Data.SqlClient"
connectionString =" Data Source = localhost; Initial
Catalog = GTSDB; Integrated Security = SSPI;" />
我的Windows应用程序都运行良好。
现在我已将Windows应用程序的DAL复制到Web中服务
项目，当我打开连接时，我得到以下例外：
无法打开数据库GTSDB登录请求。登录失败。
用户PCNAME \ ASPNET登录失败。
由于默认禁用远程连接我已经使用了Surface
启用它们的区域配置工具。
然后我为ASPNET帐户创建了一个SQL登录：
从WINDOWS创建登录[PCNAME \ASPNET]
由于它无法正常工作，我删除了ASPNET帐户的SQL登录名：
DROP LOGIN [ PCNAME \ ASPNET]
我已经确定PCNAME \ WINDOWSUSER是数据库所有者：
从sys.databases中选择suser_sname（owner_sid） name ='''GTSDB''
我已根据存储在数据库中的信息
验证了'sa''是数据库所有者本身：
从sysusers中选择suser_sname（sid），其中uid = user_id（''dbo''）
我发现PCNAME为ASPNET登录未映射到任何u在
数据库中的ser（下面的查询没有返回一行）：
（从sysusers中选择*，其中sid = suser_sid（''PCNAME \ ASPNET''） ）
我已经完成了映射：
创建用户[PCNAME \ASPNET]（DROP USER [PCNAME\ASPNET]重置为< br $>
初始状态）
我已经验证用户有权连接数据库：
select * from sys .database_permissions where grantee_principal_id =
user_id（''user_name''）
我尝试将localhost更改为192.168.xx，收到以下
即使启用混合模式，也会出现
异常：
用户''''登录失败。用户没有与受信任的SQL
服务器连接相关联。
我该怎么办？
谢谢，
Luigi。
BLUE（蓝色）写道：
我在Windows XP SP2上使用SQL Server 2005 Developer Edition并使用此
设置：
< add name =" SqlServerTrustedConn"
providerName =" System.Data.SqlClient"
connectionString =" Data Source = localhost; Initial
Catalog = GTSDB; Integrated Security = SSPI; " />
我的Windows应用程序都运行良好。
现在我已将Windows应用程序的DAL复制到Web中服务
项目，当我打开连接时，我得到以下异常：
并且Web服务器与同一台机器运行SQL Server？好吧，
显然网络服务器上有一个SQL Server实例，因为你
收到如下错误信息：
无法打开数据库GTSDB登录请求。登录失败。
用户PCNAME \ ASPNET登录失败。
但这是正确的实例吗？那就是带有GTSDB数据库的那个？
我问这个，因为你后来说：
我''尝试将localhost更改为192.168.xx，即使我启用了混合模式，也会收到以下
异常：
用户''''登录失败。用户未与受信任的SQL
服务器连接相关联。
这表示您现在指定了不同的服务器？或者
192.168.xx您机器的地址？
-
Erland Sommarskog，SQL Server MVP， es **** @ sommarskog.se
SQL Server 2005联机丛书
http://www.microsoft。 com / technet / pro ... ads / books.mspx
SQL Server 2000联机丛书
http://www.microsoft.com/sql/prodinf...ons/books.mspx
>
我该怎么办？
< add name =" SqlServerTrustedConn"
providerName =" System.Data.SqlClient"
connectionString =" Data Source = localhost; Initial
Catalog = GTSDB; Integrated Security = SSPI;" />
您需要取出集成安全性并使用通用用户ID和
psw。
需要为SQL Server数据库创建user-id和psw，并具有访问数据库的
相应的访问权限。
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.
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.
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
>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.
这篇关于从Web服务连接到SQL Server：登录失败。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
