"Gary" <ro************@yahoo.com.au> wrote in message
news:17**************************@posting.google.c om...
Hi, guys!
Some of my applications are sharing same SQL login/password to connect
to a database called "MyDB" on server "MyServer" . The password is
encrypted and stored in registry or some configuration file the
applications use. The applications use certain arithmetic to decrypt
the password and then connect to MyDB.
The problem is a few developers know the arithmetic. So virtually
there is no security here.
I am wondering whether I can do anything on the MyServer/MyDB to limit
the access to the database so that only connection from certain
servers are allowed. Say I only want connection with this known
credential to be established if it is from server "Mybox". No
connections from any other servers will be allowed. So even the
developers know the login/password, they won't be able to do anything
if they do have the access to server "MyBox".
(I know some of you would ask why I don't use application roles. Let's
say it's due to "historical" reasons and it's not totally up to me to
change the way the developers use database.)
Any idea? Triggers in Master? Not a good idea, isn't it?
Thanks in advance,
Gary
Well, if you want real security then at a minimum you need to stop using
shared logins. Create a login for each user and developer, or use Windows
security which is generally preferred, create roles with limited permissions
etc. This is the standard best practice for MSSQL security:
http://www.microsoft.com/technet/pro.../sp3sec00.mspx
It sounds as if you're trying to hack something in, rather than step back
and fix the fundamental problems. If others in the organization claim it's
too much work, too restrictive etc. then make sure that the business users
and your boss know there is no security in place to prevent abuse of the
system - if they don't care, then fine, but make sure you get that in
writing... Assuming they do care, then you should be able to get the
authority to fix the situation.
To answer your original question, triggers on system tables aren't
supported, and the sysprocesses table which shows current connections isn't
a physical table anyway, it's a fake one which is created when you query it.
You could create a scheduled job which runs every few seconds, and KILLs any
SPIDs which are not from authorized hosts (using the HOST_NAME() function),
but that's really a nasty kludge, not a proper solution.
Simon
