syscomments

# re: Security Request

You could create an application role for the application to use. This however still doesn't get you you're goal of allowing it on an IP basis, however it is now controlled on a per application basis. For more information check out the BOL entry for SQL Server of "Establishing Application Security and Application Roles" @ http://msdn.microsoft.com/library/en-us/adminsql/ad_security_89ir.asp

# re: Security Request

I'd thought about that.

App Roles are fairly complicated to set up, and not nearly as easy to deal with as a simple hosts.allow or hosts.deny-type system. Developers would have to come up with some way to hide the role password, at which point it wouldn't buy me anything to have the role password because the developers would be able to use the role, and they're the ones I need per-user auditing for. Bear in mind, I don't care if the developers are in the database, I just want them in with their own logins so I can track activity.

Basically, application roles wouldn't work to prevent mis-use of the web server account, so it's not what I need to solve the problem.

Plus, think about all of the other security problems this would patch. As long as the server has some kind of IP-Spoofing protection, then you can kiss goodbye all of the port 1433-related vunerabilities, like the slammer worm exploited. If you can only connect from approved addresses, it's like having a very simple mini-firewall, without having to figure out the port-blocking in Windows.

rs.

# re: Security Request

I like your hosts.allow/deny idea. Don't think I've heard anything new in the login/security area for Yukon. But hey, you could always come to PASS and "Ask the Experts". *grin*

Or, here's an idea we considered once. Never pursued it completely, so there may be some loose ends, but what about using the servername\iweb_XXXX user as your web login to SQL Server? That may not work if you have a web server farm. But I think it would handle your "limit to this machine" problem.