February 8, 2008 at 5:51 am
Hi,
I have a third party application which logs into a SQL 2000 database using the 'sa' account. Users of this third party application can then make certain change to the database. I'm wanting to log these changes and who makes them. Is there any way I can get the username of the person running the third party app? The SQL functions such as system_user return 'sa' (being the account used to make the changes). I also know about the host_name() function which would help, but the third party app is run from the same machine for all users (users have a shortcut pointing to a server which runs the app). So this doesn't help to identify users either. Is there any way I could get this info from SQL Server?
February 13, 2008 at 9:51 am
Afraid not. SQL Server only knows the account that is used to connect to the instance. You'd need something in the application that would report the network login running the app.
Greg
February 14, 2008 at 7:53 am
We have a similiar situation where all of our users use the same SQL login to log into the db for our in-house db application. I asked one of our developers how the application determines the Windows login for each user, and I was told it uses:
System.Environment.UserName.ToUpper
Norene Malaney
February 14, 2008 at 9:58 pm
If you audit logon/logoff events on the server where the app is installed, you can get a picture of who is on the system at a particular time. If you're running a trace on the SQL Server, you can know when commands are being generated. Compare the two and you know which users could have run the command, but if there are multiple users on the system, obviously you don't have nonrepudiation (the inability to deny you were the one who did whatever you want to deny). But if the application isn't capturing that, there's nothing from the SQL Server side you can do.
K. Brian Kelley
@kbriankelley
February 15, 2008 at 2:34 am
'sa' account should be used for administrative purposes only. If a third party application uses sa account, it must be modified to either explicitly ask for user credentials, use windows authentication or use another account that can access the specified database only.
Otherwise your database system is at risk. To force the 3rd party vendor to correct the bad design, simply change the sa password. No excuses.
February 15, 2008 at 7:12 am
A (2/15/2008)
'sa' account should be used for administrative purposes only. If a third party application uses sa account, it must be modified to either explicitly ask for user credentials, use windows authentication or use another account that can access the specified database only.Otherwise your database system is at risk. To force the 3rd party vendor to correct the bad design, simply change the sa password. No excuses.
Great in theory, but not necessarily in practice. See my rant:
A Long Overdue Database Security Rant
Case #1 is a very large security company who should know better. We put in a feature request for them to fix this. But the wheels turn slowly...
K. Brian Kelley
@kbriankelley
February 15, 2008 at 8:22 am
Thanks for all the views guys.
I didn't think it would be possible to find this info. This third party app. is very poorly designed indeed.
There are many users logged onto the app at any one time and so determining who did what dependent on when the change occurred is not possible. I guess it's the app that needs to be getting the windows credentials, but I doubt the vendor will be able to make the change in a hurry.
Like I said, this app is very bad throughout. Security is a really bad problem. We have logins stored in the database with unencrypted passwords. Worst of all is the sa account password is stored in plain text within an XML file on the server! 😎
February 15, 2008 at 9:18 am
K. Brian Kelley (2/15/2008)
Great in theory, but not necessarily in practice. See my rant:
Thumbs up. You're a dba and see this poorly designed app. You reject it and demand proper design. Boss says: No, we'll use this app as is (his son wrote it and has to study so has no more time to dedicate to it), so fix our system. You reply: ok, boss, let's put this on a paper and you and your lawyer sign it. Beware, you might get fired.
February 15, 2008 at 11:45 am
A (2/15/2008)
Thumbs up. You're a dba and see this poorly designed app. You reject it and demand proper design. Boss says: No, we'll use this app as is (his son wrote it and has to study so has no more time to dedicate to it), so fix our system. You reply: ok, boss, let's put this on a paper and you and your lawyer sign it. Beware, you might get fired.
Not quite. It's a 3rd party. It's a large security company (meaning his son didn't write it). A lot of organizations use its products. As a matter of fact, most organizations cannot do without its primary application (which is what that case #1 is in reference to) or the application provided by a competitor. So you're stuck. If you don't implement this product (which is an upgrade of a version you currently have running on every system), you can be held liable for not safeguarding data. If you do, you face this security nightmare.
Which is why I say, good in theory, but not always in practice.
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply