aliceserver2008

SQLServer2008SecurityOverviewforDatabaseAdministratorsWhitePaperPublished:January2007Updated:July2008Summary:ThispapercoverssomeofthemostimportantsecurityfeaturesinSQLServer2008.
Ittellsyouhow,asanadministrator,youcaninstallSQLServersecurelyandkeepitthatwayevenasapplicationsandusersmakeuseofthedatastoredwithin.
Forthelatestinformation,seeMicrosoftSQLServer2008.
ContentsIntroduction1SecureConfiguration2WindowsUpdate2SurfaceAreaConfiguration2Authentication3PasswordPolicyEnforcement4EndpointAuthentication5Authorization7GranularPermissions8MetadataSecurity11SQLServerAgentProxies12ExecutionContext16User/SchemaSeparation17EncryptionandKeyManagement20DataEncryption20AuditinginSQLServer200825Conclusion29IntroductionSecurityisbecomingincreasinglyimportantasmorenetworksareconnectedtogether.
Yourorganization'sassetsmustbeprotected,particularlyitsdatabases,whichcontainyourcompany'svaluableinformation.
Securityisoneofthecriticalfeaturesofadatabaseengine,protectingtheenterpriseagainstmyriadthreats.
ThesecurityfeaturesofMicrosoftSQLServer2008aredesignedtomakeitmoresecureandtomakesecuritymoreapproachableandunderstandabletothosewhoareresponsiblefordataprotection.
Duringthepastfewyears,theconceptofwhatasecure,computer-basedsystemmustbehasbeendeveloping.
Microsofthasbeenintheforefrontofthisdevelopment,andSQLServerisoneofthefirstserverproductsthatfullyimplementsthatunderstanding.
Itenablestheimportantprincipleofleastprivilegesoyoudonothavetograntusersmorepermissionsthanarenecessaryforthemtodotheirjobs.
Itprovidesin-depthtoolsfordefensesothatyoucanimplementmeasurestofrustrateeventhemostskillfulattackers.
MuchhasbeenwrittenanddiscussedabouttheMicrosoftTrustworthyComputinginitiativethatguidesallsoftwaredevelopmentatthecompany.
Formoreinformation,seeTrustworthyComputing.
Thefouressentialcomponentsofthisinitiativeare:Securebydesign.
Softwarerequiresasecuredesignasafoundationforrepellingattackersandprotectingdata.
Securebydefault.
Systemadministratorsshouldnothavetoworktomakeafreshinstallationsecure;itshouldbethatwaybydefault.
Secureindeployment.
Softwareshouldhelptokeepitselfupdatedwiththelatestsecuritypatchesandassistinmaintenance.
Communications.
Communicatebestpracticesandevolvingthreatinformationsothatadministratorscanproactivelyprotecttheirsystems.
TheseguidingprinciplesareevidentthroughoutSQLServer2008,whichprovidesallthetoolsyouneedtosecureyourdatabases.
Thispaperexploresthemostimportantsecurityfeaturesforsystemanddatabaseadministrators.
ItstartswithalookathowSQLServer2008isstraightforwardtoinstallandconfiguresecurely.
Itexploresauthenticationandauthorizationfeaturesthatcontrolaccesstotheserveranddeterminewhatausercandoonceauthenticated.
Itfinisheswithalookatthedatabasesecurityfeaturesanadministratormustunderstandinordertoprovideasecureenvironmentfordatabasesandtheapplicationsthataccessthosedatabases.
SecureConfigurationNothingmuchhaschangedintheexternalsecurityrequirementsofaserverrunningSQLServer2008.
Youneedtophysicallysecuretheserverandbackupdataregularly,putitbehindoneormorefirewallsifitisconnectedtoanetwork,avoidinstallingSQLServeronacomputerwithotherserverapplications,andenableonlytheminimumnetworkprotocolsrequired.
InstallSQLServeronaMicrosoftWindowsServer2003orMicrosoftWindowsServer2008computersothatithasfulladvantageofoperatingsystem-levelsecurityprotections.
TheSQLServer2008installationprogramdoesalltheusualinstallationtasks,andhasaSystemConfigurationCheckerthatnotifiesyouofanydeficienciesthatmightcauseproblems.
InstallingSQLServer2008doesnotenableallfeaturesbydefault.
Instead,itinstallsthecoreessentialsandwidelyusedfeatures.
Otherfeaturesthatmightnotbeneededinaproductionenvironmentareturnedoffbydefault.
Youcanusethesupportedtoolstoturnonjustthefeaturesyouneed.
ThisisallpartoftheTrustworthyComputingsecurebydefaultmandate.
Featuresthatarenotrequiredbyabasicdatabaseserverareleftuninstalled,resultinginareducedsurfacearea.
Sincebydefaultnotallfeaturesareenabledacrossallsystems,aheterogeneityisintroducedintermsoftheinstallimageofasystem.
Becausethislimitsthenumberofsystemsthathavefeaturesthatarevulnerabletoapotentialattack,ithelpsdefendagainstlarge-scaleattacksorworms.
WindowsUpdateNewthreatsandvulnerabilitiescanbediscoveredafteryoudeploySQLServerinyourenterprise.
WindowsUpdateisdesignedtoensuretimelydownloadandapplicationofpatchesthatsignificantlyreducespecificsecurityissues.
YoucanuseWindowsUpdatetoapplySQLServer2008patchesautomaticallyandreducethreatscausedbyknownsoftwarevulnerabilities.
Inmostenterpriseenvironments,youshouldusetheWindowsServerUpdateServicetomanagethedistributionofpatchesandupdatesthroughouttheorganization.
SurfaceAreaConfigurationSQLServer2008comespackedwithnumerousfeatures,manyofwhichareinstalledinadisabledstate.
Forexample,CLRintegration,databasemirroring,debugging,ServiceBroker,andmailfunctionsareinstalledbutarenotrunningandnotavailableuntilyouexplicitlyturnthemonorconfigurethem.
Thisdesignisconsistentwiththereductioninsurfaceareaparadigmofthe"securebydefault"philosophyofSQLServer,andleadstoareducedattacksurface.
Ifafeatureisnotavailableorenabled,anattackercannotmakeuseofit.
ThetradeoffisthatitcanbetimeconsumingtohuntdownalloftheTransact-SQLstatementsforturningonfeatures.
Evenwhenyoudiscoverthatthesp_configuresystemstoredproceduredoesmuchofwhatyouneed,youstillmustwritenon-intuitivecodelikethefollowing:sp_configure'showadvancedoptions',1reconfigurewithoverridesp_configure'clrenabled',1Therearefartoomanyconfigurationoptionstotakethetimetowritethiskindofcode–especiallywhenyouhavemultipleinstancesofSQLServerdeployedthroughouttheorganization.
SQLServer2008includesapolicy-basedmanagementtechnology.
Policy-BasedManagementprovidesanumberofconfigurationfacets,eachofwhichdefinesasetofrelatedconfigurationsettingsorproperties.
Youcanusethesefacetstocreateconditionsthatspecifythedesiredsettingsfortheconfigurationoptions,andenforcetheseconditionsaspoliciestoSQLServerinstancesacrosstheenterprise.
OnetheofthefacetsincludedinSQLServer2008istheSurfaceAreafacet,andyoucanusethisfacettodefineapolicythatcontrolsthestatusofvariousSQLServer2008features.
Bycreatingapolicythatdefinesthedesiredsurfaceareasettingsforyourservers,youcaneasilyenforceaminimalsurfaceareaonallSQLServerinstancesinyourorganization,andreducethepossibilityofmaliciousattack.
AuthenticationMicrosoftdevelopedSQLServer2000atatimewhendataandserversrequiredprotectionbutdidnothavetowithstandtherelentlessonslaughtofattacksseenontheInternettoday.
Thebasicauthenticationquestionremainsthesame:WhoareyouandhowcanyouproveitSQLServer2008providesrobustauthenticationfeaturesthatprovidebettersupportatthesecurityoutskirtsoftheserverforlettingthegoodguysinandkeepingthebadguysout.
SQLServerAuthenticationprovidesauthenticationfornon-Windows-basedclientsorforapplicationsusingasimpleconnectionstringcontaininguserIDsandpasswords.
Whilethislogoniseasytouseandpopularwithapplicationdevelopers,itisnotassecureasWindowsauthenticationandisnottherecommendedauthenticationmechanismSQLServer2008improvesontheSQLServerAuthenticationoption.
First,itsupportsencryptionofthechannelbydefaultthroughtheuseofSQL-generatedcertificates.
AdministratorsdonothavetoacquireandinstallavalidSSLcertificatetomakesurethatthechanneloverwhichtheSQLcredentialsflowissecure.
SQLServer2008automaticallygeneratesthesecertificates,andencryptsthechannelautomaticallybydefaultwhentransmittingloginpackets.
ThisoccursiftheclientisattheSQLServer2005levelorabove.
NoteThenativecertificategeneratedbySQLServerprotectsagainstpassiveman-in-the-middleattackswheretheattackerissniffingthenetwork.
Tosecureyoursystemsmoreeffectivelyagainstactiveman-in-the-middleattacks,deployandusecertificatesthattheclientstrustaswell.
SQLServer2008furtherenhancesSQLServerAuthenticationbecause,bydefault,thedatabaseenginenowusesWindowsGroupPolicyforpasswordcomplexity,passwordexpiration,andaccountlockoutonSQLloginswhenusedincombinationwithaWindowsServer2003orlater.
ThismeansthatyoucanenforceWindowspasswordpolicyonyourSQLServeraccounts.
PasswordPolicyEnforcementWithSQLServer2008,passwordpolicyenforcementisbuiltintotheserver.
UsingtheNetValidatePasswordPolicy()API,whichispartoftheNetAPI32libraryonWindowsServer2003,SQLServervalidatesapasswordduringauthenticationandduringpasswordsetandreset,inaccordancewithWindowspoliciesforpasswordstrength,expiration,andaccountlockout.
Thefollowingtableliststhesettingsthatcomprisethepolicy.
WindowsServer2003PasswordPolicyComponentsCategoryNameNotesPasswordPolicyEnforcepasswordhistoryPreventsusersfromreusingoldpasswords,suchasalternatingbetweentwopasswords.
MinimumpasswordlengthPasswordmustmeetcomplexityrequirementsSeetextbelow.
StorepasswordsusingreversibleencryptionAllowsretrievingthepasswordfromWindows.
Youshouldneverenablethis,unlessapplicationrequirementsoutweightheneedforsecurepasswords.
(ThispolicydoesnotapplytoSQLServer.
)PasswordExpirationMaximumpasswordageMinimumpasswordageAccountLockoutPolicyAccountlockoutdurationDurationoftheaccountlockoutinminutes.
Windowsenablesthiswhenthelockoutthresholdis>0.
AccountlockoutthresholdMaximumnumberofunsuccessfulloginattempts.
ResetaccountlockoutcounterafterTimeinminutesafterwhichWindowsresetsthecounterofunsuccessfulattempts.
Windowsenablesthiswhenthelockoutthresholdis>0.
IfyouarenotrunningWindowsServer2003orabove,SQLServerstillenforcespasswordstrengthbyusingsimplechecks,preventingpasswordsthatare:NulloremptyThesameasthenameofcomputerorloginAnyof"password","admin","administrator","sa","sysadmin"ThesamecomplexitystandardisappliedtoallpasswordsyoucreateanduseinSQLServer,includingpasswordsforthesalogin,applicationroles,databasemasterkeysforencryption,andsymmetricencryptionkeys.
SQLServeralwayschecksthepasswordpolicybydefault,butyoucansuspendenforcementforindividualloginswitheithertheCREATELOGINorALTERLOGINstatementsasinthefollowingcode:CREATELOGINbobWITHPASSWORD='S%V7Vlv3c9Es8',CHECK_EXPIRATION=OFF,CHECK_POLICY=OFFCHECK_EXPIRATIONusestheminimumandmaximumpasswordagepartoftheWindowsServer2003policy,andCHECK_POLICYusestheotherpolicysettings.
Administrativesettingsallowturningonandoffpasswordpolicychecks,turningonandoffpasswordexpirationchecks,andforcingapasswordchangethefirsttimeauserlogson.
TheMUST_CHANGEoptioninCREATELOGINforcestheusertochangethepasswordthenexttimetheylogon.
Ontheclientside,itallowsapasswordchangeatlogon.
Allofthenewclient-sidedataaccesstechnologieswillsupportthis,includingOLEDBandADO.
NET,aswellasclienttoolssuchasManagementStudio.
Iftheuserunsuccessfullyattemptstologontoomanytimesandexceedstheattemptsallowedinthepasswordpolicy,SQLServerlockstheaccount,basedonthesettingsintheWindowspolicy.
AnadministratorcanunlocktheaccountwiththeALTERLOGINstatement:ALTERLOGINaliceWITHPASSWORD='3x1Tq#PO^YIAz'UNLOCKEndpointAuthenticationSQLServer2008supportsboththetraditional,binaryTabularDataStreamforclientaccesstodataaswellasnativeXMLWebserviceaccessusingHTTP.
TheprimarybenefitofallowingaccessviaHTTPisthatanyclientsoftwareanddevelopmenttoolsthatunderstandWebserviceprotocolscanaccessdatastoredinSQLServer.
ThismeansSQLServer2008canprovidestandaloneWebservicemethodsaswellasbeacompleteendpointinaServiceOrientedArchitecture(SOA).
UsingSQLServer2008asaWebservicehostrequirestwogeneralsteps,eachwithplentyofpossiblevariations:Definingstoredproceduresanduser-definedfunctionsthatprovidetheWebservicemethodsDefininganHTTPendpointthatreceivesmethodcallsviaHTTPandroutesthemtotheappropriateprocedure.
Thispaperfocusesonthesecurityissuesinvolved.
FordetailsonconfiguringandusingHTTPendpoints,seeCREATEENDPOINT(Transact-SQL)inSQLServerBooksOnline.
BecauseXMLWebservicesinSQLServerusesHTTPand,bydefault,port80,mostfirewallsallowthetraffictopass.
However,anunprotectedendpointisapotentialvectorforattacksandyoumustsecureit,soSQLServerhasstrongauthenticationandauthorization.
Bydefault,SQLServerdoesnothaveanyendpointsandyouhavetohaveahighlevelofpermissionstocreate,alter,andenableHTTPendpoints.
SQLServer2008providesfivedifferentauthenticationtypes,similartothoseusedbyIISforWebsiteauthentication.
Basicauthentication.
BasicauthenticationispartoftheHTTP1.
1protocol,whichtransmitsthelogincredentialsincleartextthatisbase-64encoded.
ThecredentialmustmaptoaWindowslogin,whichSQLServerthenusestoauthorizeaccesstodatabaseresources.
IfyouuseBasicauthentication,youcannotsetthePORTSargumenttoCLEARbutmustinsteadsetittoSSLanduseadigitalcertificatewithSSLtoencryptthecommunicationwiththeclientsoftware.
Digestauthentication.
DigestauthenticationisalsopartoftheHTTP1.
1protocol.
IthashesthecredentialswithMD5beforesendingtotheserversothatcredentialsarenotsentacrossthewire,eveninencryptedform.
ThecredentialsmustmaptoavalidWindowsdomainaccount;youcannotuselocaluseraccounts.
NTLMauthentication.
NTLMusesthechallenge-responseprotocoloriginallyintroducedinMicrosoftWindowsNTandsupportedinallclientandserverversionsofWindowssince.
ItprovidessecureauthenticationwhenbothclientandserverareWindowssystems,andrequiresavaliddomainaccount.
Kerberosauthentication.
KerberosauthenticationisavailablewithWindows2000andlater,basedonanindustry-standardprotocolavailableonmanyoperationsystems.
Itallowsformutualauthenticationinwhichboththeclientandserverarereasonablyassuredoftheother'sidentityandprovidesahighlysecureformofauthentication.
TouseKerberosonWindowsServer2003,youmustregistertheKerberosServicePrincipalName(SPN)withHttp.
sysbyusingtheSetSPN.
exeutilitythatispartoftheWindowsSupportTools.
Integratedauthentication.
IntegratedauthenticationprovidesthebestofNTLMandKerberosauthentication.
Theserveruseswhicheverofthetwoauthenticationtypestheclientrequests,allowingtheauthenticationtheclientsupportswhilemakingtheserviceavailabletoolderversionsofWindows.
YoucanconfigureHttp.
sysinWindows2003tonegotiatewiththeclientwhichprotocolitshoulduse.
TheauthenticationmethodusedforanendpointissetwiththeAUTHENTICATIONattributeoftheCREATEorALTERENDPOINTstatement.
Forexample,thefollowingcodecreatesanendpointthatusesKerberosforauthentication:CREATEENDPOINTmyEndpointSTATE=STARTEDASHTTP(PATH='/MyHttpEndpoint',AUTHENTICATION=(KERBEROS),PORTS=(CLEAR),SITE='MySqlServer')FORSOAP(WSDL=DEFAULT,DATABASE='myDB',NAMESPACE='http://example.
com/MySqlServer/myDB/WebService')SQLServer2008supportsendpointsthatlistentoHTTPaswellasauser-definedportonTCP.
Youcanalsoformatrequestsbyusingavarietyofformats:SOAP,Transact-SQL,aformatspecifictoServiceBroker,andanotherusedfordatabasemirroring.
WhenusingSOAPyoucantakeadvantageofWS-SecurityheaderstoauthenticateSQLServerlogins.
MicrosoftimplementedWebServiceendpointauthenticationtosupportawidevarietyofprotocolsandspecifications,ofwhichthispapertouchesonjustafew.
Youwillneedtoexplicitlyenableyourauthenticationoptionandensurethatclientsareabletoprovidethetypeofcredentialsrequired.
AfterSQLServerauthenticatestheclient,youcanauthorizetheresourcesthattheloginisauthorizedtoaccess,describedinthenextsection.
AuthorizationAfterauthentication,itistimetothinkaboutwhatanauthenticatedlogincando.
Inthisarea,SQLServer2008andSQLServer2005aremoreflexiblethanearlierversions.
Permissionsarenowfarmoregranularsothatyoucangrantthespecificpermissionsrequiredratherthangrantingmembershipinafixedrolethatprobablycarrieswithitmorepermissionsthanarenecessary.
Younowhavefarmoreentities,(securables)towhichyoucanassignpermissionsthataremoregranular.
Inadditiontoenhancedprotectionforuserdata,structuralinformationandmetadataaboutaparticularsecurableisnowavailableonlytoprincipalsthathavepermissiontoaccessthesecurable.
Furthermore,itispossibletocreatecustompermissionsetsusingamechanismthatallowsonetodefinethesecuritycontextunderwhichstoredprocedurescanrun.
Inaddition,SQLServerAgentusesaflexibleproxyschemetoenablejobstepstorunandaccessrequiredresources.
AllthesefeaturesmakeSQLServermorecomplexbutfarmoresecure.
GranularPermissionsOneofthemanywaysthatSQLServer2008andSQLServer2005arefarmoresecurethanearlierversionsistheimprovedgranularityofpermissions.
Previously,anadministratorhadtograntausermembershipinafixedserverroleorfixeddatabaseroletoperformspecificoperations,butmoreoftenthannot,thoseroleshadpermissionsthatwerefartoobroadforsimpletasks.
Theprincipleofleastprivilegerequiresthatauserhaveonlytheminimumpermissionstodoajob,soassigninguserstoabroadrolefornarrowpurposesviolatesthisprinciple.
ThesetoffixedserveranddatabaserolesislargelyunchangedsinceSQLServer2000,soyoucanstilltakeadvantageofthosepredefinedbundlesofpermissionswhenusersorapplicationsrequireallormostofthedefinedpermissions.
Probablythebiggestchangeistheadditionofapublicserverrole.
However,theprincipleofleastprivilegemandatesthatyounotusearolethatisnotaperfectfitforwhattheprincipalneedstodoajob.
Althoughitrequiresmoreworktodiscoverandassignthepermissionsrequiredforaprincipal,itcanresultinafarmoresecuredatabaseenvironment.
PrincipalsandSecurablesInSQLServer2008aprincipalisanyindividual,group,orprocessthatcanrequestaccesstoaprotectedresourceandbegrantedpermissiontoaccessit.
AsinpreviousversionsofSQLServer,youcandefineaprincipalinWindowsoryoucanbaseitonaSQLServerloginwithnocorrespondingWindowsprincipal.
ThefollowinglistshowsthehierarchyofSQLServer2008principals,excludingthefixedserveranddatabaseroles,andhowyoucanmaploginsanddatabaseuserstosecurityobjects.
Thescopeoftheinfluenceoftheprincipaldependsonthescopeofitsdefinition,sothataWindows-levelprincipalismoreencompassingthanaSQLServer-levelprincipal,whichismoreencompassingthanadatabase-levelprincipal.
Everydatabaseuserautomaticallybelongstothefixedpublicrole.
Windows-levelprincipalsWindowsDomainloginWindowsLocalloginWindowsgroupSQLServer-levelprincipalsSQLServerloginSQLServerloginmappedtoaWindowsloginSQLServerloginmappedtoacertificateSQLServerloginmappedtoanasymmetrickeyDatabase-levelprincipalsDatabaseuserDatabaseusermappedtoSQLServerloginDatabaseusermappedtoaWindowsloginDatabaseusermappedtoacertificateDatabaseusermappedtoanasymmetrickeyDatabaseroleApplicationrolePublicroleTheotherpartofauthorizationistheobjectsthatyoucansecurethroughthegrantingordenyingofpermissions.
Figure1liststhehierarchyofsecurableobjectsinSQLServer2008.
Attheserverlevel,youcansecurenetworkendpointstocontrolthecommunicationchannelsintoandoutoftheserver,aswellasdatabases,bindings,androlesandlogins.
Atthedatabaseandschemalevel,virtuallyeveryobjectyoucancreateissecurable,includingthosethatresidewithinaschema.
Figure1:SecurableobjecthierarchyinSQLServer2008RolesandPermissionsForasenseofthenumberofpermissionsavailableinSQLServeryoucaninvokethefn_builtin_permissionssystemfunction:SELECT*FROMsys.
fn_builtin_permissions(default)FollowingarethenewpermissiontypesinSQLServer2005:CONTROL.
Confersowner-likepermissionsthateffectivelygrantalldefinedpermissionstotheobjectandallobjectsinitsscope,includingtheabilitytograntothergranteesanypermission.
CONTROLSERVERgrantstheequivalentofsysadminprivileges.
ALTER.
Conferspermissiontoalteranyofthepropertiesofthesecurableobjectsexcepttochangeownership.
InherentlyconferspermissionstoALTER,CREATE,orDROPsecurableobjectswithinthesamescope.
Forexample,grantingALTERpermissionsonadatabaseincludespermissiontochangeitstables.
ALTERANY.
Conferspermissiontochangeanysecurableobjectofthetypespecified.
Forexample,grantingALTERANYASSEMBLYallowschangingany.
NETassemblyinthedatabase,whileattheserverlevelgrantingALTERANYLOGINletstheuserchangeanyloginonthatserver.
IMPERSONATEON.
Conferspermissiontoimpersonatethespecifieduserorlogin.
Asyouwillseelaterinthiswhitepaper,thispermissionisnecessarytoswitchexecutioncontextsforstoredprocedures.
Youalsoneedthispermissionwhendoingimpersonatinginabatch.
TAKEOWNERSHIP.
Confersthepermissiontothegranteetotakeownershipofthesecurable,usingtheALTERAUTHORIZATIONstatement.
SQLServer2008stillusesthefamiliarGRANT,DENY,andREVOKEschemeforassigningorrefusingpermissionsonasecurableobjecttoaprincipal.
TheGRANTstatementnowcoversallofthenewpermissionoptions,suchasthescopeofthegrantandwhethertheprincipalcangrantthepermissiontootherprincipals.
SQLServerdoesnotallowcross-databasepermissions.
Tograntsuchpermissions,createaduplicateuserineachdatabaseandseparatelyassigneachdatabase'suserthepermission.
LikeearlierversionsofSQLServer,activatinganapplicationrolesuspendsotherpermissionsforthedurationthattheroleisactive.
However,inSQLServer2008andSQLServer2005,youcanunsetanapplicationrole.
AnotherdifferencebetweenSQLServer2000andlaterversionsisthatwhenactivatinganapplicationrole,therolealsosuspendsanyserverprivilege,includingpublic.
Forexample,ifyougrantVIEWANYDEFINITIONtopublic,theapplicationrolewillnothonorit.
Thisismostnoticeablewhenaccessingserver-levelmetadataunderanapplicationrolecontext.
NoteThenew,preferredalternativetoapplicationrolesistouseexecutioncontextincodemodules.
Formoreinformation,seeExecutionContextinthispaper.
Grantingaparticularpermissioncanconveytherightsofotherpermissionsbyimplication.
TheALTERpermissiononaschema,forexample,"covers"moregranularandlower-levelpermissionsthatare"implied.
"Figure2displaystheimpliedpermissionsforALTERSCHEMA.
See"Covering/ImpliedPermissions(DatabaseEngine)"inSQLServerBooksOnlinefortheTransact-SQLcodeforanImplyingPermissionsuser-definedfunctionthatassemblesthehierarchylistfromthesys.
fn_builtin_permissionscatalogviewandidentifiesthedepthofeachpermissioninthehierarchy.
AfteraddingImplyingPermissionstothemasterdatabase,IexecutedthefollowingstatementtoproduceFigure2,passingintheobjectandpermissiontype:SELECT*FROMmaster.
dbo.
ImplyingPermissions('schema','alter')ORDERBYheight,rankThisisagreatwaytoexplorethepermissionshierarchyinSQLServer2008.
Figure2:HierarchyofimpliedpermissionsofALTERSCHEMAWhenyouconsiderthenumberandtypesofprincipalsavailable,thenumberofsecurableobjectsintheserverandatypicaldatabase,andthesheernumberofavailablepermissionsandthecoveredandimpliedpermissions,itquicklybecomesclearjusthowgranularpermissionscanbeinSQLServer2008.
Creatingadatabasenowrequiresamuchmoredetailedanalysisofitssecurityneedsandcarefulcontrolofpermissionsonallobjects.
Nevertheless,thisanalysisiswellworthitandusingthecapabilitiesinSQLServer2008resultsinmoresecuredatabases.
MetadataSecurityOnebenefitofthegranularpermissionschemeisthatSQLServerprotectsmetadataaswellasdata.
PriortoSQLServer2005,auserwithanyaccesstoadatabasecouldseethemetadataofallobjectswithinthedatabase,regardlessofwhethertheusercouldaccessthedatawithinitorexecuteastoredprocedure.
SQLServer2008examinesthepermissionsaprincipalhaswithinthedatabaseandrevealsthemetadataofanobjectonlyiftheprincipalistheownerorhassomepermissionontheobject.
ThereisalsoaVIEWDEFINITIONpermissionthatcangrantpermissiontoviewmetadatainformationevenwithoutotherpermissionsintheobject.
Thisprotectionextendstoerrormessagesreturnedfromoperationstoaccessorupdateanobjecttowhichtheuserhasnoaccess.
RatherthanacknowledgingthatthereisindeedatablenamedAddress,andgivinganattackerconfirmationthatheorsheisontrack,SQLServerreturnsanerrormessagewithalternatepossibilities.
Forexample,ifauserwithnopermissionsonanyobjectsinthedatabaseattemptstodroptheAddresstable,SQLServerdisplaysthefollowingerrormessage:Msg3701,Level14,State20,Line1Cannotdropthetable'Address',becauseitdoesnotexistoryoudonothavepermission.
Thisway,anattackergetsnoconfirmationthatanAddresstableactuallyexists.
However,someonedebuggingthisproblemstillonlyhasalimitednumberofpossibilitiestoexplore.
SQLServerAgentProxiesOneofthebestexamplesoftheauthorizationmodelinSQLServer2008isSQLServerAgent.
YoucandefinevariouscredentialsoftenassociatedwithWindowslogins,linkedtouserswiththenecessarypermissionstoperformoneormoreSQLServerAgentsteps.
ASQLServerAgentproxythenlinksacredentialwithajobsteptoprovidethenecessarypermissions.
Thisprovidesagranularmeansoffollowingtheprincipleofleastprivilege:grantingajobstepthepermissionsitneedsandnomore.
Youcancreateasmanyproxiesasyouwish,associatingeachofthemwithoneormoreSQLServerAgentsubsystems.
Thisisinstarkcontrasttotheall-powerfulproxyaccountinSQLServer2000,whichlettheusercreatejobstepsinanyoftheSQLServerAgentsubsystems.
NoteWhenyouupgradeaserverfromSQLServer2000,asingleproxyaccountiscreatedandallsubsystemsareassignedtothatsingleproxyaccountsothatexistingjobswillcontinuetorun.
Afterupgrading,createcredentialsandproxyaccountstoimplementamoresecure,granularsetofproxiestoprotectserverresources.
Figure3showstheObjectExplorerinManagementStudiowithalistofsubsystemsavailableinSQLServerAgent.
Eachsubsystemcanhaveoneormoreproxiesassociatedwithitthatgranttheappropriatepermissionsforajobstep.
TheoneexceptionisthatTransact-SQLsubsystemsexecutewiththepermissionsofthemoduleownerastheydidinSQLServer2000.
Figure3:SQLServerAgentsubsystemsyoucanassociatewithproxiesUponafreshinstallationofSQLServer,onlytheSystemAdministratorrolehaspermissionstomaintainSQLServerAgentjobs,andthemanagementpaneintheManagementStudioObjectExplorerisonlyavailabletosysadmins.
SQLServer2008makesavailableafewotherrolesyoucanusetograntvariouslevelsofpermissions.
YoucanassignuserstotheSQLAgentUser,SQLAgentReaderRole,orSQLAgentOperatorroles,eachofwhichgrantsincreasinglevelsofpermissiontocreate,manage,andrunjobs,ortheMaintenanceUserrole,whichhasallthepermissionsofSQLAgentUserplustheabilitytocreatemaintenanceplans.
Membersofthesysadminrole,ofcourse,candoanythingtheywantinanysubsystem.
Tograntanyotheruserrightstousesubsystemsrequiresthecreationofatleastoneproxyaccount,whichcangrantrightstooneormoresubsystems.
Figure4showshowaproxyaccount,MyProxy,isassignedtomultipleprincipals—hereauserandarole.
Theproxyaccountusesacredential,whichlinksittoanaccount,usuallyadomainaccount,withpermissionsintheoperatingsystemnecessarytoperformwhatevertasksarerequiredbythesubsystem.
Eachproxycanhaveoneormoresubsystemsassociatedwithitthatgranttheprincipaltheabilitytorunthosesubsystems.
Figure4:SQLServerAgentproxyaccountforvarioussubsystemsThefollowingcodeshowstheTransact-SQLcodenecessarytoimplementtheschemeshowninthefigure.
Itstartsbycreatingacredential,adatabaseobjectthatprovidesthelinktotheoperatingsystemaccountwithrightstoperformthedesiredactionsinthesubsystems.
Thenitaddsaproxyaccount,MyProxy,whichisreallyjustafriendlynameforthecredential.
Next,itassignstheproxytotwoprincipals,hereaSQLServerloginandacustomrole.
FinallyitassociatestheproxywitheachofthefourSQLServerAgentsubsystems.
CREATECREDENTIALMyCredentialWITHIDENTITY='MyDOMAIN\user1'GOmsdb.
.
sp_add_proxy@proxy_name='MyProxy',@credential_name='MyCredential'GOmsdb.
.
sp_grant_login_to_proxy@login_name='MyLogin',@proxy_name='MyProxy'GOmsdb.
.
sp_grant_login_to_proxy@login_name='MyRole',@proxy_name='MyProxy'GOsp_grant_proxy_to_subsystem@proxy_name='MyProxy',@subsystem_name='ActiveScripting'GOsp_grant_proxy_to_subsystem@proxy_name='MyProxy',@subsystem_name='CmdExec'GOsp_grant_proxy_to_subsystem@proxy_name='MyProxy',@subsystem_name='ANALYSISQUERY'GOsp_grant_proxy_to_subsystem@proxy_name='MyProxy',@subsystem_name='DTS'GOSQLServerManagementStudioprovidesfullsupportforcreatingcredentialsandproxiesasshowninFigure5.
Thiscreatesthesameproxyasthepreviouscode.
Figure5:AnewSQLServerAgentproxyinSQLServerManagementStudioAproxyisnotawaytocircumventsecurityintheoperatingsystem.
Ifthecredentialusedwithaproxydoesn'thavethepermissioninWindows,suchastowritetoadirectoryacrossthenetwork,theproxywon'thaveiteither.
Youcanalsouseaproxytograntlimitedexecutionrightstoxp_cmdshell,sinceitisafavoritetoolusedbyattackerstoextendtheirreachintothenetworkoncetheycompromiseaSQLServercomputer.
Theproxyprovidesthisprotectionbecauseeveniftheprincipalhasunlimitedrightsonthenetwork,suchasadomainadministrator,anycommandsexecutedthroughtheproxyhaveonlythelimitedrightsofthecredentialaccount.
ExecutionContextSQLServerhaslongsupportedtheconceptofownershipchainingasawayofensuringthatadministratorsandapplicationdevelopershaveawaytocheckpermissionsupfrontontheentrypointstothedatabaseratherthanbeingrequiredtoprovisionpermissionsonallobjectsaccessed.
Aslongastheusercallingthemodule(storedprocedureorfunction)orviewhadexecutepermissionsonthemodule,orselectpermissionsontheview,andtheownerofthemodule,orview,wastheowneroftheobjectsaccessed(anownershipchain),nopermissionswerecheckedontheunderlyingobjects,andthecallerreceivedthedatarequested.
Iftheownershipchainwasbrokenbecausetheownerofthecodedidnotownthereferencedobject,SQLServercheckedthepermissionsagainstthecaller'ssecuritycontext.
Ifthecallerhadpermissiontoaccesstheobject,SQLServerreturnedthedata.
Ifheorshedidnot,SQLServerraisedanerror.
Ownershipchaininghassomelimitations;itappliesonlytodatamanipulationoperationsandnottodynamicSQL.
Moreover,ifyouaccessobjectsacrossownershipboundaries,ownershipchainingisnotpossible.
Hence,thisupfrontpermissionscheckingbehavioronlyworksforcertaincases.
SQLServer2008includestheabilitytomarkmoduleswithanexecutioncontext,suchthatthestatementswithinthemodulecanexecuteasaparticularuserasopposedtothecallinguser.
Thisway,whilethecallinguserstillneedspermissionstoexecutethemodule,SQLServerchecksthepermissionsforstatementswithinthemoduleagainsttheexecutioncontextofthemodule.
Youcanusethisbehaviortoovercomesomeoftheshortcomingsofownershipchainingbecauseitappliestoallstatementswithinthemodule.
Administratorswantingtoperformupfrontpermissioncheckingcanusetheexecutioncontexttodothat.
Nowwhenyoudefineuser-definedfunctions(exceptinlinetable-valued),storedprocedures,andtriggersyoucanusetheEXECUTEASclausetospecifywhichuser'spermissionsSQLServerusestovalidateaccesstoobjectsanddatareferencedbytheprocedure:CREATEPROCEDUREGetData(@Tablevarchar(40))WITHEXECUTEAS'User1'SQLServer2008providesfourEXECUTEASoptions.
EXECUTEASCALLERspecifiesthatthecodeexecutesinthesecuritycontextofthecallerofthemodule;noimpersonationoccurs.
Thecallermusthaveaccesspermissionsonalloftheobjectsreferenced.
However,SQLServeronlycheckspermissionsforbrokenownershipchains,soiftheownerofthecodealsoownstheunderlyingobjects,onlytheexecutepermissionofthemoduleischecked.
Thisisthedefaultexecutioncontextforbackwardcompatibility.
EXECUTEAS'user_name'specifiesthatthecodeexecutesinthesecuritycontextofthespecifieduser.
Thisisagreatoptionifyoudonotwanttorelyonownershipchaining.
Instead,youcreateauserwiththenecessarypermissionstorunthecodeandcreatecustompermissionsets.
EXECUTEASSELFisashortcutnotationforspecifyingthesecuritycontextoftheuserwhoiscreatingoralteringthemodule.
SQLServerinternallysavestheactualusernameassociatedwiththemoduleratherthan"SELF.
"EXECUTEASOWNERspecifiesthatthesecuritycontextisthatofthecurrentownerofthemoduleatthetimeofmoduleexecution.
Ifthemodulehasnoownerthecontextofthecontainingschema'sownerisused.
Thisisagreatoptionwhenyouwanttobeabletochangethemodule'sownerwithoutchangingthemoduleitself.
AnytimetheusercontextchangesusingtheEXECUTEASoption,thecreatororaltererofthemodulemusthaveIMPERSONATEpermissionsforthespecifieduser.
Youcannotdropthespecifieduserfromthedatabaseuntilyouhavechangedtheexecutioncontextofallmodulestootherusers.
User/SchemaSeparationSQLServer2000hadnoconceptofaschema,whichtheANSISQL-99specificationdefinesasacollectionofdatabaseobjectsownedbyasingleprincipalthatformsasinglenamespaceofobjects.
Aschemaisacontainerfordatabaseobjectssuchastables,views,storedprocedures,functions,types,andtriggers.
Itfunctionsmuchasanamespacefunctionsinthe.
NETFrameworkandXML,awaytogroupobjectssothatadatabasecanreuseobjectnames,suchasallowingbothdbo.
CustomerandFred.
Customertoexistinasingledatabase,andtogroupobjectsunderdifferentowners.
NoteYouwillneedtousecatalogviewssuchassys.
database_sys.
principals,sys.
schemas,sys.
objects,andsoforth.
Thisisbecausetheoldsysobjectssystemtabledidnotsupportschemas,andsowasincapableofsupportingU/Sseparation.
Besides,theoldcatalogviewsaredeprecated,sotheywillbedroppedinafutureversionofSQLServer.
ThetopportionofFigure6showshowschemasworkedinSQLServer2000.
WhenanadministratorcreatedausernamedAliceinadatabase,SQLServerautomaticallycreatedaschemanamedthathidbehindAlicetheuser.
IfloggedontoaserverrunningSQLServerwithoutdatabaseownershipandcreatedTable1,theactualnameofthetablewasAlice.
Table1.
Thesameheldforotherobjectscreated,suchasAlice.
StoredProcedure1andAlice.
View1.
Ifisadatabaseownerorasysadmin,theobjectsshecreateswouldbepartofthedboschemainstead.
Althoughweusedtosaythatdboownedtheobjects,itamountstothesamething.
Figure6:User/schema/objectsinSQLServer2000and2008TheproblemwiththeunificationofusersandschemasinSQLServer2000ariseswhenyouneedtochangetheownershipofobjects,suchaswhenleavesthecompanyandLucindatakesover'sjob.
AsystemadministratorwouldhavetochangeownershipofalloftheobjectsownedbytoLucinda.
MoreofaproblemisthatyouwouldhavetochangeanyTransact-SQLorclientapplicationcodethatreferredtoAlice.
Table1toLucinda.
Table1afterLucindatookownershipofthetable.
Dependingonthenumberofobjectsownsandhowmanyapplicationshadthenameembeddedinthem;thiscouldbeamajorundertaking.
Microsofthaslongrecommendedthatthebuilt-indbouserownsalldatabaseobjectstogetaroundtheseproblems.
Itwasfareasiertochangeadatabase'sownershipthantochangemanyobjectsandclientapplications.
NoteDonotbeconfusedbytheSQLServer2000CREATESCHEMAstatement,whichwasjustaneasywaytocreatetablesandviewsownedbyaparticularuserandtograntpermissions.
Youcouldusethestatementtonameaschema'sownerbutnottonametheschema.
SQLServerstillirrevocablylinkedtheownertotheschemawithalltheproblemsofchangingownership.
SQLServer2008cleansthisupandimplementstheSQL-99schemabyseparatingtheuserfromtheschemaasshowninthebottompartofFigure56.
WhenyoucreateanewuserAliceusingthenewCREATEUSERDDL,SQLServernolongerautomaticallycreatesaschemawiththesamename.
Instead,youmustexplicitlycreateaschemaandassignownershipofittoauser.
BecauseallofthedatabaseobjectsshownarenowcontainedintheSchema1schema,whichinitiallyowns,itbecomessimpletochangeownershipofalltheschema'sobjectsbysimplychangingtheownershipoftheschematoLucinda.
Eachusercanalsohaveadefaultschemaassignedtoit,sothatSQLServerassumesanyobjectsreferencedbynamewithouttheschemareferencetobeinthedefaultschema.
InthebottompartofFigure5,ifhasSchema1asherdefaultschema,shecanrefertothetableaseitherSchema1.
Table1orsimplyasTable1.
UserCarol,whoperhapsdoesnothaveadefaultschemaassociatedwithherusername,wouldhavetorefertothetableasSchema1.
Table1.
Anyuserwithoutadefaultschemadefinedhasdboasthedefault.
FullyqualifiedobjectnamesinSQLServer2008haveafour-partstructure,similartothoseinearlierversionsofSQLServer:server.
database.
schema.
objectAsinearlierversions,youcanomittheservernameiftheobjectisonthesameserverasthatwherethecodeisrunning.
Youcanomitthedatabasenameiftheconnectionhasthesamedatabaseopen,andyoucanomittheschemanameifitiseitherthedefaultschemaforthecurrentuserorisownedbydbo,sincethatistheschemaoflastresortasSQLServertriestodisambiguateanobjectname.
UsetheCREATEUSERstatement,insteadofsp_adduser,tocreatenewusers.
Thissystemstoredprocedureisstillaroundforbackwardcompatibilityandhasbeenchangedabittoconformtothenewseparationofusersfromschemas.
sp_addusercreatesaschemawiththesamenameasthenewusernameortheapplicationroleandassignstheschemaasthedefaultschemafortheuser,mimickingSQLServer2000behaviorbutprovidingaseparateschema.
NoteWhenusingtheALTERAUTHORIZATIONstatement,itispossibletoarriveinastatewhereYOUownatableinMYschema(orviceversa).
Thishassomeseriousimplications.
Forexample,whoownsthetriggeronthattable,youormeThebottomlineisthatitcannowbeverytrickytodiscoverthetrueownerofaschema-scopedobjectortype.
Therearetwowaystogetaroundthis:UseOBJECTPROPERTY(id,'OwnerId')todiscoverthetrueownerofanobject.
UseTYPEPROPERTY(type,'OwnerId')todiscoverthetrueownerofatype.
SQLServer2008canhelpsavekeystrokeswithsynonyms.
Youcancreateasynonymforanyobjectusingthetwo,three,orfour-partfullobjectname.
SQLServerusesthesynonymtoaccessthedefinedobject.
Inthefollowingcode,theHistorysynonymrepresentsthespecifiedschema.
tableintheAdventureWorksdatabase.
TheSELECTstatementreturnsthecontentsoftheEmployeeDepartmentHistorytable.
USEAdventureWorksGOCREATESYNONYMHistoryFORHumanResources.
EmployeeDepartmentHistorySELECT*FROMHistoryNoteTheadministratororownermustgrantpermissiononthesynonymifsomeoneelseistouseit.
GRANTSELECTonasynonymtoaviewortableortable-valuedfunction.
GRANTEXECUTEonasynonymtoaprocedureorscalarfunction,etc.
YoucouldalsodefinetheHistorysynonymforthecomplete,four-partnameasinthefollowingcode:CREATESYNONYMHistoryFORMyServer.
AdventureWorks.
HumanResources.
EmployeeDepartmentHistoryUsingthefull,four-partnamelikethisallowstheuseofthesynonymfromanotherdatabasecontext,assumingthecurrentuserhaspermissionstousethesynonymandreadthetable:USEpubsSELECT*FROMAdventureWorks.
.
HistoryNotetoothatifyoudonotprovideaschemanameaspartofthenewsynonymname,itwillbepartofthedefaultschema.
EncryptionandKeyManagementSecurityattheserverlevelisprobablythegreatestconcernforsystemadministrators,butthedatabaseiswherealltheactionisinaproductionenvironment.
Forthemostpart,adatabaseadministratorcanletthedatabasedeveloperworryaboutthedetailsinthedatabase,aslongasthedeveloperworkswithintheconstraintsoftheenvironment.
SQLServer2008providesplentyoffeaturesforsecuringthedatabase.
DataEncryptionSQLServer2000andearlierversionsdidnothavebuilt-insupportforencryptingthedatastoredinadatabase.
Whywouldyouneedtoencryptdatathatisstoredinawell-secureddatabaseonasecureservernestledsafelybehindstate-of-the-artfirewallsBecauseofanimportant,age-oldsecurityprincipalcalleddefenseindepth.
Defenseindepthmeanslayeringdefensessothatevenifattackerssuccessfullypierceyouroutermostdefensestheystillmustgetthroughlayerafterlayerofdefensetogettotheprize.
Inadatabase,itmeansthatifanattackergetsthroughthefirewallandthroughWindowssecurityontheservertothedatabase,heorshestillhastodosomebruteforcehackingtodecryptyourdata.
Inaddition,inthesedaysoflegislateddataandprivacyprotection,datamusthavestrongprotection.
SQLServer2008hasrichsupportforvarioustypesofdataencryptionusingsymmetricandasymmetrickeys,anddigitalcertificates.
Bestofall,ittakescareofmanagingthekeysforyou,sincekeymanagementisbyfarthehardestpartofencryption.
Keepingsecretssecretisnevereasy.
Asanadministrator,youwillprobablyneedtomanageatleasttheupperlevelofkeysinthehierarchyshowninFigure7.
Databaseadministratorsmustunderstandtheservicemasterkeyattheserverlevelandthedatabasemasterkeyatthedatabaselevel.
Eachkeyprotectsitschildkeys,whichinturnprotecttheirchildkeys,downthroughthetree.
Theoneexceptioniswhereapasswordprotectsasymmetrickeyorcertificate,whichishowSQLServerletsusersmanagetheirownkeysandtakeresponsibilityforkeepingthekeysecret.
Figure7:EncryptionkeyhierarchyinSQLServer2008NoteMicrosoftrecommendsagainstusingcertificatesorasymmetrickeysforencryptingdatadirectly.
Asymmetrickeyencryptionismanytimesslowerandtheamountofdatathatyoucanprotectusingthismechanismislimited,dependingonthekeymodulus.
Youcanprotectcertificatesandasymmetrickeysusingapasswordinsteadofbythedatabasemasterkey.
Theservicemasterkeyistheonekeythatrulesthemall,allthekeysandcertificatesinSQLServer.
ItisasymmetrickeythatSQLServercreatesautomaticallyduringinstallation.
ItisobviouslyacriticalsecretbecauseifitiscompromisedanattackercaneventuallydeciphereverykeyintheserverthatismanagedbySQLServer.
TheDataProtectionAPI(DPAPI)inWindowsprotectstheservicemasterkey.
SQLServermanagestheservicemasterkeyforyou,althoughyoucanperformmaintenancetasksonittodumpittoafile,regenerateit,andrestoreitfromafile.
However,mostofthetimeyouwillnotneedorwanttomakeanyofthesechangestothekey.
Itisadvisableforadministratorstobackuptheirservicemasterkeysincaseofkeycorruption.
Withinthescopeofadatabase,thedatabasemasterkeyistherootencryptionobjectforallkeys,certificates,anddatainthedatabase.
Eachdatabasecanhaveasinglemasterkey;youwillgetanerrorifyoutrytocreateasecondkey.
YoumustcreateadatabasemasterkeybeforeusingitbyusingtheCREATEMASTERKEYTransact-SQLstatementwithauser-suppliedpassword:CREATEMASTERKEYENCRYPTIONBYPASSWORD='EOhnDGS6!
7JKv'SQLServerencryptsthekeywithatripleDESkeyderivedfromthepasswordaswellastheservicemasterkey.
Thefirstcopyisstoredinthedatabasewhilethesecondisstoredinthemasterdatabase.
HavingthedatabasemasterkeyprotectedbythedatabasemasterkeymakesitpossibleforSQLServertodecryptthedatabasemasterkeyautomaticallywhenrequired.
Theendapplicationoruserdoesnotneedtoopenthemasterkeyexplicitlyusingthepasswordandisamajorbenefitofhavingthekeysprotectedinthehierarchy.
Detachingadatabasewithanexistingmasterkeyandmovingittoanotherservercanbeanissue.
Theproblemisthatthenewserver'sdatabasemasterkeyisdifferentfromthatoftheoldserver.
Asaresult,theservercannotautomaticallydecryptthedatabasemasterkey.
ThiscanbecircumventedbyopeningthedatabasemasterkeywiththepasswordwithwhichitisencryptedandusingtheALTERMASTERKEYstatementtoencryptitbythenewdatabasemasterkey.
Otherwise,youalwayshavetoopenthedatabasemasterkeyexplicitlybeforeuse.
Oncethedatabasemasterkeyexists,developerscanuseittocreateanyofthreetypesofkeys,dependingonthetypeofencryptionrequired:Asymmetrickeys,usedforpublickeycryptographywithapublicandprivatekeypairSymmetrickeys,usedforsharedsecretswherethesamekeybothencryptsanddecryptsdataCertificates,essentiallywrappersforapublickeyWithalltheencryptionoptionsanditsdeepintegrationintotheserveranddatabase,encryptionisnowaviablewaytoaddafinallayerofdefensetoyourdata.
Nevertheless,usethetooljudiciouslybecauseencryptionaddsalotofprocessingoverheadtoyourserver.
TransparentDataEncryptionInSQLServer2005,youcanencryptdatainthedatabasebywritingcustomTransact-SQLthatusesthecryptographiccapabilitiesofthedatabaseengine.
SQLServer2008improvesuponthissituationbyintroducingtransparentdataencryption.
Transparentdataencryptionperformsallofthecryptographicoperationsatthedatabaselevel,whichremovesanyneedforapplicationdeveloperstocreatecustomcodetoencryptanddecryptdata.
Dataisencryptedasitiswrittentodisk,anddecryptedasitisreadfromdisk.
ByusingSQLServertomanageencryptionanddecryptiontransparently,youcansecurebusinessdatainthedatabasewithoutrequiringanychangestoexistingapplications,asshowninFigure8.
Figure8:TransparentdataencryptionADatabaseEncryptionKey(DEK)isusedtoperformtheencryptionanddecryption,andthisDEKisstoredinthedatabasebootrecordforavailabilityduringrecoveryscenarios.
YoucanuseaservicemasterkeyorHardwareSecurityModule(HSM)toprotecttheDEK.
HSMsareusuallyUSBdevicesorsmartcardsandarethereforelesslikelytobestolenorlost.
ExtensibleKeyManagementWiththegrowingdemandforregulatorycomplianceandtheoverallconcernfordataprivacy,moreorganizationsareusingencryptionasawaytoprovideadefense-in-depthsolution.
Asorganizationsincreasinglyuseencryptionandkeystosecuretheirdata,keymanagementbecomesmorecomplex.
Somehighsecuritydatabasesusethousandsofkeysandyoumustemployasystemtostore,retire,andregeneratethesekeys.
Furthermore,youshouldstorethesekeysseparatelyfromthedatatoimprovesecurity.
SQLServer2008exposesencryptionfunctionalityforusebythirdpartyvendors.
ThesesolutionsworkseamlesslywithSQLServer2005andSQLServer2008databasesandprovideenterprise-widededicatedkeymanagement.
ThismovesthekeymanagementworkloadfromSQLServertoadedicatedkeymanagementsystem.
ExtensiblekeymanagementinSQLServer2008alsosupportstheuseofHSMstoprovidethephysicalseparationofkeysfromdata.
CodeModuleSigningOneofthenicebenefitsofhavingencryptionwithinSQLServeristhatitprovidestheabilitytosigncodemodulesdigitally(storedprocedures,functions,triggers,andeventnotifications)withcertificates.
Thisprovidesmuchmoregranularcontroloveraccesstodatabasetablesandotherobjects.
Likeencryptingdata,yousignthecodewiththeprivatekeycontainedwithinthecertificate.
Theresultisthatthetablesusedinthesignedcodemoduleareaccessibleonlythroughthecodeandnotallowedoutsideofthecodemodule.
Inotherwords,accesstothetablesisonlyavailableusingthecertificatesthathavebeenusedtosignthemodule.
Theeffectcanbethesamewithastoredprocedure.
Forexample,ifithasanunbrokenownershipchain,youcarefullycontrolwhichusersgetEXECUTEpermissionontheprocedure,andyoudenydirectaccesstotheunderlyingtables.
Butthisdoesn'thelpinsituationssuchaswhentheprocedurehasabrokenownershipchainorexecutesdynamicSQL,requiringthattheuserexecutingtheprocedurehavepermissionstotheunderlyingtables.
AnotherwaytoachievethesameeffectistouseEXECUTEAS,butthischangesthesecuritycontextunderwhichtheprocedureexecutes.
Thismaynotbedesirable,forexample,ifyouneedtorecordinthetabletheuserwhoactuallycausedtheproceduretorun(shortofrequiringausernameasaparametertotheprocedure).
Signingcodemoduleshastheadditionalbenefitofprotectingagainstunauthorizedchangestothecodemodule.
Likeotherdocumentsthataredigitallysigned,thecertificateisinvalidatedwhenthecodechanges.
Thecodedoesn'texecuteunderthecontextofthecertificate,soanyobjectsthathavetheiraccessprovisionedtothecertificatewillnotbeaccessible.
Todothis,youcreateacertificate,associateitwithanewuser,andsigntheprocedurewiththecertificate.
Grantthisuserwhateverpermissionsarenecessarytoexecutethestoredprocedure.
Inessence,youhaveaddedthisusertothesecuritycontextofthestoredprocedureasasecondaryidentity.
Thengrantexecutepermissionstowhateverusersorrolesneedtoexecutetheprocedure.
Thefollowingcodeshowsthesesteps.
AssumethatyouwanttosignthemySchema.
GetSecretStuffprocedure,andthatallofthereferencedobjectsalreadyexistinthedatabase:CREATECERTIFICATEcertCodeSigningENCRYPTIONBYPASSWORD='cJI%V4!
axnJXfLC'WITHSUBJECT='Codesigningcertificate'GO--SignthestoredprocedureADDSIGNATURETOmySchema.
GetSecretStuffBYCERTIFICATEcertCodeSigningWITHPASSWORD='cJI%V4!
axnJXfLC'GO--MapausertothecertificateCREATEUSERcertUserFORCERTIFICATEcertCodeSigningGO--AssignSELECTpermissionstonewcertUserGRANTSELECTONSocialSecurityTOcertUserGO--GrantexecutepermissiontotheuserwhowillrunthecodeGRANTEXECUTEONmySchema.
GetSecretStuffTOProcedureUserGONowonlyusersexplicitlygrantedEXECUTEpermissiononthestoredprocedureareabletoaccessthetable'sdata.
AuditinginSQLServer2008Animportantpartofanysecuritysolutionistheabilitytoauditactionsforaccountabilityandregulatorycompliancereasons.
SQLServer2008includesanumberoffeaturesthatmakeitpossibletoauditactivity.
AllActionAuditSQLServer2008includesauditingsupportthroughtheAuditobject,whichenablesadministratorstocaptureactivityinthedatabaseserverandstoreitinalog.
WithSQLServer2008,youcanstoreauditinformationinthefollowingdestinations:FileWindowsApplicationLogWindowsSecurityLogTowritetotheWindowsSecurityLog,theSQLServerservicemustbeconfiguredtorunasLocalSystem,LocalService,NetworkService,oradomainaccountthathastheSeAuditPrivilegeprivilegeandthatisnotaninteractiveuser.
TocreateanAuditobject,youmustusetheCREATESERVERAUDITstatement.
ThisstatementdefinesanAuditobject,andassociatesitwithadestination.
ThespecificoptionsusedtoconfigureanAuditobjectdependontheauditdestination.
Forexample,thefollowingTransact-SQLcodecreatestwoAuditobjects;onetologactivitytoafile,andtheothertologactivitytotheWindowsApplicationlog.
CREATESERVERAUDITHIPAA_File_AuditTOFILE(FILEPATH='\\SQLPROD_1\Audit\');CREATESERVERAUDITHIPAA_AppLog_AuditTOAPPLICATION_LOGWITH(QUEUE_DELAY=500,ON_FAILURE=SHUTDOWN);Notethatwhenloggingtoafiledestination,thefilenameisnotspecifiedintheCREATESERVERAUDITstatement.
AuditfilenamestaketheformAuditName_AuditGUID_nn_TS.
sqlauditwhereAuditNameisthenameoftheAuditobject,AuditGUIDisauniqueidentifierassociatedwiththeAuditobject,nnisapartitionnumberusedtopartitionfilesets,andTSisatimestampvalue.
Forexample,theHIPAA_FILE_AuditAuditobjectcreatedbythepreviouscodesamplecouldgeneratealogfilewithanamesimilartothefollowing:HIPAA_File_Audit_{95A481F8-DEF3-40ad-B3C6-126B68257223}_00_29384.
sqlauditYoucanusetheQUEUE_DELAYauditoptiontoimplementasynchronousauditingforperformancereasons,andtheON_FAILUREoptiondeterminestheactiontobetakeniftheauditinformationcannotbewrittentothedestination.
InthepreviouslyshownHIPAA_AppLog_Auditexample,theON_FAILUREoptionisconfiguredtoshutdowntheSQLServerinstanceifthelogcannotbewrittento;inthiscase,theuserwhoexecutestheCREATESERVERAUDITstatementmusthaveSHUTDOWNpermission.
AfteryoucreateanAuditobject,youcanaddeventswithitbyusingtheCREATESERVERAUDITSPECIFICATIONandCREATEDATABASEAUDITSPECIFICATIONstatements.
TheCREATESERVERAUDITSPECIFICATIONaddsserver-levelactiongroups(thatis,pre-definedsetsofrelatedactionsthatcanoccurattheserverlevel)toanAudit.
Forexample,thefollowingcodeaddstheFAILED_LOGIN_GROUPactiongroup(whichrecordsfailedloginattempts)totheHIPAA_File_AuditAudit.
CREATESERVERAUDITSPECIFICATIONFailed_Login_SpecFORSERVERAUDITHIPAA_File_AuditADD(FAILED_LOGIN_GROUP);TheCREATEDATABASEAUDITSPECIFICATIONstatementaddsdatabase-levelactiongroupsandindividualdatabaseeventstoanAudit.
Addingindividualactionsenablesyoutofiltertheactionsthatareloggedbasedontheobjectsandusersinvolvedintheaction.
Forexample,thefollowingcodesampleaddstheDATABASE_OBJECT_CHANGE_GROUPactiongroup(whichrecordsanyCREATE,ALTER,orDROPoperationsinthedatabase)andanyINSERT,UPDATE,orDELETEstatementperformedonobjectsintheSalesschemabytheSalesUserorSalesAdminuserstotheHIPAA_AppLog_AuditAudit.
CREATEDATABASEAUDITSPECIFICATIONSales_Audit_SpecFORSERVERAUDITHIPAA_AppLog_AuditADD(DATABASE_OBJECT_CHANGE_GROUP),ADD(INSERT,UPDATE,DELETEONSchema::SalesBYSalesUser,SalesAdmin);TheAuditobjectprovidesamanageableauditingframeworkthatmakesiteasytodefinetheeventsthatshouldbeloggedandthelocationswherethelogshouldbestored.
ThisadditiontoSQLServerhelpsyoutoimplementacomprehensiveauditingsolutiontosecureyourdatabaseandmeetregulatorycompliancerequirements.
DDLTriggersDDLtriggerswereintroducedinSQLServer2005.
UnlikeDMLtriggersthatexecuteTransact-SQLcodewhendatainatablechanges,aDDLtriggerfireswhenthestructureofthetablechanges.
Thisisagreatwaytotrackandauditstructuralchangestoadatabaseschema.
ThesyntaxforthesetriggersissimilartothatofDMLtriggers.
DDLtriggersareAFTERtriggersthatfireinresponsetoDDLlanguageevents;theydonotfireinresponsetosystem-storedproceduresthatperformDDL-likeoperations.
Theyarefullytransactional,andsoyoucanROLLBACKaDDLchange.
YoucanruneitherTransact-SQLorCLRcodeinaDDLtrigger.
DDLtriggersalsosupporttheEXECUTEASclausesimilartoothermodules.
SQLServerprovidestheinformationaboutthetriggereventasuntypedXML.
Itisavailablethroughanew,XML-emittingbuilt-infunctioncalledEVENTDATA().
YoucanuseXQueryexpressionstoparsetheEVENTDATA()XMLinordertodiscovereventattributeslikeschemaname,targetobjectname,username,aswellastheentireTransact-SQLDDLstatementthatcausedthetriggertofireinthefirstplace.
Forexamples,seeEVENTDATA(Transact-SQL)inSQLServerBooksOnline.
Database-levelDDLtriggersfireonDDLlanguageeventsatthedatabaselevelandbelow.
ExamplesareCREATE_TABLE,ALTER_USER,andsoon.
Server-levelDDLtriggersfireonDDLlanguageeventsattheserverlevel,forexampleCREATE_DATABASE,ALTER_LOGIN,etc.
Asanadministrativeconvenience,youcanuseeventgroupslikeDDL_TABLE_EVENTSasshorthandtorefertoallCREATE_TABLE,ALTER_TABLE,andDROP_TABLEevents.
ThevariousDDLeventgroupsandeventtypes,andtheirassociatedXMLEVENTDATA(),aredocumentedinSQLServerBooksOnline.
UnlikeDMLtriggernames,whichareschema-scoped,DDLtriggernamesaredatabasescopedorserver-scoped.
UsethisnewcatalogviewtodiscovertriggermetadataforDMLtriggersanddatabase-levelDDLtriggers:SELECT*FROMsys.
triggers;GOIftheparent_class_desccolumnhasavalueof'DATABASE,'itisaDDLtriggerandthenameisscopedbythedatabaseitself.
ThebodyofaTransact-SQLtriggerisfoundinthesys.
sql_modulescatalogview,andyoucanJOINittosys.
triggersontheobject_idcolumn.
ThemetadataaboutaCLRtriggerisfoundinthesys.
assembly_modulescatalogview,andagain,youcanJOINtosys.
triggersontheobject_idcolumn.
Usethiscatalogviewtodiscovermetadataforserver-scopedDDLtriggers:SELECT*FROMsys.
server_triggers;GOThebodyofaTransact-SQLserver-leveltriggerisfoundinthesys.
server_sql_modulescatalogview,andyoucanJOINittosys.
server_triggersontheobject_idcolumn.
ThemetadataaboutaCLRserver-leveltriggerisfoundinthesys.
server_assembly_modulescatalogview,andagain,youcanJOINtosys.
server_triggersontheobject_idcolumn.
YoucanuseDDLtriggerstocaptureandauditDDLactivityinadatabase.
CreateanaudittablewithanuntypedXMLcolumn.
CreateanEXECUTEASSELFDDLtriggerfortheDDLeventsoreventgroupsyouareinterestedin.
ThebodyoftheDDLtriggercansimplyINSERTtheEVENTDATA()XMLintotheaudittable.
AnotherinterestinguseofDDLtriggersistofireontheCREATE_USERevent,andthenaddcodetoautomatepermissionsmanagement.
Forexample,youwantalldatabaseuserstogetaGRANTEXECUTEonproceduresP1,P2,andP3.
TheDDLtriggercanextracttheusernamefromtheEVENTDATA()XML,dynamicallyformulateastatementlike'GRANTEXECUTEONP1TOsomeuser',andthenEXEC()it.
ConclusionSQLServer2008providesrichsecurityfeaturestoprotectdataandnetworkresources.
Itismucheasiertoinstallsecurely,sinceallbutthemostessentialfeaturesareeithernotinstalledbydefaultordisablediftheyareinstalled.
SQLServerprovidesplentyoftoolstoconfiguretheserver,particularlyforSQLServerSurfaceAreaConfiguration.
ItsauthenticationfeaturesarestrongerbecauseSQLServermorecloselyintegrateswithWindowsauthenticationandprotectsagainstweakorancientpasswords.
Grantingandcontrollingwhatausercandowhenauthenticatedisfarmoreflexiblewithgranularpermissions,SQLServerAgentproxies,andexecutioncontext.
Evenmetadataismoresecure,sincethesystemmetadataviewsreturninformationonlyaboutobjectsthattheuserhaspermissiontouseinsomeway.
Atthedatabaselevel,encryptionprovidesafinallayerofdefensewhiletheseparationofusersandschemasmakesmanaginguserseasier.
Formoreinformation:MicrosoftSQLServer2008http://www.
microsoft.
com/sqlserver/2008/en/us/default.
aspxPleasegiveusyourfeedback:DidthispaperhelpyouTellusonascaleof1(poor)to5(excellent),howwouldyouratethispaperandwhyhaveyougivenitthisratingForexample:Areyougivingitahighratingbecauseithasgoodexamples,excellentscreenshots,clearwriting,oranotherreasonAreyougivingitalowratingbecauseithaspoorexamples,fuzzyscreenshots,unclearwritingThisfeedbackwillhelpusimprovethequalityofwhitepaperswerelease.
Sendfeedback.