limit the access

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"Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
news:171bd226.0410062159.441a405b@.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/pr...n/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|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<41652f1c$1_1@.news.bluewin.ch>...
> "Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
> news:171bd226.0410062159.441a405b@.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/pr...n/sp3sec00.mspx
> It sounds as if you're trying to hack something in, rather than step back
> and fix the fundamental problems.

Simon,

Thanks for this.

I think I am pretty familiar with the standard practice and you are
right that I don't have any chance (at least at this moment) to
rollback what the developers are doing so I can standardize the way of
database use.

If others in the organization claim it's
> too much work,

Yes they certainly do!

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...

Good idea. I will try.

Assuming they do care, then you should be able to get the
> authority to fix the situation.

Again, they also DO care. That is why some "temporary solution" is
required
-:)... The good thing is I have been doing coding for more than 12
years so I know most of the tricks they have. I also have been using
MSSQL for about 7 years (not 24*7 DBA though). So I am now in a
position that I feel I know engouh to tell how bad they (including me)
are doing in regard to security while yet I don't know enough to come
up with this temporary solution for them.

> 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.

It is a pity we can't use supported database level triggers here. For
our Oracle databases, I have actually done this easily. Well, I will
continue to try before I get the mandate to enforce the proper way of
accessing database via applications in this company.

Thanks again.
> Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41652f1c$1_1@.news.bluewin.ch...
> "Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
> news:171bd226.0410062159.441a405b@.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.

<snip
Aside from everything else already mentioned, if you really want to limit
access to particular MACHINES/SERVERS, you may consider placing the SQL
Server behind a hardware- or software-based firewall and only opening the
appropriate ports to the appropriate addresses.