IIS can't connect to SQL Server 2008

So here's the scenario...

I finally finish my Restful MVC application, rich with DAL's and everything, WOOHOO!!!! What now? Well, let's put it on a server that runs IIS, and try to make restful calls to the application to add/retrieve/update/delete data from the back end SQL Server 2008 Database that was set up.

Went into IIS, added the entire folder to the wwwroot folder, set up the virtual folders in IIS to act as applications, and we're good to go, right?

EHHHHH, WRONG!

For the life of me, I couldn't figure out why I would get empty XML nodes returned to me when I executed a restful "retrieve" call for one of my DB Objects... now, a bit of background on the app might make more sense of what I just said...

A retrieve call in my application should return an xml node with the db object serialized as xml... if there are no results, based on the id passed, it will return an empty xml.

So I was getting returned empty xml, meaning that a connection was occuring correctly, but nothing was being returned (even though I know something was in the DB)...

It worked properly when I remoted into the server and executed the call locally... This told me that it must be some sort of permission issue.

After a little clever debugging, I was able to realize that the connection to the DB was occuring fine, but when I called "ExecuteDataSet", an exception was being thrown. The exception wasn't 100% clear, but essentially, after clever googling, I realized that I didn't have the appropriate level of access for the SQL User to the table.

Now, why was it working locally, but not remotely?

BECAUSE:
- When you are accessing the service call locally, it is using your regular login, and my regular login had owner permissions on ALL tables in the DB
- When you are accessing the service call remotely, the user being used is "NT AUTHORITY\NETWORK SERVICE". This didn't have db_owner role membership for the Database I was trying to access... I know, that's a head against the keyboard banger, right?! Anyway, that was the problem... since the user didn't have db_owner membership on the DB, it wasn't able to execute queries or non-queries against it...

so here's how I solved it:

Using SQL Server Management Studio (if you wanna do this using queries, google search it, I ain't getting into that here) navigate in the "Object Explorer" down to the Security Folder. You'll see a "Logins" folder, in which, you'll find all the logins SQL Server 2008 has set up. One will be: "NT AUTHORITY\NETWORK SERVICE". Right click on that, and select "Properties". In the properties window, you will see a section for "User Mapping". Select that. Select the DB you want from the "Users mapped to this login:" section, and make sure the DB is selected (and checked). Once the DB is selected, on the bottom portion, you will see, "Database role membership for: YourDB". Under here, you must make sure that you grant the appropriate level membership. For me, it was "db_owner", which allows the user to do ANYTHING to the db that you can directly. Other options might make more sense for your application.

Bam you're done. Now stop banging your head on the keyboard, walk to the nearest bar, and start drinking.

Outputting XML in aspx file...

This one, although seemingly difficult for those of you who are unfamiliar with asp.net, is surprisingly complex (sense the sarcasm please)...

you ready?

here we go...
Step 1) in your page directive, add the following: ContentType="text/xml"

This tells the browser that the output will be in XML format

Step 2) Pour yourself a beer, and enjoy the XML that will result from now on.

BAM... you're done, and welcome...

Linq to XML - Creating XDocument with Dynamic Values

I had the need to create a service utility function that would take in an object (a predefined customer object), and output the object as XML...

I decided to use Linq to XML, mainly because it's AMAZING, and will do everything for you... Linq to SQL has its fair share of issues (even Microsoft will admit this), but the Linq to ___ suite outside of to SQL is amazing, ESPECIALLY Linq to XML... anyway, let me stop stroking bill gates' ego, and get to the issue at hand...

so, I needed to create a function "customerToXML" that would take in an object (in this case a Customer Object), and would spit out XML. I decided that the output should be in the form of an XDocument...

The issue was that in defining the XDocument, Linq to XML allows you to define the XML right there in the code!!!

I needed to however not just define the XML, but also fill it with dynamic data that would be populated depending on the Customer object passed to the funtion...

The answer I was looking for was in Microsoft's MSDN Library.

Here's what I had to do (don't ask why i'm using VB.Net... sometimes clients don't know what they're asking for, but they're still always right... right?)


Public Function objectToXML(ByVal customer as Customer) as XDocument
Dim customerAsXML as XDocument = _
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<customer id=<%= customer.customer_id %> >
<customer_name><%= customer.customer_name %></customer_name>
<customer_acct><%= customer.customer_acct %></customer_acct>
</customer>
Return customerAsXML
End Function


As you can see, all you have to do is put the delimiter to embed expressions...

I found this answer here


Anyway, I hope this helps