April 8, 2009 at 4:49 am
There's a push to get some sort of enterprise wide SQL monitoring software running which doesn't touch the "live" servers but runs entirely on it's own server, monitoring from afar so to speak. So I'm testing some. 😎
The "OLE Automation Procedures" by default is off (says Microsoft) but can be swiched on via a simple tsql command. There's no mention of hackers subsequently having a field day in the MS article so I was wondering if anyone knew.
Will this blow a gaping hole in the secure(ish) settings of SQL2k5 and allow hackers, sql injection junkies and other feral script kiddies to wade in with truncate table commands?
May 20, 2009 at 8:30 am
FNS (4/8/2009)
There's a push to get some sort of enterprise wide SQL monitoring software running which doesn't touch the "live" servers but runs entirely on it's own server, monitoring from afar so to speak. So I'm testing some. 😎The "OLE Automation Procedures" by default is off (says Microsoft) but can be swiched on via a simple tsql command. There's no mention of hackers subsequently having a field day in the MS article so I was wondering if anyone knew.
Will this blow a gaping hole in the secure(ish) settings of SQL2k5 and allow hackers, sql injection junkies and other feral script kiddies to wade in with truncate table commands?
This link might answer your question:
http://www.sqlsecurity.com/Community/Discussions/tabid/68/view/topic/postid/2931/Default.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 20, 2009 at 8:54 am
To me the risk is instability in the OLE item, or perhaps a misundertanding of how it can be used by the admin. What if that component is attacked (low probability)?
I dislike adding things things onto the server, which must be very stable.
May 20, 2009 at 9:00 am
Steve Jones - Editor (5/20/2009)
To me the risk is instability in the OLE item, or perhaps a misundertanding of how it can be used by the admin. What if that component is attacked (low probability)?I dislike adding things things onto the server, which must be very stable.
The main risk is I think from granting EXEC permissions on these objects to non-sysadmins. It cannot be a good thing enabling a non-sa to have access on the server O/S.
We just received a request from non-sa users to have exec permissions on the OA procs. Because we are still on SQL-2000 we have no choice but to grant that access. Once we upgrade to SQL-2008, however, we will have better alternatives, such as specializing code for each specific task (such as the requirement for making web-service calls from SQL).
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
May 21, 2009 at 8:47 am
I think in SQL 2005 and above the OA procs can only be accessed by folks with Sysadmin authority. This means that anything monitoring your server has very high access rights. IMHO Microsoft are making these procedures harder to use in preparation to deprecating them in the next SQL release.
Even if you do use them, do not expect them to be free from memory leaks, especially if people accidentally leak memory by forgetting to properly close the handles.
Personally, I would look at other methods to implement monitoring that do not use the OA procs. In SQL 2005 and above, you can give non-sysadmin people read-only access almost anything in SQL Server by granting use of VIEW ANY DATABASE, VIEW ANY DEFINITION and VIEW SERVER STATE. With this access, they can use normal T-SQL to get the information needed.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 21, 2009 at 9:05 am
EdVassie (5/21/2009)
I think in SQL 2005 and above the OA procs can only be accessed by folks with Sysadmin authority. This means that anything monitoring your server has very high access rights. IMHO Microsoft are making these procedures harder to use in preparation to deprecating them in the next SQL release.Even if you do use them, do not expect them to be free from memory leaks, especially if people accidentally leak memory by forgetting to properly close the handles.
Personally, I would look at other methods to implement monitoring that do not use the OA procs. In SQL 2005 and above, you can give non-sysadmin people read-only access almost anything in SQL Server by granting use of VIEW ANY DATABASE, VIEW ANY DEFINITION and VIEW SERVER STATE. With this access, they can use normal T-SQL to get the information needed.
I think in our case use of the OA sprocs is done for web-service calls. It should be relatively easy to construct a generic SQL-CLR procedure that does the same thing.
If anyone has any info on how to create such an object in SQL-CLR, links, code etc., it would be appreciated.
Are OA sprocs being deprecated post SQL-2008? That would be a good argument I could put forward to our devs for starting to think past these OA sprocs.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply