ASP.NET MVC 4/Web API - How to configure SQL Server to allow access to API calls from a URL in browser?

Question

I have a server with a SQL Server 2012 Express database. I am authenticated to connect to it and am using it as the backend for an ASP.NET MVC 4 application.

With this application I'm also developing an API. Calling the API programmatically using jQuery within my own application works fine. Querying the database to get the information works as well.

However, when I run the application from VS2010 and try accessing the API by using a URL that would access the database, I get an HTTP 500 Error.

Just to make sure this issue isn't on my application's end, I then modified the API resource so it gets a local array (instead of information from the database) and when I hit the same URL, it returned the information in XML form on the localhost page.

The problem seems to be that the URL call to the API attempts to connect to the database not as the authenticated user (me), but from the browser.

How can I configure SQL Server to allow a URL call to the API in the browser to access the database and return the data in JSON/XML form? (eg. like you can do with the Twitter REST API)

UPDATE: Debugging output on HTTP Request:

Request URL:http://localhost:5555/api/Performance/ShowMachines

Request Method:GET

Status Code:500 Internal Server Error

Request Headersview source

Accept:text/html,application/xhtml+xml,application/xml;q=0.9,/;q=0.8

Accept-Charset:ISO-8859-1,utf-8;q=0.7,*;q=0.3

Accept-Encoding:gzip,deflate,sdch

Accept-Language:en-US,en;q=0.8

Connection:keep-alive

Host:localhost:5555

User-Agent:Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/536.11 (KHTML, like Gecko) Chrome/20.0.1132.47 Safari/536.11

Response Headersview source

Cache-Control:private

Connection:Close

Content-Length:0

Date:Thu, 02 Aug 2012 16:40:11 GMT

Server:ASP.NET Development Server/10.0.0.0

X-AspNet-Version:4.0.30319

I used the "Connect to Database" tool on the Server Explorer to connect to it, and then dragged the tables into a LINQ to SQL class, so there really isn't a connection string in the sense of traditional SQL. If you don't mind me asking, though, how is this related to calling the API which in turn accesses the database? For example, if you enter this URL in the browser api.twitter.com/1/users/… You will get information on the Twitter users twitterapi and twitter in XML format in the browser, info that is obviously pulled from their databases. — praetor
The Linq to SQL designer should have set the connection string in your web.config. Getting a 500 error isn't really helpful though. What's the exception being thrown? — rossisdead
There is no exception thrown. I just enter the URL in the browser, for example, localhost:5555/api/Performance/ShowMachines Do note that this works when I set the data it accesses to be an array in the application, rather than from the database, so it isn't a problem with routing. and I get the 500 Error. The connection string is: <add name="PerfMonDataConnectionString" connectionString="Data Source=cloud\sqlperf,1433;Initial Catalog=PerfMonData;Integrated Security=True" providerName="System.Data.SqlClient" /> — praetor

Daniel Powell Daniel Powell · Accepted Answer · 2012-08-02T01:10:58

change the connection string to

<add name="PerfMonDataConnectionString" connectionString="Data Source=cloud\sqlperf,1433;Initial Catalog=PerfMonData;User=myusername;Password=mypassword providerName="System.Data.SqlClient" />

and ensure that whatever the user account is has permission to the database.

This may or not be the best way depending on your security requirements

Also the user/password keys are off the top of my head it may be UserId, www.connectionstrings.com is your friend

ASP.NET MVC 4/Web API - How to configure SQL Server to allow access to API calls from a URL in browser?

2 Answers