I'm new to SQL2008 and can't seem to find a step by step guide to do the following (on a server, the VS2008 dev environment works just fine - local IIS7 on Vista Business SP2 connects to the SQL2008 developer database on the local machine - no issues there).
On Windows 2008 server, however, I ran a backup of the SQL2008 database from the development machine, then restored them to the W2K8 SQL2008 server. I created logins and mapped the logins to users (of the same name). All the SQL user accounts have complete access from SQL Management Studio - in other words, i can connect to the database(s) fine, read, write, delete, insert, select, etc.
The problem is that, from my website, I can connect to SQL2008 just fine, but cannot execute, read, write or anything else from the website. The connection string obviously works since I can connect, so the problem is between IIS 7 and SQL2008.
In a nutshell, if anyone knows of a step by step guide for getting SQL2008 connectivity to IIS7 on a Win2008 Server machine (IIS7 in Vista does not seem to be the same IIS7 in Win 2008 Server or has some settings that make it work), please send me a link or instructions! I've scoured MS's knowledge base for answers, but what I've found is vague at best.
All I want to do is connect to SQL2008 with an SQL login and grant the user/login/schema FULL access to the database in question.
I can connect fine from IIS7, just the user account has no privileges even though it is set as dbo!
Here is an example of an error I get (I connect to the DB, but the IDENTICAL login that is using the 'dbo' schema doesn't have execute privileges ! the dbo schema is the database owner, right? maybe I dont understand these accounts well enough)
The EXECUTE permission was denied on the object 'GetImageData', database 'SSProducts', schema 'dbo'.
The same stored procedure 'GetImageData' works perfectly from Mgmt Studio under the same account (that is in the web.config connection string), so I'm baffled as to why this does not work from IIS.
I discovered the problem.
I have another module that uses the app.config file to connect to the database. Even though the SQL user has ownership, the module is only connecting to the database, but does not have ownership. How do I give a module ownership? I gave NT Authority/Network Service ownership and NT Authority/IUSR ownership, but that did not help. What would prevent a separate project from being able to use app.config? Is there anyway to have 1 project use the web.config from another? This makes no sense.