| Groups | Blog | Home |
sql server (alternate) : limit the access
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,
[quoted text, click to view] "Gary" <rooty_hill2002@yahoo.com.au> wrote in message news:171bd226.0410062159.441a405b@posting.google.com... > 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/prodtechnol/sql/2000/maintain/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
[quoted text, click to view]
"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.com... > > 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/prodtechnol/sql/2000/maintain/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 [quoted text, click to view] > too much work, Yes they certainly do! too restrictive etc. then make sure that the business users [quoted text, click to view] > 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 [quoted text, click to view] > 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. [quoted text, click to view] > > 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.
[quoted text, click to view]
"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.com... >> 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.
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |