my webservice is not able to login to a local database

Question

hi there i have made a web service, it has two web methods, one method uses an xml file to get the data, and other uses a database on my machine, first method is working fine but when i call the second method using an object of the webservice from my aspx page, i am getting the below mentioned exception, i am a beginner on this and have wasted a lot of time sorting the problem, so now want the help of your experience..

System.Data.SqlClient.SqlException: Login failed for user 'D-11083\ASPNET'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at BLL.BLLclass.get_zodiac_dll(DateTime yourdate) in c:\WebSite7\App_Code\BLL.cs:line 58
at BLL.BLLclass.get_zodiac_bll(DateTime yourdate) in c:\WebSite7\App_Code\BLL.cs:line 73
at Service.get_zodiac(DateTime date) in c:\WebSite7\App_Code\Service.cs:line 29

note: d-11083 is my machine name, and the lines where error is mentioned, they are returning the output..

Answer 1

In your connection string, you need to specify and username and password of a user that has the appropriate access to the database. For testing, you could temporarily enable the SA account and use SQL Server authentication to make this simpler for you. Right now, you are trying to login with the context of the ASPNET user which probably does not (and should not) have access to your database.

Answer 2

The ASPnet process is trying to login with user id 'D-11083\ASPNET' is this user allowed into the db?

Answer 3

So the webservice is attempting to log into the database using windows authentication with the default webserver account.

Does sql server allow windows authentication? Is that particular account authorized?

You probably dont want the ASPNET user to access the database as itself, but you can get it to impersonate another user for the purposes of database access by putting something like this in the web.config

<identity impersonate="true" userName="domain\username" password="passwordgoeshere"/>

Answer 4

With Windows Authentication your user is authenticating to the web service using windows authentication. This doesn't mean that the web service can use the user's credentials to authenticate to the SQL server. What you are looking for is impersonation.

Is your SQL server on the same computer as the web service? In this case you should be okay with normal impersonation. MSDN has a nice article about impersonation in ASP.Net. Basically all you need to do is add

<identity impersonate="true" />

to the Web.config of the web service. This will run the web service code as the user and so allows SQL server to authenticate using the user's context.

However if your SQL server is located on another computer, you need to use impersonation on Delegation level. This is to say that the ASP.Net must have enough permissions to allow impersonating the user on another computer. These settings can be configured in the AD by enabling the ASP.Net user/computer to delegate and to make sure that the user you are trying to impersonate is not marked as sensitive which probhits delegation.

At least so in theory. I have spent few days trying to make the delegation work in WCF without success.

Once you have impersonation enabled, you can use WindowsIdentity to see what level impersonation is being used.

System.Security.Principal.WindowsIdentity.GetCurrent().ImpersonationLevel

If this returns Impersonation you can authenticate to processes on the local computer using the user's identity. If this returns Delegation you are able to authenticate on other computers using the user's identity.