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.
