February 4, 2016 at 8:37 am
Would I be correct in my presumption that granting access to a web servers machine account (domain\webserver$) access to SQL server as a logon, would be a rather large security (among other issues) risk?
I ask because I've got a developer that seems to continuously have issues setting up his application / web site so that it can use the logon that was created for his app to use...
Then his app tries to connect with the machine account of the web server...
Thanks,
Jason
February 4, 2016 at 9:01 am
That depends. Adding to sysadmin would of course be out of the question. But if the account is only given access to the database for the application and in this database it's only granted read and write permissions, I don't see any apparent problems.
Then of course, your local security considerations matter. How are users authenticated in his application? Not at all? Is the actual user passed somehow to the database, for instance with SET CONTEXT_INFO? Do your organisation need/mandate auditing on that level? Do your organisation require that users of the web application to be users in the database?
Note that the answer to the last question is not alluding that "no" would be bad practice. Au contraire, in many cases you don't want users to be able to access the database through SSMS, Excel etc. If they are not users in the database, that's a good thing in that perspective.
One more caveat: if you use this model, the application will stop working when you move it to a new web server. Not a big deal, but it needs to be documented.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
February 4, 2016 at 9:16 am
So not as bad as I thought, but still a borderline idea.
Users of applications are authenticated via the application itself, so they have no direct access to SQL.
The way our system is set up, we (the web and database server providers) are essentially a hosting service for the application people. They have no access to the OSes, and minimal or less access to SQL and IIS. We do have multiple apps on one web server and multiple DBs on IIS (for different apps.)
So, I'd still say no to granting this account access to my SQL server(s.) At this point, it's up to the dev to figure out what he's doing incorrectly...
February 4, 2016 at 2:22 pm
jasona.work (2/4/2016)
So not as bad as I thought, but still a borderline idea.Users of applications are authenticated via the application itself, so they have no direct access to SQL.
The way our system is set up, we (the web and database server providers) are essentially a hosting service for the application people. They have no access to the OSes, and minimal or less access to SQL and IIS. We do have multiple apps on one web server and multiple DBs on IIS (for different apps.)
So, I'd still say no to granting this account access to my SQL server(s.) At this point, it's up to the dev to figure out what he's doing incorrectly...
I've said no in the past to such requests as well. There are plenty of white papers about how to set up IIS permissions appropriately, and frankly none of the authoritative ones would recommend using the machine login for that. Just like you ought to use separate admin logins to perform admin functions and otherwise operative with a low-permission account, so should you have different security contexts within the application and outside of it.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 4, 2016 at 2:48 pm
What are the alternatives?
Since the user do not have SQL Server access, the application cannot impersonate them.
The application could use an SQL login, but I would definitely hold that as inferior solution, as that login could log in from anywhere if the password is compromised. The machine login can only login from that machine. That is definitely a feature.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
February 4, 2016 at 8:39 pm
You can assign network logins to the specific application pools. Custom logins other than the defaults typically have little or no access on the local machine and can be granted targeted access to the functions required on SQL Server. APP level security grant the specific user targeted access only what it is you want to grant: they don't have the access anywhere else.
None of this access is granted via SQL logins - this all can (and should) be windows auth.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply