Other ways to stop this from occuring?

SQL2K now, but will be going to 2005 shortly.
Howdy all. All of our apps (both inTRAnet and inTERNET) use SQL
authentication to connect to the DB's. I am well aware of the reasoning to
use WINNT authentication instead of SQL authentication, but this is currently
what we do. There are some folks on a mission to get this to change as they
want "single sign on" for their apps, but I am quite reluctant for the
following reason:
We have thousands of employees here. Currently, none of them can connect to
my OLTP server from outside the app and run their own ad-hoc queries/ DML/
etc. because their Windows account doesnâ't have permissions to do so and they
donâ't have the password to the SQL account that the app uses. I ever need to
worry the end user that decides to use MS Access to query huge amounts of
data to his remote location and causes blocking without realizing it. Nor do
I ever need to worry about the developer who likes to run his tests in Prod,
pegging the CPU, because his test environment doesnâ't contain all the data he
needs. These are scenarios Iâ've run into in other organizations. Like it or
not, SQL Authentication does provide these benefits. I do have a reporting
server set up for stuff like this currently, but I donâ't care about these
things taking place on that box.
So the options that I see:
1. Only allow apps to use stored procedures (no dynamic code). This option
just aint gonna happen. Iâ've got over 100 DBâ's already, and I think only 1
adheres to this best practice.
2. Have all apps that want to start using WINNT auth use Application Roles.
This way only the app can connect to the DB, not individual logins. I have no
issue with this, but suspect all the developers that need to re-write their
apps would.
3. Use firewalls to only allow DB connections directly from the app and web
servers. An exception box would probably need to be set up for administrators
to use tools like Enterprise Manager.
These are the options that I see, and was wondering if anyone else has any
other ideas?
TIA, ChrisR.Hi Chris
You should be selective in what users can actually log on to the specific
instances and not allow "Domain Users" this right but a sub group. If a
(different) group of users are allowed to access the database then they
should have the minimum permissions needed to do that so they are forced to
use the application role. If you are using auditing make sure that the
application role is not masking this.
John
"ChrisR" wrote:
> SQL2K now, but will be going to 2005 shortly.
> Howdy all. All of our apps (both inTRAnet and inTERNET) use SQL
> authentication to connect to the DB's. I am well aware of the reasoning to
> use WINNT authentication instead of SQL authentication, but this is currently
> what we do. There are some folks on a mission to get this to change as they
> want "single sign on" for their apps, but I am quite reluctant for the
> following reason:
> We have thousands of employees here. Currently, none of them can connect to
> my OLTP server from outside the app and run their own ad-hoc queries/ DML/
> etc. because their Windows account doesnâ't have permissions to do so and they
> donâ't have the password to the SQL account that the app uses. I ever need to
> worry the end user that decides to use MS Access to query huge amounts of
> data to his remote location and causes blocking without realizing it. Nor do
> I ever need to worry about the developer who likes to run his tests in Prod,
> pegging the CPU, because his test environment doesnâ't contain all the data he
> needs. These are scenarios Iâ've run into in other organizations. Like it or
> not, SQL Authentication does provide these benefits. I do have a reporting
> server set up for stuff like this currently, but I donâ't care about these
> things taking place on that box.
> So the options that I see:
> 1. Only allow apps to use stored procedures (no dynamic code). This option
> just aint gonna happen. Iâ've got over 100 DBâ's already, and I think only 1
> adheres to this best practice.
> 2. Have all apps that want to start using WINNT auth use Application Roles.
> This way only the app can connect to the DB, not individual logins. I have no
> issue with this, but suspect all the developers that need to re-write their
> apps would.
> 3. Use firewalls to only allow DB connections directly from the app and web
> servers. An exception box would probably need to be set up for administrators
> to use tools like Enterprise Manager.
> These are the options that I see, and was wondering if anyone else has any
> other ideas?
> TIA, ChrisR.
>
>