SQL Server Security Distilled

362 Pages·2004·14.194 MB·English

by  Morris Lewis (auth.)

SQL Server Security
Distilled
Second Edition
MORRIS LEWIS
APress Media, LLC
SSSQQQLLL SSSeeerrrvvveeerrr SSSeeecccuuurrriiitttyyy DDDiiissstttiiilllllleeeddd, ,, SSSeeecccooonnnd ddE dEEdditiiiottiinoo nn
CCCooopppyyyrrriiiggghhhtt t ©©© 222000000444 MMMooorrrrrriiisss LLLeeewwwiisis s
OOOrrriiigggiiinnnaaallllllyyy pppuuubbbllliiissshhheeeddd bbbyyy AA Appprrreeesssss sii nni n22 200000044 4
AAAllllll rrriiiggghhhtttsss rrreeessseeerrrvvveeeddd... NNNooo pppaaarrrtt too off ftt thhhiissis ww wooorrkkrk mm maaayyy bb bee err eereppprrooroddduuucceecdde doo rro ttrrr taarannnssmmsmiittittteetdde diinn i naa nnayyn yffoo frrommrm oorr o bbryy b aaynn ayyn y
mmmeeeaaannnsss,,, eeellleeeccctttrrrooonnniiiccc ooorrr mmmeeeccchhhaaannniiicccaaalll,,, iiinnncccllluuudddiiinnnggg p pphhhoootttooocccooopppyyyiiinnnggg,, ,r rreeecccooorrrdddiiinnnggg,, ,o oorr r bb byyy aa annnyyy iin ninffooforrmmrmaatatiitooinon n ss tstootorraraagggee e
ooorrr rrreeetttrrriiieeevvvaaalll sssyyysssttteeemmm,,, wwwiiittthhhooouuuttt ttthhheee ppprrriiiooorrr ww wrrriiitttttteeennn p ppeeerrnmmniiissssssiioioonnn oo off ftt thhheee cc cooopppyyyrriirggighhhtt too owwwnnneerre raa annnddd tt hhthee ep ppuuubbblillsiihsshheree.rr ..
ISBN 978-1-59059-219-9 ISBN 978-1-4302-0663-7 (eBook)
DOI 10.1007/978-1-4302-0663-7
TTTrrhaaadddeeemmmaaarrrkkkeeeddd nnnaaammmeeesss mmmaaayyy aa appppppeeeaaarr r ii ninn tt hthhiissi s bb booooookkk.. .RR Raaatthhtheerer rtt hhthaanan nuu ussees eaa attr rtaardaddeemmemaarrakkr kssy ysmmymbboobllo wwl iwitthhi t hee vveeevrreyyr y
oooccccccuuurrrrrreeennnccceee ooofff aaa tttrrraaadddeeemmmaaarrrkkkeeeddd nnnaaammmeee,, , wwweee uuussseee t tthhheee n nnaaammmeeses s o oonnnlylly y ini in n aa nan n eed edidittoiotroriiarail al flfa afsashshihiooinon n aa nandnd tdtoo t otth htehe e
bbbeeennneeefffiiittt ooofff ttthhheee tttrrraaadddeeemmmaaarrrkkk ooowwwnnneeerrr,, , wwwiiittthhh nn nooo ii nnintteetennnttiitooionn noo off fiinn inffrrfiirnningggeemmemeennettn too ffo ttfhh teeh ettrr taaradddeemmemaarrakkr..k .
TTTeeeccchhhnnniiicccaaalll RRReeevvviiieeewwweeerrrsss::: VVViiiccctttooorrriiiaaa HHHuuudddgggsssooonnn,,, SSSaaarrraaahhh LL Laaarrrdddeeerrr,,, CCCrrraaaiiiggg WW Weeellddldooonnn
EEEdddiiitttooorrriiiaaalll BBBoooaaarrrddd::: SSSttteeevvveee AAAnnngggllliiinnn,, , DDDaaannn AA Apppppplleleemmmaaannn,, ,GG Gaaarryyry CC Coorornnrneelellll,, l J,J aJaammmeeess sCC Cooxxo,, x TT, Toonnoyyn yDD Daavvaiivss,,i sJJ, ooJhhonnh n
FFFrrraaannnkkkllliiinnn,,, CCChhhrrriiisss MMMiiillllllsss,,, SSSttteeevvveee RRRyyycccrrrooofffttt,,, DDDooommmiininniicicc SS Shhhaaakkkeeessshhhaaafftft,,t ,JJ Juuulliliaaiannn SS Skkkiinninnnneerer,,r ,JJ Jiimimm S SuSumummsessree,r rK,, KKaraaerrnee nn
WWWaaatttttteeerrrsssooonnn,,, GGGaaavvviiinnn WWWrrraaayyy,,, JJJooohhhnnn ZZ Zuuukkkooowwwssskkkiii
LLLeeeaaaddd EEEdddiiitttooorrr::: TTTooonnnyyy DDDaaavvviiisss
AAAssssssiiissstttaaannnttt PPPuuubbbllliiissshhheeerrr::: GGGrrraaaccceee WWWooonnnggg
PPPrrrooojjjeeecccttt MMMaaannnaaagggeeerrr::: BBBeeettthhh CC Chhhrrriiissstttmmmaaasss
CCCooopppyyy EEEdddiiitttooorrrsss::: NNNiiicccooollleee LLleeeCCCllleeerrrccc aa annnddd NN Naaannncccyyy DD Deeeppppppeerre r
PPPrrroooddduuuccctttiiiooonnn MMMaaannnaaagggeeerrr::: KKKaaarrriil BBBrrrooooookkksss
PPPrrroooddduuuccctttiiiooonnn EEEdddiiitttooorrr::: KKKeeellllllyyy WWWiimnnqqquuuiiisssttt
PPPrrroooooofffrrreeeaaadddeeerrr::: TTThhhiiissstttllleee HHHiiillllll PPPuuubbbllliiissshhhiiinnnggg SSSeeerrrvvviiiccceeesss,,, LLLLLLCCC
CCCooommmpppooosssiiitttooorrr::: KKKiiinnneeetttiiiccc PPPuuubbbllliiissshhhiiinnnggg SSSeeerrrvvviiiccceeesss,,, LLLLLLCCC
IIInnndddeeexxxeeerrr::: JJJooohhhnnn CCCooolllllliiinnnsss
AAArrrtttiiisssttt::: KKKiiinnneeetttiiiccc PPPuuubbbllliiissshhhiiinnnggg SSSeeerrrvvviiiccceeesss,,, LLLLLLCCC
CCCooovvveeerrr DDDeeesssiiigggnnneeerrr::: KKKuuurrrttt KKKrrraaammmeeesss
MMMaaannnuuufffaaaccctttuuurrriiinnnggg MMMaaannnaaagggeeerrr::: TTTooommm DDDeeebbbooolllssskkkiii
DDDiiissstttrrriiibbbuuuttteeeddd tttooo ttthhheee bbbooooookkk tt trrraaadddeee ii innn tt thhheee UU Unnniittieeteddd SS Sttaatatteetess sbb byy ySS Spprrpiirnningggeerre--rVV-Veerrellraalgga gNN Neewwew YY ooYrrokkr,,k II, nnIccn..c,, .11, 771557 5FF iiFffttihhft h
AAAvvveeennnuuueee,,, NNNeeewww YYYooorrrkkk,,, NNNYYY,,, 111000000111000 aaannnddd oo ouuutttsssiiidddeee tt thhheee UU Unnniittiteeeddd SS Sttaatatteetess sbb byy ySS Spprrpiirnningggeerre--rVV-Veerrellraalgga gGG GmmmbbHHb H&& & CC ooC.. oKK. GGK,,G ,
TTTiiieeerrrgggaaarrrttteeennnssstttrrr... 111777,,, 666999111111222 HHHeeeiiidddeeelllbbbeeerrrggg,,, GGGeeerrrmmmaaannnyyy...
IIInnn ttthhheee UUUnnniiittteeeddd SSStttaaattteeesss::: ppphhhooonnneee 11I---88B00O00O--SS-PPSPRRRIINNINGGGEEERRR,, ,ee emmmaaaiilli loo orrrdddeeerrrsss@@@sssppprrriinningggeeerrr---nnnyyy..cc.cooommm,, .oo orr rvv viissiiistti t
hhhttttttppp::: ///// IwwWwwtIWww... sssppprrriiinnngggeeerrr---nnnyyy...cc cooommm... OOOuuutttsssiiidddeee ttthhheee UUUnnniiittteeeddd SSStttaaattteeesss::: fffaaaxxx +++444999 666222222111 3 33444555222222999,, , eeemmmaaaiiill l
ooorrrdddeeerrrsss@@@sssppprrriiinnngggeeerrr ... dddeee,,, ooorrr vvviiisssiiittt hhhttttttppp:/::///W//wwtwwIWww...sssppprrriiinnngggeeerrr...dddeee...
FFFooorrr iiinnnfffooorrrmmmaaatttiiiooonnn ooonnn tttrrraaannnssslllaaatttiiiooonnnsss,, , pppllleeeaaassseee cccooonnntttaaaccctt tAA Appprrreeessssss dd diirrireeecccttlltyyly aa att t22 2555666000 NN Niinnintthhth SS Sttrrteereeette,, t,SS Suuuiittieet e22 211199,,9 ,
BBBeeerrrkkkeeellleeeyyy,,, CCCAAA 999444777111000... PPPhhhooonnneee 555111000---555444999---555999333000,,, fffaaaxxx 555111000---555444999---555999333999,,, eeemmmaaaiilil l i ininnfffooo@@@aaappprrreeessssss..c.ccooommm,, , ooorr rv vvisiisistii tt
hhhttttttppp:::////W//wwtwwIWww...aaappprrreeessssss...cccooommm...
TTThhheee iiinnnfffooorrrmmmaaatttiiiooonnn iiinnn ttthhhiiisss bbbooooookkk ii isss dddiiissstttrrriiibbbuuuttteeeddd oo onnn aa annn "" "aaass sii ssi""s" bb baaassiisssi,,s ,ww wiittihhthoouuottu wwt waarrarrraarannnttyyty.. AA. Allttlhhthooouuuggghh hee vveeevrreyyr y
ppprrreeecccaaauuutttiiiooonnn hhhaaasss bbbeeeeeennn tttaaakkkeeennn iiinnn ttthhheee ppprrreeepppaaarrraaatttiiiooonnn oo off ftt thhhiissis ww wooorrkkrk,, ,nn neeeiittihhtheerre rtt hhthee eaa auuutthhthooorr((rss())s )nn noorro AAr Apprrpeerssesss sss hhsaahllall ll
hhhaaavvveee aaannnyyy llliiiaaabbbiiillliiitttyyy tttooo aaannnyyy pppeeerrrsssooonnn oo orrr ee ennntttiiittyyty ww wiittihhth rr reeessspppeeeccttc ttt ooto aa annnyy ylloo losssss soo rro rdd daammamaaggaeeg ecc aacuuassueesdde doorr o aarll alleellggeeegdde dttoo t o
bbbeee cccaaauuussseeeddd dddiiirrreeeccctttlllyyy ooorrr iiinnndddiiirrreeeccctttlllyyy bbbyyy tt thhheee ii innnfffooorrrmmmaaatttiiiooonnn cc cooonnnttaataiinnineeeddd ii nnin tt hhthiissi sww wooorrkkrk.. .
TTThhheee sssooouuurrrccceee cccooodddeee fffooorrr ttthhhiiisss bbbooooookkk i iisss aaavvvaaaiiilllaaabbbllleee ttotoo r rreeeaaadddeeerrrss sa aat tth hhtttttptpp:: :/// //wW/wwwtwIwW. a. .apaprprereessss.ss .c. cocoommm i niinn t htthhee e D DDooowwwnnnlollooaaaddds ss
ssseeeccctttiiiooonnn...
This book is dedicated to Dr. Donald Fairbairn, for introducing me to
programming over 25 years ago; to Dr. Dennis Hood, for introducing
me to Structured Query Language; and to Dr. William Hooper, for being
a good friend, teacher, and mentor while I finished my bachelor's
degree. I would not be where I am were it not for their tutelage. I wish
everyone were blessed to have such people in their lives.
This book is also dedicated to my Wife, Lisa, for so many, many things
it would take another book to list them all.
Contents at a Glance
About the Author ................................................
xi
Acknowledgments ................................................
xiii
Additional Information .......................................
xv
Introduction ....................................................
xvii
Chapter 1 A Security Roadmap .................................. 1
Chapter 2 Authenticating Logins .............................. 17
Chapter 3 Database Security in SQL Server 6.5 .............. 73
Chapter 4 Database Security in SQL Server 7.0
and 2000 ........................................... 107
Chapter 5 Securing Data on the Network ..................... 161
Chapter 6 Designing Security for Applications ............. 211
Chapter 7 Securing Data Transformation Services .......... 261
Chapter 8 Replication Security ..............................
285
Chapter 9 Managing Security for SQL Server CE ............. 307
Appendix A References ......................................... 333
Index .............................................................
335
v
Contents
About the Author ................................................
xi
Acknowledgments ................................................
xiii
Additional Information .......................................
xv
Introduction ....................................................
xvii
Chapter 1 A Security Roadmap.............................. 1
Authentication and Authorization ................................. 2
Options for Authentication ........................................ 3
Options for Authorization ......................................... 9
Summary ............................................................. 15
Chapter 2 Authenticating Logins ......................... 17
Password Strategies ............................................... 17
Authentication in SQL Server 6.5 ................................ 26
Authentication in SQL Server 7.0 and 2000 ..................... 43
Managing Login Accounts .......................................... 56
Server Roles ....................................................... 64
Summary ............................................................. 70
Chapter 3 Database Security in SQL Server 6.5 ...... 73
Managing Database Access ......................................... 73
Managing Database Permissions ................................... 83
Summary ............................................................ 106
Chapter 4 Database Security in SQL Server
7.0 and 2000 .................................... 107
Managing Database Access ........................................ 107
Managing Database Permissions .................................. 117
vii
Contents
The Art of Assigning Permissions ., ............................. 130
Database Roles ................................................... 137
Object Ownership ................................................. 150
Summary ............................................................ 159
Chapter 5 Securing Data on the Network ...............
161
Sniffing for Passwords .......................................... 162
Panning for Nuggets of Data .................................... 164
In Medias Res ..................................................... 166
Countermeasures .................................................. 167
limiting Access to the Server .................................. 188
Summary ............................................................ 207
Chapter 6 Designing Security for Applications .....
211
Authenticating Access to the Server ........................... 211
The SQl Injection Attack ........................................ 220
Designing Secure Applications .................................. 236
Summary ............................................................ 255
Chapter 7 Securing Data Transformation
Services ........................................
261
Creating DTS Packages ........................................... 262
Saving DTS Packages .............................................. 265
Password Protecting DTS Packages ............................... 268
Versioning DTS Packages ......................................... 270
Modifying Packages ............................................... 271
Executing a DTS Package ......................................... 273
SQl Server Agent Account Permissions .......................... 281
Moving DTS Packages Between Servers ........................... 282
Summary ............................................................ 284
Chapter 8 Replication Security ..........................
285
Replication Architecture ........................................ 285
Securing Snapshot Replication .................................. 289
Securing Transactional and Merge Replication ................. 293
Securing the Data Stream ........................................ 299
Additional Recommendations ...................................... 300
Summary ............................................................ 305
viii
Contents
Chapter 9 Managing Sec urit y for SQL Server CE .....
307
Creating Secure Databases ....................................... 309
Securing Distributed Data ....................................... 314
Setting Up Remote Data Access .................................. 322
SQL Server CE Relay Agent ....................................... 328
Summary ............................................................ 331
Appendix A References .......................................
333
Books .............................................................. 333
Web Sites ......................................................... 333
Index .............................................................
335
ix
About the Author
Monis Lewis has been smitten with Structured Query Language since the first
*
time his professor wrote SELECT FROM AUTHORS on the chalkboard 14 years ago.
He has worked with no other database server since he first installed SQL Server
4.21a on his 16 MHz Intel 386 computer with all of32 megabytes of RAM run
ningWindows NT 3.51, more than 8 years ago.
With the mantra "It is OK to worry if they really are out to get you," he has
focused on all aspects of securing Wmdows and SQL Server since he connected
his first server to the Internet, 6 years ago. Now, he runs a training and consult
ing company, Holistech Incorporated (http://www.holistech.com) , that focuses on
helping clients create better and more secure database applications, and on
teaching them how to avoid the mistakes that can lead to problems in the future.
He can be contacted at Morris@Holistech. com if you need help keeping the bad
guys out of your applications.
xi
Acknowledgments
FIRST, I NEED TO TELL my family and friends I am sincerely grateful for all the
patience they had with me for the last 6 months. I saw a lot of my office and too
little of them, but they were always supportive and encouraging. I am sure they
all will be glad to see the grumpy, old bear who growled at anyone entering his
den go into hibernation for awhile.
Second, I want to thank Richard Waymire for encouraging me to do this
book when I first mentioned it to him and for sharing freely his insight into how
SQL Server works under the hood. In many cases, I could set up tests to deter
mine what SQL Server was doing, but Richard often helped me understand why
it was doing it. This book would not be as complete without his help.
Next, I want to thank the folks at VMware (http: /www.vnrware.com) for creating
their GSX Server product. At one point I had eleven virtual machines with
a combination of two different server operating systems, all three versions of
SQL Server, and clients runningWmdows NT and 2000. Using physical hardware
would have taken significantly more resources and time, and it would have been
difficult to verify how all the different versions interacted with each other. I prob
ably could have written this book without GSX Server, but it would have been
much harder.
Finally, I want to thank the giants who have worked and written on SQL
Server security before me, for letting me hitch a ride on their shoulders. Many
books have been indispensable in teaching me howWmdows networks and SQL
Serve work, and they should be your starting point for delving deeper into the
intricacies of securing data in a Wmdows NT or 2000 network. Appendix A col
lects the references made throughout this book together, for easy reference.
xiii