<<resolved>>Web Services problem with web.config - SQLconnection

[SmagO]

Hi,
I have been trying to follow the football247 demo but am struggling to get the web service to connect to the database.

Its a sql2000 database that I setup using windowsauthentication - so there is no username and password.

The db uses stored procedures to read and update data.

I have a class that is supposed to connect to the database.

Public Function GetTeams() As TeamData
 
        Dim connection As SqlConnection = New SqlConnection(ConnectionString)
        Try
 
            Dim command As SqlCommand = New SqlCommand("GetTeams", connection)
            command.CommandType = CommandType.StoredProcedure
            Dim adapter As SqlDataAdapter = New SqlDataAdapter(command)
            adapter.TableMappings().Add("Table", "Teams")
            Dim target As TeamData = New TeamData
            adapter.Fill(target)
            Return target
 
        Finally
            If connection.State = ConnectionState.Open Then
                connection.Close()
 
 
            End If
 
        End Try
 
    End Function

ConnectionString is a property that reads from the web.config file:

Public Class TeamDataAccessor
    Public ReadOnly Property ConnectionString() As String
        Get
            Return ConfigurationSettings.AppSettings("ConnectionString")
 
        End Get
    End Property

The web.config file is:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>
<appSettings>
<!--
Use Integrated security
-->
<add
key="ConnectionString"
value="Integrated Security=SSPI;database=DataAccessSample.Net;server=localhost" />

</appSettings>
 <identity impersonate="true"
  userName=""  How should I change this so that it will connect to my SQL2000 that is using windows authentication for the login - i.e. no username and no password?
  password=""
  /> 
  
<system.web>
  
    
    <compilation defaultLanguage="vb" debug="true" />

    <customErrors mode="RemoteOnly" />

    <!--  AUTHENTICATION 
          This section sets the authentication policies of the application. Possible modes are "Windows", 
          "Forms", "Passport" and "None"

          "None" No authentication is performed. 
          "Windows" IIS performs authentication (Basic, Digest, or Integrated Windows) according to 
           its settings for the application. Anonymous access must be disabled in IIS. 
          "Forms" You provide a custom form (Web page) for users to enter their credentials, and then 
           you authenticate them in your application. A user credential token is stored in a cookie.
          "Passport" Authentication is performed via a centralized authentication service provided
           by Microsoft that offers a single logon and core profile services for member sites.
    -->
    <authentication mode="Windows" /> 


       <authorization>
        <allow users="*" /> <!-- Allow all users -->

            <!--  <allow     users="[comma separated list of users]"
                             roles="[comma separated list of roles]"/>
                  <deny      users="[comma separated list of users]"
                             roles="[comma separated list of roles]"/>
            -->
    </authorization>

    <trace enabled="false" requestLimit="10" pageOutput="false" traceMode="SortByTime" localOnly="true" />


    <sessionState 
            mode="InProc"
            stateConnectionString="tcpip=127.0.0.1:42424"
	    sqlConnectionString="data source=127.0.0.1;Trusted_Connection=yes"
            cookieless="false" 
            timeout="20" 
    />

    <globalization requestEncoding="utf-8" responseEncoding="utf-8" />
   
  </system.web>

</configuration>

The error I get when I test the web service is:

Configuration Error
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Unrecognized configuration section 'identity'

Source Error:


Line 11:
Line 12: </appSettings>
Line 13: <identity impersonate="true"
Line 14: userName=""
Line 15: password=""

If I remove the 'identity' tag then the web service allows me to select which method to invoke. I select the getteams method (the one shown above) and I get the following error:

System.Data.SqlClient.SqlException: Login failed for user 'IT-WGP2B9HIQFBJ\ASPNET'.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at DataAccessSample.Data.DataAccess.TeamDataAccessor.GetTeams() in C:\Documents and Settings\Steve\My Documents\Visual Studio Projects\DataAccessSample.net\DataAccessSample.Data.DataAccess\TeamDataAccessor.vb:line 21
at DataAccessSample.Business.Facades.TestFacade.getTeams() in C:\Documents and Settings\Steve\My Documents\Visual Studio Projects\DataAccessSample.net\DataAccessSample.Business.Facades\TestFacade.asmx.vb:line 45

Thanks in advance

[The_Duck]

This sounds like a configuration issue.

You will need to give the ASP account access to your database table

[SmagO]

thanks, but how do i do that?

[The_Duck]

To add the account to your database look under the users section of your database table

[SmagO]

Ok...i just needed to ppace the identity tag beneath the authorization tag. placing it above tha authorization tag caused an "unknown tag" error.