CODEWITHSUNDEEP
sql-serverwcfiis
Stas
Stas

Reputation: 132

WCF IIS SQL problem

I have local IIS server, local SQL server and WCF test service with 3 interface methods:

[OperationContract]
string GetData(int value);

[OperationContract]
CompositeType GetDataUsingDataContract(CompositeType composite);

[OperationContract]
string getFirstName();

The first two is the VS template, and I've added the getFirstName() method

{
   //very basic 
   string connectionString = @"Data Source=.\SqlExpress;Initial Catalog=ProjectDB;Integrated Security=True";

   SqlConnection con = new SqlConnection(connectionString);
   con.Open();

   SqlCommand command = new SqlCommand("select * from messages;", con);

   DataTable table = new DataTable();

   SqlDataAdapter adapter = new SqlDataAdapter(command);
   adapter.Fill(table);

   con.close();

   return table.Rows[0][3].ToString();
}

When I'm testing the invocation on VS's WCF Test Client, all the methods running as they should.

When the service is published to local IIS with VS wizard (publish -> Local IIS -> MyTestWeb Site) I get error when trying to invoke getFirstName() (the authentication method of SQL is windows authentication ).

Other methods : 

CompositeType GetDataUsingDataContract(CompositeType composite);

and 

string GetData(int value);

work perfect on both on VS development server and local IIS server.

Thanks .

Upvotes: 1

Views: 990

Answers (4)

Nitin Sontakke
Nitin Sontakke

Reputation: 11

Further on retrieving a single value from back-end, most appropriate method to use would probably be "ExecuteScalar", which is specifically designed for the task as name suggests.

Upvotes: 1

marc_s
marc_s

Reputation: 754388

Most likely, your error occurs because you're connecting to SQL Server Express using integrated security, and in IIS, this means the identity which is running IIS, and that account will most likely not be permissioned on your SQL Server instance.

Another recommendation: you should improve your ADO.NET code!

  • use using(.....) { .... } blocks for protecting your disposable entities (especially important in a WCF environment!)

  • don't create a SqlDataAdapter and a DataTable just to read a single column value......

My recommendation would be something like this:

public string GetFirstName()
{
   string firstName = string.Empty;

   string connectionString = @"Data Source=.\SqlExpress;Initial Catalog=ProjectDB;Integrated Security=True";

   using(SqlConnection con = new SqlConnection(connectionString))
   using(SqlCommand command = new SqlCommand("SELECT FirstName FROM dbo.Messages", con))
   {
       con.Open();

       using(SqlDataReader rdr = command.ExecuteReader())
       {
          if(rdr.Read())
          {
             firstName = rdr.GetString(0); // read column no. 0
          }

          rdr.Close();
       }

       con.close();
   }

   return firstName;
}

This is not solving your current problem, but it might help you avoid future (and hard to find/track) ones!

Upvotes: 0

BrandonZeider
BrandonZeider

Reputation: 8152

Yeah, it's probably permissions. Make sure that the user your site runs as has permissions to your SQL Server database - for example, NT Authority\SYSTEM (this will depend on your version of IIS).

Upvotes: 0

Shiraz Bhaiji
Shiraz Bhaiji

Reputation: 65381

Have a look at my answer to this question: WCF service works in cassini, but not in IIS

It looks like you have the same problem.

Upvotes: 0

Related Questions