Denying write access to SQL 2000 tables...No app source code.

[TheBugSlayer]

Hi guys.

My boss would like to prevent a third-party application that we have from writing to the database...We do not have the source code and were not sure which account the app uses. I ran a trace and all seems to indicate it uses the sa account. He wants to restrict sa rights; I think it's a bad idea and am not even sure if that's possible. Given the circumstances I believe there is not much we can do other than maybe creating an INSTEAD OF trigger on each one of these (many) tables to ignore the INSERT and UPDATE statements, or AFTER triggers to save the data in a separate table.

Not sure if the app is programmed to handle gracefully the exceptions that will be generated eventually if INSERT and UPDATE statements fail as result of the user loosing those privileges.

Do you have any idea how this could be achieve, or whether it's even possible?

Thanks much.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[Qik3Coder]

The most painless way to handle this would be to deny_db_writer to the sa account.

With that said, the default SA account should be disabled, just on principal.
The application should be using a connection string, in a config somewhere.

Ideally you do this in a test environment first, to catch some of the fallout gracefully.

When you're ready for production:
Do it on a Monday, cause it's gonna burn a little.

Scattered thought,
Lodlaiden


I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[SQLBill]

Something to consider....you have a vendor's application accessing a database - is the database created by the vendor? If so, the way they access it might be required and changing it might violate agreements with the vendor.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[TheBugSlayer]

Thank you both. Consideration will be given to both your suggestions.

Qik3Coder, I am not sure I understand disabling the sa account on the principal (they did not call it principal in SQL Server 2000 yet...) We have no access to the source code, so we can't change the app settings.

SQL Bill, I need to find out about the agreement with the vendor. I believe we host and manage the database, we own everything but I will talk to the CIO about it. Thanks again.

THanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[Qik3Coder]

Dang homonyms, meant principle.

It boils down to a hacking prevention item.
The first thing the hacker is going to do is try to login with sa/sa. Then he's gonna bruteforce the sa password trying to get in.

If the sa account doesn't exist [or work] then he has to figure out an id to login with first, before he can try to hack a password. This is also why sql logins should be ridiculously long, such as MySpecialAccountForUpdatingPasswords. It's in a web.config, so you only type it once.

N-E-Way:

If the code installed on your servers, then you have access to the parts you need. There should be a *.exe.config or a web.config depending on what type of application it is.
Is the application hosted on the database server?

SQLBill is alluding to a concern I had and didn't voice. Why are you trying to change the behavior of a 3rd party application? Work with them if it's causing problems somewhere.

Lodlaiden


I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[TheBugSlayer]

LOL...I said the same thing to the...CIO! I am little people.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)