ecurity Best Pr.docx
- 文档编号:12088041
- 上传时间:2023-04-17
- 格式:DOCX
- 页数:40
- 大小:141.76KB
ecurity Best Pr.docx
《ecurity Best Pr.docx》由会员分享,可在线阅读,更多相关《ecurity Best Pr.docx(40页珍藏版)》请在冰豆网上搜索。
ecurityBestPr
SQLServer2005SecurityBestPractices-OperationalandAdministrativeTasks
SQLServerTechnicalArticle
Writers:
BobBeauchemin,SQLskills
TechnicalReviewers:
LaurentiuCristofor,AlComeau,SameerTejani,DevendraTiwari,RobWalters,NirajNagrani
Published:
March 2007
AppliesTo:
SQLServer2005SP2
Summary:
Securityisacrucialpartofanymission-criticalapplication.ThispaperdescribesbestpracticesforsettingupandmaintainingsecurityinSQLServer2005.
Copyright
TheinformationcontainedinthisdocumentrepresentsthecurrentviewofMicrosoftCorporationontheissuesdiscussedasofthedateofpublication.BecauseMicrosoftmustrespondtochangingmarketconditions,itshouldnotbeinterpretedtobeacommitmentonthepartofMicrosoft,andMicrosoftcannotguaranteetheaccuracyofanyinformationpresentedafterthedateofpublication.
ThisWhitePaperisforinformationalpurposesonly.MICROSOFTMAKESNOWARRANTIES,EXPRESS,IMPLIEDORSTATUTORY,ASTOTHEINFORMATIONINTHISDOCUMENT.
Complyingwithallapplicablecopyrightlawsistheresponsibilityoftheuser.Withoutlimitingtherightsundercopyright,nopartofthisdocumentmaybereproduced,storedinorintroducedintoaretrievalsystem,ortransmittedinanyformorbyanymeans(electronic,mechanical,photocopying,recording,orotherwise),orforanypurpose,withouttheexpresswrittenpermissionofMicrosoftCorporation.
Microsoftmayhavepatents,patentapplications,trademarks,copyrights,orotherintellectualpropertyrightscoveringsubjectmatterinthisdocument.ExceptasexpresslyprovidedinanywrittenlicenseagreementfromMicrosoft,thefurnishingofthisdocumentdoesnotgiveyouanylicensetothesepatents,trademarks,copyrights,orotherintellectualproperty.
Unlessotherwisenoted,theexamplecompanies,organizations,products,domainnames,e-mailaddresses,logos,people,placesandeventsdepictedhereinarefictitious,andnoassociationwithanyrealcompany,organization,product,domainname,emailaddress,logo,person,placeoreventisintendedorshouldbeinferred.
©2007MicrosoftCorporation.Allrightsreserved.
Microsoft,ActiveDirectory,Windows,WindowsServer,andWindowsVistaareeitherregisteredtrademarksortrademarksofMicrosoftCorporationintheUnitedStatesand/orothercountries.
Thenamesofactualcompaniesandproductsmentionedhereinmaybethetrademarksoftheirrespectiveowners.
TableofContents
Introduction4
SurfaceAreaReduction4
ServiceAccountSelectionandManagement6
AuthenticationMode9
NetworkConnectivity10
LockdownofSystemStoredProcedures13
PasswordPolicy14
AdministratorPrivileges16
DatabaseOwnershipandTrust17
Schemas18
Authorization19
CatalogSecurity21
RemoteDataSourceExecution22
ExecutionContext23
Encryption24
Auditing26
MicrosoftBaselineSecurityAnalyzerandSQLServerBestPracticesAnalyzer29
Patching29
Conclusion29
Introduction
ThiswhitepapercoverssomeoftheoperationalandadministrativetasksassociatedwithMicrosoft®SQL Server™ 2005securityandenumeratesbestpracticesandoperationalandadministrativetasksthatwillresultinamoresecureSQL Serversystem.Eachtopicdescribesafeatureandbestpractices.Foradditionalinformationonthespecificsofutilities,features,andDDLstatementsreferencedinthiswhitepaper,seeSQL Server 2005BooksOnline.FeaturesandoptionsthatarenewordefaultsthatarechangedforSQL Server 2005areidentified.CodingexamplesforoperationaltasksuseTransact-SQL,sounderstandingTransact-SQLisrequiredforyoutogetthemostoutofthispaper.
SurfaceAreaReduction
SQLServer 2005installationminimizesthe"attacksurface"becausebydefault,optionalfeaturesarenotinstalled.Duringinstallationtheadministratorcanchoosetoinstall:
∙DatabaseEngine
∙AnalysisServicesEngine
∙ReportingServices
∙IntegrationServices
∙NotificationServices
∙DocumentationandSamples
Itisagoodpracticetoreviewwhichproductfeaturesyouactuallyneedandinstallonlythosefeatures.Later,installadditionalfeaturesonlyasneeded.SQL Server 2005includessampledatabasesforOLTP,datawarehousing,andAnalysisServices.Installsampledatabasesontestserversonly;theyarenotinstalledbydefaultwhenyouinstallthecorrespondingenginefeature.SQL Server 2005includessamplecodecoveringeveryfeatureoftheproduct.Thesesamplesarenotinstalledbydefaultandshouldbeinstalledonlyonadevelopmentserver,notonaproductionserver.Eachitemofsamplecodehasundergoneareviewtoensurethatthecodefollowsbestpracticesforsecurity.EachsampleusesMicrosoftWindows®securityprincipalsandillustratestheprincipalofleastprivilege.
SQLServerhasalwaysbeenafeature-richdatabaseandthenumberofnewfeaturesinSQL Server 2005canbeoverwhelming.Onewaytomakeasystemmoresecureistolimitthenumberofoptionalfeaturesthatareinstalledandenabledbydefault.Itiseasiertoenablefeatureswhentheyareneededthanitistoenableeverythingbydefaultandthenturnofffeaturesthatyoudonotneed.ThisistheinstallationpolicyofSQL Server 2005,knownas"offbydefault,enablewhenneeded."Onewaytoensurethatsecuritypoliciesarefollowedistomakesecuresettingsthedefaultandmakethemeasytouse.
SQL Server 2005providesa"one-stop"utilitythatcanbeusedtoenableoptionalfeaturesonaper-serviceandper-instancebasisasneeded.Althoughthereareotherutilities(suchasServicesinControlPanel),serverconfigurationcommands(suchassp_configure),andAPIssuchasWMI(WindowsManagementInstrumentation)thatyoucanuse,theSQLServerSurfaceAreaConfigurationtoolcombinesthisfunctionalityintoasingleutilityprogram.Thisprogramcanbeusedeitherfromthecommandlineorviaagraphicuserinterface.
SQL ServerServiceAreaConfigurationdividesconfigurationintotwosubsets:
servicesandconnections,andfeatures.UsetheSurfaceAreaConfigurationforServicesandConnectionstooltoviewtheinstalledcomponentsofSQLServerandtheclientnetworkinterfacesforeachenginecomponent.Thestartuptypeforeachservice(Automatic,Manual,orDisabled)andtheclientnetworkinterfacesthatareavailablecanbeconfiguredonaper-instancebasis.UsetheSurfaceAreaConfigurationforFeaturestooltoviewandconfigureinstance-levelfeatures.
Thefeaturesenabledforconfigurationare:
∙CLRIntegration
∙Remoteuseofadedicatedadministratorconnection
∙OLEAutomationsystemprocedures
∙SystemproceduresforDatabaseMailandSQLMail
∙Ad hocremotequeries(theOPENROWSETandOPENDATASOURCEfunctions)
∙SQL ServerWebAssistant
∙xp_cmdshellavailability
Thefeaturesenabledforviewingare:
∙HTTPendpoints
∙ServiceBrokerendpoint
TheSQL ServerSurfaceAreaConfigurationcommand-lineinterface,sac.exe,permitsyoutoimportandexportsettings.ThisenablesyoutostandardizetheconfigurationofagroupofSQL Server 2005instances.Youcanimportandexportsettingsonaper-instancebasisandalsoonaper-servicebasisbyusingcommand-lineparameters.Foralistofcommand-lineparameters,usethe-?
command-lineoption.Youmusthavesysadminprivilegetousethisutility.ThefollowingcodeisanexampleofexportingallsettingsfromthedefaultinstanceofSQLServeronserver1andimportingthemintoserver2:
sacoutserver1.out–Sserver1–Uadmin–IMSSQLSERVER
sacinserver1.out–Sserver2
WhenyouupgradeaninstanceofSQL ServertoSQL Server 2005byperforminganin-placeupgrade,theconfigurationoptionsoftheinstanceareunchanged.UseSQL ServerSurfaceAreaConfigurationtoreviewfeatureusageandturnofffeaturesthatarenotneeded.YoucanturnoffthefeaturesinSQL ServerSurfaceAreaConfigurationorbyusingthesystemstoredprocedure,sp_configure.Hereisanexampleofusingsp_configuretodisallowtheexecutionofxp_cmdshellonaSQL Serverinstance:
--Allowadvancedoptionstobechanged.
EXECsp_configure'showadvancedoptions',1
GO
--Updatethecurrentlyconfiguredvalueforadvancedoptions.
RECONFIGURE
GO
--Disablethefeature.
EXECsp_configure'xp_cmdshell',0
GO
--Updatethecurrentlyconfiguredvalueforthisfeature.
RECONFIGURE
GO
InSQLServer2005,SQL ServerBrowserfunctionalityhasbeenfactoredintoitsownserviceandisnolongerpartofthecoredatabaseengine.Additionalfunctionsarealsofactoredintoseparateservices.Servicesthatarenotapartofthecoredatabaseengineandcanbeenabledordisabledseparatelyinclude:
∙SQLServerActiveDirectoryHelper
∙SQLServerAgent
∙SQLServerFullTextSearch
∙SQLServerBrowser
∙SQLServerVSSWriter
TheSQL ServerBrowserserviceneedstoberunningonlytoconnecttonamedSQL ServerinstancesthatuseTCP/IPdynamicportassignments.ItisnotnecessarytoconnecttodefaultinstancesofSQL Server 2005andnamedinstancesthatusestaticTCP/IPports.Foramoresecureconfiguration,alwaysusestaticTCP/IPportassignmentsanddisabletheSQL ServerBrowserservice.TheVSSWriterallowsbackupandrestoreusingtheVolumeShadowCopyframework.Thisserviceisdisabledbydefault.IfyoudonotuseVolumeShadowCopy,disablethisservice.IfyouarerunningSQL ServeroutsideofanActiveDirectory®directoryservice,disabletheActiveDirectoryHelper.
Bestpracticesforsurfaceareareduction
∙Installonlythosecomponentsthatyouwillimmediatelyuse.Additionalcomponentscanalwaysbeinstalledasneeded.
∙Enableonlytheoptionalfeaturesthatyouwillimmediatelyuse.
∙Reviewoptionalfeatureusagebeforedoinganin-placeupgradeanddisableunneededfeatureseitherb
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- ecurity Best Pr
![提示](https://static.bdocx.com/images/bang_tan.gif)