SQL Server Security Distilled

by MORRIS LEWIS

• ISBN13: 9781590591925
• ISBN10: 1590591925
• Edition: 2nd
• Format: Paperback
• Copyright: 2003-09-01
• Publisher: Springer-Verlag New York Inc
• More Book Details
• Purchase Benefits

Summary

Securing SQL Server is one of the most important responsibilities of the SQL Server professional. Ensuring your data is safe requires a combination of good systems and database administration, and intelligent application design - weaving a security plan that matches the capabilities and vulnerabilities of each contributing part. But at its root, security is concerned with controlling access - authenticating who can access the data on the server, authorizing what users can do with that data, and securing data as it is transported. These core topics are the focus of SQL Server Security Distilled.

Table of Contents

Introduction	1	(1)
What This Book Covers	1	(2)
What You Need to Use This Book	3	(1)
Source Code	3	(1)
Conventions	3	(1)
Customer Support	4	(3)
Errata	4	(1)
E-mail Support	4	(3)
1: A Security Roadmap	7	(14)
Authentication and Authorization	8	(1)
Options for Authentication	9	(5)
Windows Authentication	10	(2)
Managing Server Access Using Windows NT Groups	12	(1)
SQL Server Authentication	13	(1)
Kerberos and Active Directory Authentication	13	(1)
Options for Authorization	14	(5)
Server Roles in 7.0 and 2000	14	(2)
Database User Accounts	16	(1)
Database Roles	17	(2)
Database Owner Rights in SQL Server 6.5	19	(1)
Summary	19	(2)
2: Authenticating Logins	21	(48)
Password Strategies	22	(7)
Creating Strong Passwords	23	(2)
Passwords in Enterprise Manager	25	(1)
The Effects of Windows on Authentication	26	(2)
Password Strength for Windows Logins	28	(1)
Authentication in SQL Server 6.5	29	(14)
Standard Security	29	(1)
Integrated Security	30	(2)
Tracing Login Network Traffic	32	(1)
The Test Network	33	(1)
Logins Using TCP/IP	34	(2)
Logins Using Named Pipes	36	(5)
User Level versus Full Control Logins	39	(1)
Disabling the Guest Account	40	(1)
Eliminating Trusted Connections with Standard Security	40	(1)
SQL Server 6.5 Named Pipes Login Summary	41	(1)
Logins Using the Multiprotocol Library	41	(2)
Authentication in SQL Server 7.0 & 2000	43	(11)
Tracing Login Network Traffic	44	(5)
The Super Sockets Network Library	46	(3)
The TCP/IP Network Library	49	(2)
Windows Authenticated Logins Via TCP/IP	50	(1)
Logins Using Named Pipes	51	(2)
The Multiprotocol Library in SQL Server 2000	53	(1)
Managing Login Accounts	54	(7)
Creating Login Accounts	58	(3)
Server Roles	61	(5)
Primary Server Roles	62	(2)
sysadmin	62	(1)
serveradmin	63	(1)
securityadmin	63	(1)
dbcreator	63	(1)
Secondary Server Roles	64	(1)
setupadmin	64	(1)
processadmin	64	(1)
diskadmin	64	(1)
bulkadmin	64	(1)
Special User Identities	64	(2)
Summary	66	(3)
3: Database Security in SQL Server 6.5	69	(30)
Managing Database Access	70	(8)
Aliasing Example	71	(1)
Advantages of Aliasing	72	(1)
Checking Database Access Privileges	72	(1)
Database User Names	73	(1)
Managing Users With T-SQL	73	(1)
Database Groups	74	(1)
Object Ownership	75	(3)
Managing Database Permissions	78	(19)
The Database Owner	79	(1)
Database Object Owner	80	(1)
SETUSER	81	(1)
Viewing System Tables	81	(1)
Executing Stored Procedures	82	(1)
The Art of Assigning Permissions	82	(9)
What Happens in sysprotects?	82	(2)
Granting and Revoking Permissions to a User	84	(1)
Working With Groups	85	(4)
Permission Hierarchies	89	(2)
Ownership Chains	91	(9)
Views	91	(4)
Stored Procedures	95	(1)
Triggers	96	(1)
Recommendations on Ownership	96	(1)
Summary	97	(2)
4: Database Security in SQL Server 7.0 and 2000	99	(46)
Managing Database Access	100	(7)
Inside the sysusets Table	101	(4)
The UID Column	103	(1)
The status Column	103	(1)
The name Column	104	(1)
The sid Column	104	(1)
The roles Column	104	(1)
The Remaining Columns	105	(1)
The Database Access Algorithm	105	(2)
Managing Database Permissions	107	(12)
Determining the List of UIDs	108	(1)
The Permissions Architecture	109	(10)
The WITH GRANT OPTION	111	(1)
Revoking the WITH GRANT OPTION	112	(2)
The DENY Option	114	(5)
The Art of Assigning Permissions	119	(6)
Granting the SELECT Permission to a User	119	(1)
Revoking the SELECT Permission to a User	120	(1)
Denying the SELECT Permission to a User	120	(1)
Granting the SELECT Permission to the public Role	121	(1)
Revoking the SELECT Permission From a Denied User	121	(1)
Testing the WITH GRANT OPTION Clause	122	(1)
Assigning Group Permissions to Deny Individual Users	123	(2)
For Windows Groups	123	(1)
For Database Roles	124	(1)
Database Roles	125	(11)
The db owner Role	126	(1)
Determining the Permissions for the Built-in Roles	126	(1)
The Public Role	127	(1)
The Junior DBO Roles	128	(3)
db_accessadmin	129	(1)
db_securityadmin	130	(1)
db_ddladmin	130	(1)
db_backupoperator	130	(1)
The Reader and Writer Roles	131	(2)
db_datareader and db_datawriter	131	(1)
db_denydatareader and db_denydatawriter	131	(2)
User-defined Database Roles	133	(1)
Application Roles	134	(1)
Aliases	134	(2)
Object Ownership	136	(6)
Ownership Chains	137	(9)
On Views	138	(1)
A More Complex Example	139	(2)
Ownership Chains and Stored Procedures	141	(1)
Triggers	141	(1)
Resetting Object Ownership	142	(1)
Summary	142	(3)
5: Designing Security for Applications	145	(50)
Choosing an Authentication Scheme	146	(15)
SQL Server Authenticated Logins	146	(1)
Obscuring Account Name and Password	147	(1)
Windows Authenticated Logins	147	(5)
Application Roles	148	(3)
Stored Procedures	151	(1)
Kerberos Security	152	(9)
Client-Server Authentication	153	(1)
Inter-Domain Authentication	154	(1)
Securing Your Server from Hackers	155	(1)
Authenticating Server to Client	156	(1)
Encryption	157	(1)
Delegation	157	(4)
Securing Internet Applications	161	(11)
Securing the Perimeter	162	(2)
Determining the Level of Security	164	(8)
Public or Private IP Addressing?	164	(1)
Virtual Private Networks?	164	(1)
Authenticating Without a VPN?	165	(1)
Securing a Publicly Accessible Server?	166	(6)
Securing SQL Server Data	172	(3)
Virtual Private Network	172	(1)
Private Subnetwork	172	(1)
Multiprotocol Network Library	173	(1)
IPSec	174	(1)
The SQL Injection Attack	175	(14)
Opening a Connection as sa	177	(2)
Opening Connections Using a Single Account	179	(1)
Alternatives for Solving Login Weaknesses	179	(4)
Using Windows Authenticated Accounts and Disable SQL Server Authentication	179	(1)
Using a SQL Server Login for Each User	180	(1)
Obfuscation Note	180	(1)
Using Application Roles	181	(2)
Disabling Ad Hoc Queries	183	(1)
Stealing Data Remotely Using the SQL Injection Attack	184	(2)
OPENROWSET	184	(2)
How to Defend Your Data Against This Attack	186	(3)
Recommendations	189	(6)
6: Securing Data Transformation Services (DTS)	195	(22)
Creating DTS Packages	196	(2)
Packages Created in Enterprise Manager	197	(1)
Packages Created from Visual Basic	198	(1)
Saving DTS Packages	198	(3)
Saving within SQL Server	198	(1)
Saving to a Visual Basic File	199	(1)
Saving to a Structured Storage File	200	(1)
Saving to Meta Data Services	200	(1)
Password Protecting DTS Packages	201	(2)
Versioning DTS Packages	203	(1)
Modifying Packages	204	(1)
Executing a DTS Package	205	(7)
Execution from Enterprise Manager	205	(1)
Execution using DTSRun	206	(3)
Execution from a Visual Basic Component	209	(2)
Scheduling Execution of DTS Packages	211	(1)
SQL Server Agent Account Permissions	212	(1)
Moving DTS Packages Between Servers	213	(2)
Ownership Issues	214	(1)
Summary	215	(2)
7: Replication Security	217	(20)
Replication Architecture	218	(3)
Replication Models	218	(3)
Snapshot Replication	219	(1)
Transactional Replication	219	(1)
Merge Replication	220	(1)
Securing Snapshot Replication	221	(3)
Administrative Shares	221	(2)
Push and Pull Subscriptions	223	(1)
Securing Transactional and Merge Replication	224	(4)
The Distribution Agent	224	(2)
The Merge Agent	226	(2)
Securing the Data Stream	228	(2)
Additional Recommendations	230	(7)
System Stored Procedures	230	(3)
Separate the Servers	233	(1)
Use Only Windows Logins	233	(1)
Using a Firewall	233	(1)
The SQL Server Agent Account	234	(1)
The Everyone Local Group	234	(3)
8: Managing Security for SQL Server CE	237	(22)
Creating Secure Databases	239	(4)
With SQL	239	(1)
With ADOCE and ADOXCE	240	(1)
Creating a Database Through Replication	241	(1)
Password Protection and Encryption	241	(2)
Securing Distributed Data	243	(6)
RDA	243	(1)
Merge Replication	243	(1)
Data Synchronization	244	(2)
Authenticating Users and Securing the Data Stream	245	(1)
Connection Strings	246	(2)
Configuration	248	(1)
Setting Up Remote Data Access	249	(5)
SQL Server CE and IIS	251	(2)
Basic vs Windows Authentication	252	(1)
eMbedded Visual Tools	253	(1)
Troubleshooting Login Errors	254	(1)
SQL Server CE Relay Agent	254	(2)
Relay Agent Limitations	256	(1)
Summary	256	(3)
Appendix A: References	259	(4)
Index:	263