April 26, 2009 at 12:35 pm
I am getting strange activity on my server , SQL Server Enterprise 2005 SP1 , Primary key was removed from one of the table and datatype i found as float in place of int , just to confirm very restricted people have access to this server and no body aware of this change and no indication in the SQL logs , 1 month back we found deletion of some records from one of the table since we have DML access to users so we thought it was done by mistake but now this PK issue , so how i can i dig more towards it ? and if my server is impacted with sql injection issue how can i figure out ?
Just to confirm this SQL backend is accessed by one of the MS Access application.
also our server has below local server logins
1.SQLServer2005MSSQLUser$ServerName$MSSQLSERVER
2.SQLServer2005SQLAgentUser$ServerName$MSSQLSERVER
sysadmin access but only NT Authourity\System is part of it so can we disable the these Local Server "Logins"?
April 26, 2009 at 1:50 pm
Personally I would set up an explicit Domain SQLAdmin group with very tightly controlled membership.
Make this group a sysadmin and get rid of NTAuthority/System.
You need to make sure that the Domain SQLAdmin group has the required privileges on the server with regard to file access. We cheated and made our SQLAdmin Group a member of local administrators.
April 26, 2009 at 2:30 pm
sqlqueue (4/26/2009)
so how i can i dig more towards it ? and if my server is impacted with sql injection issue how can i figure out ?
You're going to need to run a trace to see exactly what's happening on your server and who's doing it. I would suggest a server-side trace with a minimum of events to keep it lightweight.
You can use profiler to generate the trace script for you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 27, 2009 at 7:33 am
Also, be sure to only give the user ID which your application uses, or any regular users, the minimum access required. If security trimming is not realistic, db_datareader and db_datawriter is sufficient for the majority of application user IDs that are not using SQL stored procedures and will at least prevent any DDL changes from those IDs.
As far as SQL injection, fixing that type of thing generally requires modifying your application to use parameterized SQL and developing stored procedures should be considered in that case.
April 27, 2009 at 12:33 pm
Do the users need to delete records?
And if they are using access, do you know what ID is being used?
Could it be a data source setup as SA? That would need to be changed.
You might want to put some triggers in place too.
Sounds like a creative user or two having too much access.
Greg E
April 27, 2009 at 4:58 pm
I like Gails recommendation of a lightweight trace to monitor whats going on and when.
I would also consider using DDL triggers, have a look in BOL for "Designing DDL Triggers".
Carlton..
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply