bd9000
asked on
how do i give an SQL login/user full access to a SQL2008 database from IIS7
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.
EDIT:
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.
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.
EDIT:
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.
Is the webserver and the SQL server on a domain?
If so, try giving the webservers computer account access to the SQL DB. The account would be DOMAIN\webservername$ (note the $, which indicated this is a computer account and not a user account)
This is essentially the network service account of the webserver.
If so, try giving the webservers computer account access to the SQL DB. The account would be DOMAIN\webservername$ (note the $, which indicated this is a computer account and not a user account)
This is essentially the network service account of the webserver.
ASKER
The webserver and sql server are on the same machine - the identical config as is on the development machine.
The database role for the sqluser in question is set to only 'db_owner' with a default schema of 'dbo'
The sqluser can connect from a web application to the database and have the correct permissions.
HOWEVER, if an Application tries to connect (which is what I discovered was the real problem) with the SAME login/account and the SAME connection string, it connects, but has no permissions to execute, read, write, etc) - [makes no sense].
Basically there is an App.Config file (for the core app tier that the Web app tier connects through) and the Web.Config file (with database connections for authentication and some other services).
There are 3 connection strings in all (2 in the APP CORE TIER, 1 in the WEB TIER)
MyApp APPCORE Tier (app.config):
<add name="AppCore.My.MySetting
<add name="AppCore.My.MySetting
MyApp MANAGER WEBSITE (web.config):
<add name="Auth" connectionString="Data Source=SRV1\SQL2008,51433;
They will eventually be on 2 different servers (hence the port number), so Integrated Security is not an option (I'll have to check and see if that fails as well).
I would blame the port numbers or using an IP connection if it didn't work at all, but it only does not work from the application tier (it does on the development machine). Could there be a setting in SQL2008 that is responsible?
The database role for the sqluser in question is set to only 'db_owner' with a default schema of 'dbo'
The sqluser can connect from a web application to the database and have the correct permissions.
HOWEVER, if an Application tries to connect (which is what I discovered was the real problem) with the SAME login/account and the SAME connection string, it connects, but has no permissions to execute, read, write, etc) - [makes no sense].
Basically there is an App.Config file (for the core app tier that the Web app tier connects through) and the Web.Config file (with database connections for authentication and some other services).
There are 3 connection strings in all (2 in the APP CORE TIER, 1 in the WEB TIER)
MyApp APPCORE Tier (app.config):
<add name="AppCore.My.MySetting
<add name="AppCore.My.MySetting
MyApp MANAGER WEBSITE (web.config):
<add name="Auth" connectionString="Data Source=SRV1\SQL2008,51433;
They will eventually be on 2 different servers (hence the port number), so Integrated Security is not an option (I'll have to check and see if that fails as well).
I would blame the port numbers or using an IP connection if it didn't work at all, but it only does not work from the application tier (it does on the development machine). Could there be a setting in SQL2008 that is responsible?
ASKER
I tried the Integrated Security = true option and deleted the port number, without any luck (on the server, works on the dev machine).
It looks like the app.config is being completely ignored (I don't think the APP CORE tier is even connecting at all - I have no way of knowing for sure without logging the error to a text file when the database call fails - logging doesn't work since errors are logged on the DB it can't connect to - catch-22)
It looks like the app.config is being completely ignored (I don't think the APP CORE tier is even connecting at all - I have no way of knowing for sure without logging the error to a text file when the database call fails - logging doesn't work since errors are logged on the DB it can't connect to - catch-22)
ASKER
The mystery get more mysterious:
I can connect from a remote machine using the sqluser and the connection strings above via SQL Server Manager and execute stored procs, edit tables, etc. so it is definitely not an sqluser problem. Maybe the app.config file is not being read at all (what I suspect is that the app.config file is never read by application - so maybe I need to re-compile the app with the new connection string in the app.config file)
Right now, it will be a very long day of trial and error :(
I can connect from a remote machine using the sqluser and the connection strings above via SQL Server Manager and execute stored procs, edit tables, etc. so it is definitely not an sqluser problem. Maybe the app.config file is not being read at all (what I suspect is that the app.config file is never read by application - so maybe I need to re-compile the app with the new connection string in the app.config file)
Right now, it will be a very long day of trial and error :(
ASKER
OK, Here is what I found out:
The APP.CONFIG file is WORTHLESS during runtime. Any changes made to App.Config (say, when copying your app to another machine) will not be read, period. (unless it is a Windows program)
This means that, you have to write your own configuration settings file (XML, registry or text) OR you have to know AHEAD OF TIME what the future connection strings will be and RE-COMPILE, OR you have to have your TIER read the web.config file of it's caller (this means that it is NO LONGER MULTI-TIERED!!!!).
I'm going with the third approach as soon as I can figure out how to reference the web.config file (though, by virtue of MS's idiocy, I can't use the APP CORE when the website portion is off-line!!! - since I have to use its web.config file)
The APP.CONFIG file is WORTHLESS during runtime. Any changes made to App.Config (say, when copying your app to another machine) will not be read, period. (unless it is a Windows program)
This means that, you have to write your own configuration settings file (XML, registry or text) OR you have to know AHEAD OF TIME what the future connection strings will be and RE-COMPILE, OR you have to have your TIER read the web.config file of it's caller (this means that it is NO LONGER MULTI-TIERED!!!!).
I'm going with the third approach as soon as I can figure out how to reference the web.config file (though, by virtue of MS's idiocy, I can't use the APP CORE when the website portion is off-line!!! - since I have to use its web.config file)
ASKER
cancel that re-compile step - it won't read the app.config file even when I recompiled with the new connection strings. - that's out.
I'm going to try building a settings file and have the AppCore read the connection string from that file - a lot of work for something this friggin simple!
I'm going to try building a settings file and have the AppCore read the connection string from that file - a lot of work for something this friggin simple!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
assign sqluser as db_owner database role and then that should work.
also you can specify the default database to this user.