Enterprise manager security?

Our product has the middleware conduct transactions with the SQL server usin
g a special account. The developers know the account and password in order t
o code it in their apps. The problem is that they are not allowed access to
the platform once an app g
oes into production (we are in the banking industry). However, they use Ente
rprise Manager and as long as they know the account name and password, they
can still get to any of the databases.
Anyone know how to stop a connection to a SQL DB (by user) with Enterprise M
anager?It is bad practice to code the account and password in application. This
should be stored in an ini file or the registry (and encrypted of course) so
it can be changed easily. In that case you can set up a new account with the
same permissions and delete the old account, and make your application use
it by changing the username and password in the registry or ini file. If you
would like to store the username and paswword encoded in the application for
some reason, at least store it in a separate class, so that you can build a
secure release version by only including a different file for that class.
Blocking them from Enterprise Manager doesn't help much, how long do you
think it takes a not even very talented developer to knock up a basic app
that allows you to view tables in any database? Or they can use Query
Analyzer or osql.
Which bank uses this btw, I hope it's not mine ;-)
Jacco Schalkwijk
SQL Server MVP
"rick m" <anonymous@.discussions.microsoft.com> wrote in message
news:03A80E82-BAF0-4055-AEA7-9C33AEB30DB8@.microsoft.com...
> Our product has the middleware conduct transactions with the SQL server
using a special account. The developers know the account and password in
order to code it in their apps. The problem is that they are not allowed
access to the platform once an app goes into production (we are in the
banking industry). However, they use Enterprise Manager and as long as they
know the account name and password, they can still get to any of the
databases.
> Anyone know how to stop a connection to a SQL DB (by user) with Enterprise
Manager?|||Rick,
You have stumbled onto one of the most overlooked internal security problem
with SQL Server. Appliction developers often know logons and passwords and a
re able to connect to production databases outside of the application using
Enterprise Manager, Query A
nalyzer, Access, a custom app. etc. If you cannot get the app developers to
use Windows authentication, you really need to store the login and password
in a safe ini file or registry setting and make sure the password changes as
the application goes into
production.
Your not the only one with the same question, many other have written the sa
me question and apparently the problem with application developers being abl
e to review production data is starting to become a problem.
Sorry, there is not a good answer to your question, it is a application deve
lopment process change and not just a setting that will eventually keep appl
ication developers out of your production database.
About the only thing you can do in the short-term is have management send ou
t a warning that no app developer should be in production and then audit ent
ry into your production db and have the offending developers "taken" care of
.
Randy Dyess
www.Database-Security.Info|||"rick m" <anonymous@.discussions.microsoft.com> wrote in message
news:03A80E82-BAF0-4055-AEA7-9C33AEB30DB8@.microsoft.com...
> Our product has the middleware conduct transactions with the SQL server
using a special account. The developers know the account and password in
order to code it in their apps. The problem is that they are not allowed
access to the platform once an app goes into production (we are in the
banking industry). However, they use Enterprise Manager and as long as they
know the account name and password, they can still get to any of the
databases.
> Anyone know how to stop a connection to a SQL DB (by user) with Enterprise
Manager?
No direct way, you'd be better off blocking (or simply removing any security
permissions) for user connections to the server running SQL Server. I agree
with the other posts, the main thing to emphasize -- use Windows
Authentication! That eliminates the need to hard code user names/passwords.
Security can be implemented and secured based on NT groups.
Steve